| Merge two table using PL/SQL [message #36392] |
Sat, 24 November 2001 23:02  |
christina
Messages: 9
Registered: November 2001
|
Junior Member |
|
|
All,
I want to speed up this 2 table join in pl/sql, what should I do? I have a bench mark to do it 7 minutes, but for me it took 3 hours.
table a (120K record), with field f1, f2, f3, f4, f5, f6,dd, cc, dd, gg, etc
table b (40K record), with field f1, f2, f3, f4, f5, f6
merge a with b, add 2 extra field from b to a.
some f1, f2 field in table is null or ' '
code like:
For r_kk in select f1, f2, f3, f4, f5, f6 from b
begin
update a set f5 = r_kk.f5, f6 = r_kk.f6
where f1 = r_kk.f1 and f2 = r_kk.f2 and f3 = r_kk.f3 and f4 = r_kk.f4;
commit;
end;
end loop;
--
Christina
----------------------------------------------------------------------
|
|
|
|
| Re: Merge two table using PL/SQL [message #36400 is a reply to message #36392] |
Sun, 25 November 2001 22:56  |
Rob Baillie
Messages: 33
Registered: November 2001
|
Member |
|
|
I am assuming, from your original code, that where f1 or f2 is NULL you do not wish the join to succeed:
You don't need the loop, unless you require the commit to be executed after every single update.
The following code does the same update as yours, with only the transaction handling changing...
BEGIN
--
UPDATE a
SET ( a.f5, a.f6 ) = ( SELECT b.f5, b.f6 FROM b where a.f1 = b.f1 AND a.f2 = b.f2 AND a.f3 = b.f3 AND a.f4 = b.f4 )
--
COMMIT;
--
END;
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
