>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 <elkinsl_at_flash.net> 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
> elkinsl_at_flash.net
> 214.954.1781
>
> > -----Original Message-----
> > From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of
> > Brian_P_MacLean_at_eFunds.Com
> > 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: Brian_P_MacLean_at_eFunds.Com
> >
> > 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: ListGuru_at_fatcity.com (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: elkinsl_at_flash.net
>
> 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: ListGuru_at_fatcity.com (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: wisernet100_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Mon Aug 12 2002 - 08:13:23 CDT
