Re: DETERMINISTIC in user functions

The implication of the manual is that it is possible for two consecutive calls to the function with identical parameters to be satisfied by a single call followed by a cached look-up.

I've never yet seen anything to suggest
that this ever happens - and Connor
McDonald sent me a note of something
he'd spotted on Metalink recently that
seemed to corroborate this view.

The idea, of course, is that if you are using a function-based index, then
(for example) you may not need to
call the function for every row you
process when you do 'create index'.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





David wrote in message ...


>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