Oracle中查詢時候使index索引失效的限制條件
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.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';
