Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> COMMIT frequency
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 PennsylvaniaReceived on Mon Mar 16 1998 - 00:00:00 CST
![]() |
![]() |