This discussion is archived
0 Replies Latest reply: Sep 3, 2012 12:56 AM by 781553 RSS

how to optimize the update statement of sub query retriving from hierarchy

781553 Newbie
Currently Being Moderated
i have
table structure like

table column1column2column3 column4
rep id dt trnqty srnqty
mst id hrid hrlevel
prd id prid prdlevel
trn hid pid dt Qty
srn hid pid dt qty

need to update rep table ,values retrived in hrierachy

rep set trnqty=(select sum(qty) from trn t where exists (select 1 from prd p where prdlevel=6
START WITH p.prid = 1
CONNECT BY PRIOR = p.prid) and t.dt=rep.dt
exists (select 1 from mst m where hrlevel=6 START WITH CONNECT BY PRIOR = m.hrid))

its taking time to update need to improve performance


  • Correct Answers - 10 points
  • Helpful Answers - 5 points