在Oracle中使用TO_NUMBER函数遇到奇怪的问题 [英] Getting weird issue with TO_NUMBER function in Oracle
问题描述
如果记录数超过特定数目n,则在varchar2列的where子句中执行to_number函数时,我遇到了间歇性问题.我使用n是因为没有确切的记录数量.在一个DB上,它发生在n为一百万时,而在另一个DB上为0.1.百万.
I have been getting an intermittent issue when executing to_number function in the where clause on a varchar2 column if number of records exceed a certain number n. I used n as there is no exact number of records on which it happens. On one DB it happens after n was 1 million on another when it was 0.1. million.
例如我有一个具有1000万条记录的表,说表国家/地区具有包含数字数据和ID的field1 varchar2
E.g. I have a table with 10 million records say Table Country which has field1 varchar2 containing numberic data and 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
它再次起作用
由于我只有无效的号码,所以很混乱,但是在日志文件中却说
As I only got invalid number it was confusing, but in the log file it said
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大小有关,但是google在这方面没有给我太多帮助.
It seems its related to SGA size, but google did not give me much help on this.
对于大数据的TO_NUMBER或oracle函数,有人对这个问题有任何想法吗?
Does anyone have any idea about this issue with TO_NUMBER or oracle functions for large data?
推荐答案
假定您知道给定范围的ID始终将导致field1包含数字数据,您可以改为执行以下操作:
Assuming you know that the given range of ids will always result in field1 containing numeric data, you could do this instead:
select *
from (
select /*+NO_MERGE*/ *
from country
where id >1 and id < 100000
)
where to_number(field1) = 23;
这篇关于在Oracle中使用TO_NUMBER函数遇到奇怪的问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!