As an application user you might have been asked to write a cleanup script to delete old data from a big table which is having millions of rows based on date or some fields. Being a DBA, application guys used to ask me any best way to delete huge amounts of rows from a huge table(partition/non-partition) as fast as possible.
Depends on the table size, if you will attempt to delete the entire row at one time then it will generate huge undo and will degrade the database performance and makes the database unresponsive. So better don’t fire any delete statement on big tables otherwise query will stuck and will take lots of time to come out.
There are two way to purge older records from a huge table:
- Delete records in Batches for example per 10000 records.
- Create new table and Insert required number of rows from the main table.
PL/SQL Script to Delete Rows from a Table in Batches
sqlplus test/test <> /data/orahowscript/delete/delete_orahow_status_4.log set timing on; declare cursor ph_unq is select ID from ORAHOW where status =3 and trunc(modified_Date)<trunc(sysdate -60); TYPE ph is TABLE of phonenumber_bkp_ID%rowtype index by binary_integer; ph_un ph; begin open ph_unq; LOOP fetch ph_unq BULK COLLECT INTO ph_un limit 10000; exit when ph_un.count=0; FORALL ind in 1 .. ph_un.count delete from orahow where id = ph_un(ind).ID; commit; END LOOP; END; / EOD
Method 2: Create new table by Selecting rows from main table
You can create new table and insert required rows from the main table.
STEP 1: Create new table and inset the required rows:
SQL> create table new_table_name as select * from source_tab where status =4 and trunc(modified_Date)<trunc(sysdate -60);
STEP 2: Rename the tables:
SQL> rename source_tab to bkp_mytable; SQL> rename new_table_name to source_tab;