Faster way to Delete Lakhs of Rows from a Oracle Table

Deleting lots of rows can be slow. And there’s a chance it’ll take even longer because another session has locked the data you want to remove. We can do this by changing DML statments to DDL statements. This method is known as Remove Rows with Create-Table-as-Select. There are two different approaches for this method

Method 1

Create a new table saving the rows you want to keep
Truncate the original table
Load the saved rows back in with insert as select

For example:

create table rows_to_keep select * from massive_table where  save_these = 'Y';
truncate table massive_table;
insert into massive_table select * from rows_to_keep;

Inserting rows in a table is faster than deleting them. Loading data into a new table using create-table-as-select (CTAS) is faster still.So if you’re removing most of the rows from a table, instead of issuing a delete you use the method described above.

Method 2:

The process for this is similar:

Create a new table saving the rows you want to keep
Drop or rename the original table
Rename the new table to the original

for example:

create table rows_to_keep select * from massive_table where  save_these = 'Y';

rename massive_table to massive_archived;

rename rows_to_keep to massive_table;

This only loads the data once. So can be even faster than using truncate + insert to swap the rows over as in the previous method.

To complete the switch in both methods you also need to copy any indexes, constraints, grants, etc. from the old table to the new.

Note:We need downtime to perform these operations

Aron Anthonisamy