出于优化的目的，在SQL 的编码中我们常提到要避免对字段进行计算，通常情况下字段的计算导致相应索引无法被使用，造成语句执行的开销增大，然而编码中稍有不慎，Oracle自动进行的隐性转换仍有可能进行了我们不希望看到的操作，进而带来严重的后果，索引扫描可能被替换为几千万行的全表扫描，甚至导致业务逻辑发生变化。因此本文列举了代码中常见的几种隐性类型转换，总结并论证其间的规律，以期在开发工作中避免其带来的危害。

　　为举例创建一个表，包含隐性转换中常见的三种字段类型，并给每个字段建一个索引：

　　*********************************************************

create table TT2 　　( 　　NUMCOL NUMBER, 　　CHARCOL CHAR(10), 　　VARCHARCOL VARCHAR2(10) 　　); 　　create index IDX_CHAR on TT2 (CHARCOL); 　　create index IDX_NUM on TT2 (NUMCOL); 　　create index IDX_VARCHAR on TT2 (VARCHARCOL); 　　insert into tt2 (numcol,charcol,varcharcol) values (1,’123′,’1′);

　　*********************************************************

　　首先将varchar字段与number常量进行比较。

　　*********************************************************

SQL> select /*+ RULE*/ * from tt2 where varcharcol=1; 　　NUMCOL CHARCOL VARCHARCOL 　　———- ———- ———- 　　1 123 1 　　Execution Plan 　　——————————————————— 　　0 SELECT STATEMENT Optimizer=HINT: RULE 　　1 0 TABLE ACCESS (FULL) OF ‘TT2’

　　*********************************************************

　　这里我们加入了一个RULE 的提示，指定了RBO的优化方式，因此有索引的情况下理所当然优先使用索引，但是恰恰相反，执行计划选择了全表扫描，究其原因，乃是隐性地对 varchar2类型的字段进行了to_number的转换从而屏蔽了索引，实际的Where条件为to_number(varcharcol)=1

　　变换一下，用number字段与字符常量比较。

　　*********************************************************

SQL> select /*+ RULE */ * from tt2 where numcol=’1′; 　　NUMCOL CHARCOL VARCHARCOL 　　———- ———- ———- 　　1 123 1 　　Execution Plan 　　———————————————————- 　　0 SELECT STATEMENT Optimizer=HINT: RULE 　　1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘TT2’ 　　2 1 INDEX (RANGE SCAN) OF ‘IDX_NUM’ (NON-UNIQUE)

　　*********************************************************

　　执行计划使用了IDX_NUM索引，这次隐性转换没有去转换number字段，而是转换常量’1’，实际的where条件变化为where numcol=to_number(‘1’)，字段避免了计算，索引得以被使用。

　　由此可见，字符与数字相遇时数字类型要厉害一些，Oracle总要偷偷将字符类型进行转换去迁就数字，为了论证这个结论，接上例，再做如下测试。

　　*********************************************************

SQL> select * from tt2 where varcharcol=1; 　　NUMCOL CHARCOL VARCHARCOL 　　———- ———- ———- 　　1 123 1 　　SQL> update tt2 set varcharcol=’1A’; 　　1 row updated. 　　SQL> select * from tt2 where varcharcol=1; 　　ERROR at line 1: 　　ORA-01722: invalid number

　　*********************************************************

　　同样的语句前者执行成功，后者却执行失败，什么都没有变，只是varcharcol字段的值由’1’变成’1A’，隐性转换做to_number(varcharcol)操作时，后者无法将字母转换成数字导致ORA-01722错误发生，由此可见这种转换是确实存在的。

　　接下来，再看看关于Char类型的转换，还有值得我们注意的事情发生。

　　首先将char类型字段与varchar2进行比较，上例中charcol为char(10)类型，由于char类型的特性，插入的值’123’被自动补齐空格至10位长，因此字段值其实为’123 ‘，我们执行如下匿名块，并使用SQL_Trace提取执行计划。

　　*********************************************************

declare 　　vv varchar2(10):=’123′; 　　begin 　　UPDATE /*+ RULE*/ tt2 set numcol=1 where charcol=vv; 　　end; 　　/ 　　Rows Row Source Operation 　　——- ————————————————— 　　0 UPDATE 　　0 INDEX RANGE SCAN IDX_CHAR (object id 28748)

　　*********************************************************

　　Rows为0，说明没有行被更新，’123 ‘与’123’理应为不同的值，这样的结果合理，然而在RBO方式下IDX_CHAR索引被使用，可见并没有对charcol进行隐性的转换。

　　可是，将char类型字段与字符串常量进行比较呢?

　　*********************************************************

SQL> select /*+ RULE */ * from tt2 where charcol=’123′; 　　NUMCOL CHARCOL VARCHARCOL 　　———- ———- ———- 　　1 123 1A 　　Execution Plan 　　———————————————————- 　　0 SELECT STATEMENT Optimizer=HINT: RULE 　　1 0 TABLE ACCESS (BY INDEX ROWID) OF ‘TT2’ 　　2 1 INDEX (RANGE SCAN) OF ‘IDX_CHAR’ (NON-UNIQUE)

　　*********************************************************

　　居然有数据返回，并且IDX_CHAR 索引被使用，岂不是’123 ‘与’123’相等，同样的where语句，同样是字符串’123’，因为隐性转换产生了不同的逻辑，原来char与字符串常量比较时，隐性将常量按 char的字段类型转换，’123’转换成char(10)则变成了’123 ‘，看来使用char类型得小心了，两种不同的结果其实就对应着我们在应用开发中将SQL放在存储过程中还是放在中间件或前台代码中。

　　做了这么多例证，掌握了常用的转换规律后，我们在数据库开发中应该注意些什么呢：

　　1、 表结构设计中字段的类型以及数据库代码变量的类型应慎用char类型，采用这种类型的数据必须满足两个条件：1)非空;2)有固定长度。

　　2、 规范编码，尽量避免隐性转换，比较中使用相同类型。

　　在代码中构造动态SQL时，对字符串类型字段的比较中常有这种情况发生，实际上构造的语句是将字符与数字进行比较。

　　v_SQL:=’ … where varcharcol=’||v_str;

　　正确的做法应该是

　　v_SQL:=’ … where varcharcol=”’||v_str||””;

　　3、 某些标记性或开关意义的字段，取值范围诸如(0，1)，(1、2、3)等，尽量使用NUMBER，而不要使用varchar2。

　　如果where varcharcol=’1’误写为 where varcharcol=1将可能造成严重的性能问题，频繁隐性类型转换还可能造成不可预期的ORA-06512

　　错误，而 where numcol=1 误写为 where numcol=’1’则没有太多不利影响。