Re: commit or not commit inside a cursor loop

Andrew

As a suggestion (the best way I've found so far) for situations where you need to update millions of rows and do not want a) to avoid issues of immense rollback segments b) to avoid performance problems associated with the application of read consistency at the end of a large (should that be huge) transaction. c) avoid ora-1555 errors

AND it is NOT necessary that it is all done as ONE transaction !!!! (or the performance hit to the DB overrides the "nice to have" of one transaction) just that all rows are updated once.

1. Create a temporary table containing the key values (or rowids if you can guarantee that no other process is inserting or deleting rows at the same time) of the rows you want to affect with the update and a sequence field (give it a huge value such that step 2 does not cause chained rows). Preferably do this using a CTAS (create table as select) in parallel (for both create and select parts) and using NOLOGGING. Note that as of Oracle 8i putting ROWID in the CTAS list mangled the ROWID to be unusable. It's probably still true as of Ora 9i (I've not tried it though). I guess this is to give some protection against storing ROWIDs. You can get round this by creating a pl/sql function that takes a rowid parameter and returns a rowid result (passing the parameter straight out as the result)
2. Update the sequence field setting it equal to rownum.
3. Index this sequence field (preferably in parallel and using NOLOGGING).

You've now effectively created a pseudo-index but one that is not going to change as the data is manipulated.

4. Change your loop code to loop on these sequence numbers in this temporary table.

5. Array fetch the ROWIDs into a PL/SQL table or C array and use these to go find the actual rows to be updated - again using array update (in PL/SQL talk array fetch/update/delete is called bulk fetch/bulk bind).

6. Commit after the array update to keep the transactions small and the rollback to a minimum.

7. Don't forget to clean up the temporary table (either use options of a genuine Oracle temporary table or truncate and drop it).

This has the advantage of never reading, during the loop fetch, the data that you are changing in the update so that you avoid most of the read consistency issues. The array fetch/update helps minimise the costs of switching between the SQL and PL/SQL components of the Oracle engine.

The other beauty of this method is that you can also run the update in parallel by splitting the range of sequence numbers in the temporary table up over several processes/threads.

I've seen 5-10 hour tasks (on properly configured hardware - i.e. where the parallel query option is usable) drop to 5-10 minutes even including the extra tasks of creating the new temporary table and index. It won't work in all situations but when it does it makes you glad you're using Oracle :-))

Remember all of this is only relevant if you don't need the update to be one transaction. It's probably only relevant too if you can't do the update in a single SQL statement because of rollback constraints or the single update is too long running and you have the CPU's and disk configuration to run the whole process in parallel.

In Oracle 9i you could also look at using parallel update (though there are plenty of little restrictions on parallele DML). I assume though that because you state that you must use PL/SQL that your example is leaving out lots of detail that requires PL/SQL programming to get values for the update for each row such that a SQL statement is not constructable (is that a word ? :-) ) from the program logic.

Andy Received on Fri May 30 2003 - 04:11:34 CDT