Documentation on Transaction Management

  • From: chet justice <chet.justice@xxxxxxxxx>
  • To: oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 19 Nov 2009 17:02:49 -0500

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.

chet


chet justice

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

Other related posts: