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-----
From: Tim Gorman [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 01, 2002 4:29 PM
To: Multiple recipients of list ORACLE-L
Subject: 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" <[EMAIL PROTECTED]>
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: [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: Tim Gorman
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).
