0 Replies Latest reply on Sep 3, 2012 7:56 AM by 781553

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

      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.id = p.prid) and t.dt=rep.dt
      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