隐式转换带来的麻烦

oracle的隐式转换有时候会给我们带来很隐蔽的麻烦。看一个简单的例子：

SQL>create table t1(val varchar2(10));Table created.SQL>insert into t1 values('XXX');1 row created.SQL>insert into t1 values('100');1 row created.SQL>commit;Commit complete.SQL>select * from t1 where val=100 and rownum<2;select * from t1 where val=100 and rownum<2                       *ERROR at line 1:ORA-01722: invalid number

再看另一个例子：

SQL>create table t2(val varchar2(10));Table created.SQL>insert into t2 values('100');1 row created.SQL>insert into t2 values('XXX');1 row created.SQL>commit;Commit complete.SQL>select * from t2 where val=100 and rownum<2;VAL----------100

查询成功了。仔细观察两个例子，唯一的不同仅仅是两条数据的插入顺序不一样。数据库理论告诉我们，元组（行）在关系（表）中是没有顺序的，我们的程序不应当也不能依赖行的存储顺序。为什么这里会导致不同的结果呢？原因在于val字段类型是varchar2，在和数字100做比较的时候，oracle自动把val转换成了to_number(val)。两个例子都是全表扫描，对t1表，oracle首先读到的第一条记录是‘XXX'，to_number自然就失败了，对t2表，oracle读到的第一条记录是'100'，to_number成功且条件匹配，由于rownum条件只读取一条，所以立刻退出了，没发生任何错误。如果去掉rownum条件，则两个例子都失败。当然，如果oracle不转换val，而是把100转换成to_char(100)，那就没问题了，但oracle认为数字比较总是比字符串比较效率高，所以优先将字符串类型转换为数字类型。

这不仅仅发生在表，索引上同样也会发生类似的情况。所以在设计数据库结构的时候，一定不要为了方便用varchar2去替代其他数据类型，否则一条错误数据就可能让程序崩溃。

查询oracle自己提供的视图也会碰到类似的情况，典型例子就是v$parameter，其中的value字段类型是varchar2，但存储的值有时候是需要转换成数字来进行相应计算的。一个常见的场景是direct path read/write 事件，如果是对TEMP文件进行读写，我们需要这样来获取文件名称:（假设事件P1值是201）

select a.name from v$tempfile a, v$parameter b where b.name='db_files' and a.file# + b.value = 201;

上面的sql是从《Oracle Wait Interface: A Practical Guide to Performance Diagnositics & Tuning》这本书里摘录的，在我的环境下(11.2.0.1.0)，得到的唯一结果是ORA-01722。绕过去的办法是改变一下写法：

select a.name from v$tempfile a, v$parameter b where b.name='db_files' and a.file# = 201 - b.value ;

这样能查询到正确结果。但这样做仅仅是通过运气（如果有兴趣可以仔细研究一下两条语句不同的执行计划），正确的做法应该是始终保证比较类型的一致：

select a.name from v$tempfile a, v$parameter b where b.name='db_files' and b.value = to_char( 201 - a.file# ) ;

这样的话就能保证无论什么情况下都能得到正确结果。