0 Replies Latest reply: Sep 3, 2012 2:56 AM by 781553 RSS

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

    781553
      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