>case, the app is very bad and we (me and another member on the list) >were tasked to pull it out of the ditch (my 42nd day straight on this on >back to back to back, etc 100/hr weeks, I want to hear the violins!). If >we were to have written it, there wouldn't be the need for some of these >large updates,
violins only if you are NOT getting paid an hourly consulting fee. Otherwise, yea, you are working hard but getting paid for your time :) and yeah, knowing you and that other member of the list, it would have been done properly the first time as for that methodology on the update -- VERY slick! I like it, will keep this note to remind me when it's my turn to have to deal with someone else's code ... --- Larry Elkins <[EMAIL PROTECTED]> wrote: > All my comments are with regards to 8i. Might do things differently > with 9i > ;-) > > Familiar with the technique for doing large deletes? For example, you > want > to delete 40 million rows from a 100 million row table. It can often > times > be much more effective to do a CTAS (or insert append into an > existing > object) in parallel excluding the rows you want to delete. You can > then > truncate the source and throw the rows back in, or drop and rename > (taking > care of priv's and possible synonyms), or exchange partition, > whatever. > > The same technique can be applied to "updates". Numerous examples > where this > approach has been used with great success, I'll use one. In this > particular > example, we have a partitioned table, 162 million rows in a > partition, and > need to update 30 million rows in that partition with values from > another > table (bad, bad app, if designed correctly such a step wouldn't even > be > needed). We also have a "holding" table with the same structure. > We'll do an > insert append in parallel (implying append) outer joining to the > table > providing the values (using HJ). Use a decode to know whether or not > to > retain the value or if it should be "updated" if you found a matching > row. > Then, simply do an exchange partition no validate swapping your > "hold" table > with the partition that was to be updated. With the no validate it's > basically a dictionary operation not even having to verify the > values. Boom, > there you go, a big update done very quickly. And then truncate the > hold > table (paying attention to next extent issues after parallel insert > and ways > around them). In another recent example, we had to update a column > with a > constant for all rows in a 109 million row table (don't ask). This > type > insert and swap approach allowed it to be done in 10 to 12 minutes. > > So you might be able to apply similar techniques to your situation. > In our > case, the app is very bad and we (me and another member on the list) > were > tasked to pull it out of the ditch (my 42nd day straight on this on > back to > back to back, etc 100/hr weeks, I want to hear the violins!). If we > were to > have written it, there wouldn't be the need for some of these large > updates, > etc. But we don't have the luxury of completely rewriting the whole > thing > right now, so we apply the "update / delete becomes an insert and > exchange > partition approach" to selected areas experiencing severe performance > issues. And it works well. We had one process (cursor based of course > in the > coder's infinite wisdom updating 1 row at a time and committing every > 1000 > rows) that projected, by the rate of rows updated, to take 52.4 years > to > complete ;-). Now it takes 15 minutes. > > Just an idea that might be applicable in your situation. It's a > little > different, but not really much different than the CTAS (or insert > append) > approach that folks use for mass deletes. It's the same concept just > applied > to updates. And you can extend it to inserts / deletes. Don't know > you > situation, but maybe you do it in one statement. Seriously, I took a > few > thousand lines package doing multiple updates/deletes down to a > single > insert statement outer joining some tables and an exchange partition. > > Oh well, I'm delirious from a lack of sleep so the above might be a > bit > rambling. But I hope you get the idea. > > Regards, > > Larry G. Elkins > [EMAIL PROTECTED] > 214.954.1781 > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of > > [EMAIL PROTECTED] > > Sent: Saturday, August 10, 2002 12:38 PM > > To: Multiple recipients of list ORACLE-L > > Subject: Lock table table_name in exclusive mode - Performance > gain? > > > > > > Anyone do any bench marking, know of any papers, or using "lock > table > > table_name in exclusive mode" to get a performance boost. > > > > I'm trying to figure out how to do 90,000,000 operations > > (add/change/delete) on the same table/partitions in a 4 hour > > period, and it > > looks like lighting will have to strike twice in the same place for > it to > > happen. > > > > Any other suggestions on how to cut down on the cost of a > transaction. I > > know about dropping indexes, using hash keys, partitions, > unrecoverable, > > multi-process/threading, sql loader direct, and noarchivelog. > > > > Help Meeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee! > > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > > San Diego, California -- Public Internet access / Mailing > Lists > > > -------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like > subscribing). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Larry Elkins > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing > Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __________________________________________________ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
