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?

Whirly

put value1....value1000+ in a table and select name in (select value from table) – Denature

The error is not dependant on the environment (eg SQL*Plus or TOAD or ...) where you execute your query. – Advection

Answer 1

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

Kalif

Keep in mind that if you are wanting to use NOT IN logic you need to AND these statements together – Evaleen

Yes, this seems to be the best answer and should be tickted. Is important to note that the limitation is to more than 1000 items in the list, splitting them In various lists is a clever solution and usefull for me. – Warranty

This solution worked well for me, but it is probably not the most scalable solution. – Gerhan

I am using some helper functions to generate the SQL strings. Splitting into multiple lists makes this messier (I guess you need brackets to be on the safe side and group it as a single clause), but so does tuples. Does anyone know if there is a performance difference between the various options – Projector

How to do it in perl? Can anyone help me with this question? #62507805 – Yerga

@Adam, which helper methods are you using? – Crissycrist

@rahuls - I wrote my own :)They are related to how we generate the query strings. E.g. one which takes an enumerable and returns the string for the where clause and another that takes the query object and same enumerable and binds the values – Projector

Using Excel's MOD function, you can place your list in Excel and build a SQL statement that places "OR Name..." statement every 1000 rows. – Lupita

1st Col: '202301180000147', 2nd Col: =IF(MOD(ROW(A1001),1000)=0,"OR REFERENCE IN (" &B3,CHAR(39)&A1001&CHAR(39)&",") 3rd Col: =IF((OR(LEFT(C1001,2)="OR",LEN(TRIM(C1001))<2)),LEFT(C1000,LEN(C1000)-1)&")",C1000) – Lupita

Answer 2

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 <temporary-table>);

Hausmann

Nice summary. Do you know if there is a performance difference between the various options? – Projector

I have my data in a list in Java. I'm wondering about using a with clause: with foo as (select :foo_1 id from dual union all ... select foo_n id from dual) select * from bar inner join foo on bar.id = foo.id as an alternative to creating temporary tables for each query. Any comments? – Projector

2 is great, it saved my ass many times. 0 is even better than 'magic' in stackoverflow.com/a/17019130 – Dews

Answer 3

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.

Biddick

Answer 4

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

Sitzmark

Possible use inner join, it significantly sped up the select in our case (8 seconds vs 50 ms). – Numb

That assumes your data for the where clause is in another table in the same DB, and you know the select to get it! Not always true – Projector

Answer 5

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.

Faqir

Answer 6

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:

1. Multiple selects from Java (using tuples to get around the limit)
2. With-syntax
3. 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.

Projector

Answer 7

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<Address> allAddresses = GetAllAddresses();
    List<Employee> employees = GetAllEmployees(); // count > 1000

    List<Address> addresses = new List<Address>();

    for (int i = 0; i < employees.Count; i += 1000)
    {
        int count = ((employees.Count - i) < 1000) ? (employees.Count - i) - 1 : 1000;
        var query = (from address in allAddresses
                     where employees.GetRange(i, count).Contains(address.EmployeeId)
                     && address.State == "UT"
                     select address).ToList();

        addresses.AddRange(query);
    }

Hope this helps someone.

Bozuwa

Answer 8

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.

Frankel

Answer 9

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)

Jut

This is the best solution as it boost the performance. Just use "UNION ALL" instead of "UNION" to gain max performance. – Pacien

Answer 10

    **Divide a list to lists of n size**

    import java.util.AbstractList;
    import java.util.ArrayList;
    import java.util.List;

    public final class PartitionUtil<T> extends AbstractList<List<T>> {

        private final List<T> list;
        private final int chunkSize;

        private PartitionUtil(List<T> list, int chunkSize) {
            this.list = new ArrayList<>(list);
            this.chunkSize = chunkSize;
        }

        public static <T> PartitionUtil<T> ofSize(List<T> list, int chunkSize) {
            return new PartitionUtil<>(list, chunkSize);
        }

        @Override
        public List<T> get(int index) {
            int start = index * chunkSize;
            int end = Math.min(start + chunkSize, list.size());

            if (start > end) {
                throw new IndexOutOfBoundsException("Index " + index + " is out of the list range <0," + (size() - 1) + ">");
            }

            return new ArrayList<>(list.subList(start, end));
        }

        @Override
        public int size() {
            return (int) Math.ceil((double) list.size() / (double) chunkSize);
        }
    }





Function call : 
              List<List<String>> containerNumChunks = PartitionUtil.ofSize(list, 999)

more details: https://e.printstacktrace.blog/divide-a-list-to-lists-of-n-size-in-Java-8/

Down

The question is about SQL, not Java. How does this answer the question? – Capacitate

In Java, we can resolve this problem by the above solution and any programming language it's a way of solution – Down

Answer 11

Use Tuple :

Let's suppose input is :

List<Long> userIdList = Arrays.asList(100L,200L,300L);

StringBuilder tuple = new StringBuilder();
for(Long userId : userIdList) {
   tuple.append("(1,").append(userId).append("),");
}
tuple.deleteCharAt(tuple.length()-1);

Output will be : (1,100),(1,200),(1,300)

And we can pass this to below query like this (And YES, we can pass more than 1000 elements):

SELECT * FROM MyTable WHERE (1, USR_ID) IN ((1,100),(1,200),(1,300));

Decolorize

Answer 12

ORA-01795: maximum number of expressions in a list is 1000.

Issue: When user selects long list of values (greate or qual to 1000 values/expression) for IN/OR list of where clause, system throws error: “ORA-01795: maximum number of expressions in a list is 1000”

Root Cause: Oracle IN / OR list has limit of 1000 (actually its 999) number of expression/value list.

Proposed Solution: You need to split the list of expressions into multiple sets (using OR) and each should be less than 1000 list/expressoin combine using IN / Or list.

Example: Suppose you have a table ABC with column ZIP of CLOB type and table contains more than 1000 rows.

You need to break them in multiple list, like shown below:

( ZIP IN (1,2,3,.........N999) OR ZIP IN (1000,1001,......N999) ..... ..... )

Deepdyed

Answer 13

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.

Totipalmate

Answer 14

Pass the list and the number of records needs to return in the loop most cases = 999.

List<List<Long>> getSubLists = batchList(inputList, 999);
List<Long> newList = new ArrayList<>();
for (List<Long> subSet : getSubLists) { newList.addALL(daoCall) // add in the required list in loop }

 public static <T> List<List<T>> batchList(List<T> inputList, final int maxSize) {
    List<List<T>> sublists = new ArrayList<>();
    final int size = inputList.size();
    for (int i = 0; i < size; i += maxSize) {
        sublists.add(new ArrayList<>(inputList.subList(i, Math.min(size, i + maxSize))));
    }
    return sublists;
}








  

Hubsher

Answer 15

If you are using Hibernate Query Language(HQL) in your repository class and using IN clause, Oracle database is not going to be let you run your query. Because you are giving more then 1000 value. Oracle in clause limit is 1000. So you cannot give more then 1000 value to your IN clause.

In java, you can use this codes. It's going to be very help full. You can call your services like this, and in this example limit is 750.

Don't write more than 1000. Happy coding.

    import java.util.List;
    
    public class ListUtils {
    
        public static int getPeriodCount(List<?> list, int limit) {
            int size = list.size();
            int leap = size % limit;
            int periodCount = leap != 0 ? (size / limit) + 1 : (size / limit);
            return periodCount;
        }
    }

    public class MainClass {

        private static final int LIMIT = 750;

        private List<GivenObject> getList(List<Integer> numbers) {
            if (CollectionUtils.isEmpty(numbers)) {
                return Collections.emptyList();
            }
            List<Integer> resultList= new ArrayList<>();
            int startIndex = 0;

            int periodCount = ListUtils.getPeriodCount(numbers, LIMIT);

            for (int i = 1; i <= periodCount; i++) {
                List<Integer> collectedNumbers = numbers.stream()
                            .skip(startIndex)
                            .limit(LIMIT)
                            .collect(Collectors.toList());
                resultList.addAll(collectedNumbers);
                startIndex += LIMIT;
           }
           return resultList;
       }
}

Histopathology