how to handle updates to millions of rows

Hi all,

I am currently working on two very big tables (two times 8 mio records),
which I have to process. The problem is the infamous ora-01555 error.

Here is what I want to do:

declare
 cursor for joining two tables (a and b) begin
 for row in curser loop
   calculate some values
   insert joined row into a new table (a_b_joined)    delete joined rows from origin tables by rowids (a and b)    commit all 10,000 rows
 end loop
end;

This does not work, as I always get an ora-01555 (snapshot too old: rollback segment too small). Then I tried:

declare
 cursor for joining two tables (a and b) begin
 for row in curser loop
   calculate some values
   insert joined row into a new table (a_b_joined)    mark joined rows from tables by rowids with a flag (a and b)    commit all 10,000 rows
 end loop
 delete marked rows
end;

This didn't succeed either. What actually works is:

declare
 cursor for joining two tables (a and b) begin
 for row in curser loop
   insert joined row into a new table (a_b_joined)    commit all 10,000 rows
 end loop
end;

and afterwards

declare
 cursor for selecting merged records (a and b) begin
 for row in curser loop
   mark joined rows from tables by primary key with a flag (a and b)    commit all 10,000 rows
 end loop
end;

This solution is of course slower than it should and could be, but the question is, how can i do this more efficient, but still prevent the ora-01555?

I thought about 'cursor for update', but then I can't commit every now and then, right?

Has someone a idea about this?

Best regards,
lordi Received on Wed Aug 25 2004 - 09:06:20 CDT