Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Speed up this code for me, please
I want to update NEW_TABLE with data from T1 and T2 (aliases)
There are over 50,000 records to update. The number may grow..
There are no indexes on the target table except on the primary key.
This procedure is too slow. Can you make it faster? Suggestions, please.
Here is the example code (the real procedure is much longer but uses
same
logic)
DECLARE
CURSOR c_customers is
SELECT rowid customer_rowid,
customer_number, address
OPEN c_customers; FETCH c_customers INTO v_customers; OPEN c_customers; FETCH c_customers INTO v_customers; WHILE c_customers%FOUND LOOP BEGIN SELECT CUST.address INTO v_customers.address FROM CUSTOMERS T1, ADDRESES T2 WHERE T1.customer_number = v_customers.customer_number and T1.cust_id = T2.cust_id; EXCEPTION WHEN NO_DATA_FOUND THEN v_customers.address := null; END; BEGIN UPDATE VZN.VZN_GL_TRANSACTIONS SET address = v_customers.address WHERE rowid = v_customers.customer_rowid; END; v_customers.address := NULL; FETCH c_customers INTO v_customers; END LOOP; CLOSE c_customers; COMMIT;
![]() |
![]() |