Re: how to handle updates to millions of rows

Peter Mueller wrote:

> 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

First of all, as already mentioned, get rid of the commit inside the loop - it *causes* the 1555! Secondly, always mention basics, as platform and versions; who knows, you could have been using the merge command...

-- 

Regards,
Frank van Bortel