Re: How to tune this stored proc?

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 Thu Oct 14 1999 - 09:42:31 CDT