[9i] Not in ou not exists

**billout9** · 19/06/2007, 15h03

Bonjour,
j'ai une requête SQL avec une sous-requête imbriquée par un prédicat not in qui est extrémement longue (plusieurs heures).
J'ai modifié cette requête SQL pour utiliser le prédicat not exists et le temps d'exécution a a baissé considérablement.
Pourriez-vous m'expliquer la différence entre ces 2 prédicats ?

Merci d'avance.

PS : Je suis sur une base Oracle version 9i.

**lsaid** · 19/06/2007, 15h33

Oui pour nous aussi, c'est conseillé d'utiliser NOT EXISTS au lieu de NOT IN (temps d'exécution).

En fait, parmi les raisons c’est que l’utilisation de NOT IN empêche Oracle d'utiliser les indexs existants.

**Bruno2r** · 25/07/2007, 22h51

Bonsoir,
Je trouve logique qu'il soit plus rapide de vérifier au travers d'une équijointure que chaque valeur de A est trouvée ou pas (en utilisant effectivement l'index dès qu'il existe)
plutôt que ramener la liste de toutes les valeurs de B au travers de la clause IN (SELECT ....)
puis de parcourir cete liste (non indexée)
pour vérifier chaque valeur de la table A en est absente
(d'autant que par définition il faut parcourir toutes les valeurs de la liste B pour chaque valeur de A avant de savoir ... qu'elle ne s'y trouve pas !)

**rbaraer** · 26/07/2007, 09h30

Nous avons fait le même constat : NOT EXISTS est généralement beaucoup plus rapide.

De plus, j'utilise désormais EXISTS au lieu de IN car je me suis fait piéger une fois par le fait que la valeur que je cherchais apparaissait bien dans le SELECT que j'avais dans le IN, mais comme le SELECT retournait une valeur NULL parmi d'autres valeurs non NULL, je ne récupérait pas mon enregistrement !

Dès qu'une valeur NULL apparait dans une clause IN, celle-ci renvoie faux, ce qui est certainement dû au fait que rien n'est égal à NULL, mais c'est un peu déroutant !

rbaraer

**wang_xue** · 23/10/2007, 21h53

Pour la différence entre NOT EXISTE et NOT IN,je trouve NOT EXISTE qui renvoie un valeur false ou true,s'il satisfait la clause where,il va donner un résultat direct,mais NOT IN qu'il dois toutes les tuple et vérifient s'il correspond la condition ou non,donc si votre table est long,il va éxecuter long,si votre table est simple,il va faire vite.

**mnitu** · 25/10/2007, 19h40

A une époque on disait que si la table extérieure contient beaucoup des enregisterements et la table intérieure avait peu, le "In" peut être plus performant dans le cas contraire c'est le "Exists" qui l'est. Mais voila les résultats sur une base Oracle 10

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
 
select count(*)
  from big
 where object_id not in ( select object_id from small )
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.29       0.30          0        890          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.29       0.33          0        890          0           1
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 62  
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=890 pr=0 pw=0 time=301969 us)
 397840   HASH JOIN RIGHT ANTI (cr=890 pr=0 pw=0 time=1594361 us)
     99    INDEX FULL SCAN SMALL_IDX (cr=1 pr=0 pw=0 time=631 us)(object id 52939)
 398632    INDEX FAST FULL SCAN BIG_IDX (cr=889 pr=0 pw=0 time=1594608 us)(object id 52937)
 
********************************************************************************
 
select count(*)
  from big
 where not exists ( select null from small where small.object_id = big.object_id )
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.29       0.30          0        890          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.29       0.30          0        890          0           1
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 62  
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=890 pr=0 pw=0 time=305323 us)
 397840   HASH JOIN RIGHT ANTI (cr=890 pr=0 pw=0 time=1992169 us)
     99    INDEX FULL SCAN SMALL_IDX (cr=1 pr=0 pw=0 time=529 us)(object id 52939)
 398632    INDEX FAST FULL SCAN BIG_IDX (cr=889 pr=0 pw=0 time=1594607 us)(object id 52937)

donc même temps d'exécution ce qui n'est guerre étonant parce que les plans d'exécution sont idéntiques.

Pour rbaraer

Code :

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

1
2
3
4
5
6
7
8
9
 
 select ename, job, mgr
 from emp
 where empno in (null, 7844, 7876);
 
ENAME      JOB              MGR
---------- --------- ----------
TURNER     SALESMAN        7698
ADAMS      CLERK           7788

Probablement que t'a voulut dire NOT IN

**dyvim** · 25/10/2007, 22h01

Normalement les performances sont identiques entre les deux (Oracle en 9i ou plus récent arrive à savoir qu'il s'agit de la même requête)...

Si tu as joué tes deux requêtes l'une après l'autre, la deuxième a sans doute été plus rapide car une partie des données était déjà en mémoire...

**marchand_de_sable** · 26/10/2007, 09h55

Bonjour à tous. Je profite de la discussion pour poser une petite question à mnitu

Envoyé par mnitu

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT count(*)
  FROM big
 WHERE object_id NOT IN ( SELECT object_id FROM small )
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.29       0.30          0        890          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.29       0.33          0        890          0           1
 
Misses IN library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 62  
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=890 pr=0 pw=0 time=301969 us)
 397840   HASH JOIN RIGHT ANTI (cr=890 pr=0 pw=0 time=1594361 us)
     99    INDEX FULL SCAN SMALL_IDX (cr=1 pr=0 pw=0 time=631 us)(object id 52939)
 398632    INDEX FAST FULL SCAN BIG_IDX (cr=889 pr=0 pw=0 time=1594608 us)(object id 52937)

Comment faire pour obtenir ces informations sur l'exécution d'une requête ? Je commence à tâter de l'EXPLAIN PLAN (set autotrace on sous sqlplus), mais je n'obtiens pas toutes ces infos.