Most Effective Way to Delete Huge Records from a Table in Oracle

December 18, 2018

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.

How to Gather Statistics on Large Partitioned Tables in Oracle

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. 

delete huge records from a big table

There are two way to purge older records from a huge table:

  1.  Delete records in Batches for example per 10000 records.
  2. 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;

Must Read: