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

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 16 Oct 2006 13:14:39 -0700
Message-ID: <1161029674.218449@bubbleator.drizzle.com>

mylesv_at_gmail.com wrote:

> DA Morgan wrote:

>> 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

> 
> Thanks, Daniel.  I have five redo logs, each 10MB in size.  Do you
> think this will suffice?  I'm not concerned with the standby database.
> I'll let FAL handle that.

I think your question indicates you came to Oracle from a database of the Ingres family (SQL Server, Sybase, or Informix). The size of your redo logs is likely too small but that is irrelevant to the question you asked as Oracle's log files do not work as you expect. I can easily run a 1TB transaction, in Oracle, with a couple of 1MB log files.

Fix your code ... but not before you hit the Concept books at http://tahiti.oracle.com and visit amazon and pick up every one of Tom Kyte's books.

-- 
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

Received on Mon Oct 16 2006 - 15:14:39 CDT