Existe-t-il une solution de contournement pour ORA-01795: le nombre maximal d’expressions dans une liste est de 1000 erreurs?

Y a-t-il une solution de contournement pour

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

J’ai une requête et sélectionne des champs en fonction de la valeur d’un champ. J’utilise la clause in et il y a 10000+ valeurs

Exemple:

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

Chaque fois que j’exécute la requête, je reçois le ORA-01795: maximum number of expressions in a list is 1000 error . J’essaie d’exécuter la requête dans TOAD, pas de différence, la même erreur. Comment modifier la requête pour la faire fonctionner?

Merci d’avance

Utilisez simplement plusieurs clauses pour contourner ce problème:

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

Je suis tombé sur ce problème récemment et j’ai trouvé une façon effrontée de le faire sans enchaîner les clauses IN supplémentaires

Vous pourriez utiliser des tuples

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

Oracle permet> 1000 Tuples mais pas de valeurs simples. Plus à ce sujet ici,

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

C’est bien sûr si vous n’avez pas la possibilité d’utiliser une sous-requête dans IN pour obtenir les valeurs dont vous avez besoin dans une table temporaire.

Certaines solutions de contournement sont les suivantes:

1- Diviser la clause IN en plusieurs clauses IN où les littéraux sont inférieurs à 1000 et les combiner à l’aide de clauses OR:

Divisez la clause “WHERE” d’origine d’une condition “IN” à plusieurs conditions “IN”:

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

2- Utilisation de tuples: la limite de 1 000 s’applique aux ensembles d’éléments uniques: (x) IN ((1), (2), (3), …). Il n’y a pas de limite si les ensembles contiennent deux éléments ou plus: (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- Utilisation de la table temporaire:

 Select id from x where id in (select id from ); 

Veuillez utiliser une requête interne à l’intérieur de la clause In-Clause:

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

Une autre façon:

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

Je ne considère pas que c’est optimal, mais ça marche. Le conseil /*+ CARDINALITY(...) */ serait très utile car Oracle ne comprend pas la cardinalité du tableau passé et ne peut pas estimer un plan d’exécution optimal.

Comme autre alternative – insérer par lots dans une table temporaire et en utilisant la dernière sous-requête pour le prédicat IN .

il existe également un autre moyen de résoudre ce problème. Disons que vous avez deux tables Table1 et Table2. et il est nécessaire d’extraire toutes les entrées de Table1 non référencées / présentes dans Table2 en utilisant la requête Critères. Alors allez-y comme ça …

 List list=new ArrayList(); Criteria cr=session.createCriteria(Table1.class); cr.add(Ressortingctions.sqlRessortingction("this_.id not in (select t2.t1_id from Table2 t2 )")); . . 

. . . Il effectuera toute la fonction de sous-requête directement en SQL sans inclure plus de 1000 parameters dans SQL convertis par le framework Hibernate. Cela a fonctionné pour moi. Remarque: vous devrez peut-être modifier la partie SQL en fonction de vos besoins.

Je me rends compte que c’est une vieille question et se référant à TOAD mais si vous avez besoin de coder en utilisant c # vous pouvez diviser la liste en une boucle for. Vous pouvez essentiellement faire la même chose avec Java en utilisant la sous-liste ();

  List
allAddresses = GetAllAddresses(); List employees = GetAllEmployees(); // count > 1000 List
addresses = new List
(); 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); }

J'espère que cela aide quelqu'un.

Union operato

 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) 

Il existe une autre option: with syntaxe. Pour utiliser l’exemple des OP, cela ressemblerait à ceci:

 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; 

J’ai rencontré ce problème. Dans mon cas, j’avais une liste de données en Java où chaque élément avait un item_id et un customer_id. J’ai deux tables dans la firebase database avec des abonnements aux articles clients respectifs. Je souhaite obtenir une liste de tous les abonnements aux articles ou au client pour cet article, ainsi que l’identifiant de l’article.

J’ai essayé trois variantes:

  1. Plusieurs sélections à partir de Java (utilisation de tuples pour contourner la limite)
  2. Avec syntaxe
  3. Table temporaire

Option 1: plusieurs sélections à partir de Java

Fondamentalement, j’ai d’abord

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

alors

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

Ensuite, je construis une carte en Java avec cus_id comme clé et une liste d’éléments en tant que valeur, et pour chaque abonnement client trouvé, j’ajoute (à la liste renvoyée par la première sélection) une entrée pour tous les éléments pertinents avec cet item_id. C’est beaucoup plus de code

Option 2: avec syntaxe

Obtenez tout à la fois avec un SQL comme

 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: table temporaire

Créez une table temporaire globale avec trois champs: rownr (clé primaire), item_id et cus_id. Insérez toutes les données, puis exécutez une sélection très similaire à l’option 2, mais en liant la table temporaire à la place with data

Performance

Ce n’est pas une parsing de performance entièrement scientifique.

  • Je cours sur une firebase database de développement, avec un peu plus de 1 000 lignes dans mon dataset pour lesquelles je souhaite trouver des abonnements.
  • J’ai seulement essayé un jeu de données.
  • Je ne suis pas dans le même emplacement physique que mon serveur de firebase database. Ce n’est pas si loin, mais je remarque que si j’essaie de chez moi sur le VPN, c’est beaucoup plus lent, même si c’est la même distance (et ce n’est pas mon problème à la maison).
  • Je testais l’appel complet, donc mon API en appelle un autre (fonctionnant également dans la même instance en dev), qui se connecte également à la firebase database pour obtenir le jeu de données initial. Mais c’est la même chose dans les trois cas.

YMMV.

Cela dit, l’option de table temporaire était beaucoup plus lente. Comme en double si lent. Je prenais 14-15 secondes pour l’option 1, 15-16 pour l’option 2 et 30 pour l’option 3.

Je vais les réessayer sur le même réseau que le serveur de firebase database et vérifier si cela change les choses lorsque j’en ai l’occasion.