Re: Optimization question: Unrolling subquery into IN clause

From: joel garry <>
Date: Fri, 16 Oct 2009 10:33:12 -0700 (PDT)
Message-ID: <>

On Oct 16, 1:13 am, Wolfram Roesler <> wrote:
> Hello,
> I have a query that looks roughly like this:
> SELECT several_columns
> FROM tab1
> WHERE col1='Value'
> 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:
> SELECT key
> FROM tab2
> WHERE tab2.col3='Something';
> then save the returned keys and use these as an IN clause in
> my tab1 query WITHOUT a subquery, like this:
> SELECT several_columns
> FROM tab1
> WHERE col1='Value'
> 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

See this for some ideas on how to investigate it: Note the selectivity of 5% because of the subquery.


-- is bogus.
Received on Fri Oct 16 2009 - 12:33:12 CDT

Original text of this message