From:Steve Adams
Date:05-Apr-2001 14:44
Subject:   How to speed up an update

A single SQL statement driven by a full table scan will be the fastest.

To see if you will have enough rollback space, test the update on the first 100,000 rows, look at V$TRANSACTION.USED_UBLK and then rollback. No-one else will see your uncommitted update, but you be able to estimate how many rollback segment blocks the whole update would need.

If you don't have enough space in your rollback segment tablespace, then you may be able to create a new tablespace to hold a large rollback segment just for this update, and drop that rollback segment and tablespace afterwards. Or you may be able to switch the temporary tablespace to permanent contents for a while and use that instead.

If you insist on using PL/SQL rather than simple SQL, then may I suggest that you look at the script commit_every.sql on the Ixora web site for a way of reducing the commit frequency.

I have to update some 9+ million records as quickly as possible. We are running 8.0.5. Here's what I am planning to do in a plsql procedure

  1. Fetch records from the table by primary key by using a cursor.
  2. Update based on the fetch -- setting a sequence column to null. (I cannot drop the column and add later because I am on 8.0.5)
  3. Add 1 to a commit counter
  4. If the commit counter reaches a threshold of, say, 5000 records, a commit is called. The commit counter resets to zero.
While this scheme works just fine, it's quite slow when updating records one-by-one. I'm hesitant to just call out one big SQL statement to do the trick with one giant commit at the end -- I'd probably run out of rollback space.

Since the shop floor will *have* to be down during the time the update is in process, it's of utmost importance to complete the update as quickly as possible. Have you any suggestions that would accomplish this feat in the least amount of time?