Re: Need help performing large updates...

harry_at_merlin.unterland.de (Harald Becker) wrote:

>In article <316971E7.2781_at_chevron.com>,
>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:
 [...]
>have you tried to increase the size of your RBS by adding another datafile
>and increasing the size of the extents?
 

>some time ago we did an update to a table with over 20,000,000 rows and we
>created one large Rollback-segment with 1.5 Gigabytes and an extent-size
>of 500 Megabytes. At the beginning of the update as the first statement
>we used 'set transaction use rollback segment RBS_BIG' to select this
>big segment and it worked fine.

One thing to consider every time you update substantial parts of a tables is if it would be better to create a new table.

Instead of

   UPDATE XXX SET FIELD_X = 'DONE'
   WHERE FIELD_X = 'NOT DONE';
can run:

   CREATE TABLE XXX_NEW AS
   SELECT FIELD_1, FIELD_2 ..., 'DONE'
   FROM XXX;   DROP TABLE XXX;
  RENAME TABLE XXX_NEW TO XXX; The latter scheme will not require any rollback segment because it is a DDL-statement, and as such it is committed immidiately. Sometimes the two tables require less space than one table + rollback segment, and it certainly generates a lot less redo log information.

Yours sincerely
Torben Jeppesen, DBA

Computer consultant. Received on Sun Apr 14 1996 - 00:00:00 CEST