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