Optimization question: Unrolling subquery into IN clause
Date: Fri, 16 Oct 2009 08:13:41 +0000 (UTC)
I have a query that looks roughly like this:
AND col2 IN
SELECT key FROM tab2 WHERE tab2.col3='Something'
tab1 has about 1.7 million rows. There is an index on tab1.col1 but it has low selectivity (the value queried here is matched by about 1/3 of all rows) so Oracle ignores the index and does a full table scan on tab1. It also does a full table scan on tab2 for the subquery and then hash joins the two together. The FTS on tab1 takes about 4 seconds, the FTS on tab2 (since it's a smaller table) is negligable.
However, only 20 rows of tab2 match the subquery, and only 300 rows of tab1 match the entire query. When I unroll the subquery myself, i. e. do this first:
then save the returned keys and use these as an IN clause in my tab1 query WITHOUT a subquery, like this:
AND col2 IN (Keys returned from tab2 query);
both queries finish within a fraction of a second (since there is also on index on tab1.col2 which is now being used).
Obviously Oracle fails to recognize that in the original query it's the subquery that introduces high selectivity, and that it should resolve the subquery first (using the col2 index) and look at col1 later (i. e. do automatically what I did manually with my subquery unrolling).
This is happening in Oracle 10.2. Both tables had their statistics estimated immediately before the experiment. I get the same results every time I run the queries so it's not a caching effect.
Any ideas how to speed up the original query, i. e. make it fast while still using a subquery?
Thanks for your help
W. Rösler Received on Fri Oct 16 2009 - 03:13:41 CDT