Oracle中查询时候使index索引失效的限制条件 - 数据库编程

Oracle中查询时候使index索引失效的限制条件昨天，由于最近的项目需要进入到测试人员进行测试的阶段。因此，自己搭建好了测试环境---进行了测试。但是，奇怪的事情就发生了。以前在我自己本地开发的环境的时候却没有碰到这个问题。
由于在测试环境执行的查询的时候，不管怎么做，总是会查询失败，并且前台抛出“无法连接，请联系系统管理员”异常，开始，我就不断的跟踪这个异常，第一:在前台找了好久也设置了相应的response====timeout时间参数为60s。再去执行，还是查询失败。因此，否定了这个原因. 第二：我使用Debug模式去调试，打了一个断点去看看sql执行时候的状态。此时，当前台抛出异常的时候，后台却还在正常的执行查询，并且查询出了相应的结果。此刻，我就断定应该是sql查询时间太久前台没来得及响应。因此，查询失败。此时，我在切换到自己以前开发的DB URL，在开发环境下面还是能够查询出相应的结果而且查询也挺快的。这个时候我就郁闷了。我把查询的SQL 拿出来在PL/SQL中执行了一下。靠，果然是SQL 执行太慢分页查询20条记录也要16秒,最初的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 1=1 AND 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)) order by tran_date desc ) temp where rownum <= 20) where row_id > 0

此时，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 子句中使用函数

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

select * from staff where trunc(birthdate) = '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'

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