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
FROM NEW_TABLE
v_customers c_customers%ROWTYPE;
BEGIN
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;
END get_customer_address;
Received on Tue Jun 08 1999 - 21:40:56 CDT
