Re: how to handle updates to millions of rows

Thanks a lot for the link!!!!! It went straight to my favorites! The link really helped me a lot!

lordi

Vicchu <vicchu22_at_yahoo.com> wrote in message news:<412CA1B4.2000000_at_yahoo.com>...
> 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 Thu Aug 26 2004 - 02:45:53 CDT