How to tune this stored proc?

From: Lee Ming Fai <leemingf_at_pacific.net.hk>
Date: Wed, 13 Oct 1999 02:56:44 +0800
Message-ID: <3803846C.AE7D0296@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 - 13:56:44 CDT