Re: SELECT subquery much slower than IN ( list...)???
On 5 Mar 1998 05:07:31 GMT, "Matt Reprogle" <reprogle@iquest.net>
wrote:
>I have been having problems with a select statement of the type:
>
>select col1, col2
>from bigtable
>where col1 in
> (select key from temp_list_table);>
>In one case I looked at, the subquery returns just 13 unique values in
>subsecond time, yet it took almost 7 minutes for the main query to
>complete.
have you done a 'set explain on'?
I had a similar situation once, and I didn't realize
(until the 'explain') that the
table in the main query was really an alias (synonym, whatever) for
a table in another database on another machine. The optimizer
therefore could not use the index on the main table.
Also could be an 'update statistics' thing.
Cheers,
Douglas Wilson