Re: When should I Commit?

If you mean that you are loading from flat file data you should be using SQL*Loader which by default commits approximately every 64 rows. If on the other hand you are running an On-Line Transaction Processing system your best bet is to commit each order as it is completed -- because of business rather than data base considerations. In the event of your system crashing (perish the thought) only the current orders are at risk it is relatively easy to reenter those orders once the system is brought back to life. If on the other hand you base your commit point on data base efficiency considerations  and your system crashes recovering becomes a much more difficult task. You would have to have some independent record of the transactions that have been entered since the last commit and then have them all reentered. I could be wrong, but I don't recall that Oracle notifies you when it does a commit or provides any notification of what record(s) were committed. That means you would have to go through transaction records and determine which were in the database (committed) and which weren't.

In my opinion this is one case where sacrificing efficiency in the interests of having a more easily managed recovery scheme is the preferred decision.

Jerry

uncommitted transactions once the system is back in business

Ardley Tschetter wrote:
>
> I will be loading records that pertain to an Order which on average has 3
> lines in which I will be loading an Order Line table. I know I need to
> commit my work after a certain number of transactions, but what is the
> general rule in determining the point at which I commit?
Received on Fri Oct 10 1997 - 00:00:00 CDT