Re: Need help performing large updates...

"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';
>
>Is PL/SQL an option? Would it allow me to get around the space
>limitation I'm having with the RBSegs?

One option is to create one rollback segment large enough to handle your update. You may then explicitly use that rollback segment by using the command "SET TRANSACTION USE ROLLBACK SEGMENT your_big_rbs". This set transaction command must be the first statement in your transaction. Consult the SQL Reference manual for your Oracle version to see examples.

When creating a very large rollback segment, it can be defined with both a "maximum extents" and an "optimal size". The "extent size" and the "maximum extents" will determine the maximum size the rollback segment will be permitted to reach. The "optimal size" can be used to reduce the amount of space allocated by the rollback segment, between your large transactions. Consult the SQL Reference manual regarding "CREATE ROLLBACK SEGMENT" for more information on this. I have used this technique very successfully for updates over 1,000,000 records, spanning several tables, where the update must be completely rolled back upon any error. This approach does not require (or exclude) the use of PL/SQL.

• Andy Zitelli, Silicon Systems, Inc.