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
Hi
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


update
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.id = p.prid) and t.dt=rep.dt
and
exists (select 1 from mst m where hrlevel=6 START WITH m.hrid=r.id CONNECT BY PRIOR m.id = m.hrid))

its taking time to update need to improve performance

Legend

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