關於NULL值在索引裡的兩個疑惑

create table wxh_tbd as select * from dba_objects;

Table created.

create index t_s on wxh_tbd(object_id,OBJECT_NAME);

Index created.

select * from wxh_tbd where object_id is null and object_name>'WXH_TBD' order by object_name;

183 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2494076512

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |   246 | 50922 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY               |         |   246 | 50922 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| WXH_TBD |   246 | 50922 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_S     |     6 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

執行計劃部分竟然出現了排序？

這是因為NULL,1  NULL,2，ORACLE不知道誰大誰小，因為前面出現了ORACLE無法判斷大小的值NULL.

 

explain plan for
  2  select * from wxh_tbd where object_id is null and object_name='ss';

Explained.

@display

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 792848615

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     7 |  1449 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| WXH_TBD |     7 |  1449 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_S     |     3 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID" IS NULL AND "OBJECT_NAME"='ss')
       filter("OBJECT_NAME"='ss')
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
       1343  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

 

執行計劃部分出現了OBJECT_NAME的FILTER操作，但是OBJECT_NAME依然也出現在了ACCESS裡。ORACLE其實根據頁塊能夠精確定位到NULL,SS的記錄（其實只能精確定位到起始頁塊）。執行計劃出現的FILTER操作，只是顯示問題。跟ACCESS的效率是一樣的。

 

可是下面的filter操作則不是一回事了。

explain plan for
select * from wxh_tbd where object_id is null and object_name like '%ss%';

 

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     2 |   172 |     3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| WXH_TBD     |     2 |   172 |     3 |
|*  2 |   INDEX RANGE SCAN          | TSSAFA      |     2 |       |     2 |
---------------------------------------------------------------------------

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

   2 - access("WXH_TBD"."OBJECT_ID" IS NULL)
       filter("WXH_TBD"."OBJECT_NAME" LIKE '%ss%')

 

這個filter效能就比較差了。ORACLE要掃描所有為NULL的葉子塊，從中FILTER出object_name包含ss的條目