Re: Optimization question: Unrolling subquery into IN clause

"Wolfram Roesler" <wr_at_spam.la> wrote in message news:Xns9CA6680BD4E25wrgrpde_at_news.albasani.net...
> 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

I'd say the same as Steve Howard.

    explain plan for ...
    select * from table(dbms_xplan.display(null,null,'outline'));

We need to see the execution plan before suggesting how to change it.

The optimizer may be unnesting the subquery with or without complex view merging, or it may be converting your query to a semi-join.

Either way, its choice is dictated by the arithmetic and the current constraints. You may have to put in a stack of hints to get the path you want - is that acceptable, or are you not allowed to modify the query at all ?

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html