Re: how to handle updates to millions of rows

Just read an article on this one..

http://asktom.oracle.com/pls/ask/f?p=4950:8:13851640604002629284::NO::F4950_P8_DISPLAYID,F4950_P8_B:6407993912330,Y

Vicchu

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
>
>
Received on Wed Aug 25 2004 - 09:27:00 CDT