Re: ORA-04031 with Collections and SGA Settings

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: 29 Jun 2006 14:37:22 -0700
Message-ID: <1151617042.846972.324920@b68g2000cwa.googlegroups.com>

> I attempted a quick test of collections to see if it would work and how much
> of a time savings I could achieve. My steps for the test were as follows:
>
>
> 1. I used a BULK COLLECT to read 978,218 rows into collection #1.

Reading a million rows into a collection is not a good idea. As you said you are going to run out of memory, and its surely not the fastest way todo it.

> 2. I then created collection #2 from data directly in collection #1 and
> from concatenations and conversions of collection #1 data.

If collection #2 is derrived purely from collection #1 then you may well be able to do something like

insert into final_table (cols)
select cols
from original table(s).

That would be the most efficient way to achive what you want.

If thats really not possible, then bulk collect the data, say 100 records at a time, transform it, load it and repeat. Received on Thu Jun 29 2006 - 16:37:22 CDT