NULL 值与索引

短命女 2022-09-27 14:27 159阅读 0赞

NULL值是关系数据库系统布尔型(true,false,unknown)中比较特殊类型的一种值，通常称为UNKNOWN或空值，即是未知的，不确定的。由于  
NULL存在着无数的可能，因此NULL值也不等于NULL值，所以与NULL值相关的操作同样都为NULL值。正是基于这样一个特性，对于NULL值列上的B  
树索引导致了is null/is not null不走索引的情形，下面描述了NULL值与索引以及索引NULL列上的执行计划，如何使得NULL值走索引的情形。  
注：本文仅仅讨论的是B树索引上的NULL值，位图索引不在此范围之内。  
  
一、null值与索引的关系

**\[sql\]** view plain copy

print ?

1.  scott@ORCL> create table t1(id number,val varchar2(1));  
2.    
3.  –>为表t1创建唯一索引  
4.  scott@ORCL> create unique index i\_t1\_id on t1(id);  
5.    
6.  scott@ORCL> insert into t1 select null,‘Y’ from dual;  
7.    
8.  scott@ORCL> insert into t1 select null,‘N’ from dual;  
9.    
10. –>从上面的操作可知，尽管列id上存在唯一索引，但由于null值不等于任一null值，因此能够成功插入  
11. scott@ORCL> commit;  
12.   
13. –>再次为表添加唯一复合索引，即基于id列与val列  
14. scott@ORCL> create unique index i\_t1\_id\_val on t1(id,val);  
15.   
16. Index created.  
17.   
18. –>插入null,’N’的记录时失败，提示违反唯一性约束  
19. scott@ORCL> insert into t1 select null,‘N’ from dual;  
20. insert into t1 select null,‘N’ from dual  
21. \*  
22. ERROR at line 1:  
23. ORA-00001: unique constraint (SCOTT.I\_T1\_ID\_VAL) violated  
24.   
25. –>插入null,’Y’的记录时同样失败，提示违反唯一性约束  
26. scott@ORCL> insert into t1 select null,‘Y’ from dual;  
27. insert into t1 select null,‘Y’ from dual  
28. \*  
29. ERROR at line 1:  
30. ORA-00001: unique constraint (SCOTT.I\_T1\_ID\_VAL) violated  
31.   
32. –>插入两个null值成功  
33. scott@ORCL> insert into t1 select null,null from dual;  
34.   
35. 1 row created.  
36.   
37. scott@ORCL> insert into t1 select null,null from dual;  
38.   
39. 1 row created.  
40.   
41. scott@ORCL> insert into t1 select null,‘A’ from dual;  
42.   
43. 1 row created.  
44.   
45. scott@ORCL> commit;  
46.   
47. Commit complete.  
48.   
49. scott@ORCL> set null unknown;  
50. scott@ORCL> select \* from t1;  
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 index\_name,index\_type,blevel,leaf\_blocks,num\_rows,status,distinct\_keys  
63.   2  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能够被插入。

![save_snippets.png][]

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 autot trace exp;  
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 STATEMENT  |      |     5 |     5 |     3   (0)| 00:00:01 |  
12. |\*  1 |  TABLE ACCESS FULL| T1   |     5 |     5 |     3   (0)| 00:00:01 |  
13. ————————————————————————–  
14.   
15. Predicate Information (identified by operation id):  
16. —————————————————  
17.   
18.    1 - filter(”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 STATEMENT            |         |     1 |     1 |     0   (0)| 00:00:01 |  
32. |   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     1 |     1 |     0   (0)| 00:00:01 |  
33. |\*  2 |   INDEX FULL SCAN           | I\_T1\_ID |     1 |       |     0   (0)| 00:00:01 |  
34. —————————————————————————————  
35.   
36. Predicate Information (identified by operation id):  
37. —————————————————  
38.   
39.    2 - filter(”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 STATEMENT  |      |     2 |     2 |     3   (0)| 00:00:01 |  
55. |\*  1 |  TABLE ACCESS FULL| T1   |     2 |     2 |     3   (0)| 00:00:01 |  
56. ————————————————————————–  
57.   
58. Predicate Information (identified by operation id):  
59. —————————————————  
60.   
61.    1 - filter(”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 STATEMENT |             |     3 |     3 |     1   (0)| 00:00:01 |  
73. |\*  1 |  INDEX FULL SCAN | I\_T1\_ID\_VAL |     3 |     3 |     1   (0)| 00:00:01 |  
74. ——————————————————————————–  
75.   
76. Predicate Information (identified by operation id):  
77. —————————————————  
78.   
79.    1 - filter(”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 STATEMENT |             |     3 |     3 |     1   (0)| 00:00:01 |  
95. |\*  1 |  INDEX RANGE SCAN| I\_T1\_ID\_VAL |     3 |     3 |     1   (0)| 00:00:01 |  
96. ——————————————————————————–  
97.   
98. Predicate Information (identified by operation id):  
99. —————————————————  
100.   
101.    1 - access(”ID” IS NULL)  
102.        filter(”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 STATEMENT            |         |     1 |     1 |     0   (0)| 00:00:01 |  
116. |\*  1 |  TABLE ACCESS BY INDEX ROWID| T1      |     1 |     1 |     0   (0)| 00:00:01 |  
117. |\*  2 |   INDEX FULL SCAN           | I\_T1\_ID |     1 |       |     0   (0)| 00:00:01 |  
118. —————————————————————————————  
119.   
120. Predicate Information (identified by operation id):  
121. —————————————————  
122.   
123.    1 - filter(”VAL” IS NULL)  
124.    2 - filter(”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的其他不同组合情形不再演示，其执行计划类似。

![save_snippets.png][]

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走索引的情形

**\[sql\]** view plain copy

print ?

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 deleted.  
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 complete.  
18.   
19. –>对原有记录更新后的情形  
20. scott@ORCL> select \* from t1;  
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 successfully completed.  
30.   
31. –>修改表列id使之具有not null约束的特性  
32. scott@ORCL> alter table t1 modify(id not null);  
33.   
34. Table altered.  
35.   
36. scott@ORCL> set autot trace exp;  
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 STATEMENT |             |     1 |     5 |     0   (0)|          |  
47. |\*  1 |  FILTER          |             |       |       |            |          |  
48. |   2 |   INDEX FULL SCAN| I\_T1\_ID\_VAL |     2 |    10 |     1   (0)| 00:00:01 |  
49. ——————————————————————————–  
50.   
51. Predicate Information (identified by operation id):  
52. —————————————————  
53.   
54.    1 - filter(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 STATEMENT     |             |     1 |     5 |     2   (0)| 00:00:01 |  
69. |\*  1 |  INDEX FAST FULL SCAN| I\_T1\_ID\_VAL |     1 |     5 |     2   (0)| 00:00:01 |  
70. ————————————————————————————  
71.   
72. Predicate Information (identified by operation id):  
73. —————————————————  
74.   
75.    1 - filter(”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 dual;  
82.   
83. 1 row created.  
84.   
85. scott@ORCL> commit;  
86.   
87. Commit complete.  
88.   
89. scott@ORCL> exec dbms\_stats.gather\_table\_stats(‘SCOTT’,‘T1’,cascade=>true);  
90.   
91. PL/SQL procedure successfully completed.  
92.   
93. –>下面的查询中可以看出尽管只有列id有not null约束，当所有的索引值都被存储  
94. scott@ORCL> select index\_name,index\_type,blevel,leaf\_blocks,num\_rows,status,distinct\_keys  
95.   2  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 :   http://blog.csdn.net/robinson\_0612

![save_snippets.png][]

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 : http://blog.csdn.net/robinson\_0612 四、总结  
    无论是单列唯一索引或复合唯一索引，对于可以为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)下的情形，不同的优化器版本可能会有偏差。

五、更多参考[  
][Link 1]

[NULL 值与索引(二)  
][NULL _]

[SQL tuning 步骤][Link 1]

[高效SQL语句必杀技  
][SQL_]

[父游标、子游标及共享游标][Link 2]

[绑定变量及其优缺点][Link 3]

[dbms\_xplan之display\_cursor函数的使用][dbms_xplan_display_cursor]

[dbms\_xplan之display函数的使用][dbms_xplan_display]

[执行计划中各字段各模块描述][Link 4]

[使用 EXPLAIN PLAN 获取SQL语句执行计划][EXPLAIN PLAN _SQL]

[启用 AUTOTRACE 功能][AUTOTRACE]

[函数使得索引列失效][Link 5]

[Oracle 绑定变量窥探][Oracle]

[Oracle 自适应共享游标][Oracle 1]

[save_snippets.png]: /images/20220708/c8161189dc8441bc986cf82131ecb1d7.png
[Link 1]: http://blog.csdn.net/robinson_0612/article/details/7422254
[NULL _]: http://blog.csdn.net/robinson_0612/article/details/7438397
[SQL_]: http://blog.csdn.net/robinson_0612/article/details/7406672
[Link 2]: http://blog.csdn.net/robinson_0612/article/details/6893477
[Link 3]: http://blog.csdn.net/robinson_0612/article/details/6904229
[dbms_xplan_display_cursor]: http://blog.csdn.net/robinson_0612/article/details/6866925
[dbms_xplan_display]: http://blog.csdn.net/robinson_0612/article/details/6866870
[Link 4]: http://blog.csdn.net/robinson_0612/article/details/6860007
[EXPLAIN PLAN _SQL]: http://blog.csdn.net/robinson_0612/article/details/6837771
[AUTOTRACE]: http://blog.csdn.net/robinson_0612/article/details/6030407
[Link 5]: http://blog.csdn.net/robinson_0612/article/details/6851973
[Oracle]: http://blog.csdn.net/robinson_0612/article/details/6923627
[Oracle 1]: http://blog.csdn.net/robinson_0612/article/details/6923670