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

Matt, I too am interested in this. I have approx 50 delete statements that use a subquery on a key. All tables have at least one index on the column that I am using, with that column as the first or only element. At the moment around half use the indexes and about half sequential scan. I have not been able to figure out why they dont all use the index. If I found out more I will let you know. Jason 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. > >On the other hand, if I write out the result of the subquery explicitly, >such as: > >select col1, col2 >from bigtable >where col1 in ('A','B','C','D','E','F','G','H','I','J','K','L','M');> >the query completes in less than 2 seconds. > >I guess I had the mistaken assumption that the main query treated the >subquery result like an explicit list of the form ('val1','val2',...). > >What could cause the huge performance difference between the two query >forms? > >I am on 7.23 and Solaris 2.5.1, Sun E3000. >-- >Matt Reprogle >reprogle@iquest.net