SQL IN Clause 1000 item limit
It is possible to put more than 1000 items in the SQL IN clause? We have been getting issues with our Oracle database not being able to handle it.
IF yes, how do we put more than 1000 items in the SQL IN clause?
IF not, what else can I do?
There's another workaround for this that isn't mentioned in any of the other answers (or other answered questions):
Any in statement like x in (1,2,3)
can be rewritten as (1,x) in ((1,1), (1,2), (1,3))
and the 1000 element limit will no longer apply. I've tested with an index on x
and explain plan still reports that Oracle is using an access predicate and range scan.
You should transform the IN clauses to INNER JOIN clauses.
You can transform a query like this one
SELECT foo
FROM bar
WHERE bar.stuff IN
(SELECT stuff FROM asdf)
in a query like this other one.
SELECT b.foo
FROM (
SELECT DISTINCT stuff
FROM asdf ) a
JOIN bar b
ON b.stuff = a.stuff
You will also gain a lot of performance
We can have more than one "IN" statement for the same variable.
For ex:
select val
from table
where val in (1,2,3,...)
or
val in (7,8,9,....)