Discussion :

[isa06]

Bonjour
Nous avons un utilisateur qui veut passer des listes de +500 valeurs dans un IN

J'ai comme souvenir qu'il est nefaste de mettre dans une query un IN avec une immense liste de valeurs, mais je n'arrive plus a me souvenir pourquoi ni a trouver d'explication dans les docs et les forums (ca me paraissait pourtant simple a denicher...)
Je pense qu'il vaut mieux utiliser une table et faire une subquery.

Quelqu'un peut-il confirmer ou infirmer mon souvenir
et m'expliquer le fonctionnement de l'optimiseur dans les 2 cas (que la colonne soit indexee ou pas) ?

Merci
Isa

[mnitu]

Il y a une limite des 1000 valeurs dans la liste.
La requête qui utilise IN avec la liste des valeurs est transformée pour utiliser des OR.
Vous pouvez faire un test mais il y a peu des chances que la solution qui implique remplir une table temporaire pour ensuite l’utiliser dans la sous-requête se comporte mieux que la solution qui utilise le IN.

[ojo77]

Dans tous les cas, l'optimiseur va essayer de réécrire votre requête

Avec index :
Dans le cas colonne1 IN ( liste de valeurs )
La sélectivité de colonne1 sera, s'il n'y a pas d'histogramme, égale à la sélectivité moyenne d'une valeur multiplié par le nombre de valeurs, plus le nombre de valeurs est grand, plus la sélectivité va pousser vers le FTS.

S'il y a un histogramme, ça se complique un poil ... comme on a potentiellement plus de 254 valeurs il s'agira d'un histogramme recensant les valeurs populaires et non toutes les valeurs possibles. Soit la liste contient des valeurs populaires, soit elle n'en contient pas. Si elle en contient encore une fois on se dirige vers le FTS. Si elle n'en contient pas on se retrouve comme dans la situation où il n'y a pas d'histogramme avec un sélectivité individuelle beaucoup plus faible pour les valeurs non populaires.

Sans Index
Pas de choix => FTS

On s'oriente donc très probablement vers set FTS + FILTER ou au minimum un IFS + table access by rowid + FILTER

Dans le cas colonne1 IN ( select ... )
Les mêmes règles s'appliquent a l'exception que la partie FILTER peut être remplacée par un HASH JOIN ou un SORT MERGE JOIN

Au final je rejoins la conclusion de mnitu, mais il faut tester

[CinePhil]

Comment est consituée la liste des valeurs du IN ?
Si c'est un ensemble de valeurs prises dans une autre table répondant à une certaine conditions, alors il doit être possible de faire une jointure plutôt que le IN. Ça devrait être beaucoup plus rapide.

[mnitu]

...Ça devrait être beaucoup plus rapide.

Avez-vous regarde le plan d’exécution pour les deux requêtes : In vers Join ? Regardez-les et vous aurez une belle surprise.

[Rei Ichido]

Avez-vous regarde le plan d’exécution pour les deux requêtes : In vers Join ? Regardez-les et vous aurez une belle surprise.

Une fois le plan d'exécution fait, je pense aussi que ça doit dépendre des cas, mais qu'en général le IN gagne.
Par contre, ce qui est sûr, c'est si la condition varie, la requête à base de IN a toutes les chances de devoir être reparsée à chaque fois (même si on passe tout en Bind, une valeur de plus ou de moins = nouvelle requête), ce qui peut être pénalisant du point de vue performance s'il s'agit d'une requête rapide exécutée un grand nombre de fois ! Auquel cas il serait préférable de passer par une jointure pour laquelle on aurait un plan unique.

[mnitu]

Une fois le plan d'exécution fait, je pense aussi que ça doit dépendre des cas, mais qu'en général le IN gagne.
Par contre, ce qui est sûr, c'est si la condition varie, la requête à base de IN a toutes les chances de devoir être reparsée à chaque fois (même si on passe tout en Bind, une valeur de plus ou de moins = nouvelle requête), ce qui peut être pénalisant du point de vue performance s'il s'agit d'une requête rapide exécutée un grand nombre de fois ! Auquel cas il serait préférable de passer par une jointure pour laquelle on aurait un plan unique.

J’ai l’impression qu’on se comprend mal. Néanmoins, en supposant que vous avez construis votre requête de la façon suivante

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
Select e.first_name
  from hr.employees e
 Where e.employee_id In (:b0, :b1, :b2, ... :b500)

Rien ne vous oblige à réparser votre requête chaque fois que le nombre des valeurs "utiles" change, passez null pour les variable non "utiles".

[Rei Ichido]

En effet, c'est une solution toute simple que je n'avais pas envisagée !
Il faudrait alors aller jusqu'à 1000, pour traiter tous les cas possibles.

[mnitu]

En pratique ça sera plutôt un compromis entre avoir une seule requête pour tous les cases d'utilisations possible et avoir plusieurs requêtes, chacune adaptée à un cas d’utilisation. Supposons que vous avez dans l’interface utilisateur la possibilité de choisir entre 1 et 50 "articles" différente. Vous pouvez envisager 5 requêtes qui utilisent chacune entre 1 et 10 variables de liaison, entre 1 et 20, entre 1 et 30 etc.