Menu
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?
978
In Oracle we can't include more than 1000 values in the “IN” clause.
To query more than 1000 rows, there are two ways to go about this. Use the '$offset=' parameter by setting it to 1000 increments which will allow you to page through the entire dataset 1000 rows at a time. Another way is to use the '$limit=' parameter which will set a limit on how much you query from a dataset.
The maximum is 2100.
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.
91
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
