ITPub博客

首页 > 数据库 > Oracle > SQL条件中“is null”谓词导致全表扫描问题优化

SQL条件中“is null”谓词导致全表扫描问题优化

Oracle 作者:410192979 时间:2016-03-23 10:31:58 0 删除 编辑
前段时间针对相关SQL进行了优化,零散记录了下优化过程,这次整理出来与大家分享。若有描述不当或补充的地方请留言,谢谢!

方法1:用固定值建复合索引优化
    1)新建测试表
      SQL>create table hr_tab1(id number(10) not null,name varchar2(20) );
           
    2)未建索引之前执行计划
      SQL>explain plan for select * from hr_tab1 where name is null;
  
            Plan hash value: 2713387046

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    25 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| HR_TAB1 |     1 |    25 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
    1 - filter("NAME" IS NULL)
 
Note
-----
    - 'PLAN_TABLE' is old version
    - dynamic sampling used for this statement

      3)使用空值列name跟一个固定值建复合索引
             建复合索引:
          SQL>create index IND_HR_TAB1_HH1 on hr_tab1 (name,1)
           
     4)查看优化后执行计划
             执行SQL:
          SQL>explain plan for select * from hr_tab1 where name is null;

             Plan hash value: 538103671

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    25 |     0   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| HR_TAB1         |     1 |    25 |     0   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_HR_TAB1_HH1 |     1 |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
    2 - access("NAME" IS NULL)
 
Note
-----
    - 'PLAN_TABLE' is old version
    - dynamic sampling used for this statement (level=2)
           
    从执行计划上看,建了复合索引之后,在查询is null时走了范围扫描索引。
               
在这里有必要说明一下为什么会走索引范围扫描。索引范围扫描主要出现在以下些情况:
    在唯一索引上:在唯一索引上使用一个索引存取多行数据,这种情况索引范围扫描的典型情况是在谓词(where限制条件)中使用了范围  操作符(如>、<、<>、>=、<=、between)
    在非唯一索引上:当查询结果可能会返回多行数据时,所以会使用索引范围扫描。   

    执行SQL:explain plan for select name from hr_tab1 where name is null;
Plan hash value: 3977038940
 
------------------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |     1 |    12 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IND_HR_TAB1_HH1 |     1 |    12 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
    1 - access("NAME" IS NULL)
 
Note
-----
    - 'PLAN_TABLE' is old version
   - dynamic sampling used for this statement (level=2)

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

请登录后发表评论 登录
全部评论

注册时间:2014-03-08

  • 博文量
    153
  • 访问量
    110970