ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于NULL值在索引里的两个疑惑

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

原创 Linux操作系统 作者:wei-xh 时间:2011-12-27 20:11:39 0 删除 编辑
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博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-714034/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2284887