Re: Optimization question: Unrolling subquery into IN clause
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Fri, 16 Oct 2009 07:50:24 -0700 (PDT)
Message-ID: <497d7383-c85a-492f-abb9-08da0f9cc161_at_g19g2000yqo.googlegroups.com>
On 16 Okt., 16:21, lothar.armbrues..._at_t-online.de (Lothar Armbrüster) wrote:
> Wolfram Roesler <w..._at_spam.la> writes:
> > 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
>
> Without having tested that, but you might give the HASH hint or one of his
> cousins a try. IIRC hints are explained in the performance tuning manual.
Date: Fri, 16 Oct 2009 07:50:24 -0700 (PDT)
Message-ID: <497d7383-c85a-492f-abb9-08da0f9cc161_at_g19g2000yqo.googlegroups.com>
On 16 Okt., 16:21, lothar.armbrues..._at_t-online.de (Lothar Armbrüster) wrote:
> Wolfram Roesler <w..._at_spam.la> writes:
> > 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
>
> Without having tested that, but you might give the HASH hint or one of his
> cousins a try. IIRC hints are explained in the performance tuning manual.
And of course statistics are up to date (also system stats). What does the plan look like? Did you look at a 10053 trace to find out why the CBO choose the plan it has chosen?
Cheers
robert Received on Fri Oct 16 2009 - 09:50:24 CDT