Re: Optimization question: Unrolling subquery into IN clause

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Fri, 16 Oct 2009 10:42:55 -0700 (PDT)
Message-ID: <d2022a75-b1d5-4d74-abda-b452aafcb7ef_at_t2g2000yqn.googlegroups.com>

On Oct 16, 4: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

Hi,

Try...

explain plan for
SELECT several_columns
FROM tab1
WHERE col1='Value'
AND col2 IN
(

        SELECT key
        FROM tab2
        WHERE tab2.col3='Something'

);

select * from table(dbms_xplan.display());

...and post the output

HTH, Steve Received on Fri Oct 16 2009 - 12:42:55 CDT