Database Reference
In-Depth Information
large as they need to be. Issues such as locks, blocking, and so on should not really be considered the driving forces
behind transaction size—data integrity is
the driving
force behind the size of your transaction. Locks are not a scarce
resource, and there are no contention issues between concurrent readers and writers of data. This allows you to have
robust transactions in the database. These transactions do not have to be short in duration—they should be exactly
as long as they need to be (but no longer). Transactions are not for the convenience of the computer and its software;
they are to protect your data.
Committing in a Loop
Faced with the task of updating many rows, most programmers will try to figure out some procedural way to do it in a
loop, so that they can commit every so many rows. I've heard two
(false!)
reasons for doing it this way:
•
It is faster and more efficient to frequently commit lots of small transactions than it is to
process and commit one big transaction.
•
We don't have enough undo space.
Both of these reasons are misguided. Furthermore, committing too frequently leaves you prone to the danger
of leaving your database in an “unknown” state should your update fail halfway through. It requires complex logic to
write a process that is smoothly restartable in the event of failure. By far the best option is to commit only as frequently
as your business processes dictate and to size your undo segments accordingly.
Let's take a look at these issues in more detail.
Performance Implications
It is generally not faster to commit frequently—it is almost always faster to do the work in a single SQL statement.
By way of a small example, say we have a table,
T
, with lots of rows, and we want to update a column value for every row
in that table. We'll use this to set up such a table (run these four setup steps before each of the following three cases):
EODA@ORA12CR1> drop table t;
Table dropped.
EODA@ORA12CR1> create table t as select * from all_objects;
Table created.
EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
EODA@ORA12CR1> variable n number
Well, when we go to update, we could simply do it in a single
UPDATE
statement, like this:
EODA@ORA12CR1> exec :n := dbms_utility.get_cpu_time;
PL/SQL procedure successfully completed.
EODA@ORA12CR1> update t set object_name = lower(object_name);
72614 rows updated.
EODA@ORA12CR1> exec dbms_output.put_line((dbms_utility.get_cpu_time-:n)|| ' cpu hsecs...' );
49 cpu hsecs...
