Oracle VARCHAR列上的数字比较如何工作？

发布时间：2020-12-12 12:43:15 所属栏目：百科来源：网络整理

导读：我有一个表,其中两列的类型为VARCHAR2(3BYTE)和VARCHAR2(32BYTE).当我执行select查询(其中col1 = 10且col1 =’10’)或(其中col2 = 70001或col2 =’70001′)时,每个where子句集中提取的记录数相同.这是怎么发生的？ Oracle如何处理字符串文字和数字常量,并与

我有一个表,其中两列的类型为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中存储数字”.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!