Re: Documentation on Transaction Management
- From: chet justice <chet.justice@xxxxxxxxx>
- To: Kenneth Naim <kennaim@xxxxxxxxx>
- Date: Thu, 19 Nov 2009 19:49:54 -0500
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@xxxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
> oracle-l-bounce@xxxxxxxxxxxxx] *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
>
Other related posts:
