DETERMINISTIC in user functions

[David]

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

[Jonathan Lewis]

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 ...

[Connor McDonald]

Even in the create index scenario I've not managed to get the number of
executions lower than the number of rows in the table. The marketing
engine of Oracle tell me that:

when you issue

select ...
where function(col) = value

then

a) if there is no index, then you run 'function(' for each row in the
table
b) if there is an equivalent function based index, then the function
does not need to be executed...Voila - determinism in action

Ugh! What a cop out!

hth
connor