首页 > 代码库 > Oracle中查询时候使index索引失效的限制条件

Oracle中查询时候使index索引失效的限制条件

2024-10-18 08:53:02   212人阅读

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                                       18877620 9940
 VIEW                                                             CLEAR_TEST                       188776 209940
  COUNT STOPKEY 
   VIEW                                                             CLEAR_TEST                        188776 10550820
    SORT ORDER BY STOPKEY                                                                  188776105 29820
     NESTED LOOPS                                                                                  188775105 29820
      NESTED LOOPS                                                                                  188775105 29820
       TABLE ACCESS FULL                            CLEAR_TEST TRADEWASTE  188565105 21630
       INDEX UNIQUE SCAN                             CLEAR_TESTPK_BSTEAM           11 
      TABLE ACCESS BY INDEX ROWID            CLEAR_TESTBSTEAM                 2178
      
当我再去查询开发环境的时候，其总记录数才上百条-----所以，本地开发的环境没有出现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                                                               81 497
 VIEW                                                            CLEAR_TEST                                                81 497
  COUNT STOPKEY 
   VIEW                                                            CLEAR_TEST                                                81 484
    NESTED LOOPS                                                                                                         81 284
     NESTED LOOPS                                                                                                         81 284
      TABLE ACCESS BY INDEX ROWID           CLEAR_TESTTRADEWASTE                         61 206
       INDEX RANGE SCAN DESCENDING  CLEAR_TEST INDEX_TRAN_DATE                4 2
      INDEX UNIQUE SCAN                           CLEAR_TESTPK_BSTEAM                                 11 
     TABLE ACCESS BY INDEX ROWID           CLEAR_TESTBSTEAM                                         21 78

相比之前的SQL  执行计划采用索引扫描的效率明显高于全表扫描的结果。

同时：对于字段CLEAR_DATE也有索引，为什么？索引扫描的时候  没有扫该索引呢。这样的话  应该更加快的(答案揭晓为:对于非唯一索引，这种条件下oracle不会合并索引。它只会扫描第一个索引，因此不会扫描多个)

查阅相关的资料后，Oracle中使索引失效一些限制条件有(参考博客地址:http://www.cnblogs.com/orientsun/archive/2012/07/05/2577351.html)
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 函数，Oracle 仍然使用索引。

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';





Oracle中查询时候使index索引失效的限制条件






























        

            
                
                  
                
                

                    联系

                    我们