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 - 15:11:12 CST