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

"Connor McDonald" <connor_mcdonald_at_yahoo.com> skrev i en meddelelse news:3CBB21E8.7BA4_at_yahoo.com...
> > ... original posting snipped ...
>
> 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
>

Thanks very much, Connor, for a lucid answer. All three workarounds work like a charm on our test-system, Oracle 8.1.7.2 on AIX.

Now I am left wondering: Are these 'tricks' needed especially for Oracle, or do the query optimizers in other database engines have the same performance issues when presented with deterministic functions?

Regards, Hans Henrik Krohn Received on Tue Apr 16 2002 - 10:08:24 CDT