SQL 語句調優_SQL傳遞引數的資料型別與表索引欄位型別保持一致

 

今天在一個生產資料庫上發現了一個TOP SQL，SQL 本身很簡單，但COST 非常大（執行計劃走了全表掃描），SQL語句如下：

SELECT D.FIELD_VALUE, D.FIELD_VALUE_CLOB FROM RUNLOG_RUN_REC_DETAIL D WHERE D.RUN_RECORD_ID =:B2 AND D.FIELD_NUMBER=:B1

      這個表在欄位"RUN_RECORD_ID"上建立了索引,但為什麼沒有使用到索引呢?

 

 

一. 原因

  傳遞引數時,型別與資料庫表的欄位型別不同(表欄位型別為字元型),這樣就會產生資料型別轉換,ORACLE無法使用到索引,走了TABLE ACCESS FULL,導致get buffer過多,cpu cost也過多:

Object Type  TABLE

Order  1

Rows  1

Size (KB)  0.052

Cost  7,328

Time  88

CPU Cost  804,127,446

I/O Cost  7,286

 

二. 解決方法

1. 修改SQL 語句,建議:

SELECT D.FIELD_VALUE, D.FIELD_VALUE_CLOB FROM PROD_RUNLOG_RUN_REC_DETAIL D WHERE TO_NUMBER(D.RUN_RECORD_ID) =:B2 AND D.FIELD_NUMBER=:B1 ;

 

2. 修改傳遞引數的型別為字元型

:B2='2007'  而不是 :B2=2007

SELECT D.FIELD_VALUE, D.FIELD_VALUE_CLOB FROM PROD_RUNLOG_RUN_REC_DETAIL D WHERE D.RUN_RECORD_ID =:B2 AND D.FIELD_NUMBER=:B1 ;

 

3. 修改該表的其中一個索引(推薦）

DROP INDEX SCDC.RUN_RECORD_ID;

CREATE INDEX SCDC.RUN_RECORD_ID ON TABLE SCDC.PROD_RUNLOG_RUN_REC_DETAIL(TO_NUMBER(RUN_RECORD_ID),FIELD_NUMBER);

 

 

三. 結論

     當SQL 傳遞引數與表索引欄位型別不一致的時候,ORACLE 將自動進行資料型別轉換,這時就不會使用到索引,而是使用全表掃描,從而導致了CPU 和I/O 都開銷很大。筆者已經遇到了多次這樣的問題，今天寫出來，希望對大家有所幫助。