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

,'余额查询','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 TRAN_DATE >= ? AND TRAN_DATE <= ? AND CLEAR_DATE >= ? AND CLEAR_DATE <= ? order by tran_date desc ) 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';

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