Walt wrote:
> Chuck wrote:
>
>> Pradeep wrote:
>>
>>> Please fix what i am missing here.
>>>
>>> The argument against frequent commits is that oracle needs the
>>> transaction details, which is wiped out by COMMIT, to supports its
>>> multiversioning facility.
>
> No, that's not really it.
>
>> The primary argument against frequent commits is that you can't roll
>> them back. In theory, a transaction should be just that - one
>> transaction from start to finish with a single commit or rollback at the
>> end.
>
> Right. The primary argument against frequent commits is that you should
> commit when the business rules indicate that a logical piece of work has
> been completed. Sometimes that is one row. Sometimes that is a lot of
> rows.
>
> Sometimes programmers will throw in a commit every 100 or 1000 rows in
> the interest of performance. This is misguided - for one thing it is
> usually less performant since commits "cost" the same whether you're
> committing one row or a thousand. But the bigger reason is that by
> breaking your logical transaction into multiple physical transactions
> the database does not stay in a consistent state while you do the work.
> Moreover, if something goes wrong you have a half-completed mess on your
> hands to clean up.
>
> Commit when the business rules indicate it's appropriate to do so, not
> for performance reasons.
>
>
>> In reality however sometimes you need to do more frequent commits
>> regardless of what some will say. If you need to run an update on every
>> row of a 90 zillion row table, you're going to have to commit every now
>> and then.
>
> If you're updating 90 zillion rows, there's probably something wrong
> with the data design, i.e. no code table where ther should have been,
> non-normal data that needs to be recomputed, etc.
>
> //Walt
Sometimes though, you're stuck working with a design that you cannot
change. The real world is not always ideal.
Received on Tue Jan 17 2006 - 11:43:24 CST
