slow query with IN list

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 :).

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 :)

thanks Received on Wed Dec 07 2005 - 16:17:39 CST