Resolving In Place Alters – Part 2

In Part 1 I talked about how I knew that the prescribed method of resolving the pending in place alters in my large table by simply issuing a single large dummy update statement to update each row won’t work for large tables. There are just too many problems with long transactions, locks and excessive I/O that can trash the engine.

I decided the first thing I would try would be to update every row in the table, but do it in multiple transactions to avoid long transactions and holding on to a lot of locks. Nothing too interesting, pretty standard stuff really. The logic looks like this:

set isolation dirty read;
select {+full (large_table)} primary_key from large_table;

updated = 0
for each row:
   update large_table set primary_key = primary_key where prima...