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 - 14:52:55 CDT
