oracle自动转换列类型的测试

在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/