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的条目

ITPub博客

关于NULL值在索引里的两个疑惑

最新文章