61

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?

Jonathan
  • 11,809
  • 5
  • 57
  • 91
Jeune
  • 3,498
  • 5
  • 43
  • 54
  • 6
    possible duplicate of [Oracle SQL: How to use more than 1000 items inside an IN clause](http://stackoverflow.com/questions/2401066/oracle-sql-how-to-use-more-than-1000-items-inside-an-in-clause) – Justin Cave Jan 18 '11 at 09:19
  • 2
    Apart from being duplicate, I wanted to let you know that the in clause limit is there for a reason. Its a extremely resource intensive query. You should do what @Jonathan has mentioned. – uncaught_exceptions Jan 18 '11 at 10:11
  • 2
    possible duplicate of [How to put more than 1000 values into an Oracle IN clause](http://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause) – Clyde Lobo Sep 11 '13 at 09:38

5 Answers5

99

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.

gordy
  • 9,360
  • 1
  • 31
  • 43
  • 1
    +1 cool trick :) - tested with 10,001 elements - performance seems to suffer though – Jeffrey Kemp Feb 01 '12 at 04:08
  • 3
    @Nitish my guess is that support for tuples came later and by then the developers were enlightened with the ZOI principle – gordy Nov 07 '14 at 19:41
  • Anyone know how this can be implemented using JPA Query Language? – Vineet Bhatia Dec 04 '14 at 14:13
  • 2
    @JeffreyKemp Oracle seems to limit to 70,000 in a tuple style in clause, still better than the regular in clause limitation. – Y123 Jan 09 '17 at 00:01
  • Maybe you mean 65535, @JeffreyKemp. https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:778625947169, last comment. "I tested and the limit for in-list of tuples is 65,535 - as you seem to suggest with your example. Same Oracle version as yours, 12.2.0.1..... Just making atomic elements into 1-tuples by wrapping them in parentheses doesn't work though; the tuples must be n-tuples with n >= 2. ....And Oracle has a hard limit of 2^16 - 1 = 65,535 component conditions for such compound conditions - regardless of what those conditions are" – WesternGun Nov 04 '21 at 08:09
  • Hi @WesternGun, not sure what you mean, commenting on this post from over 9 years ago... – Jeffrey Kemp Nov 04 '21 at 13:26
65

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

Jonathan
  • 11,809
  • 5
  • 57
  • 91
12

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,....)
J. Chomel
  • 8,193
  • 15
  • 41
  • 69
Pratik Agarwal
  • 121
  • 1
  • 2
0

If you don't have the luxury of creating a temp table, you can simulate it using the WITH clause

with t as (
  select 1 val from dual 
  union all select 2 from dual
  union all select 3 from dual
    ...
  union all select 5001 from dual
  union all select 5002 from dual
)
select * 
  from mytable
 where col1 in (select val from t)

Obviously, you could also join mytable to t

I like Gordy's answer best, just showing another way.

Kevin McCabe
  • 108
  • 7
-1

Another way:

SELECT COL1, COL2, COL3 FROM YOUR_TABLE
WHERE 1=1
AND COL2 IN (
SELECT VAL1 as FAKE FROM DUAL
UNION
SELECT VAL2 as FAKE FROM DUAL
UNION
SELECT VAL3 as FAKE FROM DUAL
--...
)
Andrew
  • 7,619
  • 13
  • 63
  • 117