How to use Oracle hints or other optimization to fix function in where clause performance issue?
<p>This is slow:</p>

<pre><code>select col_x from table_a where col_y in (select col_y from table_b) and fn(col_x)=0;
</code></pre>

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

<p>So I do some testing, and I see that this is fast:</p>

<pre><code>select fn(col_x) from table_a where col_y in (select col_y from table_b);
</code></pre>

<p>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?</p>

<p>For example, conceptually, I though this would work:</p>

<pre><code>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;
</code></pre>

<p>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.</p>

<p>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.</p>