How to put more than 1000 values into an Oracle IN clause

Question

Is there any way to get around the Oracle 10g limitation of 1000 items in a static IN clause   I have a comma delimited list of many of IDs that I want to use in an IN clause   Sometimes this list can exceed 1000 items  at which point Oracle throws an error   The query is similar to this     select   from table1 where ID in  1 2 3 4     1001 1002

User · Answer

You may try to use the following form   select   from table1 where ID in  1 2 3 4     1000  union all select   from table1 where ID in  1001 1002

User · Answer

Where do you get the list of ids from in the first place   Since they are IDs in your database  did they come from some previous query   When I have seen this in the past it has been because -   a reference table is missing and the correct way would be to add the new table  put an attribute on that table and join to it a list of ids is extracted from the database  and then used in a subsequent SQL statement  perhaps later or on another server or whatever    In this case  the answer is to never extract it from the database   Either store in a temporary table or just write one query    I think there may be better ways to rework this code that just getting this SQL statement to work   If you provide more details you might get some ideas

User · Answer

Where do you get the list of ids from in the first place   Since they are IDs in your database  did they come from some previous query   When I have seen this in the past it has been because -   a reference table is missing and the correct way would be to add the new table  put an attribute on that table and join to it a list of ids is extracted from the database  and then used in a subsequent SQL statement  perhaps later or on another server or whatever    In this case  the answer is to never extract it from the database   Either store in a temporary table or just write one query    I think there may be better ways to rework this code that just getting this SQL statement to work   If you provide more details you might get some ideas

User · Answer

Instead of using IN clause  can you try using JOIN with the other table  which is fetching the id  that way we don t need to worry about limit  just a thought from my side

User · Answer

Use    from table        create or replace type numbertype as object  nr number 20 10        create or replace type number table as table of numbertype     create or replace procedure tableselect   p numbers in number table   p ref result out sys refcursor  is begin   open p ref result for     select       from employees    select     cardinality tab 10     tab nr from table p numbers  tab  tbnrs      where id   tbnrs nr   end        This is one of the rare cases where you need a hint  else Oracle will not use the index on column id  One of the advantages of this approach is that Oracle doesn t need to hard parse the query again and again  Using a temporary table is most of the times slower    edit 1 simplified the procedure  thanks to jimmyorr    example  create or replace procedure tableselect   p numbers in number table   p ref result out sys refcursor  is begin   open p ref result for     select     cardinality tab 10     emp       from  employees emp           table p numbers  tab     where tab nr   id  end      Example   set serveroutput on   create table employees   id number 10  name varchar2 100    insert into employees values  3  Raymond    insert into employees values  4  Hans    commit   declare   l number number table    number table      l sys refcursor sys refcursor    l employee employees rowtype  begin   l number extend    l number 1     numbertype 3     l number extend    l number 2     numbertype 4     tableselect l number  l sys refcursor     loop     fetch l sys refcursor into l employee      exit when l sys refcursor notfound      dbms output put line l employee name     end loop    close l sys refcursor  end      This will output   Raymond Hans

User · Answer

Put the values in a temporary table and then do a select where id in  select id from temptable

User · Answer

Where do you get the list of ids from in the first place   Since they are IDs in your database  did they come from some previous query   When I have seen this in the past it has been because -   a reference table is missing and the correct way would be to add the new table  put an attribute on that table and join to it a list of ids is extracted from the database  and then used in a subsequent SQL statement  perhaps later or on another server or whatever    In this case  the answer is to never extract it from the database   Either store in a temporary table or just write one query    I think there may be better ways to rework this code that just getting this SQL statement to work   If you provide more details you might get some ideas

User · Answer

select column X      from my table where   magic   column X   in             magic   1             magic   2             magic   3             magic   4                              magic   99999

User · Answer

I wound up here looking for a solution as well    Depending on the high-end number of items you need to query against  and assuming your items are unique  you could split your query into batches queries of 1000 items  and combine the results on your end instead  pseudocode here       remove dupes items   items RemoveDuplicates       how to break the items into 1000 item batches         batches   new batch list  batch   new batch  for  int i   0  i  lt  items Count  i          if  batch Count    1000                batches Add batch           batch Clear             batch Add items i        if  i    items Count - 1                  add the final batch  it has  lt  1000 items           batches Add batch                now go query the db for each batch results   new results  foreach batch in batches        results Add query batch        This may be a good trade-off in the scenario where you don t typically have over 1000 items - as having over 1000 items would be your  high end  edge-case scenario  For example  in the event that you have 1500 items  two queries of  1000  500  wouldn t be so bad  This also assumes that each query isn t particularly expensive in of its own right   This wouldn t be appropriate if your typical number of expected items got to be much larger - say  in the 100000 range - requiring 100 queries  If so  then you should probably look more seriously into using the global temporary tables solution provided above as the most  correct  solution  Furthermore  if your items are not unique  you would need to resolve duplicate results in your batches as well

User · Answer

select column X      from my table where   magic   column X   in             magic   1             magic   2             magic   3             magic   4                              magic   99999

User · Answer

Here is some Perl code that tries to work around the limit by creating an inline view and then selecting from it  The statement text is compressed by using rows of twelve items each instead of selecting each item from DUAL individually  then uncompressed by unioning together all columns  UNION or UNION ALL in decompression should make no difference here as it all goes inside an IN which will impose uniqueness before joining against it anyway  but in the compression  UNION ALL is used to prevent a lot of unnecessary comparing  As the data I m filtering on are all whole numbers  quoting is not an issue      generate the innards of an IN expression with more than a thousand items   use English  -no match vars   sub big IN list          lt  13 and return join              my  padding required    12 -       12     12          get first dozen and make length of    an even multiple of 12     my   a  b  c  d  e  f  g  h  i  j  k  l    splice    0 12      NULL   x  padding required         my  dozens       local  LIST SEPARATOR           how to join elements within each dozen     while              push  dozens   quot SELECT     splice    0 12    FROM DUAL quot                LIST SEPARATOR    quot  n    union all n     quot     how to join  dozens      return  lt  lt  quot EXP quot   WITH t AS       select  a A   b B   c C   d D   e E   f F   g G   h H   i I   j J   k K   l L FROM     DUAL     union all      dozens    select A from t union select B from t union select C from t union select D from t union select E from t union select F from t union select G from t union select H from t union select I from t union  select J from t union select K from t union select L from t EXP    One would use that like so  my  bases list expr   big IN list list your bases      dbh- gt do  lt  lt  quot UPDATE quot        update bases table set belong to    us      where id in   bases list expr  UPDATE

User · Answer

Yes  very weird situation for oracle   if you specify 2000 ids inside the IN clause  it will fail  this fails          select      where id in  1 2     2000     but if you simply put the 2000 ids in another table  temp table for example   it will works below query          select      where id in  select userId               from temptable with 2000 ids      what you can do  actually could split the records into a lot of 1000 records and execute them group by group

User · Answer

I am almost sure you can split values across multiple INs using OR   select   from table1 where ID in  1 2 3 4     1000  or  ID in  1001 1002     2000

User · Answer

Use    from table        create or replace type numbertype as object  nr number 20 10        create or replace type number table as table of numbertype     create or replace procedure tableselect   p numbers in number table   p ref result out sys refcursor  is begin   open p ref result for     select       from employees    select     cardinality tab 10     tab nr from table p numbers  tab  tbnrs      where id   tbnrs nr   end        This is one of the rare cases where you need a hint  else Oracle will not use the index on column id  One of the advantages of this approach is that Oracle doesn t need to hard parse the query again and again  Using a temporary table is most of the times slower    edit 1 simplified the procedure  thanks to jimmyorr    example  create or replace procedure tableselect   p numbers in number table   p ref result out sys refcursor  is begin   open p ref result for     select     cardinality tab 10     emp       from  employees emp           table p numbers  tab     where tab nr   id  end      Example   set serveroutput on   create table employees   id number 10  name varchar2 100    insert into employees values  3  Raymond    insert into employees values  4  Hans    commit   declare   l number number table    number table      l sys refcursor sys refcursor    l employee employees rowtype  begin   l number extend    l number 1     numbertype 3     l number extend    l number 2     numbertype 4     tableselect l number  l sys refcursor     loop     fetch l sys refcursor into l employee      exit when l sys refcursor notfound      dbms output put line l employee name     end loop    close l sys refcursor  end      This will output   Raymond Hans

User · Answer

I am almost sure you can split values across multiple INs using OR   select   from table1 where ID in  1 2 3 4     1000  or  ID in  1001 1002     2000

User · Answer

Use    from table        create or replace type numbertype as object  nr number 20 10        create or replace type number table as table of numbertype     create or replace procedure tableselect   p numbers in number table   p ref result out sys refcursor  is begin   open p ref result for     select       from employees    select     cardinality tab 10     tab nr from table p numbers  tab  tbnrs      where id   tbnrs nr   end        This is one of the rare cases where you need a hint  else Oracle will not use the index on column id  One of the advantages of this approach is that Oracle doesn t need to hard parse the query again and again  Using a temporary table is most of the times slower    edit 1 simplified the procedure  thanks to jimmyorr    example  create or replace procedure tableselect   p numbers in number table   p ref result out sys refcursor  is begin   open p ref result for     select     cardinality tab 10     emp       from  employees emp           table p numbers  tab     where tab nr   id  end      Example   set serveroutput on   create table employees   id number 10  name varchar2 100    insert into employees values  3  Raymond    insert into employees values  4  Hans    commit   declare   l number number table    number table      l sys refcursor sys refcursor    l employee employees rowtype  begin   l number extend    l number 1     numbertype 3     l number extend    l number 2     numbertype 4     tableselect l number  l sys refcursor     loop     fetch l sys refcursor into l employee      exit when l sys refcursor notfound      dbms output put line l employee name     end loop    close l sys refcursor  end      This will output   Raymond Hans

User · Answer

You may try to use the following form   select   from table1 where ID in  1 2 3 4     1000  union all select   from table1 where ID in  1001 1002

User · Answer

I wound up here looking for a solution as well    Depending on the high-end number of items you need to query against  and assuming your items are unique  you could split your query into batches queries of 1000 items  and combine the results on your end instead  pseudocode here       remove dupes items   items RemoveDuplicates       how to break the items into 1000 item batches         batches   new batch list  batch   new batch  for  int i   0  i  lt  items Count  i          if  batch Count    1000                batches Add batch           batch Clear             batch Add items i        if  i    items Count - 1                  add the final batch  it has  lt  1000 items           batches Add batch                now go query the db for each batch results   new results  foreach batch in batches        results Add query batch        This may be a good trade-off in the scenario where you don t typically have over 1000 items - as having over 1000 items would be your  high end  edge-case scenario  For example  in the event that you have 1500 items  two queries of  1000  500  wouldn t be so bad  This also assumes that each query isn t particularly expensive in of its own right   This wouldn t be appropriate if your typical number of expected items got to be much larger - say  in the 100000 range - requiring 100 queries  If so  then you should probably look more seriously into using the global temporary tables solution provided above as the most  correct  solution  Furthermore  if your items are not unique  you would need to resolve duplicate results in your batches as well

User · Answer

You may try to use the following form   select   from table1 where ID in  1 2 3 4     1000  union all select   from table1 where ID in  1001 1002

User · Answer

Instead of SELECT   FROM table1 WHERE ID IN  1 2 3 4     1000    Use this    SELECT   FROM table1 WHERE ID IN  SELECT rownum AS ID FROM dual connect BY level  lt   1000     Note that you need to be sure the ID does not refer any other foreign IDS if this is a dependency  To ensure only existing ids are available then     SELECT   FROM table1 WHERE ID IN  SELECT distinct ID  FROM tablewhereidsareavailable    Cheers

User · Answer

Put the values in a temporary table and then do a select where id in  select id from temptable

User · Answer

Yes  very weird situation for oracle   if you specify 2000 ids inside the IN clause  it will fail  this fails          select      where id in  1 2     2000     but if you simply put the 2000 ids in another table  temp table for example   it will works below query          select      where id in  select userId               from temptable with 2000 ids      what you can do  actually could split the records into a lot of 1000 records and execute them group by group

User · Answer

Here is some Perl code that tries to work around the limit by creating an inline view and then selecting from it  The statement text is compressed by using rows of twelve items each instead of selecting each item from DUAL individually  then uncompressed by unioning together all columns  UNION or UNION ALL in decompression should make no difference here as it all goes inside an IN which will impose uniqueness before joining against it anyway  but in the compression  UNION ALL is used to prevent a lot of unnecessary comparing  As the data I m filtering on are all whole numbers  quoting is not an issue      generate the innards of an IN expression with more than a thousand items   use English  -no match vars   sub big IN list          lt  13 and return join              my  padding required    12 -       12     12          get first dozen and make length of    an even multiple of 12     my   a  b  c  d  e  f  g  h  i  j  k  l    splice    0 12      NULL   x  padding required         my  dozens       local  LIST SEPARATOR           how to join elements within each dozen     while              push  dozens   quot SELECT     splice    0 12    FROM DUAL quot                LIST SEPARATOR    quot  n    union all n     quot     how to join  dozens      return  lt  lt  quot EXP quot   WITH t AS       select  a A   b B   c C   d D   e E   f F   g G   h H   i I   j J   k K   l L FROM     DUAL     union all      dozens    select A from t union select B from t union select C from t union select D from t union select E from t union select F from t union select G from t union select H from t union select I from t union  select J from t union select K from t union select L from t EXP    One would use that like so  my  bases list expr   big IN list list your bases      dbh- gt do  lt  lt  quot UPDATE quot        update bases table set belong to    us      where id in   bases list expr  UPDATE

User · Answer

I am almost sure you can split values across multiple INs using OR   select   from table1 where ID in  1 2 3 4     1000  or  ID in  1001 1002     2000

User · Answer

Instead of using IN clause  can you try using JOIN with the other table  which is fetching the id  that way we don t need to worry about limit  just a thought from my side

User · Answer

Put the values in a temporary table and then do a select where id in  select id from temptable

User · Answer

Use    from table        create or replace type numbertype as object  nr number 20 10        create or replace type number table as table of numbertype     create or replace procedure tableselect   p numbers in number table   p ref result out sys refcursor  is begin   open p ref result for     select       from employees    select     cardinality tab 10     tab nr from table p numbers  tab  tbnrs      where id   tbnrs nr   end        This is one of the rare cases where you need a hint  else Oracle will not use the index on column id  One of the advantages of this approach is that Oracle doesn t need to hard parse the query again and again  Using a temporary table is most of the times slower    edit 1 simplified the procedure  thanks to jimmyorr    example  create or replace procedure tableselect   p numbers in number table   p ref result out sys refcursor  is begin   open p ref result for     select     cardinality tab 10     emp       from  employees emp           table p numbers  tab     where tab nr   id  end      Example   set serveroutput on   create table employees   id number 10  name varchar2 100    insert into employees values  3  Raymond    insert into employees values  4  Hans    commit   declare   l number number table    number table      l sys refcursor sys refcursor    l employee employees rowtype  begin   l number extend    l number 1     numbertype 3     l number extend    l number 2     numbertype 4     tableselect l number  l sys refcursor     loop     fetch l sys refcursor into l employee      exit when l sys refcursor notfound      dbms output put line l employee name     end loop    close l sys refcursor  end      This will output   Raymond Hans

User · Answer

I am almost sure you can split values across multiple INs using OR   select   from table1 where ID in  1 2 3 4     1000  or  ID in  1001 1002     2000

User · Answer

Put the values in a temporary table and then do a select where id in  select id from temptable

User · Answer

Where do you get the list of ids from in the first place   Since they are IDs in your database  did they come from some previous query   When I have seen this in the past it has been because -   a reference table is missing and the correct way would be to add the new table  put an attribute on that table and join to it a list of ids is extracted from the database  and then used in a subsequent SQL statement  perhaps later or on another server or whatever    In this case  the answer is to never extract it from the database   Either store in a temporary table or just write one query    I think there may be better ways to rework this code that just getting this SQL statement to work   If you provide more details you might get some ideas

User · Answer

You may try to use the following form   select   from table1 where ID in  1 2 3 4     1000  union all select   from table1 where ID in  1001 1002

User · Answer

Instead of SELECT   FROM table1 WHERE ID IN  1 2 3 4     1000    Use this    SELECT   FROM table1 WHERE ID IN  SELECT rownum AS ID FROM dual connect BY level  lt   1000     Note that you need to be sure the ID does not refer any other foreign IDS if this is a dependency  To ensure only existing ids are available then     SELECT   FROM table1 WHERE ID IN  SELECT distinct ID  FROM tablewhereidsareavailable    Cheers

[oracle] How to put more than 1000 values into an Oracle IN clause

Examples related to oracle

Examples related to in-clause