Unlogged Deletes in Oracle

Hi All,
I am coming to grips with what 'nologging' means in Oracle.

I have a table with only 25M rows but lots of columns and lots and lots of indexes on it. I'm trying to get rid of about 8M rows but the delete is writing about 3GB per 500K rows deleted to the undo tablespace. We have set the undo retention parameter to 60 seconds and bounced the database but the undots stays populated with undo data unless we wait for what seems like overnight. (It's empty in the morning when we come in...)

I don't want to recreate the table as we have people testing and it would take 24 hours to rebuild...

Q1. Can anyone tell me the best way to perform minimal logging on deletes? (I have another client where we know we will want to do a lot of updates and we may want to do these as unlogged deletes and a load.)

Q2. And how can we clear out the undots after each set of deletes?

We are on AIX 5.1 and Oracle 9.2 64 bit version.

Thanks

Peter Nolan Received on Tue Nov 30 2004 - 04:08:23 CST