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.
E.g. I have a table with 10 million records say Table Country which has field1 varchar2 containing numberic data and Id
If I do a query as an example
select * from country where to_number(field1) = 23 and id >1 and id < 100000
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.
Does anyone have any idea about this issue with TO_NUMBER or oracle functions for large data?
© Stack Overflow or respective owner