Re: how to handle updates to millions of rows

As everybody mentioned in previous posts, you should avoid commits in cursor loops.

At each iteration, Oracle must get the block with the data as it was when the cursor was opened (query is started). These blocks live in the rollback/undo tablespace. If you commit, Oracle may remove these blocks from the rollback/undo tablespace when there is not enough free space left.

A trick that can avoid this error is to get a copy of the resultset of the query into temporary storage (temporary tablespace, temporary segment). If you add an ORDER BY to the query, a temporary segment is created and the rows in the cursor loop are fetched from this temporary segment and never from the rollback/undo. In fact, you made a copy of the data. Of course, you need enough temporary space, you should sort on non-indexed columns,...

This is a tricky solution, that can help in 'one time only' situations. Be aware that a database can change (indexes get added or dropped,...), that execution plans can change overtime.

Also, take a look at the asktom article mentioned in a previous post.

Also, read the manuals/articles about BULK collects, BULK updates,... This can reduce your execution time dramatically (much less LIO's).

Best regards,
Luc

"Peter Mueller" <lord.of.board_at_gmx.de> wrote in message news:e3b18ff5.0408250606.675787d8_at_posting.google.com...
> 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 - 14:53:51 CDT