Re: how to handle updates to millions of rows

lord.of.board_at_gmx.de (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;

Your subject talks about updates, but there are no updates here!

The commit doesn't restart your cursor, so have two intertwined transactions going on here. One transaction, which gets very old, contains your cursor, and a second transaction contains the latest batch of 10,000 insert/deletes. Your cursor's transaction needs to rollback changes all the way to its start point, but it can't because the change records were overwritten. The reason you want to commit every 10,000 is that you don't want to keep those undo records lying around, right? Well, by not restarting the cursor, you need all of these records anyway.

Is it critical that you process only that data that matched your cursor at the time the procedure started? If not, a quick and dirty method is to restart the procedure (closing and re-opening the cursor) upon 01555.

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

...
> 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;

What happens to record which newly qualify for your cursor between these two procedures?

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB