secuencia - Cómo poner más de 1000 valores en una cláusula Oracle IN

ora-32795

Question

Esta pregunta ya tiene una respuesta aquí:

• SQL IN Clause 1000 item limit 4 respuestas

¿Hay alguna forma de evitar la limitación de Oracle 10g de 1000 artículos en una cláusula IN estática? Tengo una lista delimitada por comas de muchos de los ID que quiero usar en una cláusula IN. A veces, esta lista puede exceder los 1000 elementos, momento en el que Oracle arroja un error. La consulta es similar a esto ...

select * from table1 where ID in (1,2,3,4,...,1001,1002,...)

Answer 1

¿De dónde obtienes la lista de identificadores desde el principio? Ya que son identificadores en su base de datos, ¿provienen de alguna consulta previa?

Cuando he visto esto en el pasado, ha sido porque:

1. falta una tabla de referencia y la forma correcta sería agregar la nueva tabla, poner un atributo en esa tabla y unirse a ella
2. una lista de identificadores se extrae de la base de datos y luego se usa en una declaración SQL posterior (quizás más tarde o en otro servidor o lo que sea). En este caso, la respuesta es nunca extraerlo de la base de datos. O almacena en una tabla temporal o simplemente escribe una consulta.

Creo que puede haber mejores formas de volver a trabajar con este código para que esta declaración SQL funcione. Si proporciona más detalles, puede obtener algunas ideas.

Answer 2

Aquí hay un código de Perl que intenta trabajar alrededor del límite creando una vista en línea y luego seleccionando desde allí. El texto de la declaración se comprime mediante el uso de filas de doce elementos cada uno, en lugar de seleccionar cada elemento de DUAL individualmente, y luego se descomprime al unir todas las columnas. UNION o UNION ALL en descompresión no debe hacer ninguna diferencia aquí ya que todo va dentro de un IN que impondrá unicidad antes de unirse contra él de todos modos, pero en la compresión, UNION ALL se usa para evitar una gran cantidad de comparaciones innecesarias. Como los datos que estoy filtrando son todos números enteros, cotizar no es un problema.

#
# generate the innards of an IN expression with more than a thousand items
#
use English ''-no_match_vars'';
sub big_IN_list{
    @_ < 13 and return join '', '',@_;
    my $padding_required = (12 - (@_ % 12)) % 12;  
    # get first dozen and make length of @_ an even multiple of 12
    my ($a,$b,$c,$d,$e,$f,$g,$h,$i,$j,$k,$l) = splice @_,0,12, ( (''NULL'') x $padding_required );

    my @dozens; 
    local $LIST_SEPARATOR = '', ''; # how to join elements within each dozen
    while(@_){
        push @dozens, "SELECT @{[ splice @_,0,12 ]} FROM DUAL"
    };  
    $LIST_SEPARATOR = "/n    union all/n    "; # how to join @dozens 
    return <<"EXP";
WITH t AS (
    select $a A, $b B, $c C, $d D, $e E, $f F, $g G, $h H, $i I, $j J, $k K, $l L FROM     DUAL
    union all
    @dozens
 )
select A from t union select B from t union select C from t union
select D from t union select E from t union select F from t union
select G from t union select H from t union select I from t union 
select J from t union select K from t union select L from t
EXP
}

Uno lo usaría así:

my $bases_list_expr = big_IN_list(list_your_bases());
$dbh->do(<<"UPDATE");
    update bases_table set belong_to = ''us''
    where whose_base in ($bases_list_expr)
UPDATE

Answer 3

Coloque los valores en una tabla temporal y luego seleccione select id en (seleccione id de temptable)

Answer 4

En lugar de SELECT * FROM table1 WHERE ID IN (1,2,3,4,...,1000);

Utilizar esta :

SELECT * FROM table1 WHERE ID IN (SELECT rownum AS ID FROM dual connect BY level <= 1000);

* Tenga en cuenta que debe asegurarse de que el ID no haga referencia a ningún otro IDS extranjero si se trata de una dependencia. Para garantizar que solo estén disponibles los ID existentes, entonces:

SELECT * FROM table1 WHERE ID IN (SELECT distinct(ID) FROM tablewhereidsareavailable);

Aclamaciones

Answer 5

En lugar de usar la cláusula IN , ¿puedes intentar usar JOIN con la otra tabla, que está buscando el id. de esa manera no tenemos que preocuparnos por el límite. solo un pensamiento de mi parte

Answer 6

Estoy casi seguro de que puede dividir los valores en varias entradas usando OR:

select * from table1 where ID in (1,2,3,4,...,1000) or 
ID in (1001,1002,...,2000)

Answer 7

Puede intentar usar el siguiente formulario:

select * from table1 where ID in (1,2,3,4,...,1000)
union all
select * from table1 where ID in (1001,1002,...)

Answer 8

Sí, una situación muy extraña para el oráculo.

si especifica 2000 ids dentro de la cláusula IN, fallará. esto falla:

select ... 
where id in (1,2,....2000) 

pero si simplemente pone los 2000 ID en otra tabla (tabla temporal por ejemplo), funcionará esto funciona:

select ... 
where id in (select userId 
             from temptable_with_2000_ids ) 

lo que puede hacer, en realidad podría dividir los registros en muchos 1000 registros y ejecutarlos grupo por grupo.

Answer 9

Terminé aquí buscando una solución también.

Dependiendo de la cantidad de artículos de alta calidad que necesite consultar, y suponiendo que sus artículos son únicos, puede dividir su consulta en lotes de 1000 artículos y combinar los resultados en su extremo (pseudocódigo aquí):

//remove dupes
items = items.RemoveDuplicates();

//how to break the items into 1000 item batches        
batches = new batch list;
batch = new batch;
for (int i = 0; i < items.Count; i++)
{
    if (batch.Count == 1000)
    {
        batches.Add(batch);
        batch.Clear()
    }
    batch.Add(items[i]);
    if (i == items.Count - 1)
    {
        //add the final batch (it has < 1000 items).
        batches.Add(batch); 
    }
}

// now go query the db for each batch
results = new results;
foreach(batch in batches)
{
    results.Add(query(batch));
}

Esto puede ser una buena compensación en el escenario en el que normalmente no tiene más de 1000 elementos, ya que tener más de 1000 elementos sería su escenario de "extremo" extremo. Por ejemplo, en el caso de que tenga 1500 artículos, dos consultas de (1000, 500) no serían tan malas. Esto también supone que cada consulta no es particularmente costosa por sí misma.

Esto no sería apropiado si su número típico de artículos esperados fuera mucho mayor, por ejemplo, en el rango 100000, y requiriera 100 consultas. Si es así, entonces probablemente debería considerar con más seriedad el uso de la solución global de tablas temporales proporcionada anteriormente como la solución más "correcta". Además, si sus artículos no son únicos, también deberá resolver los resultados duplicados en sus lotes.

Answer 10

Usar ... de la mesa (...:

create or replace type numbertype
as object
(nr number(20,10) )
/ 

create or replace type number_table
as table of numbertype
/ 

create or replace procedure tableselect
( p_numbers in number_table
, p_ref_result out sys_refcursor)
is
begin
  open p_ref_result for
    select *
    from employees , (select /*+ cardinality(tab 10) */ tab.nr from table(p_numbers) tab) tbnrs 
    where id = tbnrs.nr; 
end; 
/ 

Este es uno de los pocos casos en los que necesita una pista, de lo contrario, Oracle no usará el índice en la identificación de la columna. Una de las ventajas de este enfoque es que Oracle no necesita analizar la consulta una y otra vez. El uso de una tabla temporal es la mayoría de las veces más lento.

editar 1 simplificó el procedimiento (gracias a jimmyorr) + ejemplo

create or replace procedure tableselect
( p_numbers in number_table
, p_ref_result out sys_refcursor)
is
begin
  open p_ref_result for
    select /*+ cardinality(tab 10) */ emp.*
    from  employees emp
    ,     table(p_numbers) tab
    where tab.nr = id;
end;
/

Ejemplo:

set serveroutput on 

create table employees ( id number(10),name varchar2(100));
insert into employees values (3,''Raymond'');
insert into employees values (4,''Hans'');
commit;

declare
  l_number number_table := number_table();
  l_sys_refcursor sys_refcursor;
  l_employee employees%rowtype;
begin
  l_number.extend;
  l_number(1) := numbertype(3);
  l_number.extend;
  l_number(2) := numbertype(4);
  tableselect(l_number, l_sys_refcursor);
  loop
    fetch l_sys_refcursor into l_employee;
    exit when l_sys_refcursor%notfound;
    dbms_output.put_line(l_employee.name);
  end loop;
  close l_sys_refcursor;
end;
/

Esto dará como resultado:

Raymond
Hans

Answer 11

select column_X, ... from my_table
where (''magic'', column_X ) in (
        (''magic'', 1),
        (''magic'', 2),
        (''magic'', 3),
        (''magic'', 4),
             ...
        (''magic'', 99999)
    ) ...