Is there a workaround for ORA-01795 maximum number of expressions in a list is 1000 error

Question

Is there a workaround for    ORA-01795  maximum number of expressions in a list is 1000 error   I have a query and it is selecting fields based on the value of one field  I am using the in clause and there are 10000  values   example    select field1  field2  field3  from table1  where name in     value1    value2        value10000        Every time I execute the query I get the ORA-01795  maximum number of expressions in a list is 1000 error  I am trying to execute the query in TOAD  no difference  the same error  How would I modify the query to get it to work    Thanks in advance

User · Answer

Operato union  select   from tableA where tableA Field1 in  1 2    999  union select   from tableA where tableA Field1 in  1000 1001    1999  union select   from tableA where tableA Field1 in  2000 2001    2999

User · Answer

Divide a list to lists of n size        import java util AbstractList      import java util ArrayList      import java util List       public final class PartitionUtil lt T gt  extends AbstractList lt List lt T gt  gt             private final List lt T gt  list          private final int chunkSize           private PartitionUtil List lt T gt  list  int chunkSize                this list   new ArrayList lt  gt  list               this chunkSize   chunkSize                     public static  lt T gt  PartitionUtil lt T gt  ofSize List lt T gt  list  int chunkSize                return new PartitionUtil lt  gt  list  chunkSize                       Override         public List lt T gt  get int index                int start   index   chunkSize              int end   Math min start   chunkSize  list size                  if  start  gt  end                    throw new IndexOutOfBoundsException  Index     index     is out of the list range  lt 0      size   - 1      gt                                return new ArrayList lt  gt  list subList start  end                        Override         public int size                 return  int  Math ceil  double  list size      double  chunkSize                        Function call                  List lt List lt String gt  gt  containerNumChunks   PartitionUtil ofSize list  999    more details  https   e printstacktrace blog divide-a-list-to-lists-of-n-size-in-Java-8

User · Answer

One more way   CREATE OR REPLACE TYPE TYPE TABLE OF VARCHAR2 AS TABLE OF VARCHAR 100   --     SELECT field1  field2  field3   FROM table1   WHERE name IN       SELECT   FROM table  SELECT CAST   AS TYPE TABLE OF VARCHAR2  FROM dual         I don t consider it s optimal  but it works  The hint     CARDINALITY         would be very useful because Oracle does not understand cardinality of the array  passed and can t estimate optimal execution plan   As another alternative - batch insert into temporary table and using the last in subquery for IN predicate

User · Answer

Please use an inner query inside of the in-clause   select col1  col2  col3    from table1  where id in  select id from table2 where conditions

User · Answer

There is another option  with syntax   To use the OPs example  this would look like   with data as     select  value1  name from dual   union all   select  value2  name from dual   union all       select  value10000   name from dual  select field1  field2  field3  from table1 t1 inner join data on t1 name   data name    I ran into this problem   In my case I had a list of data in Java where each item had an item id and a customer id   I have two tables in the DB with subscriptions to items respective customers   I want to get a list of all subscriptions to the items or to the customer for that item  together with the item id   I tried three variants    Multiple selects from Java  using tuples to get around the limit  With-syntax Temporary table   Option 1  Multiple Selects from Java  Basically  I first   select item id  token  from item subs  where  item id  0  in    item id 0  0      item id n  0     Then  select cus id  token  from cus subs  where  cus id  0  in    cus id 0  0      cus id n  0     Then I build a Map in Java with the cus id as the key and a list of items as value  and for each found customer subscription I add  to the list returned from the first select  an entry for all relevant items with that item id   It s much messier code  Option 2  With-syntax  Get everything at once with an SQL like  with data as     select  item id 0 item id   cus id 0 cus id   union all         select  item id n item id   cus id n cus id   select I item id item id  I token token from item subs I inner join data D on I item id   D item id union all select D item id item id  C token token from cus subs C inner join data D on C cus id   D cus id   Option 3  Temporary table  Create a global temporary table with three fields  rownr  primary key   item id and cus id   Insert all the data there then run a very similar select to option 2  but linking in the temporary table instead of the with data  Performance  This is not a fully-scientific performance analysis      I m running against a development database  with slightly over 1000 rows in my data set that I want to find subscriptions for    I ve only tried one data set    I m not in the same physical location as my DB server   It s not that far away  but I do notice if I try from home over the VPN then it s all much slower  even though it s the same distance  and it s not my home internet that s the problem     I was testing the full call  so my API calls another  also running in the same instance in dev  which also connects to to the DB to get the initial data set   But that is the same in all three cases    YMMV   That said  the temporary table option was much slower   As in double so slow   I was getting 14-15 seconds for option 1  15-16 for option 2 and 30 for option 3   I ll try them again from the same network as the DB server and check if that changes things when I get the chance

User · Answer

Just use multiple in-clauses to get around this   select field1  field2  field3 from table1  where  name in   value1    value2         value999        or name in   value1000         value1999        or

User · Answer

There s also workaround doing disjunction of your array  worked for me as other solutions were hard to implement using some old framework   select   from tableA where id   1 or id   2 or id   3       But for better perfo  I would use Nikolai Nechai s solution with unions  if possible

User · Answer

there is also another way to resolve this issue  lets say you have two tables Table1 and Table2  and it is required to fetch all entries of Table1 not referred present in Table2 using Criteria query  So go ahead like this     List list new ArrayList     Criteria cr session createCriteria Table1 class   cr add Restrictions sqlRestriction  this  id not in  select t2 t1 id from Table2 t2                   It will perform all the subquery function directly in SQL without including 1000 or more parameters in SQL converted by Hibernate framework  It worked for me  Note  You may need to change SQL portion as per your requirement

User · Answer

I realize this is an old question and referring to TOAD but if you need to code around this using c  you can split up the list through a for loop   You can essentially do the same with Java using subList         List lt Address gt  allAddresses   GetAllAddresses        List lt Employee gt  employees   GetAllEmployees       count  gt  1000      List lt Address gt  addresses   new List lt Address gt          for  int i   0  i  lt  employees Count  i    1000                int count     employees Count - i   lt  1000     employees Count - i  - 1   1000          var query    from address in allAddresses                      where employees GetRange i  count  Contains address EmployeeId                        amp  amp  address State     UT                       select address  ToList             addresses AddRange query           Hope this helps someone

User · Answer

Some workaround solutions are   1  Split up IN clause  Split IN clause to multiple IN clauses where literals are less than 1000 and combine them using OR clauses    Split the original  WHERE  clause from one  IN  condition to several  IN  condition   Select id from x where id in  1  2       1000     1500     To   Select id from x where id in  1  2       999  OR id in  1000     1500     2  Use tuples  The limit of 1000 applies to sets of single items   x  IN   1    2    3         There is no limit if the sets contain two or more items   x  0  IN   1 0    2 0    3 0          Select id from x where  x id  0  IN   1  0    2  0    3  0        n  0      3  Use temporary table  Select id from x where id in  select id from  lt temporary-table gt

User · Answer

I ran into this issue recently and figured out a cheeky way of doing it without stringing together additional IN clauses  You could make use of Tuples  SELECT field1  field2  field3 FROM table1 WHERE  1  name  IN   1  value1    1  value2    1  value3        1  value5000      Oracle does allow  1000 Tuples but not simple values  More on this here    https   community oracle com message 3515498 3515498 and https   community oracle com thread 958612    This is of course if you don t have the option of using a subquery inside IN to get the values you need from a temp table

[sql] Is there a workaround for ORA-01795: maximum number of expressions in a list is 1000 error?

Examples related to sql

Examples related to toad