Existe-t-il une solution de contournement pour l'erreur ORA-01795 : maximum number of expressions in a list is 1000 ?

Question

Existe-t-il une solution de contournement pour

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

J'ai une requête qui sélectionne des champs en fonction de la valeur d'un champ. J'utilise la clause in et il y a plus de 10 000 valeurs.

exemple :

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

Chaque fois que j'exécute la requête, j'obtiens le message suivant ORA-01795: maximum number of expressions in a list is 1000 error . J'essaie d'exécuter la requête dans TOAD, aucune différence, la même erreur. Comment modifier la requête pour qu'elle fonctionne ?

Merci d'avance

Answer 1

Il suffit d'utiliser plusieurs in-clauses pour contourner ce problème :

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

Answer 2

Voici quelques solutions de contournement :

1. Séparer la clause IN

Divisez la clause IN en plusieurs clauses IN où les littéraux sont inférieurs à 1000 et combinez-les en utilisant des clauses OR :

Divisez la clause "WHERE" originale d'une condition "IN" en plusieurs conditions "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. Utiliser des tuples

La limite de 1000 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. Utiliser une table temporaire

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

Answer 3

J'ai rencontré ce problème récemment et j'ai trouvé un moyen insolent de le faire sans enchaîner les clauses IN supplémentaires.

Vous pouvez utiliser des Tuples

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

Oracle autorise les tuples >1000 mais pas les valeurs simples. Plus d'informations à ce sujet ici,

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

Ceci, 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 à partir d'une table temporaire.

Answer 4

Veuillez utiliser une requête interne à l'intérieur de la in -clause :

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

Answer 5

Encore un moyen :

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 fonctionne. L'indice /*+ CARDINALITY(...) */ serait très utile car Oracle ne comprend pas la cardinalité du tableau passé et ne peut pas estimer le plan d'exécution optimal.

Une autre alternative est l'insertion par lot dans une table temporaire et l'utilisation de la dernière sous-requête pour le traitement des données. IN prédicat.