Re: Optimization question: Unrolling subquery into IN clause

From: joel garry <joel-garry_at_home.com>
Date: Fri, 16 Oct 2009 10:33:12 -0700 (PDT)
Message-ID: <f762cdb9-6ea3-4b43-8840-85be3cf5a32d_at_v15g2000prn.googlegroups.com>



On Oct 16, 1:13 am, Wolfram Roesler <w..._at_spam.la> 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: http://jonathanlewis.wordpress.com/2006/11/08/subquery-selectivity/ Note the selectivity of 5% because of the subquery.

jg

--
_at_home.com is bogus.
http://www.philcooke.com/book_burning
Received on Fri Oct 16 2009 - 12:33:12 CDT

Original text of this message