Hi,
I am doing a DML action on a table. below are the steps used to perform the action.
There are two tables IB_SUPPORT_CONTRACTS_MV which has 174435 records and RENEWAL_ASSETS_TEMPS is a Temporary table
Step 1: Created a Procedure ( I am inserting all the row from IB_SUPPORT_CONTRACTS_MV and Inserting NULL records into below columns )
create or replace PROCEDURE renewal_asset_one AS
CURSOR s_cur
IS
SELECT
SERIAL_NUMBER,
NULL SERVICE_START_DATE,
NULL SERVICE_END_DATE,
NULL IB_CREATION_DATE,
NULL AS MIN_SERVICE_START_DATE,
NULL AS MAX_SERVICE_END_DATE,
NULL AS MIN_IB_CREATION_DATE,
NULL CONTRACT_NUMBER,
ITEM_DESCRIPTION PRODUCT_ID
FROM IB_SUPPORT_CONTRACTS_MV
WHERE
LENGTH(SERIAL_NUMBER) > 10 ;
TYPE fetch_array
IS
TABLE OF s_cur%ROWTYPE INDEX BY PLS_INTEGER;
s_array fetch_array;
BEGIN
DELETE FROM RENEWAL_ASSETS_TEMPS;
COMMIT;
OPEN s_cur;
FETCH s_cur BULK COLLECT INTO s_array;
FORALL i IN 1..s_array.COUNT
INSERT INTO RENEWAL_ASSETS_TEMPS VALUES s_array
(i
);
CLOSE s_cur;
COMMIT;
RENEWAL_ASSET_TWO();
RENEWAL_ASSET_THREE();
END renewal_asset_one;
Step 2: I am updating table RENEWAL_ASSETS_TEMPS using BULK collect (This procedure is taking lot of time to update)
create or replace PROCEDURE RENEWAL_ASSET_TWO AS
CURSOR s_cur
IS
SELECT
SERIAL_NUMBER,
MIN(SERVICE_START_DATE) AS MIN_SERVICE_START_DATE,
MAX(SERVICE_END_DATE) AS MAX_SERVICE_END_DATE,
MIN(IB_CREATION_DATE) AS MIN_IB_CREATION_DATE
FROM IB_SUPPORT_CONTRACTS_MV
WHERE
LENGTH(SERIAL_NUMBER) > 10
GROUP BY SERIAL_NUMBER ;
TYPE fetch_array
IS
TABLE OF s_cur%ROWTYPE INDEX BY PLS_INTEGER;
s_array fetch_array;
BEGIN
OPEN s_cur;
FETCH s_cur BULK COLLECT INTO s_array;
FORALL i IN 1..s_array.COUNT
UPDATE RENEWAL_ASSETS_TEMPS
SET
MIN_SERVICE_START_DATE = s_array(i).MIN_SERVICE_START_DATE,
MAX_SERVICE_END_DATE = s_array(i).MAX_SERVICE_END_DATE,
MIN_IB_CREATION_DATE = s_array(i).MIN_IB_CREATION_DATE
WHERE
SERIAL_NUMBER = s_array(i).SERIAL_NUMBER;
CLOSE s_cur;
COMMIT;
END;
Step 3: I am doing a final update on same table RENEWAL_ASSETS_TEMPS ( Even this Procedure is taking lot of time to update)
create or replace PROCEDURE RENEWAL_ASSET_THREE AS
CURSOR u_crr
IS
SELECT
IBV.SERIAL_NUMBER,
RA.MAX_SERVICE_END_DATE,
IBV.CONTRACT_NUMBER
FROM
RENEWAL_ASSETS_TEMPS RA,
IB_SUPPORT_CONTRACTS_MV IBV
WHERE
LENGTH(IBV.SERIAL_NUMBER) > 10 AND
RA.SERIAL_NUMBER = IBV.SERIAL_NUMBER AND
RA.MAX_SERVICE_END_DATE = IBV.SERVICE_END_DATE;
TYPE s_curr
IS
TABLE OF u_crr%ROWTYPE INDEX BY PLS_INTEGER;
up_crr s_curr;
BEGIN
OPEN u_crr;
FETCH u_crr BULK COLLECT INTO up_crr ;
FORALL i IN 1..up_crr.count
UPDATE RENEWAL_ASSETS_TEMPS
SET CONTRACT_NUMBER = up_crr(i).CONTRACT_NUMBER
WHERE SERIAL_NUMBER = up_crr(i).SERIAL_NUMBER
AND MAX_SERVICE_END_DATE = up_crr(i).MAX_SERVICE_END_DATE;
CLOSE u_crr;
COMMIT;
END;
Please suggest me how to improve performance to update the procedure.
Thanks
Sudhir