Oracle中查询时候使index索引失效的限制条件 昨天,由于最近的项目需要进入到测试人员进行测试的阶段。因此,自己搭建好了测试环境---进行了测试。但是,奇怪的事情就发生了。以前在我自己本地开发的环境的时候却没有碰到这个问题。
由于在测试环境执行的查询的时候,不管怎么做,总是会查询失败,并且前台抛出“无法连接,请联系系统管理员”异常,开始,我就不断的跟踪这个异常, 第一:在前台找了好久 也设置了相应的response====timeout时间参数为60s。再去执行,还是查询失败。因此,否定了这个原因. 第二:我使用Debug模式去调试,打了一个断点 去看看sql执行时候的状态。此时,当前台抛出异常的时候,后台却还在正常的执行查询,并且查询出了相应的结果。此刻,我就断定应该是sql查询时间太久 前台没来得及响应。因此,查询失败。 此时,我在切换到 自己以前开发的DB URL,在开发环境下面还是能够查询出相应的结果 而且查询也挺快的。这个时候 我就郁闷了。我把查询的SQL 拿出来 在PL/SQL中执行了一下。靠,果然是SQL 执行太慢 分页查询20条记录 也要16秒,最初的SQL语句如下:
<span style="color:#333333;">select * from ( select temp.*, rownum row_id from ( select CLEAR_MERCHID,MERCH_NAME,AMOUNT,decode(status,'0','待确认','1','完成','2','已冲正') as status ,RATE,FEE,BROKER_CHARGE,UNION_CHARGE,ACT_AMOUNT,RCV_AMOUNT ,RESP_CODE,VOUCHNO,CARDNO,TRAN_DATE,STLEXDAY,CLEAR_DATE,SETTLE_DATE_FN ,decode(TRAN_TYPE,'0','签到','1','信用卡还款','2','信用卡还款冲正','3','公共事业缴费','4','公共事业缴费冲正','5','手机话费充值','6','余额查询','7','公共事业账单查询' ,'8','上海公共事业缴费','9','消费','@','消费冲正','P','卡卡转账','E','北京账单查询','F','北京公共事业缴费','G','北京公共事业缴费冲正','W','四川公共支付账单查询','X','四川公共支付账单缴费',TRAN_TYPE) as TRAN_TYPE ,FLAGNAME,BROKERID ,IBOX_ID,REFNO,ERR_CODE,MCH_TYPE ,decode(CHANNL_ID,'2001','上海银商','2002','深圳银商','2003','易宝支付','2004', '银视通支付','2005','光大银行','2006','友邦多渠道(上海银联)' ,'2007','海科融通','2008','腾付通','2009','快钱支付','2010','浦发银行','2011','农业银行','2012','讯联支付','2013','宁波通商' ,'2014','卡富通支付','2015','联动优势','2016','上海轩辰','2017','支付宝收单','2018','微信支付','2019','翰鑫支付' ,CHANNL_ID) as CHANNL_ID ,SETTLEBATCH,ORDER_ID,TRAN_MERCHID,TRAN_TERMID,SRC_ID from vwsaledetail where 1=1 AND </span><span style="color:#cc66cc;">to_number(to_char(to_date(TRAN_DATE,'yyyy-mm-dd hh24:mi:ss'),'YYYYMMDD')) >= to_number(substr(?,0,8)) AND to_number(to_char(to_date(TRAN_DATE,'yyyy-mm-dd hh24:mi:ss'),'YYYYMMDD')) <= to_number(substr(?,0,8)) AND to_number(CLEAR_DATE) >= to_number(substr(?,0,8)) AND to_number(CLEAR_DATE) <= to_number(substr(?,0,8)</span><span style="color:#333333;">) order by tran_date desc ) temp where rownum <= 20) where row_id > 0 </span>  

此时,F5看了一下SQL的执行计划:发现 这个时候的查询执行的是全表扫描。而且数据量很大 总得记录数为上千万条。因此,查询 失败。 SELECT STATEMENT, GOAL = ALL_ROWS 188776 20 9940
   SELECT STATEMENT, GOAL = ALL_ROWS
                                       188776
20 9940
 VIEW                                                             CLEAR_TEST
                       188776 20
9940
  COUNT STOPKEY 
   VIEW                                                             CLEAR_TEST
                        188776 105
50820
    SORT ORDER BY STOPKEY                                                                  188776
105 29820
     NESTED LOOPS                                                                                  188775
105 29820
      NESTED LOOPS                                                                                  188775
105 29820
       TABLE ACCESS FULL
                            CLEAR_TEST  TRADEWASTE  188565
105 21630
       INDEX UNIQUE SCAN                             CLEAR_TEST
PK_BSTEAM           1
1 
      TABLE ACCESS BY INDEX ROWID
            CLEAR_TEST
BSTEAM
                 2
1
78
当我再去查询开发环境的时候,其总记录数才上百条-----所以,本地开发的环境没有出现SQL效率问题。因此,发现问题的所在。接下来便是进行了SQL优化。。。。由于,本人也是菜鸟一枚 对于这种比较常见的SQL优化问题 以前没真正碰到过。所以 写出来的SQL效率也很差。
我就想 到底是哪里出了问题,经过查阅一定的资料后 看到了
WHERE 子句中使用函数

<span style="color:#ff0000;">如果没有使用基于函数的索引,那么 where 子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引</span>。例如:

select * from staff where <span style="color:#ff6666;">trunc(birthdate)</span> = '01-MAY-82';

但是把函数应用在条件上,索引是可以生效的,把上面的语句改成下面的语句,就可以通过索引进行查找。

select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);

然而,我查询的视图中关联的表中 TRAN_DATE和CLEAR_DATE上都建立了索引。因此,我想,问题应该就是出在这里了。
修改了相应的SQL后:
select * from ( select temp.*, rownum row_id from ( select CLEAR_MERCHID,MERCH_NAME,AMOUNT,decode(status,'0','待确认','1','完成','2','已冲正') as status ,RATE,FEE,BROKER_CHARGE,UNION_CHARGE,ACT_AMOUNT,RCV_AMOUNT ,RESP_CODE,VOUCHNO,CARDNO,TRAN_DATE,STLEXDAY,CLEAR_DATE,SETTLE_DATE_FN ,decode(TRAN_TYPE,'0','签到','1','信用卡还款','2','信用卡还款冲正','3','公共事业缴费','4','公共事业缴费冲正','5','手机话费充值','6','余额查询','7','公共事业账单查询' ,'8','上海公共事业缴费','9','消费','@','消费冲正','P','卡卡转账','E','北京账单查询','F','北京公共事业缴费','G','北京公共事业缴费冲正','W','四川公共支付账单查询','X','四川公共支付账单缴费',TRAN_TYPE) as TRAN_TYPE ,FLAGNAME,BROKERID ,IBOX_ID,REFNO,ERR_CODE,MCH_TYPE ,decode(CHANNL_ID,'2001','上海银商','2002','深圳银商','2003','易宝支付','2004', '银视通支付','2005','光大银行','2006','友邦多渠道(上海银联)' ,'2007','海科融通','2008','腾付通','2009','快钱支付','2010','浦发银行','2011','农业银行','2012','讯联支付','2013','宁波通商' ,'2014','卡富通支付','2015','联动优势','2016','上海轩辰','2017','支付宝收单','2018','微信支付','2019','翰鑫支付' ,CHANNL_ID) as CHANNL_ID ,SETTLEBATCH,ORDER_ID,TRAN_MERCHID,TRAN_TERMID,SRC_ID from vwsaledetail WHERE <span style="color:#cc66cc;">TRAN_DATE >= ? AND TRAN_DATE <= ? AND CLEAR_DATE >= ? AND CLEAR_DATE <= ? order by tran_date desc</span> ) temp where rownum <= 40) where row_id > 20   

替而代之 上面的SQL后 执行F5 ========观察到SQL执行计划进行了索引扫描:
SELECT STATEMENT, GOAL = ALL_ROWS
                                                               8
1 497
 VIEW                                                            CLEAR_TEST
                                                8
1 497
  COUNT STOPKEY 
   VIEW                                                            CLEAR_TEST
                                                8
1 484
    NESTED LOOPS                                                                                                         8
1 284
     NESTED LOOPS                                                                                                         8
1 284
      TABLE ACCESS BY INDEX ROWID           CLEAR_TEST
TRADEWASTE                         6
1 206
       INDEX RANGE SCAN DESCENDING
  CLEAR_TEST INDEX_TRAN_DATE
                4 2

      INDEX UNIQUE SCAN                           CLEAR_TEST
PK_BSTEAM                                 1
1 
     TABLE ACCESS BY INDEX ROWID           CLEAR_TEST
BSTEAM                                         2
1 78

相比之前的SQL 执行计划采用索引扫描的效率明显高于全表扫描的结果。
同时:对于字段CLEAR_DATE也有索引,为什么?索引扫描的时候 没有扫该索引呢。这样的话 应该更加快的(答案揭晓为:对于非唯一索引,这种条件下oracle不会合并索引。它只会扫描第一个索引,因此不会扫描多个)
查阅相关的资料后,Oracle中使索引失效一些限制条件有(参考博客地址:http://www.cnblogs.com/orientsun/archive/2012/07/05/2577351.)
1. 没有 WHERE 子句

2. 使用 IS NULL 和 IS NOT NULL

SELECT ... FROM emp WHERE comm IS NULL; comm 列的索引会失效

3. WHERE 子句中使用函数

如果没有使用基于函数的索引,那么 where 子句中对存在索引的列使用函数时,会使优化器忽略掉这些索引。例如:

select * from staff where trunc(birthdate) = '01-MAY-82';

但是把函数应用在条件上,索引是可以生效的,把上面的语句改成下面的语句,就可以通过索引进行查找。

select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);

注意:对于 MIN, MAX 函数, 仍然使用索引。

4. 使用 LIKE ‘%T’ 进行模糊查询

5. WHERE 子句中使用不等于操作

不等于操作包括:<>, !=, NOT colum >= ?, NOT colum <= ?

对于这个限制条件可以通过 OR 替代,例如: colum <> 0  ===>   colum>0 OR colum<0

6. 等于和范围索引不会被合并使用

SELECT emp_id, emp_m, salary_q ... FROM emp WHERE job='manager' AND deptno>10

job 和 deptno 都是非唯一索引,这种条件下 oracle 不会合并索引,它只会使用第一个索引。

7. 比较不匹配数据类型

dept_id是一个varchar2型的字段,在这个字段上有索引,但是下面的语句会执行全表扫描。

select * from dept where dept_id = 900198;

这是因为 oracle 会自动把 where 子句转换成 to_number(dept_id)=900198,相当于使用函数,这样就限制了索引的使用。正确写法如下:

select * from dept where dept_id = '900198';