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 -> Re: Follow Up: DETERMINISTIC in user functions

Re: Follow Up: DETERMINISTIC in user functions

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 11 Jan 2005 05:44:49 -0800
Message-ID: <115451089.00011d1d.011@drn.newsguy.com> 





In article <b4cefdce.0501101228.b0e90a8_at_posting.google.com>, David says...


>

>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

>




No, it hasn't changed, however -- there is and has been "scalar subquery
caching" which I frequently use to cut down the calls.  Consider:


ops$tkyte_at_ORA10G> /*
ops$tkyte_at_ORA10G> drop table t;
ops$tkyte_at_ORA10G> create table t as select * from all_objects;
ops$tkyte_at_ORA10G> exec dbms_stats.gather_table_stats( user, 'T' );
ops$tkyte_at_ORA10G> */
ops$tkyte_at_ORA10G>

 




  1* select count(*), count(distinct object_name), count(distinct owner) from t
ops$tkyte_at_ORA10G> /
 


  COUNT(*) COUNT(DISTINCTOBJECT_NAME) COUNT(DISTINCTOWNER)


---------- -------------------------- --------------------
     48039                      28399                   27
 






so, there are 48,039 rows in there -- object_name repeats, only has 28,399
unique ones and owner really repeats.  we create a function:




ops$tkyte_at_ORA10G> create or replace function f( x in varchar2 ) return number
  2  DETERMINISTIC



  3  as


  4  begin

  5          dbms_application_info.set_client_info( userenv('client_info')+1 );
  6          return 0;




  7  end;


  8  /



Function created.




it'll just count "how often I've been called".  Now we are ready to test.  I ran
a set of 4 queries (well, 8 really -- once for object_name, again for owner)
using



    
  
  1.   reset client_info to zero
  
  2.   run query
  
  3.   remember client_info value in a table for later







Note the queries -- I run basically the same query twice, once with and once
without an inline view:



ops$tkyte_at_ORA10G> set autotrace traceonly
ops$tkyte_at_ORA10G> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.



ops$tkyte_at_ORA10G> select object_name, f(object_name) from t;
48039 rows selected.



Execution Plan




0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=154 Card=48039 Bytes=1152936)
1    0   TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=154 Card=48039 Bytes=1152936)



ops$tkyte_at_ORA10G> exec insert into msg values ( 'First => ' ||
userenv('client_info') );


PL/SQL procedure successfully completed.



ops$tkyte_at_ORA10G> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.



ops$tkyte_at_ORA10G> select object_name, f(object_name) from (select object_name
from t order by object_name);


48039 rows selected.



Execution Plan




   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=502 Card=48039 Bytes=816663)
   1    0   VIEW (Cost=502 Card=48039 Bytes=816663)

   2    1     SORT (ORDER BY) (Cost=502 Card=48039 Bytes=1152936)
3    2       TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=154 Card=48039


Bytes=1152936)



ops$tkyte_at_ORA10G> exec insert into msg values ( 'Second => ' ||
userenv('client_info') );


PL/SQL procedure successfully completed.



ops$tkyte_at_ORA10G> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.



ops$tkyte_at_ORA10G> select object_name, (select f(object_name) from dual) from t;
48039 rows selected.



Execution Plan




0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=154 Card=48039 Bytes=1152936)


   1    0   FAST DUAL (Cost=2 Card=1)


2    0   TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=154 Card=48039 Bytes=1152936)



ops$tkyte_at_ORA10G> exec insert into msg values ( 'Third => ' ||
userenv('client_info') );


PL/SQL procedure successfully completed.



ops$tkyte_at_ORA10G> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.



ops$tkyte_at_ORA10G> select object_name, (select f(object_name) from dual) from
(select object_name from t order by object_name);
48039 rows selected.



Execution Plan




   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=502 Card=48039 Bytes=816663)
   1    0   FAST DUAL (Cost=2 Card=1)


   2    0   VIEW (Cost=502 Card=48039 Bytes=816663)

   3    2     SORT (ORDER BY) (Cost=502 Card=48039 Bytes=1152936)
4    3       TABLE ACCESS (FULL) OF 'T' (TABLE) (Cost=154 Card=48039


Bytes=1152936)




Now, the counts:



ops$tkyte_at_ORA10G> select * from msg;



TXT





First => 48039


Second => 48039


Third => 33206


Fourth => 28399


First  with owner => 48039


Second  with owner => 48039


Third  with owner => 66


Fourth  with owner => 27



8 rows selected.




the "select f(x) from anything" has the function getting called once per row --
no caching happening.



However, the "select (select f(x) from dual) from anything" -- it seriously
reduced the calls



In looking at the first run with object_name -- first we reduced the count to
33,206.  That is because the object names were arriving "randomly" and the
database is only caching the last "N" scalar subquery results (i've never
bothered to figure out what N is).  We had to rerun the scalar subquery a couple
of times with the same inputs as it was out of the cache.  However, ordering the
interior inline view by object_name resulted in perfect "cache hit" in this case
- 28,399 invocations, identical to the number of distinct object_names.



We see a simlar effect with owner -- down to 66 (random arrival) and then we
sorted down to 27 (which implies the cache is smaller than 27, greater than 0 :)



You will see similar results running this example in 9i and 8i.





>

>

>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




-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation


Received on Tue Jan 11 2005 - 07:44:49 CST












Original text of this message













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