Oracle 中的 TO_NUMBER 函数出现奇怪的问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2785033/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting weird issue with TO_NUMBER function in Oracle
提问by Fazal
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.
如果记录数超过某个数字 n，则在 varchar2 列的 where 子句中执行 to_number 函数时，我会遇到间歇性问题。我使用了 n 因为没有确切数量的记录发生它。在一个 DB 上，它发生在 n 为 100 万之后，而另一个 DB 为 0.1。百万。
E.g. I have a table with 10 million records say Table Country which has field1 varchar2 containing numberic data and Id
例如，我有一个包含 1000 万条记录的表格，例如 Table Country，其中包含包含数字数据和 Id 的 field1 varchar2
If I do a query as an example
如果我以查询为例
select *
from country
where to_number(field1) = 23
and id >1 and id < 100000
This works
这有效
But if I do the query
但如果我做查询
select *
from country
where to_number(field1) = 23
and id >1 and id < 100001
It fails saying invalid number
它没有说无效号码
Next I try the query
接下来我尝试查询
select *
from country
where to_number(field1) = 23
and id >2 and id < 100001
It works again
它又起作用了
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
It seems its related to SGA size, but google did not give me much help on this.
这似乎与 SGA 大小有关，但谷歌在这方面没有给我太多帮助。
Does anyone have any idea about this issue with TO_NUMBER or oracle functions for large data?
有没有人对大数据的 TO_NUMBER 或 oracle 函数的这个问题有任何想法？
采纳答案by Jeffrey Kemp
Assuming you know that the given range of ids will always result in field1 containing numeric data, you could do this instead:
假设您知道给定的 id 范围将始终导致 field1 包含数字数据，您可以这样做：
select *
from (
select /*+NO_MERGE*/ *
from country
where id >1 and id < 100000
)
where to_number(field1) = 23;
回答by APC
which has field1 varchar2 containing numberic data
其中 field1 varchar2 包含数字数据
This is not good practice. Numeric data should be kept in NUMBER columns. The reason is simple: if we don't enforce a strong data type we might find ourselves with non-numeric data in our varchar2 column. If that were to happen then a filter like this
这不是好的做法。数字数据应保存在 NUMBER 列中。原因很简单：如果我们不强制使用强数据类型，我们可能会在 varchar2 列中发现非数字数据。如果发生这种情况，那么像这样的过滤器
where to_number(field1) = 23
would fail with ORA-01722: invalid number.
会失败ORA-01722: invalid number。
I can't for certain sure say this is what is happening in your scenario, because I don't understand why apparently insignificant changes in the filters of ID have changed the success of the query. It would be instructive to see the execution plans for the different versions of the queries. But I think it is more likely to be a problem with your data than a bug in the SGA.
我不能肯定地说这就是你的场景中发生的事情，因为我不明白为什么 ID 过滤器中明显微不足道的变化改变了查询的成功。查看不同版本查询的执行计划会很有帮助。但我认为它更有可能是您的数据问题而不是 SGA 中的错误。
回答by Dave Costa
Suggest doing the following to determine for sure whether there are records containing non-numeric data. As others have said, variations in the execution plan and order of evaluation could explain why the error does not appear consistently.
建议通过以下方式来确定是否有包含非数字数据的记录。正如其他人所说，执行计划和评估顺序的变化可以解释为什么错误不会始终如一地出现。
(assuming SQLPlus as the client)
（假设 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;
/
An alternative workaround to your original issue would be to rewrite the query to avoid implicit type conversion, e.g.
原始问题的替代解决方法是重写查询以避免隐式类型转换，例如
SELECT id, TO_NUMBER( field1 )
FROM county
WHERE field1 = '23'
AND <whatever condition on id you want, if any>
回答by Adam Musch
Consider writing an IS_NUMBER PL/SQL function:
考虑编写一个 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
