la différence entre in et EXISTS

salut
voila ma question est simple c'est quoi la différence entre in et EXISTS ??

car pour moi je peux avec les deux je peux avoir le même résulta
par exemple
Code:

1 2 3 4 5 SELECT * FROM Client cli WHERE EXISTS (SELECT * FROM Commande cmd WHERE Cmd.NumCli = cli.NumCli);
ou bien avec in
Code:

1 2 3 SELECT * FROM Client cli WHERE cli.NumCli in (SELECT Cmd.NumCli FROM Commande cmd);
8O
merci d'avance

L'existence d'un enregistrement (EXIST) est différent de la présence d'une valeur dans une liste (IN), es-tu d'accord ?
Il se trouve que tes requêtes ramènent la même chose mais de façons différentes.

Comme le dit Jérome, 'un point de vue sémantique, c'est la même chose. je ne pense pas qu'il y ait une condition que l'on peut ecrire avec l'un et pas avec l'autre.

Malheureusement, d'un point de vue performance, les plans utilisés par Oracle entre deux requettes "sémantiquement identiques", l'une en in l'autre en exists, ne sont pas toujours les mêmes.

Autre point : ce que ramene la sous requette du IN peut ramener une valeur null dans la liste... Cela peut donner des resultats étranges. (Not in).

Pour ajouter à ce qui a été dit, IN ramène toutes les valeurs trouvées (et lit donc toute la table), alors que EXISTS retourne TRUE dès la première valeur trouvée, ce qui explique (en partie) les différences de plan d'exécution.

Petit remarque : au fur et à mesure des évolutions des versions d’Oracle, mieux dit de son optimiseur, la différence entre les deux s’efface.

D'un point de vue resultat c'est la même chose (ce qui n'est pas forcément le cas avec NOT IN et NOT EXISTS) par contre d'un point de vue performance ce n'est pas la même chose. L'une ou l'autre solution peut être meilleure selon les cas.

Tom Kyte en déjà parlé sur son site (faire une recherche sur ASKTOM).

Pour le IN le CBO traite la sous requête comme une vue en ligne et fait une jointure avec la table maitre.
Pour le EXISTS la requête est traitée en faisant une boucle sur la table maitre et pour chaque ligne on a un select qui est fait sur la table de la sous requête. Donc on aura autant d'accès à la 2ème table qu'il y'a de lignes dans la 1ère. Sauf bien sûr si une correspondance est trouvée alors on sort de la boucle.

En règle générale:
Soit T1 la table de la requête principale et T2 la table de la sous requête.
Si T2 est volumineuse mais que T1 est petite et qu'on a un index dans T2 sur la colonne de jointure alors la clause EXISTS est plus appropriée.
Mais si T2 est petite alors la clause IN peut être plus performante.

Il faut toujours tester les 2 pour être sûr.

cdmt,
Ahmed

Citation:

Envoyé par Ahmed AANGOUR

...
En règle générale:
Soit T1 la table de la requête principale et T2 la table de la sous requête.
Si T2 est volumineuse mais que T1 est petite et qu'on a un index dans T2 sur la colonne de jointure alors la clause EXISTS est plus appropriée.
Mais si T2 est petite alors la clause IN peut être plus performante.

Il faut toujours tester les 2 pour être sûr.

cdmt,
Ahmed

Salut Ahmed,
Si tu ferais tes tests tu verras que l’optimiseur « connaît » la volumétrie des tables et « sait » transformer la requête pour trouver le meilleur plan d’exécution effaçant ainsi les différences entre les deux requêtes.

Citation:

Envoyé par mnitu

...l’optimiseur « connaît » la volumétrie des tables et « sait » transformer la requête pour trouver le meilleur plan d’exécution effaçant ainsi les différences entre les deux requêtes.

Ben alors, où est votre légendaire rigueur ?
En matière d'optimisation, si on ne précise pas de quelle version on parle, c'est comme si on n'avait rien dit...

j'ai retrouvé l'article sur le sujet de Tom KYTE ici:
http://asktom.oracle.com/pls/asktom/...D:953229842074

J'ai essayé de reproduire son test case en 10g mais effectivement comme l'a dit MNITU. Le CBO sait désormais transformer la requête.
je pense que Tom KYTE était encore en 8i au moment de son test car je ne reproduit le soucis que lorsque je met le paramètre optimizer_features_enable='8.1.7'.

voici mon test en 10g:

Code:

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
 
SQL> create table big as select * from all_objects;
 
Table crÚÚe.
 
SQL> insert /*+ append */ into big select * from big;
 
57060 ligne(s) crÚÚe(s).
 
SQL> commit;
 
Validation effectuÚe.
 
SQL> insert /*+ append */ into big select * from big;
 
114120 ligne(s) crÚÚe(s).
 
SQL> commit;
 
Validation effectuÚe.
 
SQL> insert /*+ append */ into big select * from big;
 
228240 ligne(s) crÚÚe(s).
 
SQL> commit;
 
Validation effectuÚe.
 
SQL> select count(1) from big;
 
  COUNT(1)
----------
    456480
 
SQL> insert /*+ append */ into big select * from big;
 
456480 ligne(s) crÚÚe(s).
 
SQL> commit;
 
Validation effectuÚe.
 
SQL> select count(1) from big;
 
  COUNT(1)
----------
    912960
 
SQL> create index big_idx on big(object_id);
 
Index crÚÚ.
 
SQL> create table small as select * from all_objects where rownum < 100;
 
Table crÚÚe.
 
SQL> create index small_idx on small(object_id);
 
Index crÚÚ.
 
SQL> analyze table big compute statistics
  2  for table
  3  for all indexes
  4  for all indexed columns
  5  /
 
Table analysÚe.
 
SQL> analyze table small compute statistics
  2  for table
  3  for all indexes
  4  for all indexed columns
  5  /
 
Table analysÚe.
 
SQL> set autotrace on
SQL> set timin on
SQL> select count(*)
  2    from big
  3   where object_id in ( select object_id from small );
 
  COUNT(*)
----------
      1584
 
EcoulÚ : 00 :00 :00.00
 
Plan d'exÚcution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=56 Card=1 Bytes=6)
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=56 Card=1584 Bytes=9504)
   3    2       SORT (UNIQUE) (Cost=1 Card=99 Bytes=198)
   4    3         INDEX (FULL SCAN) OF 'SMALL_IDX' (INDEX) (Cost=1 Car
          d=99 Bytes=198)
 
   5    2       INDEX (RANGE SCAN) OF 'BIG_IDX' (INDEX) (Cost=2 Card=1
          6 Bytes=64)
 
 
 
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL>  select count(*)
  2    from big
  3   where exists ( select null from small where small.object_id = big.object_id );
 
  COUNT(*)
----------
      1584
 
EcoulÚ : 00 :00 :00.00
 
Plan d'exÚcution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=56 Card=1 Bytes=6)
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=56 Card=1584 Bytes=9504)
   3    2       SORT (UNIQUE) (Cost=1 Card=99 Bytes=198)
   4    3         INDEX (FULL SCAN) OF 'SMALL_IDX' (INDEX) (Cost=1 Car
          d=99 Bytes=198)
 
   5    2       INDEX (RANGE SCAN) OF 'BIG_IDX' (INDEX) (Cost=2 Card=1
          6 Bytes=64)
 
 
 
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL>  select count(*)
  2    from small
  3   where object_id in ( select object_id from big );
 
  COUNT(*)
----------
        99
 
EcoulÚ : 00 :00 :00.00
 
Plan d'exÚcution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=101 Card=1 Bytes=6
          )
 
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (SEMI) (Cost=101 Card=99 Bytes=594)
   3    2       INDEX (FULL SCAN) OF 'SMALL_IDX' (INDEX) (Cost=1 Card=
          99 Bytes=198)
 
   4    2       INDEX (RANGE SCAN) OF 'BIG_IDX' (INDEX) (Cost=2 Card=9
          13040 Bytes=3652160)
 
 
 
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL>  select count(*)
  2    from small
  3   where exists ( select null from big where small.object_id = big.object_id );
 
  COUNT(*)
----------
        99
 
EcoulÚ : 00 :00 :00.00
 
Plan d'exÚcution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=101 Card=1 Bytes=6
          )
 
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (SEMI) (Cost=101 Card=99 Bytes=594)
   3    2       INDEX (FULL SCAN) OF 'SMALL_IDX' (INDEX) (Cost=1 Card=
          99 Bytes=198)
 
   4    2       INDEX (RANGE SCAN) OF 'BIG_IDX' (INDEX) (Cost=2 Card=9
          13040 Bytes=3652160)
 
 
 
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

voici le test en 8i:

Code:

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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
 
SQL> alter session set optimizer_features_enable='8.1.7';
 
Session modifiÚe.
 
EcoulÚ : 00 :00 :00.00
SQL> set lines 500
SQL> set pages 300
SQL> set timin on
SQL> set autotrace on
SQL> select count(*)
  2    from big
  3   where object_id in ( select object_id from small );
 
  COUNT(*)
----------
      1584
 
EcoulÚ : 00 :00 :00.00
 
Plan d'exÚcution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=202 Card=1 Bytes=17)
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=202 Card=1584 Bytes=26928)
   3    2       VIEW OF 'VW_NSO_1' (VIEW) (Cost=4 Card=99 Bytes=1287)
   4    3         SORT (UNIQUE) (Cost=4 Card=99 Bytes=198)
   5    4           INDEX (FULL SCAN) OF 'SMALL_IDX' (INDEX) (Cost=1 C
          ard=99 Bytes=198)
 
   6    2       INDEX (RANGE SCAN) OF 'BIG_IDX' (INDEX) (Cost=2 Card=1
          6 Bytes=64)
 
 
 
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL>  select count(*)
  2    from big
  3   where exists ( select null from small where small.object_id = big.object_id );
 
  COUNT(*)
----------
      1584
 
EcoulÚ : 00 :00 :00.06
 
Plan d'exÚcution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=399 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE)
   2    1     FILTER
   3    2       INDEX (FAST FULL SCAN) OF 'BIG_IDX' (INDEX) (Cost=399
          Card=45652 Bytes=182608)
 
   4    2       INDEX (RANGE SCAN) OF 'SMALL_IDX' (INDEX) (Cost=1 Card
          =1 Bytes=2)
 
 
 
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL>  select count(*)
  2    from small
  3   where object_id in ( select object_id from big );
 
  COUNT(*)
----------
        99
 
EcoulÚ : 00 :00 :00.02
 
Plan d'exÚcution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3966 Card=1 Bytes=15
          )
 
   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=3966 Card=99 Bytes=1485)
   3    2       VIEW OF 'VW_NSO_1' (VIEW) (Cost=3966 Card=57065 Bytes=
          741845)
 
   4    3         SORT (UNIQUE) (Cost=3966 Card=57065 Bytes=228260)
   5    4           INDEX (FAST FULL SCAN) OF 'BIG_IDX' (INDEX) (Cost=
          399 Card=913040 Bytes=3652160)
 
   6    2       INDEX (RANGE SCAN) OF 'SMALL_IDX' (INDEX)
 
 
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL>  select count(*)
  2    from small
  3   where exists ( select null from big where small.object_id = big.object_id );
 
  COUNT(*)
----------
        99
 
EcoulÚ : 00 :00 :00.00
 
Plan d'exÚcution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FULL SCAN) OF 'SMALL_IDX' (INDEX) (Cost=1 Card=5
          Bytes=10)
 
   3    2       INDEX (RANGE SCAN) OF 'BIG_IDX' (INDEX) (Cost=3 Card=1
          6 Bytes=64)
 
 
 
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

En 8i on voit bien que quand la outer query est grande et que la inner query est petite, la clause IN est plus performante que la clause EXISTS. Inversement quand la outer query est petite et que la inner query est grande (et qu'on a aun index sur la colonne de jointure) alors EXISTS est meilleur que IN.

La conclusion c'est qu'à partir de la 9i (et tant que le QUERY TRANSFORMATION est actif ) le CBO sait transformer les requêtes.

Citation:

Envoyé par Pomalaix

Ben alors, où est votre légendaire rigueur ?
En matière d'optimisation, si on ne précise pas de quelle version on parle, c'est comme si on n'avait rien dit...

Salut Pomalaix,

Oui c’est vrai. :lol:
J’avais mentionné dans ma précédente intervention que c’était les versions plus récentes d’Oracle et je pense à la 10R2 et suivantes.