ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORACLE中的隐式数据类型转换(一)

ORACLE中的隐式数据类型转换(一)

原创 Linux操作系统 作者:wei-xh 时间:2013-09-29 10:34:11 0 删除 编辑
[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2313755