在Oracle中使用TO_NUMBER函数出现奇怪的问题

Question

我在执行to_number函数时，遇到了一个间歇性问题，当varchar2列中的记录数超过某个数量n时，在where子句中使用该函数会出现问题。我使用n是因为没有确切的记录数可以确定。在一个数据库上，当n为100万时发生，在另一个数据库上，当n为10万时发生。

例如，我有一个包含1000万条记录的表，名为Country，其中有一个名为field1的varchar2字段，包含数字数据和Id。

如果我进行查询，如下所示：

select * 
from country 
where to_number(field1) = 23
and id >1 and id < 100000

这个是有效的

但如果我执行查询

select * 
from country 
where to_number(field1) = 23 
and id >1 and id < 100001

它失败了，显示无效数字。

接下来我尝试查询。

select * 
from country
where to_number(field1) = 23 
and id >2 and id < 100001

它又可以工作了

由于我只得到了无效的数字，所以很困惑，但在日志文件中它说：

Memory Notification: Library Cache Object loaded into SGA
Heap size 3823K exceeds notification threshold (2048K)
KGL object name :with sqlplan as (
    select c006 object_owner, c007 object_type,c008 object_name
      from htmldb_collections
     where COLLECTION_NAME='HTMLDB_QUERY_PLAN'
       and c007 in ('TABLE','INDEX','MATERIALIZED VIEW','INDEX (UNIQUE)')),
ws_schemas as(
    select schema 
      from wwv_flow_company_schemas
     where security_group_id = :flow_security_group_id),
t as(
        select s.object_owner table_owner,s.object_name table_name,
               d.OBJECT_ID
          from sqlplan s,sys.dba_objects d

看起来与SGA大小有关，但谷歌并没有给我太多帮助。

有人对TO_NUMBER或用于大数据的Oracle函数的这个问题有什么想法吗？

- Fazal

Answer 1

该字段为varchar2类型，包含数值数据。

这不是一个好的实践。数值数据应该存储在NUMBER列中。原因很简单：如果我们没有强制使用正确的数据类型，我们可能会发现自己在varchar2列中存储了非数值数据。如果发生这种情况，那么像这样的过滤器

where to_number(field1) = 23 

如果使用ORA-01722: invalid number，则会失败。

我无法确定您的情况是否发生了这种情况，因为我不明白ID过滤器中表面上无关紧要的更改如何改变查询的成功。查看不同版本的查询的执行计划将是有益的。但我认为问题更可能是数据问题而不是SGA中的错误。

- APC

---

数据怎么可能是那样的？他的例子似乎排除了这种可能性。 - Igby Largeman

查询中没有规定谓词的顺序。有些查询可能首先过滤id，当行被排除因为它们不匹配时，to_number()函数不会对该列进行计算。其他查询可能在id之前应用to_number()函数，同一行数据将导致错误。 - Gary Myers

值范围的大小可能导致优化器改变条件谓词的评估顺序。在这个例子中，值范围超过了1%的数据量，听起来很低，但是统计数据比实际行数更加重要。因此，如果值范围较小，它可能会使用id索引，而如果值范围很大，它可能会忽略索引并进行全表扫描，在此过程中对无效数字varchar2值执行to_number()操作，如@Gary所说。 - Alex Poole

+1 给 Gary 和 Alex - 大线索是 "如果记录数超过某个数字 n" - 有一个阈值超过它，计划就会改变。这个阈值可能对每个实例都是独特的，并且会受到表统计信息和实际数据的影响。 - Jeffrey Kemp

我仔细查看了数据库，这是我们创建的一个测试表，其中field1中肯定只包含数字数据。 - Fazal

Answer 2

假设您知道给定的ID范围将始终导致field1包含数值数据，那么您可以这样做：

select *
from (
  select /*+NO_MERGE*/ * 
  from country 
  where id >1 and id < 100000
)
where to_number(field1) = 23;

- Jeffrey Kemp

---

可能需要添加一个提示来防止将外谓词合并到内联视图中。 - Dave Costa

Answer 3

建议执行以下步骤以确保是否存在包含非数字数据的记录。正如其他人所说，执行计划和评估顺序的差异可能解释了为什么错误不一致地出现。

（假定SQLPlus是客户端）

SET SERVEROUTPUT ON

DECLARE
   x  NUMBER;
BEGIN
  FOR rec IN (SELECT id, field1 FROM country) LOOP
    BEGIN
      x := TO_NUMBER( rec.field1 );
    EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line( rec.id || ' ' || rec.field1 );
    END;
  END LOOP;
END;
/

一个替代方案是重写查询以避免隐式类型转换，例如。

SELECT id, TO_NUMBER( field1 )
  FROM county
  WHERE field1 = '23'
    AND <whatever condition on id you want, if any>

- Dave Costa

---

@Charlie：好的聪明鬼……LTRIM(field1,'0') = '23' :) - Jeffrey Kemp

Answer 4

考虑编写一个 IS_NUMBER PL/SQL 函数：

CREATE OR REPLACE FUNCTION IS_NUMBER (p_input IN VARCHAR2) RETURN NUMBER 
AS
BEGIN
  RETURN TO_NUMBER (p_input);
EXCEPTION
  WHEN OTHERS THEN RETURN NULL;
END IS_NUMBER;
/

---

SQL> SELECT COUNT(*) FROM DUAL WHERE IS_NUMBER ('TEST') IS NOT NULL;

  COUNT(*)
----------
         0

SQL> SELECT COUNT(*) FROM DUAL WHERE IS_NUMBER ('123.45') IS NOT NULL;

  COUNT(*)
----------
         1

- Adam Musch