RE: Delete performance

I would use your method to CTAS but combine it with partitioning in order to overcome the unavailability issue. The new table would be a single-partition (MAXVALUE) object that would enable the use of EXCHANGE feature. I posted a nugget a while back describing the use of a one-partition table (and indexes) and then swapped the underlying segment with a normal table on the fly. The catch is the licensing cost for partitioning. But we already had it for it's intended use and this "availability" feature was icing on the cake. This method is replaced by 9i's online reorg feature but we got a good 3 years out of it.

Tony Aponte
Home Shopping Network, Inc.

-----Original Message-----
Sent: Thursday, August 01, 2002 4:29 PM
To: Multiple recipients of list ORACLE-L

If the table can be "unavailable" for a very brief period of time while this is happening, I'd suggest performing a PARALLEL NOLOGGING CREATE TABLE AS SELECT to perform this mass deletion. Use a WHERE clause in the SELECT portion of the CTAS that picks up all the rows you want to keep, which is the logical negation of the WHERE clause you already have for the DELETE.

Advantages: faster (INSERT operations are always faster than UPDATE or DELETE), using NOLOGGING is possible (faster, reduce overall impact on system), no undo is generated (faster, reduce one possible point of failure), and if you had any ambitions to re-build the table (i.e. get rid of chained rows, move to locally-managed tablespace), that gets done too. Same for the associated indices (rebuild them in parallel, nologging, compute stats). Also, the original table can be renamed and saved in case it's ever needed (for fast "rollback", for example)...

Disadvantages: if table cannot be unavailable for the last-second RENAME operation when the "old" table is swapped for the "new", which would invalidate any associated PL/SQL stored objects and open cursors, then this won't work. However small that window of unavailability may be, sometimes you just can't go there...

Just an idea...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, August 01, 2002 8:03 AM

> Hi all:
>
> Someone at my shop wants to delete about 20% of roes
> in a table (200000 rows out of a million). He wants to
> set a commit frequency (like every 1000 records or
> so)
> to keep the rbs under control. I am not aware of any
> easy way to do it other then writing a procedure, but
> I may be missing something here. Is there any simple
> way to accomplish this?
>
> Also I have suggested instead of deleting 20% of the
> rows, create a new table as a select and insert the
> rest of the rows into it (then rebuild the indices and
> rename). This can be done in nologging mode, without
> redo logs and rbs segments. Is this a good idea to
> try?
>
> thanks for any info
>
> Gene
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Health - Feel better, live better
> http://health.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Gurelei
> INET: gurelei_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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.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: Aponte, Tony
  INET: AponteT_at_hsn.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).