Is there a fix for ORA-01795: maximum number of expressions in a list is 1000?

Problem

Is there a way to get around this?

‘ORA-01795: There are a limit of 1000 expressions in a list error’

I have a query that selects fields depending on a single field’s value. I’m using the in clause, and there are over ten thousand values.

example:

select field1, field2, field3 
from table1 
where name in 
(
'value1',
'value2',
...
'value10000+'
);

I receive the ORA-01795: maximum number of expressions in a list is 1000 error every time I run the query. I tried running the query in TOAD, but it gave me the same issue. What changes would I make to the query to make it work?

Thanks in advance

Asked by valmont74

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

Answered by Fabian Barney

Solution #2

Some workaround solutions are:

Where literals are less than 1000, split the IN clause into numerous IN clauses and merge them using OR clauses:

Split the original “WHERE” clause into many “IN” conditions as follows:

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

The 1000 restriction applies to single item sets: (x) IN ((1), (2), (3),…). If the sets contain two or more items, there is no limit: (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));

Select id from x where id in (select id from <temporary-table>);

Answered by Ahmed MANSOUR

Solution #3

I recently went into this problem and came up with a clever approach to solve it without having to utilize several IN clauses.

Tuples could be helpful.

SELECT field1, field2, field3
FROM table1
WHERE (1, name) IN ((1, value1), (1, value2), (1, value3),.....(1, value5000));

>1000 Tuples are allowed in Oracle, but simple values are not. More on this can be found here.

https://community.oracle.com/message/3515498#3515498 and https://community.oracle.com/thread/958612

If you don’t have the option of utilizing a subquery inside IN to acquire the values you require from a temp table, this is the way to go.

Answered by MangoCrysis

Solution #4

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 think it’s ideal, but it gets the job done. Because Oracle does not grasp the cardinality of the array supplied, the suggestion /*+ CARDINALITY(…) */ would be really useful.

Another option is to batch insert into a temporary table and use the IN predicate for the final in subquery.

Answered by svaor

Solution #5

Inside the in-clause, please use an inner query:

select col1, col2, col3... from table1
 where id in (select id from table2 where conditions...)

Answered by Vikas Kumar

Post is based on https://stackoverflow.com/questions/17842453/is-there-a-workaround-for-ora-01795-maximum-number-of-expressions-in-a-list-is