RE: Semi-deterministic?
- From: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
- To: vxsmimmcp@xxxxxxxxxx
- Date: Thu, 6 Feb 2014 01:57:09 +0400
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
>
>
>
>
>
>
>
Other related posts:
