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.

score 28 · Answer 1 · answered Mar 08 '10 at 12:08

28

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)

answered Mar 08 '10 at 12:08

retronym

54,768
12
155
168

4

We recently changed some code from using "in" to using a temporary lookup table. For some queries the performance increased dramatically from taking several minutes to within a second. – Rene Mar 08 '10 at 14:29
2

But is this a valid implementation for multiple parallel requests to server? As far as I understand there can be several open transactions on Oracle server, trying to create `LOOKUP` table – Ilya Ivanov Feb 20 '13 at 14:43
1

@Ilya: See http://stackoverflow.com/questions/8240810/when-will-data-in-oracle-session-temporary-table-get-deleted. With "on commit delete rows", the data should only be visible within the scope of the transaction. – shelley Jun 10 '13 at 15:27
1

@retronym: Interesting answer, I've tried this within our project. But it took to much time for me. Potentially I did something wrong with data insert. Namely I have approximately 2500 values that should be inserted to temporary table. Could you please advise how this amount of values should be correctly inserted to oracle DB? – fashuser Apr 07 '16 at 10:46

score 19 · Accepted Answer · answered Mar 08 '10 at 12:03

19

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 ?

answered Mar 08 '10 at 12:03

Pascal MARTIN

395,085
80
655
663

I've updated the question to make things clear about the strange results. Now I'm trying the code as you proposed (with extra parentheses). As soon as the results are fetched, I'll tell you. – Mehper C. Palavuzlar Mar 08 '10 at 12:22
Thanks, it works OK now. Once again I see that how extra parentheses are crucial. – Mehper C. Palavuzlar Mar 08 '10 at 15:16
You're welcome :-) -- yes, parenthesis can be helpful ;-) – Pascal MARTIN Mar 08 '10 at 18:26

score 7 · Answer 3 · answered Mar 08 '10 at 16:58

7

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:

answered Mar 08 '10 at 16:58

Scott Bailey

7,748
2
23
21

This is very elegant syntactically but the performance is disappointing. Using this syntax to select a single primary key value the CBO chooses an INDEX FAST FULL SCAN. – Noah Yetter Mar 08 '10 at 22:59
Testing with a couple thousand ids and a moderate sized table (40K rows), it is still about 3x faster than joining on a temp table (due to the hash join). That's not counting the time to create and populate the temporary table. – Scott Bailey Mar 09 '10 at 02:50

score 1 · Answer 4 · answered Mar 08 '10 at 12:09

1

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)

answered Mar 08 '10 at 12:09

David Gelhar

27,873
3
67
84

score 0 · Answer 5 · answered Mar 08 '10 at 12:14

0

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

answered Mar 08 '10 at 12:14

ninesided

23,085
14
83
107

I'm not sure about it. I'm using some views that IT has provided for me. – Mehper C. Palavuzlar Mar 08 '10 at 12:24

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

5 Answers5

Linked

Related