Re: DETERMINISTIC in user functions

Jonathan Lewis wrote:
>
> 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

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

1. if there is no index, then you run 'function(' for each row in the table
2. 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 Received on Fri Dec 20 2002 - 15:31:11 CST