Re: Managing transations involving thousands of records

On 28 Dec 2005 00:08:21 -0800, "jortizclaver" <jortizclaver_at_gmail.com> wrote:

>A. Process takes too long to complete so a rollback at the end would
>imply to restart the process. Also, I suppose a commit of thousands of
>change could be really painfull for the database.
>

A commit means just writing a commit marker in the redo log, and releasing locks. Nothing painful about that. Also there is something like a *logical* transaction. A logical transaction should either be complete or incomplete. If the transaction is big, so be it, or split it in smaller *logical* transactions.

>B. The use of one transaction for each record is the easiest way to
>implement it but it sounds like pretty intensive for the database.
>

It is a horrible approach, as it will make your application unscalable.
However, this is exactly the approach most database independent software chooses, and it will *always* kill Oracle perfromance

>C. This option allows me to make a commit each 100 records, so it seems
>like it improves the performance of the process. In the the other hand,
>if a record fails, I'd reject some other records valid for my system.
>

It is an equally horrible approach as it will easily end-up in ora-1555, snapshot too old errors. Starting from Oracle 9i, ora-1555 ends up in the alert.
>D. I perceive this last one, or at least a combination between C and D,
>like the best one but I have some concerns about the performance of the
>use of the savepoints feature.
>
>Well, someone could think I already wrote the question and the answer
>but I'd really appreciate any comment about this reflexions. I'm not a
>DBA so probably I'm wrong in more than one assumption.

Implement A and forget about the workarounds B, C, and D They don't scale.

--
Sybrand Bakker, Senior Oracle DBA