Comment mettre plus de 1000 valeurs dans une clause Oracle IN

Existe-t-il un moyen de contourner la limitation Oracle 10g de 1 000 éléments dans une clause IN statique? J’ai une liste délimitée par des virgules de nombreux ID que je souhaite utiliser dans une clause IN. Parfois, cette liste peut dépasser 1 000 éléments. Oracle génère alors une erreur. La requête est similaire à celle-ci …

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

Placez les valeurs dans une table temporaire, puis sélectionnez select id (sélectionnez id de temptable)

Je suis presque sûr que vous pouvez diviser les valeurs sur plusieurs entrées IN en utilisant OU:

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

Vous pouvez essayer d’utiliser le formulaire suivant:

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

Où trouvez-vous la liste des identifiants en premier lieu? S’agissant d’ID dans votre firebase database, sont-ils issus d’une requête précédente?

Quand j’ai vu cela par le passé, c’est parce que: –

  1. une table de référence est manquante et la manière correcte serait d’append la nouvelle table, de mettre un atsortingbut sur cette table et d’y joindre
  2. une liste d’identifiants est extraite de la firebase database, puis utilisée dans une instruction SQL ultérieure (peut-être plus tard ou sur un autre serveur ou autre). Dans ce cas, la réponse est de ne jamais l’extraire de la firebase database. Soit stocker dans une table temporaire ou simplement écrire une requête.

Je pense qu’il y a peut-être de meilleures façons de retravailler ce code pour que cette instruction SQL fonctionne. Si vous fournissez plus de détails, vous pourriez avoir des idées.

Utilisez … de la table (…:

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

C’est l’un des rares cas où vous avez besoin d’un indice, sinon Oracle n’utilisera pas l’index sur l’ID de colonne. L’un des avantages de cette approche est qu’Oracle n’a pas besoin d’parsingr la requête encore et encore. L’utilisation d’une table temporaire est la plupart du temps plus lente.

edit 1 simplifié la procédure (grâce à jimmyorr) + exemple

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

Exemple:

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

Cela va sortir:

 Raymond Hans 

J’ai fini par chercher une solution aussi.

Selon le nombre élevé d’éléments à interroger, et en supposant que vos éléments sont uniques, vous pouvez diviser votre requête en requêtes par lots de 1 000 éléments et combiner les résultats à votre place (pseudocode ici):

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

Cela peut être un bon compromis dans le cas où vous ne disposez généralement pas de plus de 1 000 éléments, car le fait d'avoir plus de 1 000 éléments serait votre scénario de pointe. Par exemple, si vous avez 1500 éléments, deux requêtes de (1000, 500) ne seraient pas si mauvaises. Cela suppose également que chaque requête n'est pas particulièrement coûteuse.

Cela ne serait pas approprié si votre nombre habituel d'éléments attendus était beaucoup plus important, par exemple dans la plage des 100 000, nécessitant 100 requêtes. Si c'est le cas, alors vous devriez probablement envisager plus sérieusement d'utiliser la solution de tables temporaires globale fournie ci-dessus comme la solution la plus "correcte". De plus, si vos éléments ne sont pas uniques, vous devrez également résoudre les doublons.

Oui, situation très étrange pour oracle.

Si vous spécifiez 2000 ID dans la clause IN, cela échouera. cela échoue:

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

mais si vous mettez simplement les identifiants 2000 dans une autre table (table temporaire par exemple), cela fonctionnera:

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

Ce que vous pouvez faire, en fait, pourrait diviser les enregistrements en 1000 enregistrements et les exécuter par groupe.

Au lieu d’utiliser la clause IN , pouvez-vous essayer d’utiliser JOIN avec l’autre table, qui récupère l’ID. De cette façon, nous n’avons pas à nous soucier de la limite. juste une pensée de mon côté.

Voici un code Perl qui tente de contourner la limite en créant une vue en ligne et en la sélectionnant. Le texte de l’instruction est compressé en utilisant des lignes de douze éléments chacune au lieu de sélectionner chaque élément de DUAL individuellement, puis décompressé en réunissant toutes les colonnes. UNION ou UNION ALL dans la décompression ne devrait faire aucune différence ici car tout se passe à l’intérieur d’un IN qui imposera l’unicité avant de se joindre à lui de toute façon, mais dans la compression, UNION ALL est utilisé pour éviter beaucoup de comparaisons inutiles. Comme les données sur lesquelles je filtre sont des nombres entiers, les citations ne sont pas un problème.

 # # 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 } 

On utiliserait ça comme ça:

 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 

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

Utilisez ceci :

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

* Notez que vous devez vous assurer que l'ID ne renvoie aucun autre IDS étranger s'il s'agit d'une dépendance. Pour vous assurer que seuls les identifiants existants sont disponibles, procédez comme suit:

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

À votre santé