Oracle Database philosophy is Data Recover-ability. This is the reason if you have a huge table with more than 100 million records and indexes on it. Cleaning it up can be a mess. Truncate is required when you have to empty the entire table. Ofcourse that is much quicker. But sometimes you need a conditional cleaning where you need to keep a small set of data and purge the rest of the records. It would take days to purge records like this. Increasing the size of undo tablespace also doesn’t help in this case sometimes. The best method is to create a new table from the table minus indexes. Use your WHERE clause on the new table. The create indexes and triggers on new table and drop the old table.
I will illustrate this with a small example. Consider a table called HISTORY it has around 100 million records. Only a few thousand records needs to be kept and the rest of the records need to be deleted. If a normal conditional delete is issued, this would kill your system. So this is now I do it:
1. Use a script and create the script of table HISTORY ( I do this using TOAD for Oracle)
2. Now we will create a new table TEST from old table HISTORY
SQL> Create table TEST as ( Select * from HISTORY);
3. Select count(*) from TEST;
# to check if no. of records same in new and test table)
4. Now use your conditional delete on the table TEST. Since no indexes or constraints are their on this table this would be so much quicker.
SQL> Delete from TEST where <column-name> != all ( Value1, Value2, Value3);
5. Drop old table (Take an export dump of this table before to be safe)
SQL> DROP TABLE HISTORY;
6. Rename TEST to new HISTORY
SQL> Alter table TEST rename to HISTORY;
7. Re-create indexes and triggers and constraints (or any more objects you have) on your new HISTORY table from the script created in step 1.
CREATE INDEX IX123 ON HISTORY;
CREATE OR REPLACE TRIGGER TRIG
BEFORE INSERT ON HISTORY
8. Now your new table is ready with all the unnecessary data cleaned. Much efficiently executed task.
P.S: Do this task at a time when there is less no. of transactions on your database. Weekend midnight is a time I choose personally to do it on a production system. And keep the application guys handy to check it from their application side.