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

最新推荐文章于 2024-02-04 11:04:44 发布

caolipeng_918

最新推荐文章于 2024-02-04 11:04:44 发布

阅读量2.4k

点赞数

分类专栏： database 文章标签： Java oracle 索引优化

本文链接：https://blog.csdn.net/caolipeng_918/article/details/42261853

版权

database 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

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