linux 隐式类型转换,数据类型的隐式转换

昨天检查程序，发现执行一条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的函数索引解决这个问题是可行的。当然最终的结果是修改程序。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-82808/，如需转载，请注明出处，否则将追究法律责任。