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
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.
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.
Some workaround solutions are:
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);
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));
Select id from x where id in (select id from <temporary-table>);
**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/
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.
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.
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...)
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)
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 ...;
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.
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:
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.
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.
Source: Stackoverflow.com