Title: 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
Hi all:
Someone at my shop wants to delete about 20% of roes
in a table (20 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
Gene,
afaik there is no direct possibility.
One way around may be:
DELETE FROM table1 WHERE ('your 20% condition') AND rownum 1001;
commit;
DELETE FROM table1 WHERE ('your 20% condition') AND rownum 1001;
commit;
until all rows are deleted
of course this may be written in plsql with
:
Sent by: Subject: Delete performance
root@fatcity.
com
I think that you better delete all indexes that are not used in the
selection of the records.
Updating the indexes will add a long time to the run.
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, August 01, 2002 4:03 PM
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
