Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Follow Up: DETERMINISTIC in user functions

Follow Up: DETERMINISTIC in user functions

From: David <dfairman16_at_hotmail.com>
Date: 10 Jan 2005 12:28:04 -0800
Message-ID: <b4cefdce.0501101228.b0e90a8@posting.google.com>


Hi, two years and one month ago I posted a question in this NG that was very satisfactorily answered. I am contemplating upgrading the firms version of Oracle to 10g and find myself asking myself the same question again, not that it is relevant to the upgrade but more out of interest. After Googl'ing through the Oracle 10g docs on-line, I've decided to ask the same question again and importantly seek expert opinion whether the situation has changed (especially the scenario outlined by Connor). Highlights from the original message thread pasted in-line for those of you whose newsservers don't go back this far.
Thank you
David

From: David (dfairman16_at_hotmail.com)
Subject: DETERMINISTIC in user functions Date: 2002-12-20 07:53:33 PST  

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

From: Jonathan Lewis (jonathan_at_jlcomp.demon.co.uk) Subject: Re: DETERMINISTIC in user functions Date: 2002-12-20 08:10:18 PST  

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









From: Connor McDonald (connor_mcdonald_at_yahoo.com)
Subject: Re: DETERMINISTIC in user functions 
Date: 2002-12-20 13:20:20 PST 

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
Received on Mon Jan 10 2005 - 14:28:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US