文章标题

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