文章标题

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的其他不同组合情形不再演示，其执行计划类似。