Need help with a slow pl/sql script

20 pts.
Tags:
Oracle 10
PL
PL/SQL
script
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following