RE: Semi-deterministic?
- From: "McPeak, Matt" <vxsmimmcp@xxxxxxxxxx>
- To: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- Date: Wed, 5 Feb 2014 21:50:37 +0000
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<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<mailto: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
Other related posts:
