Does COMMIT and ROLLBACK response time affect performance?

Question

Suppose I have a set of identifiers. For each identifier, I insert many records into many different tables based on the identifier. Between inserting difference tables, various business checks will be called up. If any check fails, all records inserted based on this identifier will be ROLLBACK. This bulk insert action is performed using PL / SQL. Does COMMIT and ROLLBACK time boost performance and how does it affect? For example, should I COMMIT after completing the process for one ID or COMMIT after completing the entire ID?

Answer 1

The description of the problem indicates that you have a large set of small logical transactions (each new identifier is a transaction). You must complete every logical transaction. Two reasons to wait to complete the entire set of transactions:

• If the entire set of transactions is actually the transaction itself, then all insertions must be successful for any rows that need to be committed. In this context, your smaller “transactions” are not really transactions.
• You do not have the option of restarting during the bulk upload process, which actually makes this a special case of item 1. If the bulk upload process is interrupted, you need a way to skip successfully applied identifiers.

Tom Kyte advises you to complete every logical unit of work - a transaction.

Answer 2

, . , , ?

, . ( !), , . Commit Oracle, , , , .

Answer 3

"".

• oracle . , . .

• . ( , , : ). () , ( ). , , .

, " ( )", : " ".

, , . . . , . ORA-01555 " ".

- , , " ", .

Answer 4

, , , - . , , . - :

begin
   --Initial batch logging
   for r_record in cur_cursor loop
      savepoint s_cursor loop;
      begin
         --Process rows
      exception
         when others then
            rollback to s_cursor;
      end;
   end loop;
   --Final batch logging
exception
   when others then
      rollback;
      raise;
end;

Answer 5

. . . ... , ID ID...