Hi All,
I have a Stored Proc which purges 3 tables. But the process is
extremely slow.
The proc is below, any help will be appreciated.
CREATE OR REPLACE PROCEDURE PROC_PURGE
IS
record_cnt NUMBER := 0;
BEGIN
FOR rec_cur IN (SELECT trn.TRANS_ID,
trn.CUST
FROM CUSTOMER_TRAN trn
WHERE TRUNC(gl.TRANS_DATE) < TRUNC(SYSDATE) - 14)
LOOP
DELETE FROM CUSTOMER_TRAN trn
WHERE trn.TRANS_ID = rec_cur.TRANS_ID
AND trn.CUST = rec_cur.CUST;
DELETE FROM CUSTOMER_ACCTS acct
WHERE acct.TRANS_ID = rec_cur.TRANS_ID;
DELETE FROM CUST_FUNDS fnd
WHERE fnd.TRANS_ID = rec_cur.TRANS_ID
AND fnd.CUST = rec_cur.CUST;
record_cnt := record_cnt + 3;
IF record_cnt >= 1000 THEN
COMMIT;
record_cnt := 0;
END IF;
END LOOP;
COMMIT;
DELETE FROM CUST_RESTORE rst
WHERE TRUNC(rst.REST_DATE) < TRUNC(SYSDATE) - 14;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-2
0001,'erro
r'||SQLCOD
E||SUBSTR(
SQLERRM,1,
100));
END;
Start Free Trial
