SQL优化系列:别让强制类型转换偷走性能

本次主题:强制类型转换性能的影响
SQL跟踪过程中发现一些表结构的DEFAULT值和SQL语句存在字段类型不一致的现象。
虽然业务逻辑没有出错,但却造成了索引失效或者增加了优化的复杂度。其中表结构DEFAULT值不正确影响更加重大,因为他影响了很多关联的SQL语句,

问题一:表结构DEFAULT值类型错误
尴尬:已有业务数据量很大,是转换表的DEFAULT值呢还是用转换类型的函数索引优化?
示例:同样是CHAR(1)类型,有的DEFAULT值是0,有的是'0'

create table STATUS_NOTE
(
  CONTAINER_ID                 NUMBER not null,
  ……
  IS_PRINT                             CHAR(1) default 0,
  CHECK_RESULT              CHAR(1) default '0',
  DELETED_FLAG                CHAR(1) default 0,
)

分析:此表在DELETED_FLAG上有索引列,但是ORACLE优化器中提示:The predicate TO_NUMBER("CS"."DELETED_FLAG")=0 
used at line ID 6 of the execution plan contains an implicit data type conversion on indexed column "DELETED_FLAG"。
因为在索引列上存在强制类型转换,导致索引失效,某SQL语句在改表上的执行计划的COST达到2023,
而如果该字段类型正确,那么COST值至少将下降到613,如此轻而易举的能够提升几倍性能,何乐而不为。

问题二:SQL语句中字段类型错误
示例:DELETED_FLAG是CHAR类型,但SQL语句确和整型比较
select t.id,t.MSG_TYPE,t.CONTENT,t.MODI_DATE,t.DELETED_FLAG
    from SEND_CONTROL t
    Where (t.state='0' )--Or t.state='2') 
    And FUNC_AVAILABLE_DATE(t.CREATE_DATE,t.TRY_TIMES)<=Sysdate  And t.DELETED_FLAG=0
分析:此表未创建索引,ORACLE优化器提醒Consider running the Access Advisor to improve the physical schema design or creating the recommended index.
SEND_CONTROL("STATE",TO_NUMBER("DELETED_FLAG"))
提示创建("STATE",TO_NUMBER("DELETED_FLAG")索引,显然,由于SQL语句中DELETED_FLAG=0导致了强制类型转换。
如果真如优化器所述创建函数索引,那么其他正确的SQL语句反而又成了强制类型转换,在此情况下需要扭转错误的SQL语句。
优化前语句COST:
SELECT STATEMENT, GOAL = ALL_ROWS    Cost=855     Cardinality=1   Bytes=134
 TABLE ACCESS FULL   Object owner=SUZHOU   Object name=SZ_SEND_CONTROL                   Cost=855         Cardinality=1   Bytes=134
优化后语句COST:
SELECT STATEMENT, GOAL = ALL_ROWS   Cost=2 Cardinality=1         Bytes=134
 TABLE ACCESS BY INDEX ROWID       Object owner=SUZHOU          Object name=SEND_CONTROL  Cost=2 Cardinality=1         Bytes=134
  INDEX RANGE SCAN                  Object owner=SUZHOU          Object name=TEST1                   Cost=1 Cardinality=1         
          
该语句在优化前COST为855,而如果语句字段类型正确,索引生肖,那么COST将降低为2,提升数百倍的性能,何其快哉。


本文转自zylhsy 51CTO博客,原文链接:http://blog.51cto.com/yunlongzheng/419455,如需转载请自行联系原作者

上一篇:【ASM学习】手工删除ASM磁盘组时提示错误ORA-15039


下一篇:AIX 5.3 Install Oracle 10g RAC 错误集锦--磁盘I/O 故障