Re: Commit

On 10 Aug 2005 10:38:03 -0700, "prg" <prmail_at_gmail.com> wrote:

>I am updating 10 million records. The query has to make joins with
>multiple tables and it's a time consuming process. I would like to
>autocommit every record as it is found by the subquery. How can i do it
>?
>
>Thanks !

Using any form of autocommit is one of the safest routes to an unscalable and error prone application.
First of all, you shouldn't split up a *logical* transaction into many *physical* transactions. If any of those physical transactions fail, in what hybrid state is your database? How do you restart the process? Secondly, you are increasing the risk you will be getting ora-1555, snapshot too old, which especially occurs when you *commit inside a loop*, or ora-1012 fetch out of sequence. Just do a research in this group on ora-1555 and read everything about all the reasons there are NOT to commit inside a loop.

You should tune your rollback/undo segments (make them large enough), and the DBWR and LGWR processes, and/or your database architecture. You should cure the patient instead of fighting symptoms by applying workaround after workaround after workaround, to end up by throwing your application away, because it is completely FUBAR, or being fired by your management, because you have piled disaster upon disaster upon disaster.
There many ways too stay out of hell.
Doing things properly is one thing. Not committing inside a loop another
Start doing things properly TODAY, and stop hacking.

--
Sybrand Bakker, Senior Oracle DBA