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 http://orasql.org 06.02.2014 1:51 пользователь "McPeak, Matt" <vxsmimmcp@xxxxxxxxxx> написал: > 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). > > > > Thanks, > > Matt > > > > > > > > > > *From:* Riyaj Shamsudeen [mailto:riyaj.shamsudeen@xxxxxxxxx] > *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: > > > > SELECT > > ... > > , 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 > > > > ; > > > Cheers > > Riyaj Shamsudeen > Principal DBA, > Ora!nternals - http://www.orainternals.com - Specialists in Performance, > RAC and EBS > Blog: http://orainternals.wordpress.com/ > Oracle ACE Director and OakTable member <http://www.oaktable.com/> > > Co-author of the books: Expert Oracle > Practices<http://tinyurl.com/book-expert-oracle-practices/> > , Pro Oracle SQL, <http://tinyurl.com/ahpvms8>Expert RAC Practices > 12c.<http://tinyurl.com/expert-rac-12c> > Expert PL/SQL practices <http://tinyurl.com/book-expert-plsql-practices> > > > > > > On Wed, Feb 5, 2014 at 1:32 PM, McPeak, Matt <vxsmimmcp@xxxxxxxxxx> wrote: > > Hi... > > > > I have a database view that needs has a select-list item something like > this: > > > > SELECT > > ... > > , 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? > > > > Thanks, > > Matt > > > > > > >