Re: Function Deterministic "Enough"?

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

Thanks again all,
Bob Received on Wed Feb 18 2004 - 12:07:42 CST