默认情况下，Oracle数据库，null在Index上是不被存储的，当在索引列以“is null”的方式访问时，无法使用索引；本案例，主要向大家演示如何在存在null的索引列上，使用“is null”访问索引。
案例分析：
1、建立表和普通索引
|
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
|
13
:
52
:
23
SCOTT@ prod >create table t2 (x int,y int);
Table created.
14
:
00
:
11
SCOTT@ prod >
insert
into
t2 values (
1
,
1
);
1
row created.
Elapsed:
00
:
00
:
00.04
14
:
00
:
21
SCOTT@ prod >
insert
into
t2 values (
1
,
null
);
1
row created.
Elapsed:
00
:
00
:
00.00
14
:
00
:
31
SCOTT@ prod >
insert
into
t2 values (
null
,
1
);
1
row created.
Elapsed:
00
:
00
:
00.00
14
:
00
:
37
SCOTT@ prod >
insert
into
t2 values (
null
,
null
);
1
row created.
Elapsed:
00
:
00
:
00.00
14
:
00
:
44
SCOTT@ prod >commit;
Commit complete.
Elapsed:
00
:
00
:
00.04
14
:
06
:
41
SCOTT@ prod >select *
from
t2;
X Y
---------- ----------
1
1
1
1
14
:
36
:
12
SCOTT@ prod >create index t2_ind
on
t2(x);
Index created.
14
:
49
:
38
SCOTT@ prod >select index_name,table_name,num_rows
from
user_indexes
where
index_name=
'T2_IND'
;
INDEX_NAME TABLE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
T2_IND T2
3
在索引中只有
3
行，在最后一行字段全为
null
值，没有被存储！
14
:
36
:
27
SCOTT@ prod >exec dbms_stats.gather_index_stats(user,
'T2_IND'
);
PL/SQL procedure successfully completed.
14
:
37
:
29
SCOTT@ prod >select *
from
t2
where
x=
1
;
X Y
---------- ----------
1
1
1
Execution Plan
----------------------------------------------------------
Plan hash value:
1173409066
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
2
|
8
|
2
(
0
)|
00
:
00
:
01
|
|
1
| TABLE ACCESS BY INDEX ROWID| T2 |
2
|
8
|
2
(
0
)|
00
:
00
:
01
|
|*
2
| INDEX RANGE SCAN | T2_IND |
2
| |
1
(
0
)|
00
:
00
:
01
|
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2
- access(
"X"
=
1
)
Statistics
----------------------------------------------------------
1
recursive calls
0
db block gets
4
consistent gets
0
physical reads
0
redo size
519
bytes sent via SQL*Net to client
415
bytes received via SQL*Net
from
client
2
SQL*Net roundtrips to/
from
client
0
sorts (memory)
0
sorts (disk)
2
rows processed
14
:
37
:
45
SCOTT@ prod >select *
from
t2
where
x is
not
null
;
X Y
---------- ----------
1
1
1
Execution Plan
----------------------------------------------------------
Plan hash value:
463061910
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
2
|
8
|
2
(
0
)|
00
:
00
:
01
|
|
1
| TABLE ACCESS BY INDEX ROWID| T2 |
2
|
8
|
2
(
0
)|
00
:
00
:
01
|
|*
2
| INDEX FULL SCAN | T2_IND |
2
| |
1
(
0
)|
00
:
00
:
01
|
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2
- filter(
"X"
IS NOT NULL)
Statistics
----------------------------------------------------------
1
recursive calls
0
db block gets
4
consistent gets
0
physical reads
0
redo size
519
bytes sent via SQL*Net to client
415
bytes received via SQL*Net
from
client
2
SQL*Net roundtrips to/
from
client
0
sorts (memory)
0
sorts (disk)
2
rows processed
14
:
38
:
00
SCOTT@ prod >select *
from
t2
where
x is
null
;
X Y
---------- ----------
1
Execution Plan
----------------------------------------------------------
Plan hash value:
1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
2
|
8
|
3
(
0
)|
00
:
00
:
01
|
|*
1
| TABLE ACCESS FULL| T2 |
2
|
8
|
3
(
0
)|
00
:
00
:
01
|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1
- filter(
"X"
IS NULL)
Statistics
----------------------------------------------------------
1
recursive calls
0
db block gets
8
consistent gets
0
physical reads
0
redo size
508
bytes sent via SQL*Net to client
415
bytes received via SQL*Net
from
client
2
SQL*Net roundtrips to/
from
client
0
sorts (memory)
0
sorts (disk)
2
rows processed
当x通过“is
null
”访问时，Oracle选择了“full table scan”方式。
|
2、通过建立常量复合索引
|
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
|
14
:
38
:
55
SCOTT@ prod >create index t2_ind
on
t2(x,
0
);
Index created.
14
:
49
:
38
SCOTT@ prod >select index_name,table_name,num_rows
from
user_indexes
where
index_name=
'T2_IND'
;
INDEX_NAME TABLE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
T2_IND T2
4
索引块上存储了表中所用的行。
14
:
39
:
50
SCOTT@ prod >select *
from
t2
where
x is
null
;
X Y
---------- ----------
1
Elapsed:
00
:
00
:
00.00
Execution Plan
----------------------------------------------------------
Plan hash value:
1173409066
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
2
|
8
|
2
(
0
)|
00
:
00
:
01
|
|
1
| TABLE ACCESS BY INDEX ROWID| T2 |
2
|
8
|
2
(
0
)|
00
:
00
:
01
|
|*
2
| INDEX RANGE SCAN | T2_IND |
2
| |
1
(
0
)|
00
:
00
:
01
|
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2
- access(
"X"
IS NULL)
Statistics
----------------------------------------------------------
1
recursive calls
0
db block gets
4
consistent gets
0
physical reads
0
redo size
508
bytes sent via SQL*Net to client
415
bytes received via SQL*Net
from
client
2
SQL*Net roundtrips to/
from
client
0
sorts (memory)
0
sorts (disk)
2
rows processed
对于x通过“is
null
”访问时，也能通过索引访问了！
|
3、建立复合索引（其他列为null）
|
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
|
13
:
59
:
40
SCOTT@ prod >create index x_ind
on
t2(x,y);
Index created.
14
:
08
:
29
SCOTT@ prod >EXEC dbms_stats.gather_table_stats(ownname=>USER,tabname=>
'T2'
);
PL/SQL procedure successfully completed.
14
:
09
:
22
SCOTT@ prod >EXEC dbms_stats.gather_index_stats(ownname=>USER,indname=>
'X_IND'
);
PL/SQL procedure successfully completed.
14
:
09
:
58
SCOTT@ prod >select index_name,num_rows
from
user_indexes
where
index_name=
'X_IND'
;
INDEX_NAME NUM_ROWS
------------------------------ ----------
X_IND
3
14
:
10
:
50
SCOTT@ prod >select count(*)
from
t2;
COUNT(*)
----------
4
14
:
11
:
28
SCOTT@ prod >set autotrace
on
14
:
12
:
33
SCOTT@ prod >select *
from
t2
where
x=
1
;
X Y
---------- ----------
1
1
1
Execution Plan
----------------------------------------------------------
Plan hash value:
3708139238
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
2
|
8
|
1
(
0
)|
00
:
00
:
01
|
|*
1
| INDEX RANGE SCAN| X_IND |
2
|
8
|
1
(
0
)|
00
:
00
:
01
|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1
- access(
"X"
=
1
)
Statistics
----------------------------------------------------------
1
recursive calls
0
db block gets
2
consistent gets
0
physical reads
0
redo size
512
bytes sent via SQL*Net to client
415
bytes received via SQL*Net
from
client
2
SQL*Net roundtrips to/
from
client
0
sorts (memory)
0
sorts (disk)
2
rows processed
14
:
12
:
47
SCOTT@ prod >select *
from
t2
where
x is
not
null
;
X Y
---------- ----------
1
1
1
Elapsed:
00
:
00
:
00.00
Execution Plan
----------------------------------------------------------
Plan hash value:
3776680409
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
2
|
8
|
1
(
0
)|
00
:
00
:
01
|
|*
1
| INDEX FULL SCAN | X_IND |
2
|
8
|
1
(
0
)|
00
:
00
:
01
|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1
- filter(
"X"
IS NOT NULL)
Statistics
----------------------------------------------------------
1
recursive calls
0
db block gets
2
consistent gets
0
physical reads
0
redo size
512
bytes sent via SQL*Net to client
415
bytes received via SQL*Net
from
client
2
SQL*Net roundtrips to/
from
client
0
sorts (memory)
0
sorts (disk)
2
rows processed
14
:
13
:
08
SCOTT@ prod >select *
from
t2
where
x is
null
;
X Y
---------- ----------
1
Execution Plan
----------------------------------------------------------
Plan hash value:
1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
2
|
8
|
3
(
0
)|
00
:
00
:
01
|
|*
1
| TABLE ACCESS FULL| T2 |
2
|
8
|
3
(
0
)|
00
:
00
:
01
|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1
- filter(
"X"
IS NULL)
Statistics
----------------------------------------------------------
1
recursive calls
0
db block gets
8
consistent gets
0
physical reads
0
redo size
508
bytes sent via SQL*Net to client
415
bytes received via SQL*Net
from
client
2
SQL*Net roundtrips to/
from
client
0
sorts (memory)
0
sorts (disk)
2
rows processed
如果，复合索引列其他列也为
null
，在查询使用’is
null
‘条件时，仍然为“full table scan”。
14
:
13
:
52
SCOTT@ prod >select *
from
t2
where
x=
1
and
y is
null
;
X Y
---------- ----------
1
Execution Plan
----------------------------------------------------------
Plan hash value:
3708139238
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
1
|
4
|
1
(
0
)|
00
:
00
:
01
|
|*
1
| INDEX RANGE SCAN| X_IND |
1
|
4
|
1
(
0
)|
00
:
00
:
01
|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1
- access(
"X"
=
1
AND
"Y"
IS NULL)
Statistics
----------------------------------------------------------
1
recursive calls
0
db block gets
2
consistent gets
0
physical reads
0
redo size
471
bytes sent via SQL*Net to client
415
bytes received via SQL*Net
from
client
2
SQL*Net roundtrips to/
from
client
0
sorts (memory)
0
sorts (disk)
1
rows processed
14
:
16
:
16
SCOTT@ prod >select *
from
t2
where
x is
null
and
y=
1
;
X Y
---------- ----------
1
Execution Plan
----------------------------------------------------------
Plan hash value:
3708139238
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
1
|
4
|
1
(
0
)|
00
:
00
:
01
|
|*
1
| INDEX RANGE SCAN| X_IND |
1
|
4
|
1
(
0
)|
00
:
00
:
01
|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1
- access(
"X"
IS NULL AND
"Y"
=
1
)
filter(
"Y"
=
1
)
Statistics
----------------------------------------------------------
1
recursive calls
0
db block gets
1
consistent gets
0
physical reads
0
redo size
471
bytes sent via SQL*Net to client
415
bytes received via SQL*Net
from
client
2
SQL*Net roundtrips to/
from
client
0
sorts (memory)
0
sorts (disk)
1
rows processed
|
4、建立复合索引（其他列为 not null）
|
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
|
15
:
13
:
38
SCOTT@ prod >desc t2;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
X NUMBER(
38
)
Y NUMBER(
38
)
15
:
13
:
43
SCOTT@ prod >alter table t2 modify (y NUMBER(
38
)
not
null
);
Table altered.
15
:
14
:
01
SCOTT@ prod >desc t2;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
X NUMBER(
38
)
Y NOT NULL NUMBER(
38
)
15
:
12
:
54
SCOTT@ prod >
insert
into
t2 values (
1
,
1
);
1
row created.
Elapsed:
00
:
00
:
00.02
15
:
13
:
02
SCOTT@ prod >
insert
into
t2 values (
null
,
1
);
1
row created.
Elapsed:
00
:
00
:
00.00
15
:
13
:
12
SCOTT@ prod >
insert
into
t2 values (
null
,
2
);
1
row created.
Elapsed:
00
:
00
:
00.00
15
:
13
:
36
SCOTT@ prod >commit;
Commit complete.
15
:
15
:
00
SCOTT@ prod >create index t2_ind
on
t2 (x,y);
Index created.
15
:
15
:
29
SCOTT@ prod >exec dbms_stats.gather_table_stats(user,
'T2'
,cascade=>
true
);
PL/SQL procedure successfully completed.
15
:
16
:
09
SCOTT@ prod >select index_name,table_name,num_rows
from
user_indexes
where
index_name=
'T2_IND'
;
INDEX_NAME TABLE_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
T2_IND T2
3
15
:
17
:
20
SCOTT@ prod >set autotrace trace
15
:
17
:
26
SCOTT@ prod >SELECT *
from
t2
where
x is
null
Elapsed:
00
:
00
:
00.00
Execution Plan
----------------------------------------------------------
Plan hash value:
2876512201
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
|
0
| SELECT STATEMENT | |
2
|
10
|
1
(
0
)|
00
:
00
:
01
|
|*
1
| INDEX RANGE SCAN| T2_IND |
2
|
10
|
1
(
0
)|
00
:
00
:
01
|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1
- access(
"X"
IS NULL)
Statistics
----------------------------------------------------------
0
recursive calls
0
db block gets
2
consistent gets
0
physical reads
0
redo size
510
bytes sent via SQL*Net to client
415
bytes received via SQL*Net
from
client
2
SQL*Net roundtrips to/
from
client
0
sorts (memory)
0
sorts (disk)
2
rows processed
在复合索引中，如果其他列为
not
null
，则在“is
null
”条件下，仍然可以使用索引访问。
|
结论：
对于普通的索引，null值不能进行索引的正确理解应该是，对于某一行，索引的所有列的值都是null值时，该行才不能被索引。
本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1582469，如需转载请自行联系原作者
