Re: Delete performance

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