Re: Documentation on Transaction Management
Date: Thu, 19 Nov 2009 19:49:54 -0500
Message-ID: <8311a5b60911191649ndf56918t6fc795f88dd84e7a_at_mail.gmail.com>
In talking to friend I may have come up with a better analogy, and ETL like process but on an OLTP system. Basically scrubbing the data.
I did find this link:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_sqlproc.htm#i1025370
which is in reference ot serializable (I'd much rather defer to those who know more about this than I do), but I believe that is what I was looking for in terms of documentation.
The unfortunate thing about this particular problem is that there is an UPDATE_DATE but you'd then have to compare that with the UPDATED_BY column (I'm not sure this is unfortunate, *I'm* still thinking through this problem).
More information, there are about 10 million rows, if that makes a difference at all.
On Thu, Nov 19, 2009 at 5:35 PM, Kenneth Naim <kennaim_at_gmail.com> wrote:
> 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.
>
>
>
> Ken
>
>
>
>
>
>
>
> *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.
>
> 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-lReceived on Thu Nov 19 2009 - 18:49:54 CST