How to fast delete lot of data in a table.
Hi experts,
I have a table with around 650,000,000 rows and we need to delete about 60,000,000 rows at the end every month and same amount of rows accumulate throughout the month. The deletion usually takes overnight to delete. We are using 10r2 in IBM AIX. The procedure we are using to delete is:
declare
ln_count number:=0;
begin
for i in (select rowid from table1 where some_id<2012090000)
loop
delete from table1
where rowid=i.rowid;
if ln_count=10000 then
commit;
ln_count:=1;
end if;
ln_count:=ln_count+1;
end loop;
commit;
end;
When this procedure is started I mostly see that the session is busy in user i/o wait for db sequencial file read. Will using cursor instead will give better results. Any basic suggestions to make the deletion time lower.
Thanks