Documentation on Transaction Management

Database is 10gR2

(This is for a friend, so I don't have any more specifics than what he described)

I need to update millions of records in an address table, specifically 2 columns. CTAS was the first thought. But there's a small catch, any user accessing the system needs to be able to update their record during that "maintainence" time. So far, the fastest he can get it to run in a test environment is anywhere from 12-24 hours, using a simple LOOP and committing every *n* number of records.

He had the idea to loop through using the PK of the address table, perform the lookups/updates necessary using SELECT FOR UPDATE and then commit. From my understanding of how Oracle works, getting the individual record within the outer loop would get it as it exists at that moment in time (committed or roll(ed)back). Using one big loop with the PK and the other values would get the idea as it existed at the beginning of the query, thus ultimately potentially writing over any updates made by end-users.

Am I off my rocker here? Is my thinking correct?

If so, where can I find that documentation, specifically? A quick glance and I couldn't find anything related specifically what I am trying to accomplish (or advise).

If I didn't explain this well enough, I'll accept any hand-slapping and dutifully provide more information.

chet

chet justice

813.863.1213

http://oraclenerd.com
http://twitter.com/oraclenerd
http://www.linkedin.com/in/chetjustice

--
http://www.freelists.org/webpage/oracle-l