Re: Need help performing large updates...

rollback segments are essential for keeping the read consistent database. There is no way "around" that.

Either you have to divide your job to divide work and commit, or you can make a big rollback segment and use it using SET TRANSACTION USE ROLLBACK SEGMENT. I think you should divide work, that would also improve performance. If you have a sequenced/indexed primary key, try going for x number of spans of the primary key. Or you can think of some other intelligent way of dividing work.

C. Eric Ladner (clad_at_chevron.com) wrote:
> I have a couple of huge tables (>500,000 rows) that need to be updated.
> The nature of the update causes every row to be changed. The problem
> I'm running into is that I keep running out of extents in the rollback
> segments. This is using just a regular SQL statement like:
>
> update foo set bar = 'NEW' where bar = 'OLD';
 

> The only way I can seem to get it done is to break it up into a bunch of
> sub-updates that get about 20 percent of the table.
 

> Is PL/SQL an option? Would it allow me to get around the space
> limitation I'm having with the RBSegs?
 

> Thanks in advance,
 

> ========================================================================
> Eric Ladner | UNIX/Oracle/Passport Sys-Administration, General
> clad_at_chevron.com | Applications Development, etc., etc.
> ========================================================================

--
**************************************************************
*                          Saad Ahmad                        *
*                          Senior Software Engineer          *
*                          McHugh Freeman & Associates, Inc. *
*                          Ph:  (414) 798-7457               *
*                          Fax: (414) 798-8619               *
*                          E-Mail: sahmad_at_mfa.com            *
**************************************************************