Re: Function Deterministic "Enough"?

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 18 Feb 2004 21:44:25 GMT
Message-ID: <c10mbp$1cm3bv$1@ID-82536.news.uni-berlin.de>

> Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message news:<c0u633$1cgar4$2_at_ID-82536.news.uni-berlin.de>...

>> > I understand Deterministic functions would
>> > execute only once.
>> 
>> Your understanding is wrong. 
>> The function is called twice, although being deterministic and marked as such.
>> 
>> Rene

>
>
> Thank you for your example, Rene, but this isn't quite the problem
> we're having. I should have been more clear.
>
> The problem we have shows up when we're joining several tables....
>
> Very Rough Example:
> select rows
> from large_table T1, another_large_table T2, smaller_table T3
> where T1.key = T2.key
> and T2.key2 = T3.key
> and nasty_function(T3.some_column) = 1
>
> The function in the Where clause references a table with, say, 1000
> rows, but the function isn't being called 1000 times. In some cases,
> it's called 1000000 times or more, as an intermediate step in joining
> the tables. This is the behaviour I'm trying to avoid. I'm hoping
> the "deterministic" keyword can assure Oracle that the return value
> won't change during the time it's running the query, so that it will
> only call the function once per input parameter value (ie. 1000 times
> in this case).

Bob,

not sure if I understand now. It seems that the execution plan is not optimal. Can you show the output of the explain plan?

Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch

Received on Wed Feb 18 2004 - 15:44:25 CST