Home » RDBMS Server » Server Administration » urgent :: pl/sql block taking so long time
urgent :: pl/sql block taking so long time [message #62436] Wed, 21 July 2004 23:20 Go to next message
BhavinShah
Messages: 105
Registered: February 2004
Senior Member
hi.. friends,

i have made a pl.sql block for data updation .. but it will take so long time to execute... what should be the reason..for that..

Declare
/* curosr for locn kkl*/
cursor c1 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from kkljun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/* cursor for chn*/
cursor c2 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from chnjun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/* cursor for rjy*/
cursor c3 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from rjyjun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/* cursor for calcutta*/
cursor c4 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from caljun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/* cursor for sibsagar */
cursor c5 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from sibjun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/* cursor for agartalla */
cursor c6 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from agtjun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/*cursor for silchar */
cursor c7 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from siljun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/*cursor for dvp -jorhat */
cursor c8 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from dvpjun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/* cursor for gsd-jorhat */
cursor c9 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from gsdjun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/* cursor for hazira */
cursor c10 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from hazjun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/*cursor for ankleshwar */
cursor c11 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from ankjun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/*cursor for baroda--bda locn */
cursor c12 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from bdajun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/* cursor for cws(bdw) baroda */
cursor c13 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from bdwjun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/*cursor for ahmedabad */
cursor c14 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from amdjun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/* cursor for mehsana */
cursor c15 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from mhnjun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/*cursor for jodhpur */
cursor c16 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from jodjun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/*cursor for irs -ahmedabad*/
cursor c17 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from irsjun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/*cursor for bombay */
cursor c18 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from mumjun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/* cursor for cambay */
cursor c19 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from cbyjun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

/*cursor for dehradhun */
cursor c20 is select mm_matcode,sum(nvl(mm_qty_hand,0)) mm_qty_hand ,sum(nvl(mm_val_hand,0)) mm_val_hand
from ddnjun.mf_matrl_new
where substr(mm_matcode,1,2) <> '00'
and mm_qty_hand > 0
group by mm_matcode;

Begin

for a in c1 loop
update nonmoving.all_masters set qty_hand=qty_hand+a.mm_qty_hand,val_hand=val_hand+a.mm_val_hand where
matcode=a.mm_matcode;
end loop;
commit;

for b in c2 loop
update nonmoving.all_masters set qty_hand=qty_hand+b.mm_qty_hand,val_hand=val_hand+b.mm_val_hand where
matcode=b.mm_matcode;
end loop;
commit;

for c in c3 loop
update nonmoving.all_masters set qty_hand=qty_hand+c.mm_qty_hand,val_hand=val_hand+c.mm_val_hand where
matcode=c.mm_matcode;
end loop;
commit;

for d in c4 loop
update nonmoving.all_masters set qty_hand=qty_hand+d.mm_qty_hand,val_hand=val_hand+d.mm_val_hand where
matcode=d.mm_matcode;
end loop;
commit;

for e in c5 loop
update nonmoving.all_masters set qty_hand=qty_hand+e.mm_qty_hand,val_hand=val_hand+e.mm_val_hand where
matcode=e.mm_matcode;
end loop;
commit;

for f in c6 loop
update nonmoving.all_masters set qty_hand=qty_hand+f.mm_qty_hand,val_hand=val_hand+f.mm_val_hand where
matcode=f.mm_matcode;
end loop;
commit;

for g in c7 loop
update nonmoving.all_masters set qty_hand=qty_hand+g.mm_qty_hand,val_hand=val_hand+g.mm_val_hand where
matcode=g.mm_matcode;
end loop;
commit;

for h in c8 loop
update nonmoving.all_masters set qty_hand=qty_hand+h.mm_qty_hand,val_hand=val_hand+h.mm_val_hand where
matcode=h.mm_matcode;
end loop;
commit;

for i in c9 loop
update nonmoving.all_masters set qty_hand=qty_hand+i.mm_qty_hand,val_hand=val_hand+i.mm_val_hand where
matcode=i.mm_matcode;
end loop;
commit;

for j in c10 loop
update nonmoving.all_masters set qty_hand=qty_hand+j.mm_qty_hand,val_hand=val_hand+j.mm_val_hand where
matcode=j.mm_matcode;
end loop;
commit;

for k in c11 loop
update nonmoving.all_masters set qty_hand=qty_hand+k.mm_qty_hand,val_hand=val_hand+k.mm_val_hand where
matcode=k.mm_matcode;
end loop;
commit;

for l in c12 loop
update nonmoving.all_masters set qty_hand=qty_hand+l.mm_qty_hand,val_hand=val_hand+l.mm_val_hand where
matcode=l.mm_matcode;
end loop;
commit;

for m in c13 loop
update nonmoving.all_masters set qty_hand=qty_hand+m.mm_qty_hand,val_hand=val_hand+m.mm_val_hand where
matcode=m.mm_matcode;
end loop;
commit;

for n in c14 loop
update nonmoving.all_masters set qty_hand=qty_hand+n.mm_qty_hand,val_hand=val_hand+n.mm_val_hand where
matcode=n.mm_matcode;
end loop;
commit;

for o in c15 loop
update nonmoving.all_masters set qty_hand=qty_hand+o.mm_qty_hand,val_hand=val_hand+o.mm_val_hand where
matcode=o.mm_matcode;
end loop;
commit;

for p in c16 loop
update nonmoving.all_masters set qty_hand=qty_hand+p.mm_qty_hand,val_hand=val_hand+p.mm_val_hand where
matcode=p.mm_matcode;
end loop;
commit;

for q in c17 loop
update nonmoving.all_masters set qty_hand=qty_hand+q.mm_qty_hand,val_hand=val_hand+q.mm_val_hand where
matcode=q.mm_matcode;
end loop;
commit;

for r in c18 loop
update nonmoving.all_masters set qty_hand=qty_hand+r.mm_qty_hand,val_hand=val_hand+r.mm_val_hand where
matcode=r.mm_matcode;
end loop;
commit;

for s in c19 loop
update nonmoving.all_masters set qty_hand=qty_hand+s.mm_qty_hand,val_hand=val_hand+s.mm_val_hand where
matcode=s.mm_matcode;
end loop;
commit;

for t in c20 loop
update nonmoving.all_masters set qty_hand=qty_hand+t.mm_qty_hand,val_hand=val_hand+t.mm_val_hand where
matcode=t.mm_matcode;
end loop;
commit;

End;

here kkl,rjy .. all are location

thax ,
bhavin

Report message to a moderator

Re: urgent :: pl/sql block taking so long time [message #62444 is a reply to message #62436] Thu, 22 July 2004 03:27 Go to previous message
Oskar
Messages: 26
Registered: May 2002
Junior Member
where substr(mm_matcode,1,2) <> '00' prevents from using the index (i'm guessing here) on that column, besides, i'd avoid using cursors and try to update rows in one statement.
One last thing, disable any trigger fired while updating if possible to speed up the proccess.

Report message to a moderator

Previous Topic: system tablespace defragmentation
Next Topic: urgent: can i see rows_processed of pl/sql block
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jan 09 03:49:50 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.