PL/SQL script to delete millions of rows from a table based on date

[rthota211]

HI All

I am very new to the database stuff. Correct me if I am wrong.

Is there a possiblity to write a script in oracle which deletes the rows from a table based on the age. i.e., I want to delete the rows which are >60 days. I have a table with millions of rows in it and I want to keep only the latest two months rows. I have the following table with column names as
ERROR_IND
FEED_ID
QUEUE_ID
FEED_DT_TM -date datatype .

I have written few of the scripts to implement it.

1.DELETE FROM table_name WHERE sysdate - feed_dt_tm > 60;

2. declare
begin
create table hif_psfeed1
as
select *
from hif_psfeed
where feed_dt_tm > (sysdate-60);

drop table hif_psfeed;
alter table hif_psfeed1 rename to hif_psfeed;
end;
/


3. declare
job_delete in number;
begin
sys.dbms_job.submit
(job=>job_delete,
what=>'begin delete from hif_psfeed WHERE sysdate - feed_dt_tm >''60''; end;',
next_date=>trunc(sysdate)+1,
interval=>'trunc(sysdate)+1'
);
commit;
end;
/


The first one and second seems to take long time and I am getting errors while running the 3rd one. Please advise me on this. I need to keep running this script everyday to keep only the latest 2 months of data in the table

Thanks In advance

[PAVB]

How many rows in the table?
How many rows are expected to be deleted?
Is this a one off process or are you planning to run it in a montly basis or so?

[rthota211]

@PAVB: There 19 million rows in the table. If I consider only the latest two months , it will be around 100,000 rows. The remaining rows are expected to be deleted. Yes, I am plannning it to perform weekly or monthly basis.

[Naeem]

Look at this site It gives lot of information about this problem
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2345591157689

[PAVB]

I would suggest to do reverse purging at least for the first run where about 18.9 million rows have to be "deleted" out of a total of 19 million.

In this context a version of Option #2 is the way to go.

I would...
1- Pre Create hif_psfeed1 as nologging, with no indexes
2- Insert into it the 100,000 targetted-to-stay-alive rows probably using /*+ append */ hint.
3- Rename table hif_psfeed as hif_psfeed_old and keep it alive for a couple of days (you never know).
4- Rename table hif_psfeed1 as hif_psfeed.
5- Create all needed indexes and fix referential integrity if needed.
6- Alter table to logging.
7- Gather fresh stats on table and related indexes.