NULL值是关系数据库系统布尔型(true,false,unknown)中比较特殊类型的一种值，通常称为UNKNOWN或空值，即是未知的，不确定的。由于
NULL存在着无数的可能，因此NULL值也不等于NULL值，所以与NULL值相关的操作同样都为NULL值。正是基于这样一个特性，对于NULL值列上的B
树索引导致了is null/is not null不走索引的情形，下面描述了NULL值与索引以及索引NULL列上的执行计划，如何使得NULL值走索引的情形。
注：本文仅仅讨论的是B树索引上的NULL值，位图索引不在此范围之内。
一、null值与索引的关系
1. scott@ORCL> create table
2.
3. –>为表t1创建唯一索引
4. scott@ORCL> create unique index i_t1_id on
5.
6. scott@ORCL> insert into t1 select null,‘Y’ from
7.
8. scott@ORCL> insert into t1 select null,‘N’ from
9.
10. –>从上面的操作可知，尽管列id上存在唯一索引，但由于null值不等于任一null值，因此能够成功插入
11. scott@ORCL> commit;
12.
13. –>再次为表添加唯一复合索引，即基于id列与val列
14. scott@ORCL> create unique index i_t1_id_val on
15.
16. Index
17.
18. –>插入null,’N’的记录时失败，提示违反唯一性约束
19. scott@ORCL> insert into t1 select null,‘N’ from
20. insert into t1 select null,‘N’ from
21. *
22. ERROR at
23. ORA-00001: unique constraint
24.
25. –>插入null,’Y’的记录时同样失败，提示违反唯一性约束
26. scott@ORCL> insert into t1 select null,‘Y’ from
27. insert into t1 select null,‘Y’ from
28. *
29. ERROR at
30. ORA-00001: unique constraint
31.
32. –>插入两个null值成功
33. scott@ORCL> insert into t1 select null,null from
34.
35. 1 row created.
36.
37. scott@ORCL> insert into t1 select null,null from
38.
39. 1 row created.
40.
41. scott@ORCL> insert into t1 select null,‘A’ from
42.
43. 1 row created.
44.
45. scott@ORCL> commit;
46.
47. Commit
48.
49. scott@ORCL> set null
50. scott@ORCL> select * from
51.
52. ID VAL
53. ———- ——————————
54. unknown Y
55. unknown N
56. unknown unknown
57. unknown unknown
58. unknown A
59.
60. scott@ORCL> exec dbms_stats.gather_table_stats(‘SCOTT’,‘T1’,cascade=>true);
61.
62. scott@ORCL> select
63. from user_indexes where table_name=‘T1’;
64.
65. INDEX_NAME INDEX_TYPE BLEVEL LEAF_BLOCKS NUM_ROWS STATUS DISTINCT_KEYS
66. ————— ———- ———- ———– ———- ——– ————-
67. I_T1_ID NORMAL 0 0 0 VALID 0
68. I_T1_ID_VAL NORMAL 0 1 3 VALID 3
69.
70. –>从上面的情形可知，
71. –>基于单列的唯一索引，可以多次插入null值，但其索引上并不存储null值。
72. –>基于多列的复合索引，尽管全为null值的行可以多次插入，但不全为null的重复行则不能被插入(注,非唯一复合索引不存在此限制，此处不演示)。
73. –>基于多列的复合索引，对于全为null值的索引值也不会被存储。如上面的情形，尽管插入了5条记录，复合索引中只存储了3条。
74. –>注：对于唯一性约束，null值不等于null值，同样(null,null)也不等同于(null,null)，所以上面的两次null能够被插入。
scott@ORCL> create table t1(id number,val varchar2(1));
-->为表t1创建唯一索引
scott@ORCL> create unique index i_t1_id on t1(id);
scott@ORCL> insert into t1 select null,'Y' from dual;
scott@ORCL> insert into t1 select null,'N' from dual;
-->从上面的操作可知，尽管列id上存在唯一索引，但由于null值不等于任一null值，因此能够成功插入
scott@ORCL> commit;
-->再次为表添加唯一复合索引，即基于id列与val列
scott@ORCL> create unique index i_t1_id_val on t1(id,val);
Index created.
-->插入null,'N'的记录时失败，提示违反唯一性约束
scott@ORCL> insert into t1 select null,'N' from dual;
insert into t1 select null,'N' from dual
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.I_T1_ID_VAL) violated
-->插入null,'Y'的记录时同样失败，提示违反唯一性约束
scott@ORCL> insert into t1 select null,'Y' from dual;
insert into t1 select null,'Y' from dual
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.I_T1_ID_VAL) violated
-->插入两个null值成功
scott@ORCL> insert into t1 select null,null from dual;
1 row created.
scott@ORCL> insert into t1 select null,null from dual;
1 row created.
scott@ORCL> insert into t1 select null,'A' from dual;
1 row created.
scott@ORCL> commit;
Commit complete.
scott@ORCL> set null unknown;
scott@ORCL> select * from t1;
ID VAL
---------- ------------------------------
unknown Y
unknown N
unknown unknown
unknown unknown
unknown A
scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true);
scott@ORCL> select index_name,index_type,blevel,leaf_blocks,num_rows,status,distinct_keys
2 from user_indexes where table_name='T1';
INDEX_NAME INDEX_TYPE BLEVEL LEAF_BLOCKS NUM_ROWS STATUS DISTINCT_KEYS
--------------- ---------- ---------- ----------- ---------- -------- -------------
I_T1_ID NORMAL 0 0 0 VALID 0
I_T1_ID_VAL NORMAL 0 1 3 VALID 3
-->从上面的情形可知，
-->基于单列的唯一索引，可以多次插入null值，但其索引上并不存储null值。
-->基于多列的复合索引，尽管全为null值的行可以多次插入，但不全为null的重复行则不能被插入(注,非唯一复合索引不存在此限制，此处不演示)。
-->基于多列的复合索引，对于全为null值的索引值也不会被存储。如上面的情形，尽管插入了5条记录，复合索引中只存储了3条。
-->注：对于唯一性约束，null值不等于null值，同样(null,null)也不等同于(null,null)，所以上面的两次null能够被插入。
二、null值与执行计划
1. scott@ORCL> set
2. scott@ORCL> select * from t1 where id is null;
3.
4. Execution Plan
5. ———————————————————-
6. Plan hash value: 3617692013
7.
8. ————————————————————————–
9. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
10. ————————————————————————–
11. | 0 | SELECT
12. |* 1 | TABLE ACCESS FULL| T1 | 5 | 5 | 3 (0)| 00:00:01 |
13. ————————————————————————–
14.
15. Predicate Information (identified by
16. —————————————————
17.
18. ”ID” IS NULL)
19.
20. –>从上面的测试可知，由于null值是不被存储的，因此当使用id is null作为谓词时，走了全表扫描
21.
22. scott@ORCL> select * from t1 where id is not null;
23.
24. Execution Plan
25. ———————————————————-
26. Plan hash value: 796913935
27.
28. —————————————————————————————
29. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
30. —————————————————————————————
31. | 0 | SELECT
32. | 1 | TABLE ACCESS BY INDEX
33. |* 2 | INDEX FULL
34. —————————————————————————————
35.
36. Predicate Information (identified by
37. —————————————————
38.
39. ”ID” IS NOT NULL)
40.
41. –>从上面的测试可知，尽管当前表上id列上的所有值都为null，但不排除后续记录插入的id不为null的列。
42. –>故当使用id is not null作为谓词时，此时执行计划中走了索引全扫描。
43.
44. –>下面来看看复合索引的情形
45. scott@ORCL> select * from t1 where val is null;
46.
47. Execution Plan
48. ———————————————————-
49. Plan hash value: 3617692013
50.
51. ————————————————————————–
52. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
53. ————————————————————————–
54. | 0 | SELECT
55. |* 1 | TABLE ACCESS FULL| T1 | 2 | 2 | 3 (0)| 00:00:01 |
56. ————————————————————————–
57.
58. Predicate Information (identified by
59. —————————————————
60.
61. ”VAL” IS NULL)
62.
63. scott@ORCL> select * from t1 where val is not null;
64.
65. Execution Plan
66. ———————————————————-
67. Plan hash value: 1931510411
68.
69. ——————————————————————————–
70. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
71. ——————————————————————————–
72. | 0 | SELECT
73. |* 1 | INDEX FULL
74. ——————————————————————————–
75.
76. Predicate Information (identified by
77. —————————————————
78.
79. ”VAL” IS NOT NULL)
80.
81. –>对于复合唯一索引的情形，当使用单列且非前导列谓词时，使用is null与 is not null等同于单列唯一索引的情形。
82. –>即原理也是一样的，val is null走全表扫描而val is not null走索引。因为null值不会被存储。
83.
84. –>下面看看两个列都作为谓词的情形
85. scott@ORCL> select * from t1 where id is null and val is not null;
86.
87. Execution Plan
88. ———————————————————-
89. Plan hash value: 1040510552
90.
91. ——————————————————————————–
92. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
93. ——————————————————————————–
94. | 0 | SELECT
95. |* 1 | INDEX
96. ——————————————————————————–
97.
98. Predicate Information (identified by
99. —————————————————
100.
101. ”ID” IS NULL)
102. ”VAL” IS NOT NULL)
103.
104. –>从上面的测试可知，尽管两个谓词列上都存在索引，一个为单列唯一索引，一个为复合唯一索引。Oracle 选择了复合索引I_T1_ID_VAL。
105.
106. scott@ORCL> select * from t1 where id is not null and val is null;
107.
108. Execution Plan
109. ———————————————————-
110. Plan hash value: 796913935
111.
112. —————————————————————————————
113. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
114. —————————————————————————————
115. | 0 | SELECT
116. |* 1 | TABLE ACCESS BY INDEX
117. |* 2 | INDEX FULL
118. —————————————————————————————
119.
120. Predicate Information (identified by
121. —————————————————
122.
123. ”VAL” IS NULL)
124. ”ID” IS NOT NULL)
125.
126. –>同样的情形，谓词的顺序与复合索引定义的顺序一样，只不过第一个谓词为id is not null，而第二个谓词为val is null。
127. –>此时Oracle 选择了单列唯一索引I_T1_ID
128. –>看到此，不知道大家是否已明白，即哪个列为is not null，则会使用该列上的索引，原因还是那句话，索引不存储null值。
129. –>对于颠倒id列与val列以及id,val列为null或not null的其他不同组合情形不再演示，其执行计划类似。
scott@ORCL> set autot trace exp;
scott@ORCL> select * from t1 where id is null; Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 5 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 5 | 5 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ID" IS NULL) -->从上面的测试可知，由于null值是不被存储的，因此当使用id is null作为谓词时，走了全表扫描 scott@ORCL> select * from t1 where id is not null; Execution Plan ---------------------------------------------------------- Plan hash value: 796913935 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1 | 0 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 0 (0)| 00:00:01 | |* 2 | INDEX FULL SCAN | I_T1_ID | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID" IS NOT NULL) -->从上面的测试可知，尽管当前表上id列上的所有值都为null，但不排除后续记录插入的id不为null的列。 -->故当使用id is not null作为谓词时，此时执行计划中走了索引全扫描。 -->下面来看看复合索引的情形 scott@ORCL> select * from t1 where val is null; Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 2 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 2 | 2 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("VAL" IS NULL) scott@ORCL> select * from t1 where val is not null; Execution Plan ---------------------------------------------------------- Plan hash value: 1931510411 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 3 | 1 (0)| 00:00:01 | |* 1 | INDEX FULL SCAN | I_T1_ID_VAL | 3 | 3 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("VAL" IS NOT NULL) -->对于复合唯一索引的情形，当使用单列且非前导列谓词时，使用is null与 is not null等同于单列唯一索引的情形。 -->即原理也是一样的，val is null走全表扫描而val is not null走索引。因为null值不会被存储。 -->下面看看两个列都作为谓词的情形 scott@ORCL> select * from t1 where id is null and val is not null; Execution Plan ---------------------------------------------------------- Plan hash value: 1040510552 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 3 | 1 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| I_T1_ID_VAL | 3 | 3 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("ID" IS NULL) filter("VAL" IS NOT NULL) -->从上面的测试可知，尽管两个谓词列上都存在索引，一个为单列唯一索引，一个为复合唯一索引。Oracle 选择了复合索引I_T1_ID_VAL。 scott@ORCL> select * from t1 where id is not null and val is null; Execution Plan ---------------------------------------------------------- Plan hash value: 796913935 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1 | 0 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 1 | 0 (0)| 00:00:01 | |* 2 | INDEX FULL SCAN | I_T1_ID | 1 | | 0 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("VAL" IS NULL) 2 - filter("ID" IS NOT NULL) -->同样的情形，谓词的顺序与复合索引定义的顺序一样，只不过第一个谓词为id is not null，而第二个谓词为val is null。 -->此时Oracle 选择了单列唯一索引I_T1_ID -->看到此，不知道大家是否已明白，即哪个列为is not null，则会使用该列上的索引，原因还是那句话，索引不存储null值。 -->对于颠倒id列与val列以及id,val列为null或not null的其他不同组合情形不再演示，其执行计划类似。
三、使用is null走索引的情形
1. scott@ORCL> set autot off;
2. –删除原有表上的null值记录
3. scott@ORCL> delete from t1 where val not in(‘Y’,‘N’) or val is null;
4.
5. 3 rows
6.
7. scott@ORCL> update t1 set id=1 where val=‘Y’;
8.
9. 1 row updated.
10.
11. scott@ORCL> update t1 set id=2 where val=‘N’;
12.
13. 1 row updated.
14.
15. scott@ORCL> commit;
16.
17. Commit
18.
19. –>对原有记录更新后的情形
20. scott@ORCL> select * from
21.
22. ID VAL
23. ———- ——————————
24. 1 Y
25. 2 N
26.
27. scott@ORCL> exec dbms_stats.gather_table_stats(‘SCOTT’,‘T1’,cascade=>true);
28.
29. PL/SQL procedure
30.
31. –>修改表列id使之具有not null约束的特性
32. scott@ORCL> alter table t1 modify(id not null);
33.
34. Table
35.
36. scott@ORCL> set
37. scott@ORCL> select * from t1 where id is null;
38.
39. Execution Plan
40. ———————————————————-
41. Plan hash value: 3160894736
42.
43. ——————————————————————————–
44. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
45. ——————————————————————————–
46. | 0 | SELECT
47. |* 1 | FILTER | | | | | |
48. | 2 | INDEX FULL
49. ——————————————————————————–
50.
51. Predicate Information (identified by
52. —————————————————
53.
54. NULL IS NOT NULL)
55.
56. –>从上面的执行计划中可知，当表t1列id上具有not null 约束时，此时使用id is null选择了索引范围扫描
57.
58. –>下面来看看列val is null 的情形
59. scott@ORCL> select * from t1 where val is null;
60.
61. Execution Plan
62. ———————————————————-
63. Plan hash value: 48744011
64.
65. ————————————————————————————
66. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
67. ————————————————————————————
68. | 0 | SELECT
69. |* 1 | INDEX FAST FULL
70. ————————————————————————————
71.
72. Predicate Information (identified by
73. —————————————————
74.
75. ”VAL” IS NULL)
76.
77. –>尽管val列上允许null值存在，但由于列id上具有not null 约束，且id列与val列存在复合唯一索引，因此此时选择了索引快速全扫描
78. –>其余不同组合情形大致相同，不再演示
79.
80. –>为表t1新增一条val为null的记录
81. scott@ORCL> insert into t1 select 3,null from
82.
83. 1 row created.
84.
85. scott@ORCL> commit;
86.
87. Commit
88.
89. scott@ORCL> exec dbms_stats.gather_table_stats(‘SCOTT’,‘T1’,cascade=>true);
90.
91. PL/SQL procedure
92.
93. –>下面的查询中可以看出尽管只有列id有not null约束，当所有的索引值都被存储
94. scott@ORCL> select
95. from user_indexes where table_name=‘T1’;
96.
97. INDEX_NAME INDEX_TYPE BLEVEL LEAF_BLOCKS NUM_ROWS STATUS DISTINCT_KEYS
98. ————— ———- ———- ———– ———- ——– ————-
99. I_T1_ID NORMAL 0 1 3 VALID 3
100. I_T1_ID_VAL NORMAL 0 1 3 VALID 3
101.
102. –>Author : Robinson Cheng
103. –>Blog :
scott@ORCL> set autot off;
--删除原有表上的null值记录
scott@ORCL> delete from t1 where val not in('Y','N') or val is null;
3 rows deleted.
scott@ORCL> update t1 set id=1 where val='Y';
1 row updated.
scott@ORCL> update t1 set id=2 where val='N';
1 row updated.
scott@ORCL> commit;
Commit complete.
-->对原有记录更新后的情形
scott@ORCL> select * from t1;
ID VAL
---------- ------------------------------
1 Y
2 N
scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true);
PL/SQL procedure successfully completed.
-->修改表列id使之具有not null约束的特性
scott@ORCL> alter table t1 modify(id not null);
Table altered.
scott@ORCL> set autot trace exp;
scott@ORCL> select * from t1 where id is null; Execution Plan ---------------------------------------------------------- Plan hash value: 3160894736 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 0 (0)| | |* 1 | FILTER | | | | | | | 2 | INDEX FULL SCAN| I_T1_ID_VAL | 2 | 10 | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(NULL IS NOT NULL) -->从上面的执行计划中可知，当表t1列id上具有not null 约束时，此时使用id is null选择了索引范围扫描 -->下面来看看列val is null 的情形 scott@ORCL> select * from t1 where val is null; Execution Plan ---------------------------------------------------------- Plan hash value: 48744011 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:00:01 | |* 1 | INDEX FAST FULL SCAN| I_T1_ID_VAL | 1 | 5 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("VAL" IS NULL) -->尽管val列上允许null值存在，但由于列id上具有not null 约束，且id列与val列存在复合唯一索引，因此此时选择了索引快速全扫描 -->其余不同组合情形大致相同，不再演示 -->为表t1新增一条val为null的记录 scott@ORCL> insert into t1 select 3,null from dual; 1 row created. scott@ORCL> commit; Commit complete. scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true); PL/SQL procedure successfully completed. -->下面的查询中可以看出尽管只有列id有not null约束，当所有的索引值都被存储 scott@ORCL> select index_name,index_type,blevel,leaf_blocks,num_rows,status,distinct_keys 2 from user_indexes where table_name='T1'; INDEX_NAME INDEX_TYPE BLEVEL LEAF_BLOCKS NUM_ROWS STATUS DISTINCT_KEYS --------------- ---------- ---------- ----------- ---------- -------- ------------- I_T1_ID NORMAL 0 1 3 VALID 3 I_T1_ID_VAL NORMAL 0 1 3 VALID 3 -->Author : Robinson Cheng -->Blog :
四、总结
无论是单列唯一索引或复合唯一索引，对于可以为null的列或复合null值，Oracle不会为其存储索引值。
故在基于单列创建B树唯一索引或多列创建B树复合唯一索引的情形下，
当列上允许为null值时
where子句使用了基于is null的情形，其执行计划走全表扫描。
where子句使用了基于is not null的情形，其执行计划走索引扫描(索引范围扫描或索引全扫描)。
当列上不允许为null值时，存在非null约束
where子句使用了基于is null的情行，其执行计划走索引扫描。
where子句使用了基于is not null的情形，其执行计划也是走索引扫描。
注：此在Oracle 10g R2(linux)下的情形，不同的优化器版本可能会有偏差。
NULL 值与索引(二)
SQL tuning 步骤
高效SQL语句必杀技
父游标、子游标及共享游标
绑定变量及其优缺点
dbms_xplan之display_cursor函数的使用
dbms_xplan之display函数的使用
执行计划中各字段各模块描述
使用 EXPLAIN PLAN 获取SQL语句执行计划
启用 AUTOTRACE 功能
函数使得索引列失效
Oracle 绑定变量窥探
Oracle 自适应共享游标
NULL值是关系数据库系统布尔型(true,false,unknown)中比较特殊类型的一种值，通常称为UNKNOWN或空值，即是未知的，不确定的。由于
NULL存在着无数的可能，因此NULL值也不等于NULL值，所以与NULL值相关的操作同样都为NULL值。正是基于这样一个特性，对于NULL值列上的B
树索引导致了is null/is not null不走索引的情形，下面描述了NULL值与索引以及索引NULL列上的执行计划，如何使得NULL值走索引的情形。
注：本文仅仅讨论的是B树索引上的NULL值，位图索引不在此范围之内。
一、null值与索引的关系
1. scott@ORCL> create table
2.
3. –>为表t1创建唯一索引
4. scott@ORCL> create unique index i_t1_id on
5.
6. scott@ORCL> insert into t1 select null,‘Y’ from
7.
8. scott@ORCL> insert into t1 select null,‘N’ from
9.
10. –>从上面的操作可知，尽管列id上存在唯一索引，但由于null值不等于任一null值，因此能够成功插入
11. scott@ORCL> commit;
12.
13. –>再次为表添加唯一复合索引，即基于id列与val列
14. scott@ORCL> create unique index i_t1_id_val on
15.
16. Index
17.
18. –>插入null,’N’的记录时失败，提示违反唯一性约束
19. scott@ORCL> insert into t1 select null,‘N’ from
20. insert into t1 select null,‘N’ from
21. *
22. ERROR at
23. ORA-00001: unique constraint
24.
25. –>插入null,’Y’的记录时同样失败，提示违反唯一性约束
26. scott@ORCL> insert into t1 select null,‘Y’ from
27. insert into t1 select null,‘Y’ from
28. *
29. ERROR at
30. ORA-00001: unique constraint
31.
32. –>插入两个null值成功
33. scott@ORCL> insert into t1 select null,null from
34.
35. 1 row created.
36.
37. scott@ORCL> insert into t1 select null,null from
38.
39. 1 row created.
40.
41. scott@ORCL> insert into t1 select null,‘A’ from
42.
43. 1 row created.
44.
45. scott@ORCL> commit;
46.
47. Commit
48.
49. scott@ORCL> set null
50. scott@ORCL> select * from
51.
52. ID VAL
53. ———- ——————————
54. unknown Y
55. unknown N
56. unknown unknown
57. unknown unknown
58. unknown A
59.
60. scott@ORCL> exec dbms_stats.gather_table_stats(‘SCOTT’,‘T1’,cascade=>true);
61.
62. scott@ORCL> select
63. from user_indexes where table_name=‘T1’;
64.
65. INDEX_NAME INDEX_TYPE BLEVEL LEAF_BLOCKS NUM_ROWS STATUS DISTINCT_KEYS
66. ————— ———- ———- ———– ———- ——– ————-
67. I_T1_ID NORMAL 0 0 0 VALID 0
68. I_T1_ID_VAL NORMAL 0 1 3 VALID 3
69.
70. –>从上面的情形可知，
71. –>基于单列的唯一索引，可以多次插入null值，但其索引上并不存储null值。
72. –>基于多列的复合索引，尽管全为null值的行可以多次插入，但不全为null的重复行则不能被插入(注,非唯一复合索引不存在此限制，此处不演示)。
73. –>基于多列的复合索引，对于全为null值的索引值也不会被存储。如上面的情形，尽管插入了5条记录，复合索引中只存储了3条。
74. –>注：对于唯一性约束，null值不等于null值，同样(null,null)也不等同于(null,null)，所以上面的两次null能够被插入。
scott@ORCL> create table t1(id number,val varchar2(1));
-->为表t1创建唯一索引
scott@ORCL> create unique index i_t1_id on t1(id);
scott@ORCL> insert into t1 select null,'Y' from dual;
scott@ORCL> insert into t1 select null,'N' from dual;
-->从上面的操作可知，尽管列id上存在唯一索引，但由于null值不等于任一null值，因此能够成功插入
scott@ORCL> commit;
-->再次为表添加唯一复合索引，即基于id列与val列
scott@ORCL> create unique index i_t1_id_val on t1(id,val);
Index created.
-->插入null,'N'的记录时失败，提示违反唯一性约束
scott@ORCL> insert into t1 select null,'N' from dual;
insert into t1 select null,'N' from dual
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.I_T1_ID_VAL) violated
-->插入null,'Y'的记录时同样失败，提示违反唯一性约束
scott@ORCL> insert into t1 select null,'Y' from dual;
insert into t1 select null,'Y' from dual
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.I_T1_ID_VAL) violated
-->插入两个null值成功
scott@ORCL> insert into t1 select null,null from dual;
1 row created.
scott@ORCL> insert into t1 select null,null from dual;
1 row created.
scott@ORCL> insert into t1 select null,'A' from dual;
1 row created.
scott@ORCL> commit;
Commit complete.
scott@ORCL> set null unknown;
scott@ORCL> select * from t1;
ID VAL
---------- ------------------------------
unknown Y
unknown N
unknown unknown
unknown unknown
unknown A
scott@ORCL> exec dbms_stats.gather_table_stats('SCOTT','T1',cascade=>true);
scott@ORCL> select index_name,index_type,blevel,leaf_blocks,num_rows,status,distinct_keys
2 from user_indexes where table_name='T1';
INDEX_NAME INDEX_TYPE BLEVEL LEAF_BLOCKS NUM_ROWS STATUS DISTINCT_KEYS
--------------- ---------- ---------- ----------- ---------- -------- -------------
I_T1_ID NORMAL 0 0 0 VALID 0
I_T1_ID_VAL NORMAL 0 1 3 VALID 3
-->从上面的情形可知，
-->基于单列的唯一索引，可以多次插入null值，但其索引上并不存储null值。
-->基于多列的复合索引，尽管全为null值的行可以多次插入，但不全为null的重复行则不能被插入(注,非唯一复合索引不存在此限制，此处不演示)。
-->基于多列的复合索引，对于全为null值的索引值也不会被存储。如上面的情形，尽管插入了5条记录，复合索引中只存储了3条。
-->注：对于唯一性约束，null值不等于null值，同样(null,null)也不等同于(null,null)，所以上面的两次null能够被插入。
二、null值与执行计划
[sql]
view plain
copy
print
?
1. scott@ORCL> set
2. scott@ORCL> select * from t1 where id is null;
3.
4. Execution Plan
5. ———————————————————-
6. Plan hash value: 3617692013
7.
8. ————————————————————————–
9. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
10. ————————————————————————–
11. | 0 | SELECT
12. |* 1 | TABLE ACCESS FULL| T1 | 5 | 5 | 3 (0)| 00:00:01 |
13. ————————————————————————–
14.
15. Predicate Information (identified by
16. —————————————————
17.
18. ”ID” IS NULL)
19.
20. –>从上面的测试可知，由于null值是不被存储的，因此当使用id is null作为谓词时，走了全表扫描
21.
22. scott@ORCL> select * from t1 where id is not null;
23.
24. Execution Plan
25. ———————————————————-
26. Plan hash value: 796913935
27.
28. —————————————————————————————
29. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
30. —————————————————————————————
31. | 0 | SELECT
32. | 1 | TABLE ACCESS BY INDEX
33. |* 2 | INDEX FULL
34. —————————————————————————————
35.
36. Predicate Information (identified by
37. —————————————————
38.
39. ”ID” IS NOT NULL)
40.
41. –>从上面的测试可知，尽管当前表上id列上的所有值都为null，但不排除后续记录插入的id不为null的列。
42. –>故当使用id is not null作为谓词时，此时执行计划中走了索引全扫描。
43.
44. –>下面来看看复合索引的情形
45. scott@ORCL> select * from t1 where val is null;
46.
47. Execution Plan
48. ———————————————————-
49. Plan hash value: 3617692013
50.
51. ————————————————————————–
52. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
53. ————————————————————————–
54. | 0 | SELECT
55. |* 1 | TABLE ACCESS FULL| T1 | 2 | 2 | 3 (0)| 00:00:01 |
56. ————————————————————————–
57.
58. Predicate Information (identified by
59. —————————————————
60.
61. ”VAL” IS NULL)
62.
63. scott@ORCL> select * from t1 where val is not null;
64.
65. Execution Plan
66. ———————————————————-
67. Plan hash value: 1931510411
68.
69. ——————————————————————————–
70. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
71. ——————————————————————————–
72. | 0 | SELECT
73. |* 1 | INDEX FULL
74. ——————————————————————————–
75.
76. Predicate Information (identified by
77. —————————————————
78.
79. ”VAL” IS NOT NULL)
80.
81. –>对于复合唯一索引的情形，当使用单列且非前导列谓词时，使用is null与 is not null等同于单列唯一索引的情形。
82. –>即原理也是一样的，val is null走全表扫描而val is not null走索引。因为null值不会被存储。
83.
84. –>下面看看两个列都作为谓词的情形
85. scott@ORCL> select * from t1 where id is null and val is not null;
86.
87. Execution Plan
88. ———————————————————-
89. Plan hash value: 1040510552
90.
91. ——————————————————————————–
92. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
93. ——————————————————————————–
94. | 0 | SELECT
95. |* 1 | INDEX
96. ——————————————————————————–
97.
98. Predicate Information (identified by
99. —————————————————
100.
101. ”ID” IS NULL)
102. ”VAL” IS NOT NULL)
103.
104. –>从上面的测试可知，尽管两个谓词列上都存在索引，一个为单列唯一索引，一个为复合唯一索引。Oracle 选择了复合索引I_T1_ID_VAL。
105.
106. scott@ORCL> select * from t1 where id is not null and val is null;
107.
108. Execution Plan
109. ———————————————————-
110. Plan hash value: 796913935
111.
112. —————————————————————————————
113. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
114. —————————————————————————————
115. | 0 | SELECT
116. |* 1 | TABLE ACCESS BY INDEX
117. |* 2 | INDEX FULL
118. —————————————————————————————
119.
120. Predicate Information (identified by
121. —————————————————
122.
123. ”VAL” IS NULL)
124. ”ID” IS NOT NULL)
125.
126. –>同样的情形，谓词的顺序与复合索引定义的顺序一样，只不过第一个谓词为id is not null，而第二个谓词为val is null。
127. –>此时Oracle 选择了单列唯一索引I_T1_ID
128. –>看到此，不知道大家是否已明白，即哪个列为is not null，则会使用该列上的索引，原因还是那句话，索引不存储null值。
129. –>对于颠倒id列与val列以及id,val列为null或not null的其他不同组合情形不再演示，其执行计划类似。
scott@ORCL> set autot trace exp;
scott@ORCL> select * from t1 where id is null;
