索引中空值的问题

创建一张测试表：
SQL> create table t1 as select * from dba_objects;
表已创建。
SQL> insert into t1(object_name) values(null);
已创建 1 行
SQL> commit;
提交完成。
SQL> create index idx_T1 on t1(object_name);
索引已创建。
SQL> set autot trace
SQL> select object_name from t1;
已选择72552行。




执行计划
----------------------------------------------------------
Plan hash value: 3617692013
-------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 77386 |  4987K|   240   (1)| 00:00:03 |
|   1 |  TABLE ACCESS FULL| T1   | 77386 |  4987K|   240   (1)| 00:00:03 |
--------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)




统计信息
----------------------------------------------------------
         28  recursive calls
          0  db block gets
       5895  consistent gets
       1356  physical reads
          0  redo size
    2744733  bytes sent via SQL*Net to client
      53716  bytes received via SQL*Net from client
       4838  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72552  rows processed
但是这样查询出来的结果不包含NULL值
SQL> select /*+ index_ffs(t1,idx_t1) */ object_name from t1 where object_name is not null;


已选择72551行。

执行计划
----------------------------------------------------------
Plan hash value: 1387720244


-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        | 77386 |  4987K|    85   (2)| 00:00:02 |
|*  1 |  INDEX FAST FULL SCAN| IDX_T1 | 77386 |  4987K|    85   (2)| 00:00:02 |
-------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("OBJECT_NAME" IS NOT NULL)


Note
-----
   - dynamic sampling used for this statement (level=2)




统计信息
----------------------------------------------------------
          4  recursive calls
          0  db block gets
       5257  consistent gets
          1  physical reads
          0  redo size
    2301807  bytes sent via SQL*Net to client
      53716  bytes received via SQL*Net from client
       4838  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72551  rows processed




SQL>
SQL> drop index idx_t1
  2  ;


索引已删除。


方法：
创建一个组合索引，使用虚拟列创建多列索引 。
SQL> create index idx_t1 on t1(object_name,1);


索引已创建。


SQL> select /*+ index_ffs(t1,idx_t1) */ object_name from t1 where object_name is not null;


已选择72551行。




执行计划
----------------------------------------------------------
Plan hash value: 1387720244


-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        | 77386 |  4987K|    92   (2)| 00:00:02 |
|*  1 |  INDEX FAST FULL SCAN| IDX_T1 | 77386 |  4987K|    92   (2)| 00:00:02 |
-------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("OBJECT_NAME" IS NOT NULL)


Note
-----
   - dynamic sampling used for this statement (level=2)




统计信息
----------------------------------------------------------
         63  recursive calls
          0  db block gets
       5286  consistent gets
        392  physical reads
          0  redo size
    2301807  bytes sent via SQL*Net to client
      53716  bytes received via SQL*Net from client
       4838  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72551  rows processed


SQL> select /*+ index_ffs(t1,idx_t1) */ object_name from t1;


已选择72552行。




执行计划
----------------------------------------------------------
Plan hash value: 1387720244


-------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        | 77386 |  4987K|    92   (2)| 00:00:02 |
|   1 |  INDEX FAST FULL SCAN| IDX_T1 | 77386 |  4987K|    92   (2)| 00:00:02 |
-------------------------------------------------------------------------------


Note
-----
   - dynamic sampling used for this statement (level=2)




统计信息
----------------------------------------------------------
        192  recursive calls
          0  db block gets
       5307  consistent gets
          0  physical reads
          0  redo size
    2301813  bytes sent via SQL*Net to client
      53716  bytes received via SQL*Net from client
       4838  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
      72552  rows processed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31402276/viewspace-2128501/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31402276/viewspace-2128501/