Database Reference
In-Depth Information
Restartable Processes Require Complex Logic
The most serious problem with the “commit before the logical transaction is over” approach is the fact that it
frequently leaves your database in an unknown state if the
UPDATE
fails halfway through. Unless you planned for this
ahead of time, it is very hard to restart the failed process, allowing it to pick up where it left off. For example, say we
were not applying the
LOWER()
function to the column, as in the previous example, but rather some other function of
the column, such as this:
last_ddl_time = last_ddl_time + 1;
If we halted the
UPDATE
loop partway through, how would we restart it? We could not just rerun it, as we would
end up adding 2 to some dates, and 1 to others. If we fail again, we would add 3 to some, 2 to others, 1 to the rest,
and so on. We need yet more complex logic—some way to “partition” the data. For example, we could process every
OBJECT_NAME
that starts with
A
, and then
B
, and so on:
EODA@ORA12CR1> create table to_do
2 as
3 select distinct substr( object_name, 1,1 ) first_char
4 from T
5 /
Table created.
EODA@ORA12CR1> begin
2 for x in ( select * from to_do )
3 loop
4 update t set last_ddl_time = last_ddl_time+1
5 where object_name like x.first_char || '%';
6
7 dbms_output.put_line( sql%rowcount || ' rows updated' );
8 delete from to_do where first_char = x.first_char;
9
10 commit;
11 end loop;
12 end;
13 /
238 rows updated
5730 rows updated
1428 rows updated
...
262 rows updated
1687 rows updated
PL/SQL procedure successfully completed.
Now, we could restart this process if it fails, since we would not process any object name that had already been
processed successfully. The problem with this approach, however, is that unless we have some attribute that evenly
partitions the data, we will end up having a very wide distribution of rows. The second
UPDATE
did more work than
all of the others combined. Additionally, if other sessions are accessing this table and modifying the data, they might
update the
OBJECT_NAME
field as well. Suppose that some other session updates the object named
Z
to be
A
,
after
we
