Index avec composante nulle [Résolu]

[pat29]

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

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

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

Merci.

[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

[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

[pifor]

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

[orafrance]

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

[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.