| ORA-22813: operand value eceeds system limits [message #204809] |
Wed, 22 November 2006 03:41  |
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  |
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 |
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 |
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 |
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 #205113 is a reply to message #205100] |
Thu, 23 November 2006 06:11 |
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>; |
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
No index is function based.