Re: Very long query

replace
 "exit ...." with "if sql%rowcount=0 then exit; end if;" if problem resist then put "commit;" before exit

Zachary Agatstein wrote:

> 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 Thu Dec 10 1998 - 22:56:27 CST