Oracle SQL: How to use more than 1000 items inside an IN clause

Question

I have an SQL statement where I would like to get data of 1200 ep_codes by making use of IN clause. When I include more than 1000 ep_codes inside IN clause, Oracle says I'm not allowed to do that. To overcome this, I tried to change the SQL code as follows:

SELECT period, ...
FROM   my_view
WHERE  period = '200912'
       ...
       AND ep_codes IN (...1000 ep_codes...)
       OR  ep_codes IN (...200 ep_codes...)

The code was executed succesfully but the results are strange (calculation results are fetched for all periods, not just for 200912, which is not what I want). Is it appropriate to do that using OR between IN clauses or should I execute two separate codes as one with 1000 and the other with 200 ep_codes?

---

Pascal Martin's solution worked perfectly. Thanks all who contributed with valuable suggestions.

Answer 1

Not sure that using so many values in a IN() is that good, actually -- especially for performances.

When you say "the results are strange", maybe this is because a problem with parenthesis ? What if you try this, instead of what you proposed :

SELECT ...
FROM ...
WHERE ...
      AND (
          ep_codes IN (...1000 ep_codes...)
          OR  ep_codes IN (...200 ep_codes...)
      )

Does it make the results less strange ?

Answer 2

The recommended way to handle this in Oracle is to create a Temporary Table, write the values into this, and then join to this. Using dynamically created IN clauses means the query optimizer does a 'hard parse' of every query.

create global temporary table LOOKUP
(
    ID NUMBER
) on commit delete rows;

-- Do a batch insert from your application to populate this table
insert into lookup(id) values (?)

-- join to it
select foo from bar where code in (select id from lookup)

Answer 3

Seems like it would be a better idea, both for performance and maintainability, to put the codes in a separate table.

SELECT ...
FROM ...
WHERE ...
   AND ep_code in (select code from ep_code_table)

Answer 4

could you insert the 1200 ep_code values into a temporary table and then INNER JOIN to that table to filter rows instead?

SELECT a.*
FROM mytable a
INNER JOIN tmp ON (tmp.ep_code = a.ep_code)
WHERE ...

Answer 5

Actually you can use collections/multisets here. You'll need a number table type to store them.

CREATE TYPE NUMBER_TABLE AS TABLE OF NUMBER;
...
SELECT *
FROM my_view
WHERE period MEMBER OF NUMBER_TABLE(1,2,3...10000)

Read more about multisets here: