Re: Follow Up: DETERMINISTIC in user functions

It looks like the mechanism is the one
used for filter subqueries. The number
76 isn't special, it just happens to be
unlucky.

Oracle is building a hash table of
(driving value, subquery result)
When you create the table with mod(rownum,76) the value 75 appears in the column, and 75 happens to collide with 48. On a collision, Oracle doesn't create an overflow, it simply doesn't store the newer result.

Try:

    update t set y = 76 where y = 75;
or

    update t set y = 76 where y = 75;

Either change stops the collision.

Your 75/76 test doesn't reproduce on 8i or 9i, by the way because they use a hash table of 256 elements, and 10g uses a hash table of 1,024 (according to my best guess).

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Dec 23rd 2004

"Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:115458097.00017811.088_at_drn.newsguy.com...
>
> run this -- flipping 76 to 75. 75 seems "magic", but that doesn't explain
> the
> 27/66 above.
>
> But basically -- it is a cache that works best if the bind variables to
> the
> scalar subquery comes to it sorted.
>
> set echo on
> set linesize 121
>
> drop table t;
> create table t as
> select rownum x, mod(rownum,76) y
> from all_objects
> where rownum <= 5000;
>
>
> create or replace function f( x in number ) return number
> as
> begin
> dbms_application_info.set_client_info( userenv('client_info')+1 );
> return 0;
> end;
> /
>
> set autotrace traceonly
> exec dbms_application_info.set_client_info(0);
> select (select f(y) from dual) from (select * from t order by x);
> exec dbms_output.put_line( userenv('client_info') );
>
> exec dbms_application_info.set_client_info(0);
> select (select f(y) from dual) from (select * from t order by y);
> exec dbms_output.put_line( userenv('client_info') );
>
> set autotrace off
>
Received on Tue Jan 11 2005 - 15:10:02 CST