Re: DETERMINISTIC Function in Pkg

Michel Cadot schrieb:

>
> And if you want a headache:
>

Yes, this one can cause some ;-)
However, i assume, your question is more related to the first record oddity - the fact, that oracle reuse result returned by deterministic function since 10.2.0.1 is not well documented (as to my knowledge, well documentation states this for fbi and mv, but this is completely different context) but at least often mentioned (last time i tested it on 10.1.0.3 - it didn't work, maybe in latest patchset this feature was added - i don't know). I don't think however, the optimizer is responsible for this behaviour - with rule based optimizer results are the same as with cbo.
And the different first record seems to be the sqlplus "addon"

SQL> create table function_reuse as select dont_trustme(1) dont_trustme   from dba_objects;

Table created.

SQL> select count(*),dont_trustme from function_reuse group by dont_trustme;

   COUNT(*) DONT_TRUSTME
---------- ------------

       9881 .404984106

And you can as well try your example in some other client than sqlplus - i suppose you get the expected results ( i.e. - all values the same).

Best regards

Maxim Received on Thu Oct 04 2007 - 13:13:07 CDT