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 都开销很大。笔者已经遇到了多次这样的问题，今天写出来，希望对大家有所帮助。

 

 

 

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