Re: How to tune this stored proc?

no, it's not indexed!

Sybrand Bakker wrote:

> Is trandate indexed? You are purging about 10 percent of the table. This
> would urge for forcing a full table scan to be used in the delete.
>
> Hth
> --
> Sybrand Bakker, Oracle DBA
> Lee Ming Fai <leemingf_at_pacific.net.hk> wrote in message
> news: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 Tue Oct 12 1999 - 22:17:08 CDT