Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Very long query

Re: Very long query

From: TS - Toni Suhartono <toni_s_at_my-dejanews.com>
Date: Fri, 11 Dec 1998 12:00:30 +0700
Message-ID: <3670A6ED.BF05772F@my-dejanews.com>


I meant, put"commit;" after delete statement

TS - Toni Suhartono wrote:

> 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 - 23:00:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US