truncate table with large number of data

[Roshan]

Oracle Database 11.2.0.3.0

SunOS

 

Hi,

 

I have a table with 17 million records. I want to delete records (around 900,000) from the table where date is March 2014

 

The issue is it takes lots of time to scan the table and delete. 1 record takes around 5 minutes to complete.

 

how can I increase the rate to delete records?

 

Regards,

 
Roshann

[ErmanArslansOracleBlog]

There are 3 ways that come to my mind;

1)create a table using where clause.. Do an unrecoverable operation for faster execution. Drop the old table, rename the new table.

create table newtable unrecoverable as select * from your_table where date != March214
drop table yourtable;
rename newable to yourtable;
create indexes if there are any.

2)partition your table and then delete. This will parallelize the delete operation.

3)partition your table in a way, so that you can do a drop a partition instead of doing a delete operation.

Reference: Asktom.