Re: slow query with IN list

On 7 Dec 2005 14:17:39 -0800, ns666ns_at_yahoo.com wrote:

>Hi, I have a view (a few tables join) that contains a few million rows,
>lets call it myview, one of the column is ID, now I have a long list
>(say 700) of ID's that need to be filtered with myview, so the query is
>something like:
>
>1) select ID from myview where MYCOND='MYVAL' and ID in (1,2,3... 700);
>
>now that query is very slow, take minutes, I don't understand why (I
>think ID is indexed), that's my first question :).
>

did you look at the EXPLAIN PLAN? Which optimizer are you using? Are the statistics current? Did you verify the statement is using the IN-LIST iterator? Does your version have a number? Or do you think everyone is running the same version, and Oracle nevers improves its optimizer?

>
>now if I just get the ID's that satisfy the condition, it takes only 4
>seconds, return say 1000 ID's:
>
>2) select ID from myview where MYCOND='MYVAL'
>
>then even just do INTERSECT will be way faster than query 1), but my
>question is I don't know how to make my (1,2,3... 700) into a "select"
>result, something like this doesn't work
>
>3) select ID from myview where MYCOND='MYVAL' intersect select
>(1,2,3...700) from dual?
>
>so how to properly write 3) is my second question, well of course I can
>do the "intersect" in java code, but if can be done in 1 sql statement
>that will be better :)
>
>

If you have 700 id's put them in a table.

>thanks

--
Sybrand Bakker, Senior Oracle DBA