Home » SQL & PL/SQL » SQL & PL/SQL » Merge two table using PL/SQL
Merge two table using PL/SQL [message #36392] Sat, 24 November 2001 23:02 Go to next message
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 Go to previous message
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;

----------------------------------------------------------------------
Previous Topic: PLEASE HELP, URGENT
Next Topic: Regarding PL/SQL Arguments
Goto Forum:
  


Current Time: Fri Apr 03 06:09:45 CDT 2020