我有一个表,其中两列的类型为VARCHAR2(3BYTE)和VARCHAR2(32BYTE).当我执行select查询(其中col1 = 10且col1 =’10’)或(其中col2 = 70001或col2 =’70001′)时,每个where子句集中提取的记录数相同.这是怎么发生的？ Oracle如何处理字符串文字和数字常量,并与列数据类型的数据进行比较？
但这对VARCHAR2类型的列(128BYTE)不起作用.查询需要在col3 =’55555555001’工作的地方,并且col3 = 55555555001抛出ORA-01722错误.
解决方法
如
SQL Language Reference所述：
- During SELECT FROM operations,Oracle converts the data from the column to the type of the target variable.
- …
- When comparing a character value with a numeric value,Oracle converts the character data to a numeric value.
当类型不匹配时,在表列上执行隐式转换.通过在sql * Plus中跟踪一些虚拟数据可以看出这一点.
create table t42 (foo varchar2(3 byte));
insert into t42 (foo) values ('10');
insert into t42 (foo) values ('2A');
set autotrace on explain
这有效：
select * from t42 where foo = '10'; FOO --- 10 Execution Plan ---------------------------------------------------------- Plan hash value: 3843907281 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T42 | 1 | 3 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("FOO"='10') Note ----- - dynamic sampling used for this statement (level=2)
但是这个错误：
select * from t42 where foo = 10; ERROR: ORA-01722: invalid number Execution Plan ---------------------------------------------------------- Plan hash value: 3843907281 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T42 | 1 | 3 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(TO_NUMBER("FOO")=10)
注意过滤器的区别;过滤器(“FOO”=’10’)与过滤器(TO_NUMBER(“FOO”)= 10).在后一种情况下,与数字进行比较,对表中的每一行执行to_number(),将该转换的结果与固定值进行比较.因此,如果无法转换任何字符值,您将获得ORA-01722.正在应用的函数还将停止正在使用的索引(如果该列上存在索引).
有趣的地方是你有多个过滤器. Oracle可能会在不同的时间以不同的顺序对它们进行评估,因此您可能并不总是看到ORA-01722,并且它有时会弹出.假设你有foo = 10和bar =’X’.如果Oracle认为它可以先过滤掉非X值,那么它只会将to_number()应用于剩下的值,而较小的样本可能在foo中没有非数值.但是如果你有和bar =’Y’,非Y值可能包括非数字,或者Oracle可能首先过滤foo,具体取决于它认为值的选择性.
道德是永远不会将数字信息存储为字符类型.
我正在寻找一个AskTom引用来支持道德,而first one I looked at方便地指的是“谓词顺序的改变”的效果,以及“不要在varchar2中存储数字”.
