1 Reply Latest reply: Apr 19, 2014 4:09 AM by Etbin RSS

    rewrite the for Performance tunning

    959406

      in this query fetch the data takes long time how to rewrite the sql and improve the performance

       

       

       

      SELECT
      f.SROW,ROWNUM,
      E.RINTID,CINTID,
      C.SINTID,SRTS
      PC,
      TO_CHAR(C.SRDTS,'YYYY-MM-DD'),
      C.UORGSYNM,C.USTXT,
      C.CNTP,C.CNSUBTP,
      C.SUBPUI,
      E.RPARTUI,
      E.CPARTUI
      TRUNC(C.EDIF1),TRUNC(C.DIF2),TRUNC(C.STMNDT1),
      TRUNC(C.STMNDT2),C.NOTCD1,C.AM1
      C.NOTCD2,C.AM2,C.USAM1,C.PFRQCD1,
      C.PMCD1,C.PFRQCD2,C.PMCD2,
      C.OPT,E.RPDIR,C.UNDERNM1,
      C.UNDERNM2,C.OPTEDT,NULL,NULL,C.LEGAL,C.SUBID
      FROM RTRANSTB1 A left outer join Rtransend b
      on a.tid=b.tid
      rsubtb1 c Left outer join rsubend d
      on c.sid=d.sid
      rsubdtb1 e rcutoff f

      where
      a.tid=c.tid
      And c.sid=e.sid
      and f.tcut>=a.stime
      and f.tcut< nvl(b.time, pchigh)
      and f.tcut>=c.stime
      and f.tcut< nvl(d.time, pchigh)
      and a.srparty=0
      and(e.rpartf1='N' or e.partf1='N')
      and ( ( c.csub='ssss'
                 and c.optexp>f.tsod )
           or(c.csubtp='fff' and c.edt1> f.tsod)
           or ( c.csubtp not in('ssss', 'fff')
                and c.stmndt1>f.tsod
                or c.stmndt2> f.tsod )))
      and c.orgsrcvts=(select max(a.orgsrcvts) from rtransdtb1 a left outer join rtransend b
      a.dimid=b.endid
      rsubdimtb1 c left outer join rsubdimend d
      c.sid=d.tid
      rsubdimtbl
      where a.dimid=b.endid
      and c.subid=d.subid
      and f.tcut>=a.stime
      and f.tcut< nvl(b.time, pchigh)
      and f.tcut>=c.stime
      and f.tcut< nvl(d.time, pchigh)
      and a.srparty=0
      and a.uorignm=c.orignm
      and a.ustxt=c.ustxt
      and c.msgtp=c.msgtp
      c.trantp=c.trantp
      e.rintid=e.rintid)
      and not exists (
      select 1 from rtrandtbl left outer join rtransend
      on a.dimid=b.endid
      rsubdimtb1 c left outer join rsubdimend d
      c.sid=d.tid
      where a.dimid=b.endid
      and c.subid=d.subid
      and f.tcut>=a.stime
      and f.tcut< nvl(b.time, pchigh)
      and f.tcut>=c.stime
      and f.tcut< nvl(d.time, pchigh)
      and a.srparty=0
      and a.uorignm=c.orignm
      and a.ustxt=c.ustxt
      and a.tsrcprty>0)
      union all
      SELECT
      f.SROW,ROWNUM,
      E.RINTID,CINTID,
      C.SINTID,SRTS
      PC,
      TO_CHAR(C.SRDTS,'YYYY-MM-DD'),
      C.UORGSYNM,C.USTXT,
      C.CNTP,C.CNSUBTP,
      C.SUBPUI,
      E.RPARTUI,
      E.CPARTUI
      TRUNC(C.EDIF1),TRUNC(C.DIF2),TRUNC(C.STMNDT1),
      TRUNC(C.STMNDT2),C.NOTCD1,C.AM1
      C.NOTCD2,C.AM2,C.USAM1,C.PFRQCD1,
      C.PMCD1,C.PFRQCD2,C.PMCD2,
      C.OPT,case when e.rpdir='buyer
               then 'seller'
      WHEN E.rpdir='seller'
                then 'buyer'
      else  e.rpdir
      End as direction,C.UNDERNM1,
      C.UNDERNM2,C.OPTEDT,NULL,NULL,C.LEGAL,C.SUBID
      FROM RTRANSTB1 A left outer join Rtransend b
      on a.tid=b.tid
      rsubtb1 c Left outer join rsubend d
      on c.sid=d.sid
      rsubdtb1 e rcutoff f

      where
      a.tid=c.tid
      And c.sid=e.sid
      and f.tcut>=a.stime
      and f.tcut< nvl(b.time, pchigh)
      and f.tcut>=c.stime
      and f.tcut< nvl(d.time, pchigh)
      and a.srparty=0
      and(e.rpartf1='N' or e.partf1='N')
      and ( ( c.csub='ssss'
                 and c.optexp>f.tsod )
           or(c.csubtp='fff' and c.edt1> f.tsod)
           or ( c.csubtp not in('ssss', 'fff')
                and c.stmndt1>f.tsod
                or c.stmndt2> f.tsod )))
      and c.orgsrcvts=(select max(a.orgsrcvts) from rtransdtb1 a left outer join rtransend b
      a.dimid=b.endid
      rsubdimtb1 c left outer join rsubdimend d
      c.sid=d.tid
      rsubdimtbl
      where a.dimid=b.endid
      and c.subid=d.subid
      and f.tcut>=a.stime
      and f.tcut< nvl(b.time, pchigh)
      and f.tcut>=c.stime
      and f.tcut< nvl(d.time, pchigh)
      and a.srparty=0
      and a.uorignm=c.orignm
      and a.ustxt=c.ustxt
      and c.msgtp=c.msgtp
      c.trantp=c.trantp
      e.rintid=e.rintid)
      and not exists (
      select 1 from rtrandtbl left outer join rtransend
      on a.dimid=b.endid
      rsubdimtb1 c left outer join rsubdimend d
      c.sid=d.tid
      where a.dimid=b.endid
      and c.subid=d.subid
      and f.tcut>=a.stime
      and f.tcut< nvl(b.time, pchigh)
      and f.tcut>=c.stime
      and f.tcut< nvl(d.time, pchigh)
      and a.srparty=0
      and a.uorignm=c.orignm
      and a.ustxt=c.ustxt
      and a.tsrcprty>0)

      order by  part;

        • 1. Re: rewrite the for Performance tunning
          Etbin

          After reformatting to reveal patterns ( and arbitrarily fixing what appear to be typos and some unnecessary true conditions anded) the parts in red seem to be the only difference between unioned sets


          select f.srow,

                 rownum,

                 e.rintid,

                 cintid, -- alias missing

                 c.sintid,

                 srts,-- alias missing

                 pc,-- alias missing

                 to_char(c.srdts,'yyyy-mm-dd'),

                 c.uorgsynm,

                 c.ustxt,

                 c.cntp,

                 cnsubtp,-- alias missing should be c. judging from as below

                 c.subpui,

                 e.rpartui,

                 e.cpartui, -- comma added

                 trunc(c.edif1),

                 trunc(c.dif2),

                 trunc(c.stmndt1),

                 trunc(c.stmndt2),

                 c.notcd1,

                 c.am1, -- comma added

                 c.notcd2,

                 c.am2,

                 c.usam1,

                 c.pfrqcd1,

                 c.pmcd1,

                 c.pfrqcd2,

                 c.pmcd2,

                 c.opt,

                 e.rpdir,

                 c.undernm1,

                 c.undernm2,

                 c.optedt,

                 null null_1, -- no row name

                 null null_2, -- no row name

                 c.legal,

                 c.subid

            from rtranstb1 a

                 left outer join

                 rtransend b

              on a.tid = b.tid

                 rsubtb1 c

                 left outer join

                 rsubend d

              on c.sid = d.sid

                 rsubdtb1 e

                 rcutoff f

          where a.tid = c.tid

             and c.sid = e.sid

             and f.tcut >= a.stime

             and f.tcut < nvl(b.time,pchigh)

             and f.tcut >= c.stime

             and f.tcut < nvl(d.time,pchigh)

             and a.srparty = 0

             and (e.rpartf1 = 'N'

              or  e.partf1 = 'N'

                 )

             and (

                  ( c.csub = 'ssss'

             and    c.optexp > f.tsod

                  )

              or  (c.csubtp = 'fff'

             and   c.edt1 > f.tsod

                  )

              or  (c.csubtp not in ('ssss','fff')

             and   c.stmndt1 > f.tsod

              or   c.stmndt2 > f.tsod

                  )

                 ) -- ) unmatched parenthesis

             and c.orgsrcvts = (select max(a.orgsrcvts)

                                  from rtransdtb1 a

                                       left outer join

                                       rtransend b

                                    on a.dimid = b.endid

                                       rsubdimtb1 c

                                       left outer join

                                       rsubdimend d

                                    on c.sid = d.tid

                                       rsubdimtbl -- alias missing f assumed

                                 where a.dimid = b.endid

                                   and c.subid = d.subid

                                   and f.tcut >= a.stime

                                   and f.tcut < nvl(b.time,pchigh)

                                   and f.tcut >= c.stime

                                   and f.tcut < nvl(d.time,pchigh)

                                   and a.srparty = 0

                                   and a.uorignm = c.orignm

                                   and a.ustxt = c.ustxt

                                   and c.msgtp = c.msgtp

                                   and c.trantp = c.trantp -- added

                                   and e.rintid = e.rintid -- added

                               )

            and not exists(select 1

                             from rtrandtbl

                                  left outer join

                                  rtransend

                               on a.dimid = b.endid

                                  rsubdimtb1 c

                                  left outer join

                                  rsubdimend d

                               on c.sid = d.tid

                            where a.dimid = b.endid

                              and c.subid = d.subid

                              and f.tcut >= a.stime

                              and f.tcut < nvl(b.time,pchigh)

                              and f.tcut >= c.stime

                              and f.tcut < nvl(d.time,pchigh)

                              and a.srparty = 0

                              and a.uorignm = c.orignm

                              and a.ustxt = c.ustxt

                              and a.tsrcprty > 0

                          )

          union all

          select f.srow,

                 rownum,

                 e.rintid,

                 cintid,-- alias missing

                 c.sintid,

                 srts,-- alias missing

                 pc,-- alias missing

                 to_char(c.srdts,'yyyy-mm-dd'),

                 c.uorgsynm,

                 c.ustxt,

                 c.cntp,

                 c.cnsubtp,

                 c.subpui,

                 e.rpartui,

                 e.cpartui, -- comma added

                 trunc(c.edif1),

                 trunc(c.dif2),

                 trunc(c.stmndt1),

                 trunc(c.stmndt2),

                 c.notcd1,

                 c.am1, -- comma added

                 c.notcd2,

                 c.am2,

                 c.usam1,

                 c.pfrqcd1,

                 c.pmcd1,

                 c.pfrqcd2,

                 c.pmcd2,

                 c.opt,

                 case when e.rpdir = 'buyer

                      then 'seller'

                      when e.rpdir = 'seller'

                      then 'buyer'

                      else e.rpdir

                 end as direction,

                 c.undernm1,

                 c.undernm2,

                 c.optedt,

                 null,

                 null,

                 c.legal,

                 c.subid

            from rtranstb1 a

                 left outer join

                 rtransend b

              on a.tid = b.tid

                 rsubtb1 c

                 left outer join

                 rsubend d

              on c.sid = d.sid

                 rsubdtb1 e

                 rcutoff f

          where a.tid = c.tid

             and c.sid = e.sid

             and f.tcut >= a.stime

             and f.tcut < nvl(b.time,pchigh)

             and f.tcut >= c.stime

             and f.tcut < nvl(d.time,pchigh)

             and a.srparty = 0

             and (e.rpartf1 = 'N'

              or  e.partf1 = 'N'

                 )

             and (

                  (c.csub = 'ssss'

             and   c.optexp > f.tsod

                  )

              or  (c.csubtp = 'fff'

             and   c.edt1 > f.tsod

                  )

              or  (c.csubtp not in ('ssss','fff')

             and   c.stmndt1 > f.tsod

              or   c.stmndt2 > f.tsod

                  )

                 ) -- ) unmatched parenthesis

             and c.orgsrcvts = (select max(a.orgsrcvts)

                                  from rtransdtb1 a

                                       left outer join

                                       rtransend b

                                    on a.dimid = b.endid

                                       rsubdimtb1 c

                                       left outer join

                                       rsubdimend d

                                    on c.sid = d.tid

                                       rsubdimtbl -- alias missing f assumed

                                 where a.dimid = b.endid

                                   and c.subid = d.subid

                                   and f.tcut >= a.stime

                                   and f.tcut < nvl(b.time,pchigh)

                                   and f.tcut >= c.stime

                                   and f.tcut < nvl(d.time,pchigh)

                                   and a.srparty = 0

                                   and a.uorignm = c.orignm

                                   and a.ustxt = c.ustxt

                                   and c.msgtp = c.msgtp

                                   and c.trantp = c.trantp -- added

                                   and e.rintid = e.rintid -- added

                               )

             and not exists(select 1

                              from rtrandtbl a

                                   left outer join

                                   rtransend b

                                on a.dimid = b.endid

                                   rsubdimtb1 c

                                   left outer join

                                   rsubdimend d

                                on c.sid = d.tid

                             where a.dimid = b.endid

                               and c.subid = d.subid

                               and f.tcut >= a.stime

                               and f.tcut < nvl(b.time,pchigh)

                               and f.tcut >= c.stime

                               and f.tcut < nvl(d.time,pchigh)

                               and a.srparty = 0

                               and a.uorignm = c.orignm

                               and a.ustxt = c.ustxt

                               and a.tsrcprty > 0

                           )

          order by part

           

          it's not practical to retrieve each set separately before unioning them so the first step could be


          with

          get_data as

          (select f.srow,

                  rownum,

                  e.rintid,

                  cintid,

                  c.sintid,

                  srts,

                  pc,

                  to_char(c.srdts,'yyyy-mm-dd') srdts,

                  c.uorgsynm,

                  c.ustxt,

                  c.cntp,

                  c.cnsubtp,

                  c.subpui,

                  e.rpartui,

                  e.cpartui,

                  trunc(c.edif1) edif1,

                  trunc(c.dif2) dif2,

                  trunc(c.stmndt1) stmndt1,

                  trunc(c.stmndt2) stmndt2,

                  c.notcd1,

                  c.am1,

                  c.notcd2,

                  c.am2,

                  c.usam1,

                  c.pfrqcd1,

                  c.pmcd1,

                  c.pfrqcd2,

                  c.pmcd2,

                  c.opt,

                  e.rpdir,

                  c.undernm1,

                  c.undernm2,

                  c.optedt,

                  null null_1,

                  null null_2,

                  c.legal,

                  c.subid

             from rtranstb1 a

                  left outer join

                  rtransend b

               on a.tid = b.tid

                  rsubtb1 c

                  left outer join

                  rsubend d

               on c.sid = d.sid

                  rsubdtb1 e

                  rcutoff f

            where a.tid = c.tid

              and c.sid = e.sid

              and f.tcut >= a.stime

              and f.tcut < nvl(b.time,pchigh)

              and f.tcut >= c.stime

              and f.tcut < nvl(d.time,pchigh)

              and a.srparty = 0

              and (e.rpartf1 = 'N'

               or  e.partf1 = 'N'

                  )

              and (

                   ( c.csub = 'ssss'

              and    c.optexp > f.tsod

                   )

               or  (c.csubtp = 'fff'

              and   c.edt1 > f.tsod

                   )

               or  (c.csubtp not in ('ssss','fff')

              and   c.stmndt1 > f.tsod

               or   c.stmndt2 > f.tsod

                   )

                  )

              and c.orgsrcvts = (select max(a.orgsrcvts)

                                   from rtransdtb1 a

                                        left outer join

                                        rtransend b

                                     on a.dimid = b.endid

                                        rsubdimtb1 c

                                        left outer join

                                        rsubdimend d

                                     on c.sid = d.tid

                                        rsubdimtbl f

                                  where a.dimid = b.endid

                                    and c.subid = d.subid

                                    and f.tcut >= a.stime

                                    and f.tcut < nvl(b.time,pchigh)

                                    and f.tcut >= c.stime

                                    and f.tcut < nvl(d.time,pchigh)

                                    and a.srparty = 0

                                    and a.uorignm = c.orignm

                                    and a.ustxt = c.ustxt

                                )

             and not exists(select 1

                              from rtrandtbl

                                   left outer join

                                   rtransend

                                on a.dimid = b.endid

                                   rsubdimtb1 c

                                   left outer join

                                   rsubdimend d

                                on c.sid = d.tid

                             where a.dimid = b.endid

                               and c.subid = d.subid

                               and f.tcut >= a.stime

                               and f.tcut < nvl(b.time,pchigh)

                               and f.tcut >= c.stime

                               and f.tcut < nvl(d.time,pchigh)

                               and a.srparty = 0

                               and a.uorignm = c.orignm

                               and a.ustxt = c.ustxt

                               and a.tsrcprty > 0

                           )

          )

          select srow,rownum,rintid,cintid,sintid,srts,pc,srdts,uorgsynm,ustxt,

                 cntp,cnsubtp,subpui,rpartui,cpartui,edif1,dif2,stmndt1,stmndt2,

                 notcd1,am1,notcd2,am2,usam1,pfrqcd1,pmcd1,pfrqcd2,pmcd2,opt,

                 rpdir,undernm1,undernm2,optedt,null_1,null_2,legal,subid

            from get_data

          union all

          select srow,rownum,rintid,cintid,sintid,srts,pc,srdts,uorgsynm,ustxt,

                 cntp,cnsubtp,subpui,rpartui,cpartui,edif1,dif2,stmndt1,stmndt2,

                 notcd1,am1,notcd2,am2,usam1,pfrqcd1,pmcd1,pfrqcd2,pmcd2,opt,

                 case when rpdir = 'buyer

                      then 'seller'

                      when rpdir = 'seller'

                      then 'buyer'

                      else rpdir

                 end as direction,

                 undernm1,undernm2,optedt,null_1,null_2,legal,subid

            from get_data

           

          the next step could be pushing predicates into joins and/or influence joining by using parenthesis but that's something you should know best as it's your data we know nothing about so take the following as a pattern as it might not provide the same result as the original (for not considering the presence of null values ...)


             from rtranstb1 a

                  left outer join

                  rtransend b

               on a.tid = b.tid

              and a.srparty = 0

                  inner join

                  rsubtb1 c

               on a.tid = c.tid

                  left outer join

                  rsubend d

               on c.sid = d.sid

                  inner join

                  rsubdtb1 e

               on e.sid = c.sid

              and (e.rpartf1 = 'N'

               or  e.partf1 = 'N'

                  )

                  inner join

                  rcutoff f

               on f.tcut >= a.stime

              and f.tcut < nvl(b.time,pchigh)

              and f.tcut >= c.stime

              and f.tcut < nvl(d.time,pchigh)

              and (

                   ( c.csub = 'ssss'

              and    c.optexp > f.tsod

                   )

               or  (c.csubtp = 'fff'

              and   c.edt1 > f.tsod

                   )

               or  (c.csubtp not in ('ssss','fff')

              and   c.stmndt1 > f.tsod

               or   c.stmndt2 > f.tsod

                   )

                  )


          or maybe


             from (rtranstb1 a

                   left outer join

                   rtransend b

               on  a.tid = b.tid

              and  a.srparty = 0

                  ) x

                  inner join

                  (

                   (rsubtb1 c

                    inner join

                    rsubdtb1 e

               on   e.sid = c.sid

              and   (e.rpartf1 = 'N'

               or    e.partf1 = 'N'

                    )

                   )

                   left outer join

                   rsubend d

               on  c.sid = d.sid

                  ) y

               on x.tid = y.tid

                  inner join

                  rcutoff f

               on f.tcut >= a.stime

              and f.tcut < nvl(b.time,pchigh)

              and f.tcut >= c.stime

              and f.tcut < nvl(d.time,pchigh)

              and (

                   (x.csub = 'ssss'

              and   x.optexp > f.tsod

                   )

               or  (x.csubtp = 'fff'

              and   x.edt1 > f.tsod

                   )

               or  (x.csubtp not in ('ssss','fff')

              and   x.stmndt1 > f.tsod

               or   x.stmndt2 > f.tsod

                   )

                  )

           

          Regards

           

          Etbin

           

          Message was edited by: Etbin another join example showing the use of parenthesis added