Home » SQL & PL/SQL » SQL & PL/SQL » ORA-22813: operand value eceeds system limits
ORA-22813: operand value eceeds system limits [message #204809] Wed, 22 November 2006 03:41 Go to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I am putting 8.1M rows in a collection. Then insert those to a table using:
        insert into tab..
         select  .. table(cast(<the collection>..))

After sometime, I got this error:
ORA-22813: operand value exceeds system limits
Any idea? Anyone??
Re: ORA-22813: operand value eceeds system limits [message #204811 is a reply to message #204809] Wed, 22 November 2006 03:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Test for the error message is:
ORA-22813 operand value exceeds system limits

Cause: Object or Collection value was too large. 
The size of the value might have exceeded 30k in a SORT context,
 or the size might be too big for available memory.

Action: Choose another value and retry the operation.


I'd recommend using a Global Temporary Table for that much data.

[Updated on: Wed, 22 November 2006 05:52]

Report message to a moderator

Re: ORA-22813: operand value eceeds system limits [message #204834 is a reply to message #204811] Wed, 22 November 2006 05:31 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Actually, that's the original design - to use global temporary table. But no statistics are created for global temporary tables, right? And my knowledge on DBMS_STATS.SET_TABLE_STATS or set_index_stats, numrows, avglen, numblk, etc. is very limited. Hence, I resorted to using an actual table and leave the computation of stats to Oracle (using DBMS_STATS.gather_table_stats..). Using a non-global temp table, and I have:
insert into TAB..
         select  .. from <permanent table>

I'm having performance issues. It's executing for more than 12 hours. However, the select... from <permanent table> itself just takes 6 minutes.
I'm kind of desperate so am trying several stuff, like using a collection...
I would greatly appreciate if you can shed a light on this. By the way, the select returns 8.1M rows and the table TAB has 22.3M rows already
Re: ORA-22813: operand value eceeds system limits [message #204836 is a reply to message #204834] Wed, 22 November 2006 05:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you're just selecting all the rows from the GTT and inserting them into another table, then the statistics won't matter - a FTS is a FTS, no matter how you get to it.

Q1) Are you sure that the SELECT from TAB takes 6 minutes? How are you doing this? (You may well be right, but a lot of people run a query in TOAD, get the first results back and take that as the time taken to execute the whole query.

Assuming you are right about the Select time, it must be the Insert that is taking the time.

Q2) Are there any triggers on the table you are inserting into.

Q3) Is there a foreign key constraint on the table that points to a primary key with no index.


Re: ORA-22813: operand value eceeds system limits [message #204838 is a reply to message #204836] Wed, 22 November 2006 05:58 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Good catch!
A1) The 'select count(1)' takes 6 minutes, and getting everything (which I tried by putting the result set in a collection) takes 17 mins. And yes, you're right - it's the Insert that is taking the time.

A2) nno triggers

A3) There are 2 foreign key constraints and 3 indexes (1 PK and 2 normal indexes)

But, is it 'normal' for the INSERT to take more than 12 hours while the SELECT just takes less than 30 mins??

Re: ORA-22813: operand value eceeds system limits [message #204842 is a reply to message #204838] Wed, 22 November 2006 06:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd say that it seems to be taking an abnormally long time to do the inserts.

Are the primary keys that the two foreign keys point to indexes?

none of the indexes on the table are function based are they?
Re: ORA-22813: operand value eceeds system limits [message #205057 is a reply to message #204809] Thu, 23 November 2006 03:02 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Also, try using the /*+ APPEND */ hint to speed up the inserts. Also try taking the trace dump and find exactly what is taking time or any contention happening on the given table while doing insert.
Re: ORA-22813: operand value eceeds system limits [message #205100 is a reply to message #205057] Thu, 23 November 2006 05:33 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
JRowbottom:
Quote:
Are the primary keys that the two foreign keys point to indexes?
none of the indexes on the table are function based are they?

Yes and Yes.

I tried the APPEND hint, too, with NOLOGGING. But it did not help much. And the DBA has yet to get back to me for the dump file.. Confused
Re: ORA-22813: operand value eceeds system limits [message #205113 is a reply to message #205100] Thu, 23 November 2006 06:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is that Yes, the indexes are function based, or Yes, the indexes are not function based?

I'm starting to clutch at straws a little.
What version of the Db are you on?

Can you run this query for the table and its indexes
select count(*) from user_extents where segment_name = <table or index name>;
Re: ORA-22813: operand value eceeds system limits [message #205134 is a reply to message #205113] Thu, 23 November 2006 07:22 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Sorry for the confusion Smile No index is function based.

My db version is: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

SQL> select count(*) from user_extents where segment_name = 'COMPANY';

  COUNT(*)
----------
       195


Re: ORA-22813: operand value eceeds system limits [message #205136 is a reply to message #204809] Thu, 23 November 2006 07:29 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

How about a bulk collect with a limit of 1000 ?

Disabel the table index, which you are inserting into, and rebuild them after ?

[Updated on: Thu, 23 November 2006 07:31]

Report message to a moderator

Re: ORA-22813: operand value eceeds system limits [message #207069 is a reply to message #205136] Mon, 04 December 2006 01:24 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Thanks! That worked - I just need to disable contraints then reenable them and also rebuild indexes. It's now taking 15 mins only.
Re: ORA-22813: operand value eceeds system limits [message #207197 is a reply to message #207069] Mon, 04 December 2006 10:06 Go to previous message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Great Smile
Previous Topic: How to print (v)array (merged)
Next Topic: Hide a URL Redirect
Goto Forum:
  


Current Time: Fri Dec 09 09:30:31 CST 2016

Total time taken to generate the page: 0.05027 seconds