Re: Need help performing large updates...

C. Eric Ladner 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.
> ========================================================================

Eric -

A PL/SQL routine will allow you perform a COMMIT at regular intervals, say every 100,000 rows and this would be lighter on your rollback segments. However, you can expect some performance degradation since your PL/SQL routine will be processing a single row at a time as opposed to the 'array' processing available to you through most tools. This could be an even bigger problem if the PL/SQL is client- rather than server-based because of network traffic issues.

I suggest you run a subset of the update with SQL_TRACE=TRUE with both scenarios and run the trace files produced through TKPROF. Paying close attention to the 'execute' counts should help you with your evaluation of the two methods. If you have the TIMED_STATISTICS initialization parameter set to TRUE, you will also be able to see the relative amounts of CPU resource consumed as well as the time taken to carry out the updates.

Of course, the best solution would be, as recommended in Saad's message... ensure that you have at least one rollback segment available for the ENTIRE update. Beware, however, that other transactions could already be busy in the large rollback segment before your update starts. Since your update won't 'leapfrog' over the other transactions, additional extents will be added even 'tho the rest of the rollback segment is unused and your original extent problem will resurface! If possible, run your update at a quiet time of day/night.

Allan Seabrook

Tel (Cellular) : +27 11 82 800 4344
           Fax : +27 11 313 5126
        E-Mail : seabrook_at_is.co.za 
--------------------------------------------------------------------------