Need help with a slow pl/sql script
Hello. I am running this script and it is taking over 3 hours on Oracle 10. Can anyone help.
----------------------------------
spool noaddressusagerelfix.LOG
set timing on;
SET SERVEROUT ON;
DECLARE
vpartyCustomerNo TableB.I_OCPRTY_USAGES%TYPE;
vI_OCPOSTAD_USAGES TableB.I_OCPOSTAD_USAGES%TYPE;
vC_OCPOSTAD_USAGES TableB.C_OCPOSTAD_USAGES%TYPE;
vpartyAddressUsageIValue TableB.I%TYPE;
vpartyAddressUsageCValue TableB.C%TYPE;
vCounter NUMBER(10):=0;
CURSOR cursor1 IS
SELECT C, I, I_OCPRTY_USAGES FROM TableB
WHERE I_OCPOSTAD_USAGES IS NULL
AND C_OCPOSTAD_USAGES IS NULL;
CURSOR cursor2 IS
SELECT I_OCPOSTAD_USAGES, C_OCPOSTAD_USAGES FROM TableA
WHERE I_OCPRTY_USAGES = vpartyCustomerNo
AND I_OCPOSTAD_USAGES IS NOT NULL
AND C_OCPOSTAD_USAGES IS NOT NULL
AND USAGE IN(1088000,1088001)
AND ROWNUM = 1;
BEGIN
DBMS_OUTPUT.PUT_LINE('---- Start ----');
OPEN cursor1;
LOOP
FETCH cursor1 INTO vpartyAddressUsageCValue, vpartyAddressUsageIValue,vpartyCustomerNo;
EXIT WHEN cursor1%NOTFOUND;
OPEN cursor2;
LOOP
FETCH cursor2 INTO vI_OCPOSTAD_USAGES,vC_OCPOSTAD_USAGES;
EXIT WHEN cursor2%NOTFOUND;
/* Update TableB set the usages */
UPDATE TableB SET I_OCPOSTAD_USAGES = vI_OCPOSTAD_USAGES,
C_OCPOSTAD_USAGES = vC_OCPOSTAD_USAGES
WHERE I = vpartyAddressUsageIValue
AND C = vpartyAddressUsageCValue
AND I_OCPRTY_USAGES = vpartyCustomerNo;
vCounter := vCounter + 1;
END LOOP;
CLOSE cursor2;
END LOOP;
CLOSE cursor1;
DBMS_OUTPUT.PUT_LINE('---- NO. OF RECORDS CHANGED ----');
DBMS_OUTPUT.PUT_LINE(vCounter);
DBMS_OUTPUT.PUT_LINE('--------------------------------');
COMMIT;
DBMS_OUTPUT.PUT_LINE('---- END ----');
END;
/
set timing off
spool off
-------------------------------------
Thanks in advance
Software/Hardware used:
Software/Hardware used:
ASKED:
May 23, 2008 11:00 AM
UPDATED:
June 17, 2008 10:14 PM
Answer Wiki:
Hello Darrankavanagh
I would suggest the following:
- Since cursor2 will allways return only 1 row, there is no need to use acursor. It would be better to use a 'select' .
- It would be better to use a cursor for loop for cursor1. If you define it as 'for update' you can take advantage of the 'where current of" condition to update the rows.
For example:
<pre>DECLARE
vI_OCPOSTAD_USAGES TableB.I_OCPOSTAD_USAGES%TYPE;
vC_OCPOSTAD_USAGES TableB.C_OCPOSTAD_USAGES%TYPE;
vCounter NUMBER(10):=0;
CURSOR cursor1 IS
SELECT C, I, I_OCPRTY_USAGES FROM TableB
WHERE I_OCPOSTAD_USAGES IS NULL
AND C_OCPOSTAD_USAGES IS NULL
FOR UPDATE OF I_OCPOSTAD_USAGES;
BEGIN
DBMS_OUTPUT.PUT_LINE('---- Start ----');
for i in cursor1 LOOP
SELECT I_OCPOSTAD_USAGES, C_OCPOSTAD_USAGES into vI_OCPOSTAD_USAGES,vC_OCPOSTAD_USAGES
FROM TableA
WHERE I_OCPRTY_USAGES = I.I_OCPRTY_USAGES
AND I_OCPOSTAD_USAGES IS NOT NULL
AND C_OCPOSTAD_USAGES IS NOT NULL
AND USAGE IN(1088000,1088001)
AND ROWNUM = 1;
/* Update TableB set the usages */
UPDATE TableB SET I_OCPOSTAD_USAGES = vI_OCPOSTAD_USAGES, C_OCPOSTAD_USAGES = vC_OCPOSTAD_USAGES
WHERE current of cursor1;
vCounter := vCounter + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('---- NO. OF RECORDS CHANGED ----');
DBMS_OUTPUT.PUT_LINE(vCounter);
DBMS_OUTPUT.PUT_LINE('--------------------------------');
COMMIT;
DBMS_OUTPUT.PUT_LINE('---- END ----');
END;</pre>
Regards,
-cadlg
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
