sql - Oracle 中的 TO_NUMBER 函数出现奇怪的问题

Question

如果记录数超过某个数字 n，则在 varchar2 列的 where 子句中执行 to_number 函数时，我会遇到间歇性问题。我使用 n 是因为没有确切数量的记录发生。在一个 DB 上，它发生在 n 为 100 万之后，而另一个 DB 为 0.1。百万。

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

如果我以查询为例

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 函数的这个问题有任何想法？

Answer 1

which has field1 varchar2 containing numberic data

这不是好的做法。数字数据应保存在 NUMBER 列中。原因很简单:如果我们不强制使用强数据类型，我们可能会在 varchar2 列中发现非数字数据。如果发生这种情况，那么像这样的过滤器

where to_number(field1) = 23 

将失败 ORA-01722: invalid number .

我不能肯定地说这就是你的场景中发生的事情，因为我不明白为什么 ID 过滤器中明显微不足道的变化改变了查询的成功。查看不同版本查询的执行计划会很有帮助。但我认为这更可能是您的数据问题而不是 SGA 中的错误。