| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Help fast update
A copy of this was sent to bs260_at_FreeNet.Carleton.CA (Paschal Mushubi)
(if that email address didn't require changing)
On 9 Jun 1999 00:12:03 GMT, you wrote:
>
>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;
>
why not just:
update new_table
set address = ( select t2.address
from customers t1, addresses t2
where t1.customer_number = new_table.customer_number
and t1.cust_id = t2.cust_id );
in straight SQL? It does the same as the above code. You would want indexes on T1.customer_number and t2.cust_id for this to perform. If there might exist rows in NEW_TABLE that will not have a mate in the T1 table, and you don't want to NULL out the address, you would code:
update new_table
set address = ( select t2.address
from customers t1, addresses t2
where t1.customer_number = new_table.customer_number
and t1.cust_id = t2.cust_id )
where exists ( select NULL
from customers t1, addresses t2
where t1.customer_number = new_table.customer_number
and t1.cust_id = t2.cust_id );
(assuming vzn.vzn_gl_transactions is really NEW_TABLE in the above. Also -- assuming the alias CUST in the select of the address was really supposed to be T2 -- also assuming the double open and fetch of c_customers was a copy error)
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
Current article is "Fine Grained Access Control", added June 8'th
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA--
 |
 |