Re: Large # Items in IN () Very Slow

There are a couple of oddities in the
query and the execution path you've
posted.

You didn't mention a sort/aggregate in
your introduction - and the plan suggests that this is being applied to something
in the order of 24,000 rows, which would help to explain the slow-down.

Nevertheless, 60 cpu minutes to do the
sort seems a bit extreme (the extra 6
is within the realms of possibility for
the 896 disk reads)

Would you care to tell us more about
the SQL statement - like how many
columns are aggregated, what type
of aggregation is taking place, and
whether there any complex conditions
in the where clause which are (say)
calling PL/SQL functions.

Also, of course, is the 24,000 rows
indicated in the plan roughly correct ?

--
Regards

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

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Jonathan D. Trumbull wrote in message
<266c2da3.0210291539.4d11f1db_at_posting.google.com>...


>Hi,

>

>I have searched and searched but I can't find even a hint of a

>reference on this.

>

>Basically I have a query in the form:

>

>SELECT ColumnA,ColumnB

>FROM SomeTable

>WHERE ColumnA IN ('Item1','Item2',....'ItemN')

>