Re: SELECT subquery much slower than IN ( list...)???

David Williams wrote: + Matt Reprogle wrote: +>select col1, col2 +>from bigtable +>where col1 in +> (select key from temp_list_table); +> + Foreach row in big table + get value of col1 (A) + run the subquery and get the results (B) + check if a in B + end foreach [edited] This is absolutely incorrect. Later in your response you tell this poster to avoid a coorelated subquery - which this query is not. A correlated subquery would have a where clause in the subselect that included an expression referencing a column from the "master" table. The way the query, as written, would work is: Execute subquery into result set For each row in big table get value of col1 (A), test if it is in result set end foreach The subquery as written is functionally the same as a literal set. Why is it taking so much longer? A couple of ideas: It was stated that the subselect returns 13 or so unique values, yet the subquery does not specify UNIQUE values. So rather that testing against a set with 13 members, it is testing against a set of X members, where X is the number of rows in the temp table queried in the subselect. Since an IN is reduced to a series of ORs by the optimizer, you wind up with a lot more conditions to test. The optimizer will not eliminate a comparison to a literal string even if it happens to be redundant, as it does not KNOW it is redundant. Another possibility depends on whether or not the big table is fragmented by expression or not. If it is, the use of a literal set would allow the optimizer to do fragmentation elimination. With the subselect, since the set members are not derived until runtime, it cannot include fragment elimination in the plan, and thus all fragments would be scanned. This could result in a significant difference in I/O, and thus query time.