SQL & PL/SQL

I have the below cursor that is running slow.

declare
cursor c1 is select po from zz_1;
cursor c2(ppo number) is select invnum,invdate from zz_2 where po=ppo;
begin
for i in c1 loop
for j in c2(i.po) loop
update zz_1 set invnum=j.invnum,invdate=j.invdate where po=i.po;
end loop;
end loop;
commit;
end;
/

Both tables zz_1 and zz_2 have about 100,000 records each and have index on column po. The above cursor takes about 75 minutes to complete.

I tried the below

update zz_1 a set a.invnum=(select b.invnum
from zz_2 b where b.po=a.po);
update zz_1 a set a.invdate=(select b.invdate
from zz_2 b where b.po=a.po);

But it did not help either.


Please help to fine tune/revise the script so that it runs faster and more efficiently.



Using Oracle9i Enterprise Edition Release 9.2.0.1.0