Règle sur les Index

**dorian53** · 01/10/2006, 16h28

Bonjour,

Peut-on me confirmer cette règle svp ?

Si une PK est composée de plusieurs colonnes, inutile de lui créer un index sur la 1ère colonne. On peut par contre créer un index sur les autres.

Merci.

**efficks** · 01/10/2006, 16h50

L'index sert à accélérer la recherche sur une colonne en particulire. QUand l'on met une clé primaire, la ou LES colonnes deviennent indéxées automatiquement.
Attention par contre, trop d'index va faire la même chose que pas du tout car plus il y a d'index, plus il y a de champs a chercher. Sinon, l'on mettrait toujours toutes les colonnes en INDEX.

**dorian53** · 01/10/2006, 16h59

Envoyé par efficks

QUand l'on met une clé primaire, la ou LES colonnes deviennent indéxées automatiquement.

Oui mais le groupe de colonne devient indexé, pas chacune d'entres elles ?

ABC est indexé pas A ni B ni C.
A consitue la premire colonne, pour cette raison peut-on se passer de l'indexation de A ?

**LeoAnderson** · 01/10/2006, 18h44

Si des règles simples pouvaient être établies, peut-être que Oracle créerait automatiquement les indexes qui vont bien.... ;-)

donc non, on ne peut pas généraliser...
ça dépent notamment de la volumétrie, des types de requêtes, des clauses utilisées, des types d'opérations, ....

**gregory.broissard** · 02/10/2006, 07h25

Si tu as une PK constituée des colonnes (A,B,C)

Alors il est inutile de faire un index avec seulement A.

Par contre il peut être utile d'indexer (B,C) ou B et C si dans ton application tu accèdes à ta table en filtrant sur B et/ou C sans filtrer sur A.

Globalement pour reprendre ce que disait Leo, ca depend de ton application. Et généralement en phase de conception, les developpeurs ont déjà une bonne vision des index qu'ils auront besoin

**orafrance** · 02/10/2006, 09h12

il peut aussi être intéressant d'indexer (A,C)

**dorian53** · 02/10/2006, 17h27

Envoyé par gregory.broissard

Si tu as une PK constituée des colonnes (A,B,C)

Alors il est inutile de faire un index avec seulement A.

Oké j'suis d'accord ça colle avec ma définition.

Merci à tous.

**dorian53** · 28/10/2006, 18h01

Re,

Au passage je crois qu'il est inutile d'indexer un type DATE, oui/non ? (sous MySQL aussi ?)

Merci.

**LeoAnderson** · 28/10/2006, 19h05

Comme une petite démonstration vaut mieux qu'un long discours :

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
SQL> create table TEST_INDEX_DATE
  2  AS SELECT Object_Name, Created FROM DBA_OBJECTS;
 
Table created.
 
SQL> explain plan 
  2  for
  3  select Object_Name
  4    from TEST_INDEX_DATE
  5   WHERE Created = to_date('20/04/2006', 'DD/MM/YYYY');
 
Explained.
 
SQL>  select * from table ( dbms_xplan.display );
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 973432711
 
-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |    20 |   500 |    17   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_INDEX_DATE |    20 |   500 |    17   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
 
   1 - filter("CREATED"=TO_DATE('2006-04-20 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss'))
 
14 rows selected.
 
 
SQL> create index ix_test_index_date on TEST_INDEX_DATE ( Created );
 
Index created.
 
SQL> analyze table TEST_INDEX_DATE compute statistics;
 
Table analyzed.
 
SQL>  explain plan for select Object_Name from TEST_INDEX_DATE WHERE Created =  to_date('20/04/2006', 'DD/MM/YYYY');
 
Explained.
 
SQL> select * from table ( dbms_xplan.display );
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3683830856
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |    20 |   500 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_INDEX_DATE    |    20 |   500 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IX_TEST_INDEX_DATE |    20 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------
 
   2 - access("CREATED"=TO_DATE('2006-04-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
 
14 rows selected.

Donc, non, un index sur une date, cela a un sens et cela est utile (le coût estimé est tout de même divisé par plus de 8 !)

mais évidement, cela dépend des requêtes que vous faites.
Ainsi, une clause WHERE basée sur une inégalité :

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
 
SQL> explain  plan for select Object_Name FROM TEST_INDEX_DATE WHERE Created >= to_date('20/04/2006', 'DD/MM/YYYY');
 
Explained.
 
SQL> select * from table ( dbms_xplan.display );
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 973432711
 
-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |  8948 |   218K|    17   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_INDEX_DATE |  8948 |   218K|    17   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
 
   1 - filter("CREATED">=TO_DATE('2006-04-20 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss'))
 
14 rows selected.

ou une clause where utilisant une fonction :

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
 
SQL> explain  plan for select Object_Name FROM TEST_INDEX_DATE 
  2   WHERE trunc(Created) = Trunc(to_date('20/04/2006', 'DD/MM/YYYY') );
 
Explained.
 
SQL> select * from table ( dbms_xplan.display );
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 973432711
 
-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |   126 |  3150 |    19  (11)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_INDEX_DATE |   126 |  3150 |    19  (11)| 00:00:01 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
 
   1 - filter(TRUNC(INTERNAL_FUNCTION("CREATED"))=TO_DATE('2006-04-20
              00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
 
14 rows selected.

ne fonctionnent pas, sauf à utiliser un index basé sur une fonction :

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
 
SQL> create index FIX_TEST_DATE on TEST_INDEX_DATE ( TRUNC(Created) );
 
Index created.
 
SQL> explain  plan for select Object_Name FROM TEST_INDEX_DATE
  2  WHERE trunc(Created) = Trunc(to_date('20/04/2006', 'DD/MM/YYYY') );
 
Explained.
 
SQL> select * from table ( dbms_xplan.display );
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1846713100
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |   126 |  3150 |    12   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_INDEX_DATE |   126 |  3150 |    12   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FIX_TEST_DATE   |    50 |       |    11   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
---------------------------------------------------
 
   2 - access(TRUNC(INTERNAL_FUNCTION("CREATED"))=TO_DATE('2006-04-20 00:00:00',
              'yyyy-mm-dd hh24:mi:ss'))
 
15 rows selected.

mais l'on voit tout de même qu'un index basé sur une fonction est moins efficace qu'un index "pur" mais plus efficace que rien !

**pifor** · 30/10/2006, 12h24

Il semble qu'une clause WHERE d"inégalité de dates peut utiliser un index sur une colonne de type DATE si on change le paramètre optimizer_mode de la session (et peut-être aussi de la version d'Oracle utilisée voire de la façon dont on calcule les statistiques ?):

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
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
 
SQL> 
SQL> select * from v$version;
 
BANNER                                                                          
----------------------------------------------------------------                
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production                      
PL/SQL Release 9.2.0.6.0 - Production                                           
CORE	9.2.0.6.0	Production                                                       
TNS for 32-bit Windows: Version 9.2.0.6.0 - Production                          
NLSRTL Version 9.2.0.6.0 - Production                                           
 
SQL> 
SQL> drop table tid;
 
Table dropped.
 
SQL> 
SQL> CREATE TABLE  tid
  2  AS SELECT object_name, created FROM DBA_OBJECTS;
 
Table created.
 
SQL> select count(*) from tid;
 
  COUNT(*)                                                                      
----------                                                                      
     29521                                                                      
 
SQL> select count(*) from tid where created > to_date('20/04/2006','DD/MM/YYYY');
 
  COUNT(*)                                                                      
----------                                                                      
        17                                                                      
 
SQL> select count(*) from tid where created = to_date('20/04/2006','DD/MM/YYYY');
 
  COUNT(*)                                                                      
----------                                                                      
         0                                                                      
 
SQL> 
SQL> exec dbms_stats.gather_table_stats(ownname => 'TEST',  tabname =>'TID', cascade => TRUE);
 
PL/SQL procedure successfully completed.
 
SQL> 
SQL> set autotrace traceonly explain;
SQL> 
SQL> SELECT object_name
  2  FROM tid;
 
Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=29521 Bytes=          
          708504)                                                               
 
   1    0   TABLE ACCESS (FULL) OF 'TID' (Cost=17 Card=29521 Bytes=708          
          504)                                                                  
 
 
 
 
SQL> 
SQL> SELECT object_name
  2  FROM tid
  3  WHERE created = to_date('20/04/2006', 'DD/MM/YYYY');
 
Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=11 Bytes=341          
          )                                                                     
 
   1    0   TABLE ACCESS (FULL) OF 'TID' (Cost=17 Card=11 Bytes=341)            
 
 
 
SQL> 
SQL> CREATE INDEX ix  ON tid (created );
 
Index created.
 
SQL> exec dbms_stats.gather_table_stats(ownname => 'TEST',  tabname =>'TID', cascade => TRUE);
 
PL/SQL procedure successfully completed.
 
SQL> 
SQL> 
SQL> ALTER SESSION set optimizer_mode=all_rows;
 
Session altered.
 
SQL> SELECT Object_Name FROM tid WHERE Created > to_date('20/04/2006', 'DD/MM/YYYY');
 
Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=17 Card=3501 Bytes          
          =108531)                                                              
 
   1    0   TABLE ACCESS (FULL) OF 'TID' (Cost=17 Card=3501 Bytes=1085          
          31)                                                                   
 
 
 
 
SQL> SELECT object_name FROM tid WHERE created =  to_date('20/04/2006', 'DD/MM/YYYY');
 
Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=11 Bytes=34          
          1)                                                                    
 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TID' (Cost=7 Card=11 Byt          
          es=341)                                                               
 
   2    1     INDEX (RANGE SCAN) OF 'IX' (NON-UNIQUE) (Cost=1 Card=11)          
 
 
 
SQL> 
SQL> ALTER SESSION set optimizer_mode=first_rows;
 
Session altered.
 
SQL> SELECT Object_Name FROM tid WHERE Created > to_date('20/04/2006', 'DD/MM/YYYY');
 
Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1647 Card=3501 Bytes=108531)                                                          
 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TID' (Cost=1647 Card=350                 1 Bytes=108531)                                                       
 
   2    1     INDEX (RANGE SCAN) OF 'IX' (NON-UNIQUE) (Cost=11 Card=35         
          01)                                                                   
 
 
 
 
SQL> SELECT object_name FROM tid WHERE created =  to_date('20/04/2006', 'DD/MM/YYYY');
 
Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=7 Card=11 Bytes=          
          341)                                                                  
 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TID' (Cost=7 Card=11 Byt          
          es=341)                                                               
 
   2    1     INDEX (RANGE SCAN) OF 'IX' (NON-UNIQUE) (Cost=1 Card=11)

Mais pourquoi une telle différence de coût ?

**remi4444** · 30/10/2006, 18h50

Envoyé par pifor

Il semble qu'une clause WHERE d"inégalité de dates peut utiliser un index sur une colonne de type DATE si on change le paramètre optimizer_mode de la session (et peut-être aussi de la version d'Oracle utilisée voire de la façon dont on calcule les statistiques ?):

Mais pourquoi une telle différence de coût ?

Lorsque tu as passé tes stats, tu l'as fait de manière classique sans histogramme, ainsi il ne calculera son plan qu'en fonction de la selectivité de colonne en général. Cette selectivité lui dira qu'une égalité lui ramène en moyene disons 0.1 % des lignes, son cout sera calculé en conséquence et donc le passage par l'index s'impose.

Pour une inégalité, il ne saura à priori pas si cette clause va être selective ou pas, et meme il considère que ce n'est que très peu selectif (disons 1/3 ou un truc de cet ordre...). Donc il va considérer qu'il vaut bien mieux faire un accès complet à la table (d'autant qu'elle ne contient pas tant de blocs que ça étant donnée qu'elle n'a que 2 pauvres petites colonnes) et jeter les lignes au fur et à mesure. Etant donné que tu veux ramener beaucoup de lignes, il va te faire un calcul basé sur du "ALL ROWS" et optimiser le travail global.

Maintenant si tu lui précise "FIRST ROWS" ça veux dire que peu t'importe que le travail global soit long pourvu que tu ais ta première ligne le plus vite possible. A ce moment là il sait qu'en passant par l'index, il aura une ligne tout de suite, mais après ça se gate... comme il passe par l'index, il va devoir faire pour chaque ligne (je rappelle qu'il considère qu'il en ramènera beaucoup) en plus d'un acces l'index, un acces individuel par le rowid aux données, donc à l'arrivée, il considère qu'il y aura beaucoup d'I/O.

Attention tout ça n'est que de l'estimation, il se peux qu'en vérité il n'y ait aucune ligne supérieure au '20/04/2006' et qu'en réel le passage par l'index soit très rapide. Mais pour anticiper ça il faut que l'optimiseur ait une vision plus fine sur la répartition des valeurs dans ta colonne date, d'où l'interret de l'histogramme. L'histogramme fait entrer en ligne de compte la valeur avec laquelle on fait la comparaison. Ainsi il ne choisira pas le même plan si tu fait l'inégalité "> to_date('20/10/2006', 'DD/MM/YYYY')" que si tu fait "> to_date('01/01/1900', 'DD/MM/YYYY')". Voilà pourquoi c'est souvent interressant de faire des histogramme très réguliers sur des colonne de dates quand on sait qu'on va faire ce genre de recherche...

**nuke_y** · 30/10/2006, 19h05

Merci de l'explication, c'est toujours bon à savoir.

**LeoAnderson** · 30/10/2006, 19h06

Belle explication !

**pifor** · 30/10/2006, 19h56

Merci pour cette explication.

Au début, j'avais bien pensé aux histogrammes mais je les ai ensuite délaissé car ce problème m'a rappelé un problème d'optimisation sur une application avec une table de 2 millions de lignes et un index sur une colonne de type DATE. On voyait bien que la requête avec 1 clause WHERE de type

WHERE d > data1 AND d < date2

ne retournait que quelques centaines de lignes mais même avec les histogrammes, je ne suis pas arrivé à ce que le CBO utilise l'index autrement qu'en ajoutant le hint.

Si je reprends mon exemple, les coûts du CBO sont effectivement très nettement diminués pour le mode FIRST_ROWS mais curieusement la sélectivité ne change pas (cardinalité d'environ 3500):

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
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
SQL> exec dbms_stats.gather_table_stats(ownname => 'TEST',  tabname =>'TID', method_opt => 'FOR COLUMNS SIZE AUTO', cascade => TRUE);
 
PL/SQL procedure successfully completed.
 
SQL> 
SQL> ALTER SESSION set optimizer_mode=all_rows;
 
Session altered.
 
SQL> SELECT Object_Name FROM tid WHERE Created > to_date('20/04/2006', 'DD/MM/YYYY');
 
Execution Plan
----------------------------------------------------------                                                                          
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=17 Card=3506 Bytes                                                              
          =108686)                                                                                                                  
 
   1    0   TABLE ACCESS (FULL) OF 'TID' (Cost=17 Card=3506 Bytes=1086                                                              
          86)                                                                                                                       
 
 
 
 
SQL> SELECT object_name FROM tid WHERE created =  to_date('20/04/2006', 'DD/MM/YYYY');
 
Execution Plan
----------------------------------------------------------                                                                          
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=11 Bytes=34                                                              
          1)                                                                                                                        
 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TID' (Cost=2 Card=11 Byt                                                              
          es=341)                                                                                                                   
 
   2    1     INDEX (RANGE SCAN) OF 'IX' (NON-UNIQUE) (Cost=1 Card=11)                                                              
 
 
 
SQL> 
SQL> ALTER SESSION set optimizer_mode=first_rows;
 
Session altered.
 
SQL> SELECT Object_Name FROM tid WHERE Created > to_date('20/04/2006', 'DD/MM/YYYY');
 
Execution Plan
----------------------------------------------------------                                                                          
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=33 Card=3506 Byt                                                              
          es=108686)                                                                                                                
 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TID' (Cost=33 Card=3506                                                               
          Bytes=108686)                                                                                                             
 
   2    1     INDEX (RANGE SCAN) OF 'IX' (NON-UNIQUE) (Cost=11 Card=35                                                              
          06)                                                                                                                       
 
 
 
 
SQL> SELECT object_name FROM tid WHERE created =  to_date('20/04/2006', 'DD/MM/YYYY');
 
Execution Plan
----------------------------------------------------------                                                                          
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=11 Bytes=                                                              
          341)                                                                                                                      
 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TID' (Cost=2 Card=11 Byt                                                              
          es=341)                                                                                                                   
 
   2    1     INDEX (RANGE SCAN) OF 'IX' (NON-UNIQUE) (Cost=1 Card=11)                                                              
 
 
 
SQL> 
SQL> 
SQL> prompt avec histogramme
avec histogramme
SQL> exec dbms_stats.gather_table_stats(ownname => 'TEST',  tabname =>'TID', method_opt => 'FOR COLUMNS SIZE 254', cascade => TRUE);
 
PL/SQL procedure successfully completed.
 
SQL> 
SQL> ALTER SESSION set optimizer_mode=all_rows;
 
Session altered.
 
SQL> SELECT Object_Name FROM tid WHERE Created > to_date('20/04/2006', 'DD/MM/YYYY');
 
Execution Plan
----------------------------------------------------------                                                                          
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=17 Card=3506 Bytes                                                              
          =108686)                                                                                                                  
 
   1    0   TABLE ACCESS (FULL) OF 'TID' (Cost=17 Card=3506 Bytes=1086                                                              
          86)                                                                                                                       
 
 
 
 
SQL> SELECT object_name FROM tid WHERE created =  to_date('20/04/2006', 'DD/MM/YYYY');
 
Execution Plan
----------------------------------------------------------                                                                          
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=11 Bytes=34                                                              
          1)                                                                                                                        
 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TID' (Cost=2 Card=11 Byt                                                              
          es=341)                                                                                                                   
 
   2    1     INDEX (RANGE SCAN) OF 'IX' (NON-UNIQUE) (Cost=1 Card=11)                                                              
 
 
 
SQL> 
SQL> ALTER SESSION set optimizer_mode=first_rows;
 
Session altered.
 
SQL> SELECT Object_Name FROM tid WHERE Created > to_date('20/04/2006', 'DD/MM/YYYY');
 
Execution Plan
----------------------------------------------------------                                                                          
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=33 Card=3506 Byt                                                              
          es=108686)                                                                                                                
 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TID' (Cost=33 Card=3506                                                               
          Bytes=108686)                                                                                                             
 
   2    1     INDEX (RANGE SCAN) OF 'IX' (NON-UNIQUE) (Cost=11 Card=35                                                              
          06)                                                                                                                       
 
 
 
 
SQL> SELECT object_name FROM tid WHERE created =  to_date('20/04/2006', 'DD/MM/YYYY');
 
Execution Plan
----------------------------------------------------------                                                                          
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=11 Bytes=                                                              
          341)                                                                                                                      
 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TID' (Cost=2 Card=11 Byt                                                              
          es=341)                                                                                                                   
 
   2    1     INDEX (RANGE SCAN) OF 'IX' (NON-UNIQUE) (Cost=1 Card=11)

**remi4444** · 31/10/2006, 00h23

Envoyé par pifor

Si je reprends mon exemple, les coûts du CBO sont effectivement très nettement diminués pour le mode FIRST_ROWS mais curieusement la sélectivité ne change pas (cardinalité d'environ 3500):

effectivement c'est bizarre.... j'avoue que j'ai toujours eu un peu de mal à comprendre la cardinalité affichée dans un plan, je me demande si y'a pas des bugs qui trainent... peut etre que tu pourrais essayer de faire un histogramme plus précis sur cette colonne en particulier 'FOR COLUMNS SIZE 50 CREATED' (d'apres Fred_D, l'auto sampling marche moyen...).

Sur sybase, il existe un mode de debug vachement interressant qui permet de voir les traces complètes des calculs et des choix de l'optimiseur, on peut donc comprende exactement le pourquoi du comment l'optimiseur à choisi un plan plutot qu'un autre. Je ne sais pas si ça existe sous oracle, j'ai l'impression que non car ils considèrent que ça fait partie de leur "secret de fabrication"...

De manière générale, ça ne me choque pas tant que ça de devoir mettre un HINT pour utiliser un index sur une colonne date, car bien souvent on connais de manière fonctionnelle la répartition des données (meme avec les histogrammes, oracle ne peux pas tout deviner) et au moins on a la garantie que le temps d'exécution d'une requête sera juste proportionnelle au nombre de lignes qu'il y aura entre les 2 dates, on limite ainsi les risques. Le CBO c'est tres puissant mais c'est pas une baguette magique non plus...

Ton exemple peut etre aussi faussé du fait que tu n'ais que 2 colonnes dans la tables, car il faut bien comprende qu'oracle ne ramène pas en mémoire des lignes mais des blocs, et moins il y a de colonnes, plus un bloc contient de lignes. Ajouter à ça le 'multi_block_read' qui peut etre à 8 ou 16, il va donc assez vite considerer que faire un acces complet ne lui fait pas tant d'I/O que ça...

**LeoAnderson** · 31/10/2006, 08h15

Concernant le hint, je suis vachement plus réservé : ça devient la m**** en cas de changement de version et/ou de structure de la base, on risque d'avoir des incohérences que rien ne pourra corriger mis à part une recompilation complète de l'appli ce qui n'est pas forcément évident quelques années après la release, ....

Donc, des hints, pourquoi pas si la modification de la requête se fait simplement (par exemple, un fichier texte à plat contenant chaque requête exécutée par le produit).

Si on ne peut pas garantir que le hint pourra être retiré/modifé instantanément, non, on n'implémente pas de hint.

**nuke_y** · 31/10/2006, 09h04

Et puis les hints on ne peut pas les implémenter partout malheureusement...

**remi4444** · 31/10/2006, 09h29

Envoyé par LeoAnderson

Concernant le hint, je suis vachement plus réservé ...

Attention, tout dépends de la démarche, si on met des HINT au petit bonheur la chance en testant le resultat sans comprendre ce qu'on viens de faire, évidement que c'est à banir. (ça peut arriver quand on est excédé par des jours d'arrachage de cheuveux sur une requête récalcitrante...

).
Maintenant un simple hint qui incite à passer dans un index parcequ'on connais la répartition de ses données (typiquement une inégalité, sur une colonne date ou on ramène les lignes correspondant à une activité du jour), alors ce hint là sécurise un plan

**LeoAnderson** · 31/10/2006, 09h40

Aujourd'hui, oui.

mais dans 2 ans, quand on sera sur la 11g ? avec un CBO "nouvelle version" (simple supposition ! je ne lâche pas un scoop... ) ???
et dans 3 quand on sera passé en tables partitionnées parce que le volume est trop important ? il ne restera que cette f*** requête qui ramera malgré tout nos efforts parce qu'un jour, quelqu'un aura pris la décision de mettre un hint qui ne sera plus du tout d'actualité !
Et vu que pour modifier la requête, on devra recompiler tous les softs....

**nuke_y** · 31/10/2006, 09h49

D'où le problème des programmes compilés...

Règle sur les Index

Administration Oracle

Discussions similaires

Partager

Partager