monline

I was quite thrilled, when I first read about the "DETERMINISTIC" compiler hint introduced in 8i. Unfortunately it works not as one would hope/expect.

The documentation says: "The hint DETERMINISTIC helps the optimizer avoid redundant function calls. If a stored function was called previously with the same arguments, the optimizer can elect to use the previous result. The function result should not depend on the state of session variables or schema objects. Otherwise, results might vary across calls. Only DETERMINISTIC functions can be called from a function-based index or a materialized view that has query-rewrite enabled."

The emphasis is on the "can elect" phrase. Actually this deterministic keyword is currently only used for function-based indexes and materialized views (the "examples" in the above description). It does not work in general ... so if you simply write a deterministic function, tag it with the "DETERMINISTIC" keyword and call it in a loop with the same arguments, then it will still be executed as many times as it was called ... the result of previous executions is not cached by the PL/SQL engine. The same is true if you use the function in an SQL query.

The "DETERMINISTIC" hint helps only in the two special cases that the description mentions.

You can find some info on the subject at AskTom.

PS: I've just read at Tom's blog that in 10gR2 the "DETERMINISTIC" hint got what we originally wanted. It truely caches all sorts of function calls, not just in case of function-based indexes and materialized views. Unfortunately non of my company's clients uses 10gR2 yet ... And one another thing: why did it take Oracle two major versions (5 or more years?) to make this step? The "DETERMINISTIC" hint was introduced in one of the 8.1.x versions ...