RE: Lock table table_name in exclusive mode - Performance gain?

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).