Re: Optimization question: Unrolling subquery into IN clause

> 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.

HTH,
Lothar

-- 
Lothar Armbrüster  | lothar.armbruester_at_t-online.de
Hauptstr. 26       |
65346 Eltville     |