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

Question

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

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

Answer 1

如 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)

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

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

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

<小时/>

我正在寻找 AskTom 引用资料来支持道德，而 first one I looked at方便地指“谓词顺序的更改”以及“不要在 varchar2 中存储数字”的效果。