昨天检查程序，发现执行一条sql语句的buffer gets异常的高（看执行计划是正常的），这个表大小10M，而且谓词使用主关键字，跟踪程序才发现，开发人员使用的绑定变量与查询条件关键字段的类型不一致。这个问题竟然隐藏这么久，程序是第三方的，我无法修改代码。我想既然存在隐式转换，如果我建立这个字段的函数型索引是否可以暂时解决这个问题。
以下是测验的例子：
1.建立测验例子以及主索引。
CREATE TABLE hideconv AS
SELECT to_char(ROWNUM) cserial, all_objects.*
FROM all_objects where rownum
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的函数索引解决这个问题是可行的。当然最终的结果是修改程序。
以下是测验的例子：
1.建立测验例子以及主索引。
CREATE TABLE hideconv AS
SELECT to_char(ROWNUM) cserial, all_objects.*
FROM all_objects where rownum
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的函数索引解决这个问题是可行的。当然最终的结果是修改程序。
