Re: Follow Up: DETERMINISTIC in user functions

In article <cs1fba$c41$1_at_titan.btinternet.com>, Jonathan Lewis says...
>
>
>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;
>

do (a) or do (a) :)

>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
>
>

thanks -- that's neat. never could figure out what the cache size was, that clears it up rather nicely....

(this will be an example I use when I say "i learn something *new* about Oracle each and every day :)

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation