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

 

 

转自：http://space.itpub.net/32980/viewspace-676870