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