Re: Deterministic functions not recognized by optimizer - will they ever?

Hans Henrik Krohn wrote:
>
> I am surely not the last to find out that deterministic PL/SQL
> functions are not recognized as such by the optimizer, contrary to
> what I was led to expect on two grounds:
> a) using common sense, and
> b) reading chapter 9 of Application Developer's Gude - Fundamentals.
>
> At least it doesn't give any performace gain on 8.1.5 and 8.1.7.2 on
> AIX, and according to bug # 2070151 on MetaLink it doesn't work in
> 9.0.1 either. Testcase is supplied below.
>
> I am wondering whether any of you experts out there have any view upon
> whether it ever has worked or will work in the forseeable future?
>
> Regards to all, Hans Henrik Krohn
>
> TESTCASE, to be cut/pasted into sql*plus:
>
> -- 1) Create a package containing a very deterministic function:
> (could just as well be a stand-alone function)
> create or replace package TESTD is
> function fortyseven (pNum IN number) return number
> PARALLEL_ENABLE DETERMINISTIC;
> PRAGMA RESTRICT_REFERENCES (fortyseven, WNDS, RNDS, WNPS, RNPS,
> TRUST);
> END TESTD;
> /
> create or replace package body TESTD is
> function fortyseven (pNum IN number) return number
> is begin
> return 47;
> end;
> END TESTD;
> /
>
> -- 2) Create a test-table containing 20.000 values:
> create table test_determ as
> select a1.object_id from all_objects a1, all_objects a2
> where rownum <= 20000;
> commit;
>
> -- 3) Run two selects, and note the difference in execution time:
> set timing on
> select count (1) from test_determ where object_id != to_number ('47');
> select count (1) from test_determ where object_id != TESTD.fortyseven
> (47);
>
> --4) That's all, Folks!

I can't recall seeing any example where 'deterministic' gave a performance gain - it seems to be more for function based indexes. But you can get around the performance issue with some 'tricks' along the lines of:

select count (1) from test_determ where object_id !=   ( select distinct TESTD.fortyseven from dual )

or

select count (1) from test_determ where object_id !=   ( select TESTD.fortyseven from dual where rownum = 1)

or

select count (1) from test_determ where object_id !=   ( select /*+ NO_MERGE */ TESTD.fortyseven from dual)

all of which (depending on version) are means to stop Oracle from merging the inner sql with the main one

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."