开发人员提交一个执行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');
改写完之后效率已经是当前最高