Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Very long query
Hi,
I have a problem deleting stale records from a large table. If I try to delete a day's worth of records (every night: delete from ... where ... < sysdate-7;), the query runs forever.
If, on the other hand, I limit my deletion to only, say a 1000 records:
delete... where rownum < 1001;
I have no problem with this one.
So, I tried to create a PL/SQL procedure, like this:
/*******************************************/
declare
dateval DATE;
begin
select sysdate-&1 into dateval from dual;
loop
delete from traffic_log
where report_date < dateval
and rownum < 1001;
exit when SQL%NOTFOUND;
end loop;
end;
/
commit;
/******************************************/
Unfortunately, this also runs forever.
I have fairly large rollback segments (four of them, about 136 MB each). Why if I can successfully delete 1000 records, I cannot consecutively delete all of them in 1000-record chunks?
Thanks
Zach Agatstein Received on Wed Dec 09 1998 - 10:40:08 CST