一则对or条件的优化处理

开发人员提交一个执行sql:

DELETE SMS_LOVERMOBILENUMBER AWHERE USERNUMBER = :B1OR A.LOVERMOBILENUMBER =LTRIM(:B1 ,'86');

 

SMS_LOVERMOBILENUMBER的表结构以及索引如下：

create table OSS01.SMS_LOVERMOBILENUMBER

(

  USERNUMBER        VARCHAR2(32) not null,

  LOVERMOBILENUMBER VARCHAR2(32) not null,

  CREATETIME        DATE default sysdate,

  MODIFYTIME        DATE default sysdate       /*修改日期      */,

  AGREE             NUMBER(5) default 0,

  SEX               NUMBER(5) default 0

)  ;

alter table OSS01.SMS_LOVERMOBILENUMBER

  add constraint SMS_LOVERMOBILENUMBER_PK primary key

 

这种sql提交上来的时候 , dba第一反应就要注意到or会影响到索引的使用:结果查看执行计划，确实是走全表:

从上面的表结构来看，很明显开发人员是希望sql语句利用主键搜索来快速删除数据，但事实上执行计划却走了全表扫描，为什么？

 

WHERE USERNUMBER = :B1肯定会走主键，那么后面的查询条件会怎么走呢，后面的查询如果要走索引只能走索引跳跃式扫描：由于第一列usernumber

（手机号）的业务特性，所以这个效率是非常低的，注意跳跃式扫描一定要注意第一列的distinct 值要少（这个主要是因为对于第一列，跳跃式扫描是穷举法使用组合索引）

 

那这个时候很多人会使用这样:

Create index oss01.ix_SMS_LOVERMOBILENUMBER on SMS_LOVERMOBILENUMBER(LOVERMOBILENUMBER);

 

建立了这个索引之后这个查询的性能就高了吗？

注意这个表是无法使用同时走两个索引的，除非你走位图合并： 执行计划如下:

SELECT STATEMENT, GOAL = ALL_ROWS 

    TABLE ACCESS BY INDEX ROWID    SMS_LOVERMOBILENUMBER 

     BITMAP CONVERSION TO ROWIDS            

      BITMAP OR             

       BITMAP CONVERSION FROM ROWIDS            

        INDEX RANGE SCAN         SMS_LOVERMOBILENUMBER_PK    

       BITMAP CONVERSION FROM ROWIDS            

        INDEX RANGE SCAN         ix_SMS_LOVERMOBILENUMBER

上面的执行计划的流程：分别对索引SMS_LOVERMOBILENUMBER_PK和ix_SMS_LOVERMOBILENUMBER进行扫描，然后把两个扫描的rowid结果集进行位图合并。合并后再通过rowid访问表SMS_LOVERMOBILENUMBER。

那有没有更好的执行计划呢？

 

其实只需要改写如下sql就可以:

(使用union 还是union all)  可以由业务决定

Select * from SMS_LOVERMOBILENUMBER AWHERE USERNUMBER = :B1

union all

Select * from SMS_LOVERMOBILENUMBER AWHERE A.LOVERMOBILENUMBER =LTRIM(:B1 ,'86');

 

改写完之后效率已经是当前最高