Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> COMMIT frequency

COMMIT frequency

From: Tad Davis <davist_at_isc.upenn.edu>
Date: 1998/03/16
Message-ID: <350D57D9.64D7E05@isc.upenn.edu>#1/1

We have a number of PL/SQL batch routines that perform various updates. The question has arisen from time to time about how often to do commits. Typically the updates are very simple one-row-at-a-time procedures, no complex transaction or distributed processing to worry about, no special rollback considerations. (Our updates are written in such a way that if a job fails halfway through, it can simply be restarted.)

Is there "conventional wisdom" about how often to commit in this kind of situation? One approach is simply to commit after every update; another is to maintain a counter and commit after every X updates. From a logical standpoint, the first approach seems preferable: each update is a standalone change, and should be made permanent. From a performance standpoint, it seems like the second approach MIGHT be preferable, though I haven't seen much difference in actual run time with either.

What are the advantages and disadvantages of each? Is there an official Oracle reference I've missed on this subject?

-- 
Tad Davis                  Lead Programmer Analyst
davist_at_isc.upenn.edu       Information Systems and Computing
voice 215-898-7864         Administrative Information Technologies
fax   215-898-0386         University of Pennsylvania
Received on Mon Mar 16 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US