Is there a difference in overhead between COMMIT and ROLLBACK?
I am developing an application that, within a single transaction, writes to a table and then reads that table. Once this is done, these changes are no longer needed and can be safely discarded. But on the other hand, the changes will not interfere with anything else if they are committed.
In short, I can use either rollback or commit to complete the transaction, considering only efficiency.
So what, if either, will be faster than the other, and why?
source to share
Oracle generates repeated on-line log files and discards data. The online redo log files contain DML / DDL statements for redoing a transaction (in case of dimming) and for rolling back your data if a rollback statement is invoked.
Commit is a very fast statement, its time is constant and does not depend on the size of the transaction. This is possible because the LGWR process writes the repeated changes to disk during a transaction in the background. If you are using asynchronous commits, for example commit write nowait batch;
, the commit time will be almost 0.
Rollback depends on the size of the transaction because it needs to rollback any expression you have in the redo log files associated with the transaction, so the transaction time can be equal to the transaction rollback time.
For a short term transaction there may be differences, but for a medium and long term transaction, you will notice that the time or rollback is almost equal to the time of the transaction.
source to share
Commit should be faster because Oracle uses "fast commit", ie. assumes that you will still commit and write changes to DB files or buffer cache right away.
Since you can still choose not to rollback, it stores "before" information in its rollback segments. The transfer essentially discards portions of the rollback segments and marks the transaction as committed.
The rollback, however, is like a complete "undo" in the application. Oracle needs to replace the actual data representation with data in rollback segments. This is real work.
Eventually you need to clear the data and then you need to spend some time.
source to share