sql - portable - ¿Existe una solución para ORA-01795: el número máximo de expresiones en una lista es 1000 de error?

toad oracle

Question

¿Hay una solución para

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

Tengo una consulta y selecciona campos en función del valor de un campo. Estoy usando la cláusula in y hay más de 10000 valores

ejemplo:

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

Cada vez que ejecuto la consulta, obtengo el ORA-01795: maximum number of expressions in a list is 1000 error . Estoy intentando ejecutar la consulta en TOAD, no hay diferencia, el mismo error. ¿Cómo modificaría la consulta para que funcione?

Gracias por adelantado

Answer 1

Algunas soluciones alternativas son:

1- Dividir cláusula IN en múltiples cláusulas IN donde los literales son menores que 1000 y combinarlos usando cláusulas OR:

Divida la cláusula original "WHERE" de una condición "IN" a varias condiciones "IN":

Select id from x where id in (1, 2, ..., 1000,…,1500);

A:

Select id from x where id in (1, 2, ..., 999) OR id in (1000,...,1500);

2- Uso de Tuplas: El límite de 1000 se aplica a conjuntos de elementos individuales: (x) IN ((1), (2), (3), ...). No hay límite si los conjuntos contienen dos o más elementos: (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- Uso de la tabla temporal:

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

Answer 2

Hay otra opción: with sintaxis. Para usar el ejemplo de OP, esto se vería así:

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;

Me encontré con este problema. En mi caso, tenía una lista de datos en Java donde cada elemento tenía un item_id y un customer_id. Tengo dos tablas en el DB con suscripciones a los artículos respectivos clientes. Deseo obtener una lista de todas las suscripciones a los artículos o al cliente para ese artículo, junto con la identificación del artículo.

Intenté tres variantes:

1. Múltiples selecciones de Java (usando tuplas para superar el límite)
2. Con sintaxis
3. Tabla temporal

Opción 1: selecciones múltiples de Java

Básicamente, primero

select item_id, token
from item_subs
where (item_id, 0) in ((:item_id_0, 0)...(:item_id_n, 0))

Entonces

select cus_id, token
from cus_subs
where (cus_id, 0) in ((:cus_id_0, 0)...(:cus_id_n, 0))

Luego construyo un Mapa en Java con el cus_id como clave y una lista de elementos como valor, y para cada suscripción de cliente encontrada agrego (a la lista devuelta desde la primera selección) una entrada para todos los elementos relevantes con ese item_id. Es un código mucho más desordenado

Opción 2: sin sintaxis

Obtenga todo a la vez con un SQL como

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

Opción 3: tabla temporal

Cree una tabla temporal global con tres campos: rownr (clave principal), item_id y cus_id. Inserte todos los datos allí y luego ejecute una selección muy similar a la opción 2, pero vinculando en la tabla temporal en lugar de la with data

Actuación

Este no es un análisis de rendimiento completamente científico.

• Me estoy ejecutando en contra de una base de datos de desarrollo, con un poco más de 1000 filas en mi conjunto de datos para las que quiero encontrar suscripciones.
• Solo he probado un conjunto de datos.
• No estoy en la misma ubicación física que mi servidor de base de datos. No está tan lejos, pero me doy cuenta si intento desde mi casa a través de la VPN, entonces todo es mucho más lento, a pesar de que es la misma distancia (y ese no es el problema de Internet de mi casa).
• Estaba probando la llamada completa, por lo que mi API llama a otra (también se ejecuta en la misma instancia en dev) que también se conecta al DB para obtener el conjunto de datos inicial. Pero eso es lo mismo en los tres casos.

YMMV.

Dicho esto, la opción de tabla temporal fue mucho más lenta. Como en el doble tan lento. Recibía 14-15 segundos para la opción 1, 15-16 para la opción 2 y 30 para la opción 3.

Los probaré nuevamente desde la misma red que el servidor de bases de datos y verifico si eso cambia las cosas cuando tengo la oportunidad.

Answer 3

Me doy cuenta de que esta es una vieja pregunta y me estoy refiriendo a TOAD, pero si necesitas codificar esto usando c #, puedes dividir la lista a través de un ciclo for. En esencia, puede hacer lo mismo con Java utilizando 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);
}

Espero que esto ayude a alguien.

Answer 4

Me encontré con este tema recientemente y descubrí una forma descarada de hacerlo sin enredar cláusulas adicionales de IN

Podrías hacer uso de Tuples

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

Oracle permite> 1000 Tuplas pero no valores simples. Más sobre esto aquí,

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

Esto es, por supuesto, si no tiene la opción de utilizar una subconsulta dentro de IN para obtener los valores que necesita de una tabla temporal.

Answer 5

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)

Answer 6

Simplemente use múltiples cláusulas para evitar esto:

select field1, field2, field3 from table1
where name in (''value1'', ''value2'', ..., ''value999'')
or name in (''value1000'', ..., ''value1999'')
or ...;

Answer 7

Una forma más:

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

No lo considero óptimo, pero funciona. La sugerencia /*+ CARDINALITY(...) */ sería muy útil porque Oracle no entiende la cardinalidad de la matriz pasada y no puede estimar el plan de ejecución óptimo.

Como otra alternativa, insertar por lotes en la tabla temporal y usar el último en la subconsulta para el predicado IN .

Answer 8

Utilice una consulta interna dentro de la cláusula in use:

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

Answer 9

también hay otra forma de resolver este problema. digamos que tiene dos tablas Tabla1 y Tabla2. y se requiere obtener todas las entradas de la Tabla 1 no referida / presente en la Tabla 2 usando la consulta Criteria. Así que adelante así ...

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

. . . Realizará todas las funciones de subconsulta directamente en SQL sin incluir 1000 o más parámetros en SQL convertidos por Hibernate framework. Funcionó para mí Nota: Es posible que deba cambiar la porción de SQL según su requisito.