Ideally, your decision should be based on the definition of the
business transaction.
The only reason to commit more often would be due to a performance
restriction, where the business transaction is too large. There is
nothing wrong with updating 10,000 or 100,000 rows between commits,
provided your redologs are properly sized, and the complexity of the
update statement allows it to complete in a relatively 'quick' time.
You are ultimately trying to balance 1- putting all updates belonging
to one business transaction in one system transaction (one commit),
with 2- the impact from/to other session doing their business
(typically performing better when all other session only have small
transactions), and 3- system limitations or performance impacts.
hth
Received on Thu Oct 19 2006 - 08:07:06 CDT
