Re: How to tune this stored proc?

If we may assume this is run once per day? Is it batch mode when no one is on.
If so -- why not have large rollback to set online and do all at once, if not -- perhaps at least one larger rollback and "set transaction" statement

Using rownum -- oracle still continues looking at records even after the rownum is exceeded ... Thus you are performing too much work.

Jerry Gitomer <jgitomer_at_hbsrx.com> wrote in message news:7u4qfn$elb$1_at_autumn.news.rcn.net...
> Hi,
>
> Have you investigated partitiioning by date?
>
> regards
> jerry gitomer
>
> Lee Ming Fai wrote in message
> <3803846C.AE7D0296_at_pacific.net.hk>...
> >We have a table which contain about 4.5M records and need to
> purge about
> >580000 records each day by transaction date.
> >The problem is that we found it takes too long, more than 4
> hours to
> >delete these records. The following is the stored proc we use,
> is there
> >any better method or how to tune this stored proc?
> >
> >
> >PROCEDURE SP_PurgeTxn (p_date IN DATE)
> >IS
> >BEGIN
> > LOOP
> > DELETE FROM detail_txn dt
> > WHERE tran_date = p_date
> > AND rownum <= 4000;
> > IF SQL%NOTFOUND THEN
> > EXIT;
> > ELSE
> > COMMIT;
> > END IF;
> > END LOOP;
> > COMMIT;
> >END;
> >
> >
>
>
Received on Fri Oct 15 1999 - 17:10:11 CDT