Oracle VARCHAR 列上的数字比较如何工作? [英] How does numeric comparison on Oracle VARCHAR column work?

问题描述

我有一个表，其中两列的类型为 VARCHAR2(3BYTE) 和 VARCHAR2(32BYTE).当我执行选择查询 (where col1=10 和 where col1='10') 或 (where col2=70001 或 col2='70001')在每组 where 子句中获取的记录数相同.这是怎么发生的?Oracle 如何处理字符串文字和数字常量并与列数据类型的数据进行比较?

I have a table where two columns are of type VARCHAR2(3BYTE) and VARCHAR2(32BYTE). When I do a select query (where col1=10 and where col1='10') or (where col2=70001 or col2='70001') the number of records fetched are the same in each set of where clauses. How does this happen? How does Oracle treat string literals and numeric constants and compare to the data despite column data-type?

但这不适用于 VARCHAR2(128BYTE) 类型的列.查询需要是 where col3='55555555001' 工作，并且 where col3=55555555001 抛出 ORA-01722 错误.

But this does not work for a column of type VARCHAR2(128BYTE). The query needed to be where col3='55555555001' to work and where col3=55555555001 throws ORA-01722 error.

推荐答案

如SQL 语言参考:

• 在 SELECT FROM 操作期间，Oracle 将列中的数据转换为目标变量的类型.
• ...
• 将字符值与数值进行比较时，Oracle 会将字符数据转换为数值.

当类型不匹配时，对表列执行隐式转换.这可以通过在 SQL*Plus 中使用一些虚拟数据进行跟踪来看到.

Implicit conversion is performed on the table column when the types don't match. This can be seen by tracing in SQL*Plus, with some dummy data.

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)

注意过滤器的不同；filter("FOO"='10') 与 filter(TO_NUMBER("FOO")=10).在后一种情况下，与数字进行比较，对表中的每一行执行 to_number() ，并将该转换的结果与固定值进行比较.因此，如果任何字符值无法转换，您将收到 ORA-01722.正在应用的函数还将停止正在使用的索引(如果该列上存在索引).

Note the difference in the filter; filter("FOO"='10') versus filter(TO_NUMBER("FOO")=10). In the latter case, comparing against a number, a to_number() is being performed against every row in the table the the result of that conversion is compared against the fixed value. So if any of the character values cannot be converted, you'll get an ORA-01722. The function being applied will also stop an index being used, if one is present on that column.

有趣的地方在于您是否有多个过滤器.Oracle 可能会在不同的时间以不同的顺序评估它们，因此您可能不会总是看到 ORA-01722，它有时会弹出.假设你有 where foo = 10 and bar = 'X'.如果 Oracle 认为它可以首先过滤掉非 X 值，它只会将 to_number() 应用于剩下的内容，并且较小的样本可能没有非数字foo 中的值.但是如果您有 和 bar = 'Y'，非 Y 值可能包括非数字，或 Oracle 可能会过滤 foo first，取决于它认为值的选择性.

Where it gets interesting is if you have more than one filter. Oracle may evaluate them in different orders at different times, so you might not always see the ORA-01722, and it'll pop up sometimes. Say you had where foo = 10 and bar = 'X'. If Oracle thought it could filter out the non-X values first, it would only apply the to_number() to what's left, and that smaller sample might not have non-numeric values in foo. But if you has and bar = 'Y', the non-Y values might include non-numerics, or Oracle might filter on foo first, depending on how selective it thinks the values are.

道德是永远不要将数字信息存储为字符类型.

The moral is to never store numeric information as a character type.

我正在寻找一个 AskTom 参考来支持道德，以及 我看到的第一个方便地指的是改变谓词顺序"以及不要将数字存储在varchar2中"的效果.

I was looking for an AskTom reference to back up the moral, and the first one I looked at conveniently refers to the effect of "a change in the order of a predicate" as well as saying "don't store numbers in varchar2's".

这篇关于Oracle VARCHAR 列上的数字比较如何工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！