CODEWITHSUNDEEP
oracle-databaseoptimizationplsqldatabase-performance
Bob Thule
Bob Thule

Reputation: 751

How to use Oracle hints or other optimization to fix function in where clause performance issue?

This is slow:

select col_x from table_a where col_y in (select col_y from table_b) and fn(col_x)=0;

But I know that this will return 4 rows fast, and that I can run fn() on 4 values fast.

So I do some testing, and I see that this is fast:

select fn(col_x) from table_a where col_y in (select col_y from table_b);

When using the fn() in the where clause, Oracle is running it on every row in table_a. How can I make it so Oracle first uses the col_y filter, and only runs the function on the matched rows?

For example, conceptually, I though this would work:

with taba as (
   select fn(col_x) x from table_a where col_y in (select col_y from table_b)
)
select * from taba where x=0;

because I thought Oracle would run the with clause first, but Oracle is "optimizing" this query and making this run exactly the same as the first query above where fn(col_x)=0 is in the where clause.

I would like this to run just as a query and not in a pl/sql block. It seems like there should be a way to give oracle a hint, or do some other trick, but I can't figure it out. BTW, table is indexed on col_y and it is being used as an access predicate. Stats are up to date.

Upvotes: 3

Views: 675

Answers (4)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

1) Why you don't try join table_a and table_b using col_y.

  select a.col_x from table_a a,table_b b
    where a.col_y = b.col_y
     and fn(col_x) = 0

2) NO_PUSH_PRED - 

 select /*+ NO_PUSH_PRED(v) */ col_x from (
    select col_x from table_a  where col_y in (select col_y from table_b) 
   ) v 
   where fn(col_x) =0

3) Exists and PUSH_SUBQ.

select col_x from table_a a 
    where exists( select /*+ PUSH_SUBQ */ 1  from table_b  b where a.col_y = b.coly )
     and fn(col_x) = 0;

Upvotes: 0

Arcan3
Arcan3

Reputation: 89

There are two ways you could go around it,

1) add 'AND rownum >=0' in the subquery to force materialization.

OR

2) use a Case statement inside the query to force the execution priority (maybe)

Upvotes: 2

Joel
Joel

Reputation: 2257

You could try the HAVING clause in your query. This clause is not executed until the base query is completed, and then the HAVING clause is run on the resulting rows. It's typically used for analytic functions, but could be useful in your case.

select col_x 
from table_a 
where col_y in (select col_y from table_b) 
having fn(col_x)=0;

A HAVING clause restricts the results of a GROUP BY in a SelectExpression. The HAVING clause is applied to each group of the grouped table, much as a WHERE clause is applied to a select list. If there is no GROUP BY clause, the HAVING clause is applied to the entire result as a single group. The SELECT clause cannot refer directly to any column that does not have a GROUP BY clause. It can, however, refer to constants, aggregates, and special registers.

http://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj14854.html

Upvotes: 0

Bob Thule
Bob Thule

Reputation: 751

This works, but if anyone has a better answer, please share:

select col_x
from table_a 
where col_y in (select col_y from table_b) 
  and (select 1 from dual where fn(col_x)=0);

Kind of kludgy, but works. Takes a query running in 60+ seconds down to .1 seconds.

Upvotes: 0

Related Questions