Re: Optimization question: Unrolling subquery into IN clause

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

(snip)

Various people have spoken, but in quickly glancing through I did not see anything like the following. I think that this is a tom kyte trick (aren't they all). Obviously I haven't tested this to see if it helps you, but it has worked for me in other situations.

   google: materialize rownum
 might give you better examples. My (untested) first attempt would be

	SELECT several_columns
	FROM tab1
	WHERE col1='Value'
	AND col2 IN
	(
	      SELECT key
		from
		( SELECT rownum, key
	      	  FROM tab2
		  WHERE tab2.col3='Something'
		)
	);

You could also try using the rownum select in a WITH clause at the top. The rownum forces Oracle to build a small temporary materialized version of the inner select. Received on Thu Oct 22 2009 - 12:46:03 CDT