Re: deterministic function

Hmm, I don't know anything about Oracle internals, I have a feeling that this kind of test isn't actually proving what you intended to prove. I mean, I don't know if and how they managed to cache deterministic function values, may be the function is called anyway and just at a later stage it gets its cached value, it depends on the implementation. My coding suggestion was meant to get rid of any doubt by forcing some unnecessary load during function execution, just to see if this code gets executed at all times or not. If the function contains arguments, at some stage it must be decided that this call is going to return a known value, the point is to understand when this happens.

On the other hand you could also try with a completely different approach: declare your function as deterministic and then return a random number or a value based on the current time and see what you get... :-)

Bye,
Flavio

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:as3ff9$r3j$1$8302bc10_at_news.demon.co.uk...
>
> I don't need to do anything subtle.
>
> Create a deterministic function to do a
> simple 'return input' based on a number.
>
> Create function based index using said
> function, on a column where every single
> row has the same value.
>
> Watch the session stats do one recursive
> call per row in the table. QED - the function
> is not taking advantage of being deterministic.
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____Denver_______December 2/4
> ____England______January 21/23
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
>
> FC wrote in message ...
> >Well, I expect Oracle to cache the result(s) somewhere, I think you
> should
> >try with some sort of loop or sleep time taking seconds or even
> minutes
> >before returning the result, then, if it works as you say, the second
> time
> >you call the function with the same arguments, the response time
> should be
> >instantaneous.
> >
> >Bye,
> >Flavio
>
>
>
Received on Wed Nov 27 2002 - 17:30:57 CST