Re: Function Deterministic "Enough"?

"Bob Burgess" <burgess_at_sympatico.ca> wrote in message news:df35b276.0402181007.4dd387a7_at_posting.google.com... --snip--
> 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

Bob,

I believe you are seeing something which can be reproduced in a simple example as shown below:
SQL> create table t1 (a) as select rownum from user_tables where rownum < 9; Table created.

SQL> create or replace function f1 (in_num in number)   2 return number
  3 is
  4 begin
  5 dbms_lock.sleep(2);
  6 return 1;
  7 end;
  8 /

Function created.

SQL> set timing on
SQL> select count(*) from t1 where a < 2 and f1(a) = 1;

  COUNT(*)

         1

Elapsed: 00:00:16.43
SQL> select count(*) from t1 where f1(a) = 1 and a < 2;

  COUNT(*)

         1

Elapsed: 00:00:02.08

.....

Thus it seems like the order in which the predicates are being evaluated is causing an issue for you.

To get around this problem, you can provide query hints to force a particular
query plan .. or predicate evaluation.

If the problem concerns predicate evaluation then the hint ORDERED_PREDICATES
might be useful for you.

A way that is sometimes used is to use an inline view like this: SQL> select count(*) from (select * from t1 where a < 2) where f1(a) = 1;

  COUNT(*)

         1

Elapsed: 00:00:02.07

However, that is unreliable as oracle can merge views in the final execution.

There are many other ways you can rewrite a query to force other predicates to be
evaluated earlier.

I'll leave some researching for you ...
go and visit asktom.oracle.com .... or google groups to see other opinions about
forcing predicate evaluation

HTH Anurag Received on Wed Feb 18 2004 - 19:09:36 CST