Discussion :

[devkais]

bonjour ,
Je souhaite savoir la différence (surtout en termes de performances) entre les 2 requêtes suivantes :

Code :

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

1
2
3
select * from T1 , T2
where T1.col1 = T2.col2
and T1.col1='A';

et

Code :

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

1
2
3
4
select * from T1 , T2
where T1.col1 = T2.col2
and T1.col1='A'
and T2.col2='A';

La condition and T2.col2='A' a t-elle son importance ? rendra-t-elle la requête plus performante ?
merci

[mnitu]

La présence d’un deuxième prédicat dans votre requête a pour conséquence une modification de la sélectivité de cette requête qui se traduit par une estimation différente des nombres des enregistrements ramenés. Analyser la colonne Rows et les deux plans d’exécution.

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
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
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> create table t1(col1 integer primary key, acol varchar2(100))
  2  /
 
Table created
SQL> create table t2(col1 integer primary key, col2 references t1, acol varchar2(100))
  2  /
 
Table created
SQL> create index ixt2 on t2(col2)
  2  /
 
Index created
SQL> insert into t1 select rownum, lpad('x', 100, 'x') from dual connect by level <= 10
  2  /
 
10 rows inserted
SQL> insert into t2 select rownum, mod(rownum,10) + 1, lpad('y', 100, 'y') from dual connect by level <= 1000
  2  /
 
1000 rows inserted
SQL> commit
  2  /
 
Commit complete
SQL> exec dbms_stats.gather_table_stats(user,'T1',cascade =>true)
 
PL/SQL procedure successfully completed
SQL> exec dbms_stats.gather_table_stats(user,'T2',cascade =>true)
 
PL/SQL procedure successfully completed
SQL> explain plan for
  2  select *
  3    from T1 , T2
  4  where T1.col1 = T2.col2
  5  and T1.col1=5
  6  /
 
Explained
SQL> select * from table(dbms_xplan.display)
  2  /
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4087331481
--------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |   100 | 21100 |     4   (0
|   1 |  NESTED LOOPS                |              |   100 | 21100 |     4   (0
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |     1 |   104 |     1   (0
|*  3 |    INDEX UNIQUE SCAN         | SYS_C0083293 |     1 |       |     0   (0
|*  4 |   TABLE ACCESS FULL          | T2           |   100 | 10700 |     3   (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."COL1"=5)
   4 - filter("T2"."COL2"=5)
 
17 rows selected
SQL> explain plan for
  2  select *
  3    from T1 , T2
  4  where T1.col1 = T2.col2
  5  and T1.col1=5
  6  And T2.col2=5
  7  /
 
Explained
SQL> select * from table(dbms_xplan.display)
  2  /
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3865957191
--------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |    10 |  2110 |     4   (0
|   1 |  NESTED LOOPS                |              |    10 |  2110 |     4   (0
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |     1 |   104 |     1   (0
|*  3 |    INDEX UNIQUE SCAN         | SYS_C0083293 |     1 |       |     0   (0
|   4 |   TABLE ACCESS BY INDEX ROWID| T2           |    10 |  1070 |     3   (0
|*  5 |    INDEX RANGE SCAN          | IXT2         |    10 |       |     1   (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."COL1"=5)
   5 - access("T2"."COL2"=5)
       filter("T1"."COL1"="T2"."COL2")
 
19 rows selected
 
SQL>

[devkais]

merci Mnitu pour l'exemple

Cela veut-il dire qu'il vaut mieux rajouter ce filtre systématiquement ?

AND T2.col2=5 ? (dans votre exemple).

and T2.col2='A' ? (dans mon exemple).

Quelle est la bonne norme d'écriture dans ce cas ?
merci

[mnitu]

Cela veut-il dire qu'il vaut mieux rajouter ce filtre systématiquement ?

Pas du tout, mon exemple montre plutôt le contraire. En fait, le nombre des enregistrements ramenés par les requêtes est le même, c’est-à-dire 100. Le fait d’ajouter le prédicat redondant ne fait que tromper l’optimiseur dans ses estimations. Et c’est à cause de cette erreur d’estimation que le mauvais plan, qu’utilise l’index, a été choisi.