今天,以前开发的一个系统出问题,一句类似这样的sql语句:
1|AM"95/E2E$G082PW",http://www.itwz.netselect * from t where a=1
1|AM"95/E2E$G082PW",http://www.itwz.net其中a是varchar类型。在测试机上跑得好好的,在生产机就报invalid number的错。原因涉及oracle数据库在解析sql语句的时候,对数据进行了类型转换,实际执行的是:
1|AM"95/E2E$G082PW",http://www.itwz.netselect * from t where to_number(a)=1
1|AM"95/E2E$G082PW",http://www.itwz.net只要表t里面有一条记录的a字段含有不能转换为number的数字,就出错了,而错误当然就是invalid number了。
1|AM"95/E2E$G082PW",http://www.itwz.net此外,由于这样写语句要对表中a字段所有数据都做一次运算,什么索引都没用了,每次搜索都会进行全表扫描和运算,造成性能的下降。所以,就算不出错,也一定要根据数据类型写sql语句。如上例,写成这样就好了:
1|AM"95/E2E$G082PW",http://www.itwz.netselect * from t where a='1'
1|AM"95/E2E$G082PW",http://www.itwz.net当然,因为这个系统比较小,业务简单,所以是用简单jsp实现的,所有sql语句都自己生成。如果大一点的系统,就可能会用到框架。如何优化对数据库的访问很大程度上都是框架的问题了。
1|AM"95/E2E$G082PW",http://www.itwz.net下面是网上搜索到的一篇关于oracle数据类型隐性转换的文章:
1|AM"95/E2E$G082PW",http://www.itwz.netORACLE数据类型的隐性转换
1|AM"95/E2E$G082PW",http://www.itwz.net出于优化的目的,在SQL 的编码中我们常提到要避免对字段进行计算,通常情况下字段的计算导致相应索引无法被使用,造成语句执行的开销增大,然而编码中稍有不慎,Oracle自动进行的隐性转换仍有可能进行了我们不希望看到的操作,进而带来严重的后果,索引扫描可能被替换为几千万行的全表扫描,甚至导致业务逻辑发生变化。因此本文列举了代码中常见的几种隐性类型转换,总结并论证其间的规律,以期在开发工作中避免其带来的危害。
1|AM"95/E2E$G082PW",http://www.itwz.net为举例创建一个表,包含隐性转换中常见的三种字段类型,并给每个字段建一个索引:
1|AM"95/E2E$G082PW",http://www.itwz.net*********************************************************
1|AM"95/E2E$G082PW",http://www.itwz.netcreate table TT2
1|AM"95/E2E$G082PW",http://www.itwz.net(
1|AM"95/E2E$G082PW",http://www.itwz.netNUMCOL NUMBER, CHARCOL CHAR(10),
1|AM"95/E2E$G082PW",http://www.itwz.netVARCHARCOL VARCHAR2(10)
1|AM"95/E2E$G082PW",http://www.itwz.net);
1|AM"95/E2E$G082PW",http://www.itwz.netcreate index IDX_CHAR on TT2 (CHARCOL);
1|AM"95/E2E$G082PW",http://www.itwz.netcreate index IDX_NUM on TT2 (NUMCOL);
1|AM"95/E2E$G082PW",http://www.itwz.netcreate index IDX_VARCHAR on TT2 (VARCHARCOL);
1|AM"95/E2E$G082PW",http://www.itwz.netinsert into tt2 (numcol,charcol,varcharcol) values (1,'123','1');
1|AM"95/E2E$G082PW",http://www.itwz.net*********************************************************
1|AM"95/E2E$G082PW",http://www.itwz.net首先将varchar字段与number常量进行比较。
1|AM"95/E2E$G082PW",http://www.itwz.net*********************************************************
1|AM"95/E2E$G082PW",http://www.itwz.netSQL> select /*+ RULE*/ * from tt2 where varcharcol=1;
1|AM"95/E2E$G082PW",http://www.itwz.netNUMCOL CHARCOL VARCHARCOL
1|AM"95/E2E$G082PW",http://www.itwz.net---------- ---------- ----------
1|AM"95/E2E$G082PW",http://www.itwz.net1 123 1
1|AM"95/E2E$G082PW",http://www.itwz.netExecution Plan
1|AM"95/E2E$G082PW",http://www.itwz.net---------------------------------------------------------
1|AM"95/E2E$G082PW",http://www.itwz.net0 SELECT STATEMENT Optimizer=HINT: RULE
1|AM"95/E2E$G082PW",http://www.itwz.net1 0 TABLE ACCESS (FULL) OF 'TT2'
1|AM"95/E2E$G082PW",http://www.itwz.net这里我们加入了一个RULE 的提示,指定了RBO的优化方式,因此有索引的情况下理所当然优先使用索引,但是恰恰相反,执行计划选择了全表扫描,究其原因,乃是隐性地对 varchar2类型的字段进行了to_number的转换从而屏蔽了索引,实际的Where条件为to_number(varcharcol)=1
1|AM"95/E2E$G082PW",http://www.itwz.net变换一下,用number字段与字符常量比较。
1|AM"95/E2E$G082PW",http://www.itwz.net*********************************************************
1|AM"95/E2E$G082PW",http://www.itwz.netSQL> select /*+ RULE */ * from tt2 where numcol='1';
1|AM"95/E2E$G082PW",http://www.itwz.netNUMCOL CHARCOL VARCHARCOL
1|AM"95/E2E$G082PW",http://www.itwz.net---------- ---------- ----------
1|AM"95/E2E$G082PW",http://www.itwz.net1 123 1
1|AM"95/E2E$G082PW",http://www.itwz.netExecution Plan
1|AM"95/E2E$G082PW",http://www.itwz.net----------------------------------------------------------
1|AM"95/E2E$G082PW",http://www.itwz.net0 SELECT STATEMENT Optimizer=HINT: RULE
1|AM"95/E2E$G082PW",http://www.itwz.net1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TT2'
1|AM"95/E2E$G082PW",http://www.itwz.net2 1 INDEX (RANGE SCAN) OF 'IDX_NUM' (NON-UNIQUE)
1|AM"95/E2E$G082PW",http://www.itwz.net*********************************************************
1|AM"95/E2E$G082PW",http://www.itwz.net执行计划使用了IDX_NUM索引,这次隐性转换没有去转换number字段,而是转换常量'1',实际的where条件变化为where numcol=to_number('1'),字段避免了计算,索引得以被使用。
1|AM"95/E2E$G082PW",http://www.itwz.net由此可见,字符与数字相遇时数字类型要厉害一些,Oracle总要偷偷将字符类型进行转换去迁就数字,为了论证这个结论,接上例,再做如下测试。
1|AM"95/E2E$G082PW",http://www.itwz.net*********************************************************
1|AM"95/E2E$G082PW",http://www.itwz.netSQL> select * from tt2 where varcharcol=1;
1|AM"95/E2E$G082PW",http://www.itwz.netNUMCOL CHARCOL VARCHARCOL
1|AM"95/E2E$G082PW",http://www.itwz.net---------- ---------- ----------
1|AM"95/E2E$G082PW",http://www.itwz.net1 123 1
1|AM"95/E2E$G082PW",http://www.itwz.netSQL> update tt2 set varcharcol='1A';
1|AM"95/E2E$G082PW",http://www.itwz.net1 row updated.
1|AM"95/E2E$G082PW",http://www.itwz.netSQL> select * from tt2 where varcharcol=1;
1|AM"95/E2E$G082PW",http://www.itwz.netERROR at line 1:
1|AM"95/E2E$G082PW",http://www.itwz.netORA-01722: invalid number
1|AM"95/E2E$G082PW",http://www.itwz.net*********************************************************
1|AM"95/E2E$G082PW",http://www.itwz.net同样的语句前者执行成功,后者却执行失败,什么都没有变,只是varcharcol字段的值由'1'变成'1A',隐性转换做to_number(varcharcol)操作时,后者无法将字母转换成数字导致ORA-01722错误发生,由此可见这种转换是确实存在的。
1|AM"95/E2E$G082PW",http://www.itwz.net接下来,再看看关于Char类型的转换,还有值得我们注意的事情发生。
1|AM"95/E2E$G082PW",http://www.itwz.net首先将char类型字段与varchar2进行比较,上例中charcol为char(10)类型,由于char类型的特性,插入的值'123'被自动补齐空格至10位长,因此字段值其实为'123 ',我们执行如下匿名块,并使用SQL_Trace提取执行计划。
1|AM"95/E2E$G082PW",http://www.itwz.net*********************************************************
1|AM"95/E2E$G082PW",http://www.itwz.netdeclare
1|AM"95/E2E$G082PW",http://www.itwz.netvv varchar2(10):='123';
1|AM"95/E2E$G082PW",http://www.itwz.netbegin
1|AM"95/E2E$G082PW",http://www.itwz.netUPDATE /*+ RULE*/ tt2 set numcol=1 where charcol=vv;
1|AM"95/E2E$G082PW",http://www.itwz.netend;
1|AM"95/E2E$G082PW",http://www.itwz.net/
1|AM"95/E2E$G082PW",http://www.itwz.netRows Row Source Operation
1|AM"95/E2E$G082PW",http://www.itwz.net------- ---------------------------------------------------
1|AM"95/E2E$G082PW",http://www.itwz.net0 UPDATE
1|AM"95/E2E$G082PW",http://www.itwz.net0 INDEX RANGE SCAN IDX_CHAR (object id 28748)
1|AM"95/E2E$G082PW",http://www.itwz.net*********************************************************
1|AM"95/E2E$G082PW",http://www.itwz.netRows为0,说明没有行被更新,'123 '与'123'理应为不同的值,这样的结果合理,然而在RBO方式下IDX_CHAR索引被使用,可见并没有对charcol进行隐性的转换。
1|AM"95/E2E$G082PW",http://www.itwz.net可是,将char类型字段与字符串常量进行比较呢?
1|AM"95/E2E$G082PW",http://www.itwz.net*********************************************************
1|AM"95/E2E$G082PW",http://www.itwz.netSQL> select /*+ RULE */ * from tt2 where charcol='123';
1|AM"95/E2E$G082PW",http://www.itwz.netNUMCOL CHARCOL VARCHARCOL
1|AM"95/E2E$G082PW",http://www.itwz.net---------- ---------- ----------
1|AM"95/E2E$G082PW",http://www.itwz.net1 123 1A
1|AM"95/E2E$G082PW",http://www.itwz.netExecution Plan
1|AM"95/E2E$G082PW",http://www.itwz.net----------------------------------------------------------
1|AM"95/E2E$G082PW",http://www.itwz.net0 SELECT STATEMENT Optimizer=HINT: RULE
1|AM"95/E2E$G082PW",http://www.itwz.net1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TT2'
1|AM"95/E2E$G082PW",http://www.itwz.net2 1 INDEX (RANGE SCAN) OF 'IDX_CHAR' (NON-UNIQUE)
1|AM"95/E2E$G082PW",http://www.itwz.net*********************************************************
1|AM"95/E2E$G082PW",http://www.itwz.net居然有数据返回,并且IDX_CHAR 索引被使用,岂不是'123 '与'123'相等,同样的where语句,同样是字符串'123',因为隐性转换产生了不同的逻辑,原来char与字符串常量比较时,隐性将常量按 char的字段类型转换,'123'转换成char(10)则变成了'123 ',看来使用char类型得小心了,两种不同的结果其实就对应着我们在应用开发中将SQL放在存储过程中还是放在中间件或前台代码中。
1|AM"95/E2E$G082PW",http://www.itwz.net做了这么多例证,掌握了常用的转换规律后,我们在数据库开发中应该注意些什么呢:
1|AM"95/E2E$G082PW",http://www.itwz.net1、 表结构设计中字段的类型以及数据库代码变量的类型应慎用char类型,采用这种类型的数据必须满足两个条件:1)非空;2)有固定长度。
1|AM"95/E2E$G082PW",http://www.itwz.net2、 规范编码,尽量避免隐性转换,比较中使用相同类型。
1|AM"95/E2E$G082PW",http://www.itwz.net在代码中构造动态SQL时,对字符串类型字段的比较中常有这种情况发生,实际上构造的语句是将字符与数字进行比较。
1|AM"95/E2E$G082PW",http://www.itwz.netv_SQL:=' ... where varcharcol='||v_str;
1|AM"95/E2E$G082PW",http://www.itwz.net正确的做法应该是
1|AM"95/E2E$G082PW",http://www.itwz.netv_SQL:=' ... where varcharcol='''||v_str||'''';
1|AM"95/E2E$G082PW",http://www.itwz.net3、 某些标记性或开关意义的字段,取值范围诸如(0,1),(1、2、3)等,尽量使用NUMBER,而不要使用varchar2。
1|AM"95/E2E$G082PW",http://www.itwz.net如果where varcharcol='1'误写为 where varcharcol=1将可能造成严重的性能问题,频繁隐性类型转换还可能造成不可预期的ORA-06512
1|AM"95/E2E$G082PW",http://www.itwz.net错误,而 where numcol=1 误写为 where numcol='1'则没有太多不利影响
1|AM"95/E2E$G082PW",http://www.itwz.netTags(标签):Ora
天天网摘 Copyright(版权所有) © 天天网摘 2001~2009 本站文章来源于网络,如果有侵犯你的权宜的地方,请指出我们会即时更正。 |