一次oracle sql調優的經歷（隱士轉換導致索引失效）

賀子_DBA時代發表於2015-11-10
OracleSQL索引
今天同事給了一個語句，SELECT IMAGE FROM zhuaqu03.SL_ALIBABA4_PRODUCT_IMAGE WHERE P_ID =38184496132。他說查詢慢。
首先我看到：表裡一共有200多萬。
SQL> select count(*) from zhuaqu03.SL_ALIBABA4_PRODUCT_IMAGE; 

  COUNT(*)
----------
   2622603
然後給P_ID列建立普通索引。再次執行  ，速度沒有增加！！！
檢視執行計劃：發現沒有走索引，還是全表掃描。

這是很奇怪的，因為從大資料裡選取少量資料，是應該走索引的。

老大提醒說因為，是欄位字元型別的原因。 p_id是VARCHAR2型別的。
SQL> desc  zhuaqu03.SL_ALIBABA4_PRODUCT_IMAGE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL VARCHAR2(4000)
 FETCH_DATE                                         DATE
 P_ID                                                    VARCHAR2(4000)
 IMAGE                                                 VARCHAR2(4000)
 IMAGE_SRC                                         VARCHAR2(4000)
 錯誤分析：語句卻沒有加單引號。
SELECT IMAGE FROM zhuaqu03.SL_ALIBABA4_PRODUCT_IMAGE WHERE P_ID =38184496132
正常的情況字元型的資料要新增上單引號，但是oracle會隱士轉換。所以你不加單引號也不報錯，但是會導致索引失效。
調優之後：
SELECT IMAGE FROM zhuaqu03.SL_ALIBABA4_PRODUCT_IMAGE WHERE P_ID =‘38184496132’；
然後再看執行計劃：發現已經走索引了。


小節：
Oracle中對不同型別的處理具有顯式型別轉換(Explicit)和隱式型別轉換(Implicit)兩種方式，對於顯式型別轉換，我們是可控的，但是對於隱式型別轉換，當然不建議使用, 因為很難控制，有不少缺點，但是我們很難避免碰到隱式型別轉換,如果不瞭解隱式型別轉換的規則，那麼往往會改變我們SQL的執行計劃，從而可能導致效率降低或其它問題。

容易引起oracle索引失效的原因很多： 
1、在索引列上使用函式。如SUBSTR,DECODE,INSTR等，對索引列進行運算.需要建立函式索引就可以解決了。 
2、新建的表還沒來得及生成統計資訊，分析一下就好了 
3、基於cost的成本分析，訪問的表過小，使用全表掃描的消耗小於使用索引。 
4、使用<>、not in 、not exist，對於這三種情況大多數情況下認為結果集很大，一般大於5%-15%就不走索引而走FTS（全表掃描）。 
5、單獨的>、<。 
6、like "%_" 百分號在前。 
7、單獨引用複合索引裡非第一位置的索引列。也就是說查詢謂詞並未使用組合索引的第一列，此處有一個INDEX SKIP SCAN概念
 8、字元型欄位為數字時在where條件裡不新增引號。 
 9、當變數採用的是times變數，而表的欄位採用的是date變數時.或相反情況。 
10、索引失效，可以考慮重建索引，rebuild online。 
11、B-tree索引 is null不會走,is not null會走,點陣圖索引 is null,is not null  都會走、聯合索引 is not null 只要在建立的索引列（不分先後）都會走
12 、在包含有null值的table列上建立索引，當時使用select count(*) from table時不會使用索引。

13、加上hint 還不走索引，那可能是因為你要走索引的這列是nullable，雖然這列沒有空值。（將欄位改為not null，就會走）









來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29654823/viewspace-1827361/，如需轉載，請註明出處，否則將追究法律責任。

相關文章