Index avec composante nulle

Version imprimable

09/01/2008, 11h42
pat29

Index avec composante nulle

Bonjour,

Petite question sur les index (en 9i) :

j'ai un index avec 3 colonnes A, B, C avec B qui peut être nulle.
Est-ce que dans l'arbre binaire de l'index la valeur nulle est considérée comme les autres ?

En clair si j'ai une requête

Citation:

SELECT ... FROM ...
WHERE A=... AND B is NULL AND C=...

est-ce que l'index utilisera ses 3 colonnes ?

Merci.
09/01/2008, 11h53
orafrance

NULL n'est pas référencé dans l'index si c'est ta question :?

Eventuellement tu peux utiliser cette astuce : http://www.dba-oracle.com/oracle_tips_null_idx.htm que je n'apprécie pas des masses :?
09/01/2008, 13h15
McM

Tu es sur Orafrance ?

Le NULL sur toutes les valeurs n'est pas dans l'index.
Si au moins 1 colonne d'un index multicolonnes est NOT NULL alors la ligne d'index est créée.

Donc l'absence de valeur est dans l'index, mais par contre l'index ne la prendra pas en compte dans le cas d'un WHERE B IS NULL

Si une partie des colonnes est nulle au sens SQL, alors l'index les prends en compte.

Exemple:

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
dev001> 
dev001> drop table t;
 
Table dropped.
 
dev001> 
dev001> select * from v$version;
 
BANNER                                                                          
----------------------------------------------------------------                
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod                
PL/SQL Release 10.2.0.2.0 - Production                                          
CORE	10.2.0.2.0	Production                                                      
TNS for 32-bit Windows: Version 10.2.0.2.0 - Production                         
NLSRTL Version 10.2.0.2.0 - Production                                          
 
dev001> 
dev001> create table t(x int primary key, y int null , z int null);
 
Table created.
 
dev001> 
dev001> insert into t select object_id, object_id/2, object_id * 3 from all_objects;
 
4272 rows created.
 
dev001> commit;
 
Commit complete.
 
dev001> 
dev001> create index i on t(y,z);
 
Index created.
 
dev001> exec dbms_stats.gather_table_stats(ownname =>'O', tabname => 'T');
 
PL/SQL procedure successfully completed.
 
dev001> 
dev001> update t set z=null where x=y + 1000;
 
2 rows updated.
 
dev001> commit;
 
Commit complete.
 
dev001> 
dev001> select * from t where z is null;
 
         X          Y          Z                                                
---------- ---------- ----------                                                
      2000       1000                                                           
      2001       1001                                                           
 
dev001> 
dev001> set autotrace on explain;
dev001> select * from t where y=1000 and z is null;
 
         X          Y          Z                                                
---------- ---------- ----------                                                
      2000       1000                                                           
 
 
Execution Plan
----------------------------------------------------------                      
Plan hash value: 242607798                                                      
 
--------------------------------------------------------------------------------
----                                                                            
 
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time  
   |                                                                            
 
--------------------------------------------------------------------------------
----                                                                            
 
|   0 | SELECT STATEMENT            |      |     1 |    12 |     2   (0)| 00:00:
01 |                                                                            
 
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    12 |     2   (0)| 00:00:
01 |                                                                            
 
|*  2 |   INDEX RANGE SCAN          | I    |     1 |       |     1   (0)| 00:00:
01 |                                                                            
 
--------------------------------------------------------------------------------
----                                                                            
 
 
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
 
   2 - access("Y"=1000 AND "Z" IS NULL)                                         
 
dev001> 
dev001> exit

09/01/2008, 13h27
orafrance

Citation:

Envoyé par McM

Donc l'absence de valeur est dans l'index, mais par contre l'index ne la prendra pas en compte dans le cas d'un WHERE B IS NULL

Bien sûr... je dis juste que s'il limite la clause à B IS NULL comme NULL n'est pas dans l'index ça aura pour effet de faire un FULL SCAN... désolé si c'était pas clair
09/01/2008, 14h51
pat29

Donc NULL est traité comme les autres valeurs.
Merci pour la démo. Je suppose que c'est vrai quelle que soit la version.