Re: most expeditious way to update a large number of rows

mylesv_at_gmail.com wrote:
> Hello. I need to update a single column in every row of a large table
> (671 000 rows.)
>
> In order to prevent the redo logs from filling faster than Oracle can
> archive them, I set up a loop to update 5000 rows at a time, commit,
> and then sleep for two seconds.
>
> The log archiver was able to keep up, but I estimate that it'll take
> about 74 hours for every row to be updated. I cannot use "nolog" as I
> have a physical standby database that depends on the archived redo
> logs.
>
> Thanks in advance for any hints!
> Myles

671K rows is NOT a large number. Perform the update then commit. What you have done is an invitation for a visit from ORA-01555 and very inefficient.

If it is taking you 74 hours to perform the update archive logging is not your issue. Explain Plan with DBMS_XPLAN should be the next thing you do.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org