在Performance Tuning Guide中的11.5.3.2 Avoid Transformed Columns in the WHERE Clause中写着:
Avoid mixed-mode expressions, and beware of implicit type conversions. When you want to use an index on the VARCHAR2 column charcol, but the WHERE clause looks like this:
AND charcol = numexpr
where numexpr is an expression of number type (for example, 1, USERENV('SESSIONID'), numcol, numcol+0,...), Oracle translates that expression into:
AND TO_NUMBER(charcol) = numexpr
但是在实际系统中:strt_id为数字型
SQL> set autotrace traceonly
SQL> select * from tb_adr_street_11 where strt_id = '123';
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 281614409
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_ADR_STREET_11 | 1 | 66 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_C00184290 | 1 | | 2 (0)| 00:00:01 |
可以看到还是使用了索引,所以实际oracle内部转换过程是:strt_id = to_number('123'),而不是to_char(strt_id) = '123'
并且:
SQL> set autotrace on;
SQL> select * from TB_ADR_STREET_11 t where T.STRT_ID = '0110000579732';
STRT_ID
----------
110000579732
执行计划
----------------------------------------------------------
Plan hash value: 281614409
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 66 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_ADR_STREET_11 | 1 | 66 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_C00184290 | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."STRT_ID"=110000579732)
把where条件中的strt_id写为'0110000579732',可以看到实际执行时条件是STRT_ID=110000579732,从而证明了oracle内部转换过程是:strt_id = to_number('0110000579732'),而不是to_char(strt_id) = '0110000579732',这样就保证了索引的使用。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7629154/viewspace-683356/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7629154/viewspace-683356/