Organizational Research By

Surprising Reserch Topic

oracle sql how to use more than 1000 items inside an in clause


oracle sql how to use more than 1000 items inside an in clause  using -'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?

asked Sep 29, 2015 by rolvyrf
edited Sep 29, 2015 by rajesh
0 votes
13 views



Related Hot Questions

5 Answers

0 votes

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 Sep 29, 2015 by devkumargupta
0 votes

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 Sep 29, 2015 by sachin valanju
0 votes

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 Sep 29, 2015 by maurya
0 votes

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 Sep 29, 2015 by devkumargupta
0 votes

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 Sep 29, 2015 by girisha

...