RE: Semi-deterministic?

From: McPeak, Matt <>
Date: Wed, 5 Feb 2014 22:28:12 +0000
Message-ID: <>

Sayan nailed it. Thanks!

Inline view and "WITH" clause (no MATERIALIZE) do not work -- they call the function once per row.

WITH /*+ MATERIALIZE */ works, but increases buffer gets to 76 per execution.

Sayan's method works and requires only 5 buffer gets per execution.

Thank you, everyone!


From: Sayan Malakshinov [] Sent: Wednesday, February 05, 2014 4:57 PM To: McPeak, Matt
Cc: ORACLE-L; Riyaj Shamsudeen
Subject: RE: Semi-deterministic?

You can simply use scalar subquery caching: Select ....
,add_months (... , (select your_function (..) from dual Best regards,
Sayan Malakshinov
Senior Oracle performance tuning engineer PSBANK
06.02.2014 1:51 пользователь "McPeak, Matt" <> написал: Not sure that works, but I’ll try it.  
By the way I should mention that my best approach so far is to access the functions in a WITH clause with the /*+ MATERIALIZE */ hint.  I’m looking to improve on that.  (The drawback there is that the materialized temp table gets created pretty big for some reason – the query takes 76 buffer gets instead of 5 buffer gets if I just hard-code the values).  


From: Riyaj Shamsudeen [] Sent: Wednesday, February 05, 2014 4:45 PM To: McPeak, Matt
Cc: Oracle Mailinglist
Subject: Re: Semi-deterministic?
Hello Matt
  Join that as an inline view. For example:  

, et.eta_date,

FROM big_table 
   join (select ADD_MONTHS(po_date, xxcust_profiles_pkg.get_profile_value(‘LEADTIME_MONTHS’))  eta_date from dual) et  


Riyaj Shamsudeen
Principal DBA,
Ora!nternals - Specialists in Performance, RAC and EBS Blog: Oracle ACE Director and OakTable member Co-author of the books: Expert Oracle Practices, Pro Oracle SQL, Expert RAC Practices 12c. Expert PL/SQL practices  
On Wed, Feb 5, 2014 at 1:32 PM, McPeak, Matt <> wrote: Hi…
I have a database view that needs has a select-list item something like this:  

, ADD_MONTHS(po_date, xxcust_profiles_pkg.get_profile_value(‘LEADTIME_MONTHS’)) eta_date,

FROM big_table;
Now, here is the thing: I want to execute get_profile_value once per call, but not once per row.  
I don’t think I can make the function DETERMINISTIC, because it’s not (the profile value is stored in the database and may be changed).  
Also, I am shy about giving xxcust_profiles_pkg the SERIALLY_REUSABLE pragma, because I cannot guarantee that it won’t be called from places where it will yield an ORA-6534 (Cannot access serially reusable package…).  
I really want it to behave like SYSDATE – where all rows in the query use the same value but not all queries in the same session do.  
Does anyone have any ideas / thoughts?


i0zX+n{+i^ Received on Wed Feb 05 2014 - 23:28:12 CET

Original text of this message