昨天檢查程式，發現執行一條sql語句的buffer gets異常的高（看執行計劃是正常的），這個表大小10M，而且謂詞使用主關鍵字，跟蹤程式才發現，開發人員使用的繫結變數與查詢條件關鍵欄位的型別不一致。這個問題竟然隱藏這麼久，程式是第三方的，我無法修改程式碼。我想既然存在隱式轉換，如果我建立這個欄位的函式型索引是否可以暫時解決這個問題。

以下是測驗的例子：

1.建立測驗例子以及主索引。
CREATE TABLE hideconv AS
SELECT to_char(ROWNUM) cserial, all_objects.*
FROM all_objects where rownum<=10000 ;

CREATE INDEX I_hideconv_to_number_cserial ON hideconv
(to_number(cserial)) ;

CREATE INDEX I_hideconv_cserial ON hideconv
(cserial) ;

Analyze Table hideconv Compute Statistics ;

2。試驗：
set autotrace traceonly ;
select * from hideconv where cserial=100 ;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=88)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HIDECONV' (Cost=1 Card=1 Bytes=88)

2 1 INDEX (RANGE SCAN) OF 'I_HIDECONV_TO_NUMBER_CSERIAL' (NON-UNIQUE) (
Cost=1 Card=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1202 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

select * from hideconv where cserial='100' ;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=88)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HIDECONV' (Cost=1 Card=1 Bytes=88)

2 1 INDEX (RANGE SCAN) OF 'I_HIDECONV_CSERIAL' (NON-UNIQUE) (Cost=1 Car
d=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1202 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

結論：可見建立一個to_number的函式索引解決這個問題是可行的。當然最終的結果是修改程式。

資料型別的隱式轉換

相關文章