Yea, you'll want to do something like the following (which was ripped from Tom :-)

open cursor;
loop
fetch c bulk collect into l_data LIMIT 1000; /* This limits each collection to 1000, adjust as necessary */
loop
begin
forall i in l_start .. l_data.count
insert into t values ( l_data(i) );
EXIT;
exception
when others then
dbms_output.put_line( 'Bad row index = ' || (l_start+sql%rowcount) ||
' ' || sqlerrm );
l_start := l_start + sql%rowcount + 1;
end;
end loop;
exit when c%notfound;
end loop;

close cursor;

-----Original Message-----
From: [Email Address Removed] [mailto:[Email Address Removed] Friday, May 30, 2003 1:08 PM
To: Quintin, Richard; LazyDBA.com Discussion
Subject: RE: RE: how does oracle manage unique constraints?


we have master tables as large as 8GB and we only have 500MB in the buffer
cache, plus other processes going on. I think the biggest slow down comes that
while we are doing the large ingestions those blocks are getting pushed out of
the buffer cache and being re-queried by the anti-join.

I ran statspack, but Im just digging into it and am not sure what to check for.

so I think Ill need less memory with these bulk collects actually and less
problems with re-querying the same data.

only issue is multiple bulk collects. Ill play with it.

Oracle LazyDBA home page