RE: Documentation on Transaction Management
Date: Thu, 19 Nov 2009 17:35:35 -0500
Assuming millions mean ~5 million and not 500 million that update shouldn't take more than a few minutes, even on laptop. The for loop with committing every n records will be very slow, probably the slowest method, short of adding a sleep between records. He should use a merge, bulk processing, correlated update, ctas with parallelism etc. to handle the update. Once the process runs in minutes you pretty much avoid the user update issue.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of chet justice
Sent: Thursday, November 19, 2009 5:03 PM To: oracle-l
Subject: Documentation on Transaction Management
Here's the situation:
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.
813.863.1213Received on Thu Nov 19 2009 - 16:35:35 CST