Re: How to tune this stored proc?

Partitioning by date seems best since you appear to age them off by date and likely insert by date as well. Also, if you don't care about the time of day, you may want to trunc() the date when you insert/update the column. Fewer keys on the index.

In article <7u4qfn$elb$1_at_autumn.news.rcn.net>,   "Jerry Gitomer" <jgitomer_at_hbsrx.com> wrote:
> 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;
> >
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Oct 17 1999 - 20:43:23 CDT