Re: Where col in (num, num) vs where col in (select # from tab)

The most significant thing about this question is that the last time it was posed to the group the "in list" query was the fast one and the subquery was the slow one.

Look at the execution plans - this should always be the first point. You will probably find (depending on Oracle version) that the slow query is doing a tablescan and the fast subquery has unnested and is doing an indexed nested loop.

The trouble is that Oracle knows 'exactly' what it is chasing when you give it the list, but it can only estimate the return set when given the subquery. Depending on the actual data, and the quality of the related statistics, the estimate may be a long way from the truth, and cause Oracle to do something which is accidentally very clever (your case) or very stupid (the previous contrary example).

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            June / July
        Australia      July / August
http://www.jlcomp.demon.co.uk/seminar.html

Allen wrote in message
<43B71900B1ED02F9.B62008260ADBAA4E.84BA062CC0ED0C57_at_lp.airnews.net>...


>

>I have a rather nasty query..  If I do a:

>

>select columns from tables where col_value in (num1, num2, num3)

>

>it takes several minutes.   If I do a:

>

>select columns from tables where col_value in (select num from temptable)

>

>it runs much faster.

>

>Any ideas why a where col in (list of values) is so much slower than where

>col in (select list of values from a table) ?

>

>thx.. Allen