Question

  Asked: May 23 2008   11:00 AM GMT
  Asked by: Darrankavanagh


Need help with a slow pl/sql script


PL, SQL, script, Oracle 10, PL/SQL

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

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window

Answer Wiki (Improve, edit or add to this answer)


 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0



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:

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;


Regards,

-cadlg
  • AddThis Social Bookmark Button

Browse more Questions and Answers on Development and Oracle.

Looking for relevant Development Whitepapers? Visit the SearchWinDevelopment.com Research Library.


Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register