ORACLE中的隐式数据类型转换（一）

[i=s] 本帖最后由 wei-xh 于 2013-9-29 10:34 编辑

ORACLE中的隐式数据类型转换可能有很多你没留意到的内容。
create table test (id varchar2(100));
create unique index test_id_ind on test(id);


创建了一张表，列id的类型为varchar2，id上有唯一索引。
我们做如下查询，id的值设置为数字型的1。


SQL> SELECT * FROM test WHERE id=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 217508114


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    52 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    52 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter(TO_NUMBER("ID")=1)


执行计划的尾部出现了TO_NUMBER("ID")=1，然后不可避免的执行计划走了全表扫描。
可能大多数的人都认为ORACLE会采用如下的数据类型转换：
WHERE id = 1

转换为
id=to_char(1)

但是实际发生的转换却是：
to_number(id)=1

ORACLE在查询的列上采用函数，导致索引被忽略，可能这点让你费解，为什么ORACLE不在查询条件上采用函数，而在查询列上使用，难道ORACLE就不关注效率吗？


不然。
ORACLE当然关注效率，但是ORACLE更多时候更应该尊重SQL所表达的意图以及返回结果的正确性。

当你发出一个查询
select * from test where id=1的时候，ORACLE理解的你的意图是想查询出ID列转换为数值后，结果为1的记录，如果不这样做，有可能会导致结果的不正确。
当数字以字符类型被存储的时候，1可以被存储为‘1’，也可以被存储为‘001’，‘0000001’等等，看如下实验：


SQL> insert into test values('1');


1 row created.


SQL> insert into test values('0001');


1 row created.


SQL> commit;


Commit complete.


SQL> select * from test where id=1;


ID
---------------------------------
1
0001


select to_number('0000000000001') from dual;


TO_NUMBER('0000000000001')
--------------------------
                         1
当你发出一个查询
select * from test where id=1
如果ORACLE自以为是的把你的查询条件变为id=to_char(1)，那么返回的结果将会不正确：
SQL> select * from test where id=to_char(1);


ID
-----------------------------------
1
丢失了id为0001的记录


但是你可能会说，不对，这不符合我的预期，我希望返回的只是id为'1'的记录，既然这样，那还是请你遵守ORACLE的法则，明确的写明：id=to_char(1)或者id='1'


还有一点需要注意，隐式转换过程中，可能出现转换错误：
SQL> select * from test;

ID
----------------------------------------------------------------------------------------------------------------------------------------------------------------
1
0001


insert into test values('xxx');
commit;


SQL> select * from test where id=1;
ERROR:
ORA-01722: invalid number

由于xxx不能被转换为数字，因此在查询里的隐式类型转换错误，导致查询报错。

这里说了隐式转换的几个问题，为了规避这些问题，我们应该遵守：
1）如果一个列为数值型的，那就将列设置为数值型，不要设置为varchar2
2）写查询条件的时候，请注意列的字符类型，避免类型的隐式转换，除非这个转换是你需要的，否则都请显式的进行转换。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-773574/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-773574/