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

Question

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.

Answer 1

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)