DETERMINISTIC in user functions

All, just reading Tom Kytes "Expert one-to-one Oracle" and I note on page 291/2 he says

CREATE OR REPLACE FUNCTION my_soundex(p_string IN VARCHAR2) RETURN VARCHAR2
DETERMINISTIC
AS
...
...
...

END;
/

Quote: "Notice in this function, I am using a new keyword DETERMINISTIC. This declares that the above function - when given the sample inputs - will always return the exact same output. It is needed in order to create an index on a user written function. You must tell Oracle that the function is DETERMINISTIC ....."

I've written all my Oracle stored functions in the past without the DETERMINISTIC keyword, and of-course everything has worked okay. And I'm far from being alone. There seems to be a minimal amount of Oracle documentation & other newsgroup discussion available on this, but Oracle do say quote: "In some cases the optimizer can use a previously calculated value rather than executing a user-written function. This is only safe for functions that behave in a restricted manner. The ...".

So, if I am pondering correctly, the behind-the-scenes implementation of the DETERMINISTIC keyword allows Oracle to cache function results. Is that it or is there something a little deeper I'm missing. And what does "In some cases the optimizer...." mean, what cases, or more specifically what cases doesn't it not cache the output for the same input.

Comments most welcome.
David Received on Fri Dec 20 2002 - 09:53:32 CST