1 2 Previous Next 15 Replies Latest reply on Nov 7, 2013 2:09 PM by BluShadow

    sql request optimizing

    marchello-OC

      Hi all,

       

      my need is to optimize sql request that I've inherited from previous developer.

       

      It works about 30 min for "week" period,

                  about 2,5 hours for "month" period (this is more or less reasonable time).

       

      The problem is that customer needs to perform this sql request for "9 monthes" period. Simple math proportion shows that it will perform for more than 26 hours. The production Oracle server turns into one-user regime to close operation day properly. So I don't have the environment to run the sql statement for more than 24 hours.

       

      Could you please help me find the part of sql statement where it wastes the most of time?

       

      I will attach the query and explain plan in the next messages here.

        • 1. Re: sql request optimizing
          marchello-OC

          sql statement:

           

           

          select /*+ first_rows(1) */ * from (

          with

          tdealcommitment1 as (

          select d.*

          from creator.calendar c,

          table(ZOO."PKG_BOREP130".fn_vdealcommitment(c.arcdate)) d/*(+)*/

          where c.arcdate =

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

          ),

          tdealcommitment2 as (

          select d.*

          from creator.calendar c,

          table(ZOO."PKG_BOREP130".fn_vdealcommitment(c.arcdate)) d/*(+)*/

          where c.arcdate =

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

          ),

          tdealreport1 as (

          select d.*

          from creator.calendar c,

          table(ZOO."PKG_BOREP130".fn_vdealreport(c.arcdate)) d/*(+)*/

          where c.arcdate =

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

          ),

          tdealreport2 as (

          select d.*

          from creator.calendar c,

          table(ZOO."PKG_BOREP130".fn_vdealreport(c.arcdate)) d/*(+)*/

          where c.arcdate =

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

          ),

          forgiven as (

          select * from ZOO.offbalanceforgiven

          ),

          tmaxsessionsOLD as (

          select * from ZOO.AR_LOANPORTFOLIO_SESSION

          --select arcdate, max(sessionid) maxsessionid from creator.ar_loanportfolio

          --where arcdate between date '2012-12-29'

          -- and date '2013-09-30' 

          ),

          exprep_h as (

          select * from zoo.z129exprep

          where arcdate =

           

             to_date(

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

            )

          or arcdate =

           

          to_date(

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

          )

           

          )

          Select

             /*+ USE_NL ( k md kd1 kd2 kc1 kc2 ) */

            (

             select to_char(u1.qualityclassid) from zoo.postanova23nbu u1

             where u1.arcdate = k.arcdate

             and u1.contragentid = k.customerid

             and u1.ownerid = k.OwnerId

             and rownum = 1

            ) qualityclassid1,

           

            (

             select u1.servicestate from zoo.postanova23nbu u1

             where u1.arcdate = k.arcdate

             and u1.contragentid = k.customerid

             and u1.ownerid = k.OwnerId

             and rownum = 1

            ) servicestate1,

           

            (

             select u1.finclass from zoo.postanova23nbu u1

             where u1.arcdate = k.arcdate

             and u1.contragentid = k.customerid

             and u1.ownerid = k.OwnerId

             and rownum = 1

            ) finclass1,

          --

           

            (

             select to_char(u1.qualityclassid) from zoo.postanova23nbu u1

             where u1.arcdate = k.arcdate2

             and u1.contragentid = k.customerid

             and u1.ownerid = k.OwnerId

             and rownum = 1

            ) qualityclassid2,

           

            (

             select u1.servicestate from zoo.postanova23nbu u1

             where u1.arcdate = k.arcdate2

             and u1.contragentid = k.customerid

             and u1.ownerid = k.OwnerId

             and rownum = 1

            ) servicestate2,

           

            (

             select u1.finclass from zoo.postanova23nbu u1

             where u1.arcdate = k.arcdate2

             and u1.contragentid = k.customerid

             and u1.ownerid = k.OwnerId

             and rownum = 1

            ) finclass2,

           

          k.*,

          --------Погашення------------------------------------------ 

             case when  'Так' ='Так'

             then (

             /*

            select nvl(Sum(case when isreversal = 1 then (-1)*Usesumma else usesumma end) / 100,0)

            from creator.dealdoctransaction t inner join creator.dealcommercialtranche d on t.dealid = d.dealid

            where t.dealdocumenttypeid in (12,205) and

            */

           

             select sum(d.IntSumma)

             from ZOO.REP130_cancellation d

             where

            d.dealid=k.DealId

           

             and d.arcdate between

             to_date(

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

            )+1

           

             and

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

            )

             else 0

             end as IntSumma,

             case when  'Так' ='Так'

             then (

             /*

            select nvl(Sum(case when isreversal = 1 then (-1)*Usesumma else usesumma end) / 100,0)

            from creator.dealdoctransaction t inner join creator.dealcommercialtranche d on t.dealid = d.dealid

            where t.dealdocumenttypeid = 2 and d.loandealid=k.DealId

            */

             select sum(d.RestSumma)

             from ZOO.REP130_cancellation d

             where

            d.dealid=k.DealId

             and d.arcdate between

           

             to_date(

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

            )+1

           

             and

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

            )

             else 0

             end as RestSumma,

             case when  'Так' ='Так'

             then (

             /*

            select nvl(Sum(case when isreversal = 1 then (-1)*Usesumma else usesumma end*cr.rate/cr.base)/ 100,0)

            from creator.dealdoctransaction t inner join creator.dealcommercialtranche d on t.dealid = d.dealid

            inner join creator.currencyrateall cr on cr.arcdate = t.arcdate and cr.currencyid = d.currencyid

            where t.dealdocumenttypeid in (12,205) and d.loandealid=k.DealId

            */

           

             select sum(d.IntSummaEQ)

             from ZOO.REP130_cancellation d

             where

            d.dealid=k.DealId

             and d.arcdate between

           

             to_date(

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

            )+1

           

             and

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

            )

             else 0

             end as IntSummaEQ,

             case when  'Так' ='Так'

             then  ( /*

            select nvl(Sum(case when isreversal = 1 then (-1)*Usesumma else usesumma end*cr.rate/cr.base)/100,0)

            from creator.dealdoctransaction t inner join creator.dealcommercialtranche d on t.dealid = d.dealid

            inner join creator.currencyrateall cr on cr.arcdate = t.arcdate and cr.currencyid = d.currencyid

            where t.dealdocumenttypeid=2 and d.loandealid=k.DealId

            */

             select sum(d.RestSummaEQ)

             from ZOO.REP130_cancellation d

             where

            d.dealid=k.DealId

             and d.arcdate between

           

             to_date(

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

            )+1

           

             and

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

            )

             else 0

             end as RestSummaEQ,

          --------Погашення--позабаланс--Всього-------------------------------------- 

             case when  'Так' ='Так'

             then (

             /*

            select nvl(Sum(case when isreversal = 1 then (-1)*Usesumma else usesumma end) / 100,0)

            from creator.dealdoctransaction t inner join creator.dealcommercialtranche d on t.dealid = d.dealid

            where t.dealdocumenttypeid=206 and d.loandealid=k.DealId and t.otbs=1

            */

             --IntSumma9

             select sum(d.IntSumma9)

             from ZOO.REP130_cancellation d

             where

            d.dealid=k.DealId

             and d.arcdate between

           

             to_date(

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

            )+1

           

             and

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

            )

             else 0

             end as IntSumma9,

           

             case when  'Так' ='Так'

             then (

             /*

            select nvl(Sum(case when isreversal = 1 then (-1)*Usesumma else usesumma end) / 100,0)

            from creator.dealdoctransaction t inner join creator.dealcommercialtranche d on t.dealid = d.dealid

            where t.dealdocumenttypeid = 157 and d.loandealid=k.DealId and t.otbs=1

            */

             select sum(d.RestSumma9)

             from ZOO.REP130_cancellation d

             where

            d.dealid=k.DealId

             and d.arcdate between

           

             to_date(

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

            )+1

           

             and

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

            )

             else 0

             end

           

             --+ (

             --select nvl(sum(z_h),0)

             --from exprep_h u

             --where

             --)

             /*

            + (

            select nvl(sum(bodynominal),0)

            from ZOO.offbalanceforgiven f

            where f.arcdate between

            

            to_date(

            

            decode ( date '2012-12-29' , --prompt begin

            date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

            date '2012-12-29' 

            )

            

            )+1

            

            and

            

            decode ( date '2013-09-30' , --prompt end

            date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

            date '2013-09-30' 

            )

            

            and f.contragentid = k.customerid

          --select * from zoo.vdealreport v where v.arcdate = date '2012-08-10' and customerid = 108441 and rownum = 1

            ) --!!!!!

            */

             as RestSumma9,

             case when  'Так' ='Так'

             then ( /*

            select nvl(Sum(case when isreversal = 1 then (-1)*Usesumma else usesumma end*cr.rate/cr.base)/ 100,0)

            from creator.dealdoctransaction t inner join creator.dealcommercialtranche d on t.dealid = d.dealid

            inner join creator.currencyrateall cr on cr.arcdate = t.arcdate and cr.currencyid = d.currencyid

            where t.dealdocumenttypeid =206 and d.loandealid=k.DealId and t.otbs=1

            */

             select sum(d.IntSummaEQ9)

             from ZOO.REP130_cancellation d

             where

            d.dealid=k.DealId

             and d.arcdate between

           

             to_date(

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

            )+1

           

             and

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

            )

             else 0

             end as IntSummaEQ9,

           

             case when  'Так' ='Так'

             then  ( /*

            select nvl(Sum(case when isreversal = 1 then (-1)*Usesumma else usesumma end*cr.rate/cr.base)/100,0)

            from creator.dealdoctransaction t inner join creator.dealcommercialtranche d on t.dealid = d.dealid

            inner join creator.currencyrateall cr on cr.arcdate = t.arcdate and cr.currencyid = d.currencyid

            where t.dealdocumenttypeid=157 and d.loandealid=k.DealId and t.otbs=1

            */

             select sum(d.RestSummaEQ9)

             from ZOO.REP130_cancellation d

             where

            d.dealid=k.DealId

             and d.arcdate between

           

             to_date(

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

            )+1

           

             and

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

            )

             else 0

             end

          /*

            + nvl((

            select sum(nvl(f.bodynominal,0) * cr.rate/cr.base) --!!!

            from ZOO.offbalanceforgiven f, creator.currencyrateall cr

            where f.arcdate between

            

            to_date(

            

            decode ( date '2012-12-29' , --prompt begin

            date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

            date '2012-12-29' 

            )

            

            )+1

            

            and

            

            decode ( date '2013-09-30' , --prompt end

            date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

            date '2013-09-30' 

            )

            

            and f.arcdate = cr.arcdate

            and f.currency = CR.CURRENCYID

            and f.contragentid = k.customerid

          --select * from zoo.vdealreport v where v.arcdate = date '2012-08-10' and customerid = 108441 and rownum = 1

            ),0) --!!!!!

          */

             as RestSummaEQ9,

          --------Погашення--позабаланс---сплачено позичальником--------------- 

             case when  'Так' ='Так'

             then ( /*

            select nvl(Sum(case when isreversal = 1 then (-1)*Usesumma else usesumma end) / 100,0)

            from creator.dealdoctransaction t inner join creator.dealcommercialtranche d on t.dealid = d.dealid

            where t.dealdocumenttypeid=206 and d.loandealid=k.DealId and t.otbs=1

            */

             select sum(d.IntSumma9_1)

             from ZOO.REP130_cancellation d

             where

            d.dealid=k.DealId

             and d.arcdate between

           

             to_date(

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

            )+1

           

             and

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

             --and exists (select 1 from creator.arc_document ad where t.documentid=ad.id and ad.DOCUMENTTYPEID in (2,3))

            )

             else 0

             end as IntSumma9_1,

             case when  'Так' ='Так'

             then ( /*

            select nvl(Sum(case when isreversal = 1 then (-1)*Usesumma else usesumma end) / 100,0)

            from creator.dealdoctransaction t inner join creator.dealcommercialtranche d on t.dealid = d.dealid

            where t.dealdocumenttypeid = 157 and d.loandealid=k.DealId and t.otbs=1

            */

             select sum(d.RestSumma9_1)

             from ZOO.REP130_cancellation d

             where

            d.dealid=k.DealId

             and d.arcdate between

           

             to_date(

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

            )+1

           

             and

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

             --and exists (select 1 from creator.arc_document ad where t.documentid=ad.id and ad.DOCUMENTTYPEID in (2,3))

            )

             else 0

             end as RestSumma9_1,

             case when  'Так' ='Так'

             then/*

            select nvl(Sum(case when isreversal = 1 then (-1)*Usesumma else usesumma end*cr.rate/cr.base)/ 100,0)

            from creator.dealdoctransaction t inner join creator.dealcommercialtranche d on t.dealid = d.dealid

            inner join creator.currencyrateall cr on cr.arcdate = t.arcdate and cr.currencyid = d.currencyid

            where t.dealdocumenttypeid =206 and d.loandealid=k.DealId and t.otbs=1

            */

             select sum(d.IntSummaEQ9_1)

             from ZOO.REP130_cancellation d

             where

            d.dealid=k.DealId

             and d.arcdate between

           

             to_date(

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

            )+1

           

             and

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

             --and exists (select 1 from creator.arc_document ad where t.documentid=ad.id and ad.DOCUMENTTYPEID in (2,3))

            )

             else 0

             end as IntSummaEQ9_1,

             case when  'Так' ='Так'

             then  ( /*

            select nvl(Sum(case when isreversal = 1 then (-1)*Usesumma else usesumma end*cr.rate/cr.base)/100,0)

            from creator.dealdoctransaction t inner join creator.dealcommercialtranche d on t.dealid = d.dealid

            inner join creator.currencyrateall cr on cr.arcdate = t.arcdate and cr.currencyid = d.currencyid

            where t.dealdocumenttypeid=157 and d.loandealid=k.DealId and t.otbs=1

            */

             select sum(d.RestSummaEQ9_1)

             from ZOO.REP130_cancellation d

             where

            d.dealid=k.DealId

             and d.arcdate between

           

             to_date(

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

            )+1

           

             and

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

             --and exists (select 1 from creator.arc_document ad where t.documentid=ad.id and ad.DOCUMENTTYPEID in (2,3))

            )

             else 0

             end as RestSummaEQ9_1,

          --------RatingNBU------------------------------------------ 

            (  select v.cratingnbusname

             From zoo.vContragentRating v

             where k.Arcdate between v.validfrom and v.validtill

             and v.contragentid=k.customerid

             and rownum=1) as Rating1,

            (  select v.cratingnbusname

             From zoo.vContragentRating v

             where k.Arcdate2 between v.validfrom and v.validtill

             and v.contragentid=k.customerid

             and rownum=1)as Rating2,

             case when  '0-Контрагент' ='0-Контрагент'

             Then

            (  select v.risktypesname

             From zoo.vContragentRating v

             where k.Arcdate between v.validfrom and v.validtill

             and v.contragentid=k.customerid

             and rownum=1)

             Else

            (Select to_char(MAX(r.RISKTYPEID))

             from CREATOR.ARC_ReservationReport r

             Where r.Arcdate=(Select MAX(h.ArcDate)

             From CREATOR.RESERVEARCDATES h

             where h.isfixed=1

             and h.arcdate=k.ArcDate)

             and r.DealId=k.DealId)

             End as Cat1,

             case when  '0-Контрагент' ='0-Контрагент'

             Then

            (  select v.risktypesname

             From zoo.vContragentRating v

             where k.Arcdate2 between v.validfrom and v.validtill

             and v.contragentid=k.customerid

             and rownum=1)

             Else

            (Select to_char(MAX(r.RISKTYPEID))

             from CREATOR.ARC_ReservationReport r

             Where r.Arcdate=(Select MAX(h.ArcDate)

             From CREATOR.RESERVEARCDATES h

             where h.isfixed=1

             and h.arcdate=k.ArcDate2)

             and r.DealId=k.DealId)

             End as Cat2,

          -----------DISCARDBYRESERVEAMOUNT_INT--------------------------------------- 

             case when  'Так' ='Так'

             then/*

            select nvl(Sum(case when isreversal = 1 then (-1)*Usesumma else usesumma end) / 100,0)

            from creator.dealdoctransaction t inner join creator.dealcommercialtranche d on t.dealid = d.dealid

            inner join creator.arc_document ad on t.documentid=ad.id and (substr(ad.ACCOUNTANO,1,2)='24'or substr(ad.ACCOUNTbNO,1,2)='24')

            where t.dealdocumenttypeid in (12,205) and d.loandealid=k.DealId

            and t.arcdate

            */

             select sum(d.DISCARDBYRESERVE_INT)

             from ZOO.REP130_cancellation d

             where

            d.dealid=k.DealId

             and d.arcdate

           

             between

           

             to_date(

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

            )+1

           

             and

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

            )

             else 0

             end as DISCARDBYRESERVE_INT,

             case when  'Так' ='Так'

             then/*

            select nvl(Sum(case when isreversal = 1 then (-1)*Usesumma else usesumma end*cr.rate/cr.base)/ 100,0)

            from creator.dealdoctransaction t inner join creator.dealcommercialtranche d on t.dealid = d.dealid

            inner join creator.currencyrateall cr on cr.arcdate = t.arcdate and cr.currencyid = d.currencyid

            inner join creator.arc_document ad on t.documentid=ad.id and (substr(ad.ACCOUNTANO,1,2)='24'or substr(ad.ACCOUNTbNO,1,2)='24')

            where t.dealdocumenttypeid in (12,205) and d.loandealid=k.DealId

            and t.arcdate between

            */

             select sum(d.DISCARDBYRESERVE_INTEQ)

             from ZOO.REP130_cancellation d

             where

            d.dealid=k.DealId

             and d.arcdate between

             to_date(

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

            )+1

           

             and

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

            )

             else 0

             end as DISCARDBYRESERVE_INTEQ,

             case when  'Так' ='Так'

             then ( select nvl(Sum(case when isreversal = 1 then (-1)*Usesumma else usesumma end) / 100,0)

             from creator.dealdoctransaction t inner join creator.dealcommercialtranche d on t.dealid = d.dealid

            inner join creator.arc_document ad on t.documentid=ad.id and (substr(ad.ACCOUNTANO,1,2)='24'or substr(ad.ACCOUNTbNO,1,2)='24')

             where t.dealdocumenttypeid = 2 and d.loandealid=k.DealId

             and t.arcdate between

           

             to_date(

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

            )+1

           

             and

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

            )

             else 0

             end as DISCARDBYRESERVEAMOUNT,

             case when  'Так' ='Так'

             then ( select nvl(Sum(case when isreversal = 1 then (-1)*Usesumma else usesumma end*cr.rate/cr.base)/ 100,0)

             from creator.dealdoctransaction t inner join creator.dealcommercialtranche d on t.dealid = d.dealid

            inner join creator.currencyrateall cr on cr.arcdate = t.arcdate and cr.currencyid = d.currencyid

            inner join creator.arc_document ad on t.documentid=ad.id and (substr(ad.ACCOUNTANO,1,2)='24'or substr(ad.ACCOUNTbNO,1,2)='24')

             where t.dealdocumenttypeid = 2 and d.loandealid=k.DealId

             and t.arcdate between

           

             to_date(

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

            )+1

           

             and

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

            )

             else 0

             end as DISCARDBYRESERVEAMOUNTEQ,

          --------RiskLevel------------------------------------------ 

            (  select r.name

             from zoo.crl_clientlevel v,zoo.crl_risklevel r

             where v.contragentid=k.customerid

             and v.levelid=r.id

             and v.id=(select max(id) from zoo.crl_clientlevel v1 where v.contragentid=v1.contragentid and v1.arcdate<=k.ArcDate)

            ) as RL1,

            (  select v.restrdatebegin

             from zoo.crl_deallevel v

             where v.dealid=k.DealId

             and v.id=(select max(id) from zoo.crl_deallevel v1 where v.dealid=v1.dealid and v1.arcdate<=k.ArcDate)

            ) as RB1,

            (  select v.restrdateend

             from zoo.crl_deallevel v

             where v.dealid=k.DealId

             and v.id=(select max(id) from zoo.crl_deallevel v1 where v.dealid=v1.dealid and v1.arcdate<=k.ArcDate)

            ) as RE1,

            (  select add_months(v.hard3mdate,p.mm3)hard3mdate

             from zoo.crl_deallevel v, zoo.crl_param p

             where v.dealid=k.DealId

             and p.arcdate=(select max(arcdate) from zoo.crl_param where arcdate<=k.ArcDate)

             and v.id=(select max(id) from zoo.crl_deallevel v1 where v.dealid=v1.dealid and v1.arcdate<=k.ArcDate)

            ) as RH1,

            (  select r.name

             from zoo.crl_clientlevel v,zoo.crl_risklevel r

             where v.contragentid=k.customerid

             and v.levelid=r.id

             and v.id=(select max(id) from zoo.crl_clientlevel v1 where v.contragentid=v1.contragentid and v1.arcdate<=k.ArcDate2)

            ) as RL2,

            (  select v.restrdatebegin

             from zoo.crl_deallevel v

             where v.dealid=k.DealId

             and v.id=(select max(id) from zoo.crl_deallevel v1 where v.dealid=v1.dealid and v1.arcdate<=k.ArcDate2)

            ) as RB2,

            (  select v.restrdateend

             from zoo.crl_deallevel v

             where v.dealid=k.DealId

             and v.id=(select max(id) from zoo.crl_deallevel v1 where v.dealid=v1.dealid and v1.arcdate<=k.ArcDate2)

            ) as RE2,

            (  select add_months(v.hard3mdate,p.mm3)hard3mdate

             from zoo.crl_deallevel v, zoo.crl_param p

             where v.dealid=k.DealId

             and p.arcdate=(select max(arcdate) from zoo.crl_param where arcdate<=k.ArcDate2)

             and v.id=(select max(id) from zoo.crl_deallevel v1 where v.dealid=v1.dealid and v1.arcdate<=k.ArcDate2)

            ) as RH2,

          --------RatingARZ------------------------------------------

          NVL(ZOO.PK_GetRatingParam.getratingarz(k.customerid,k.ArcDate),' ') as RVA1,

            (  Select rc.RATINGSTARTDATE

             From creator.RatingContragent_log rc

             Where ratinginstrument<>'KDSEGBASEL'

             and rc.contragentid=k.customerid

             and ratingstartdate=(select max(ratingstartdate) from creator.RatingContragent_log rm where rm.contragentid=rc.contragentid and rm.ratinginstrument<>'KDSEGBASEL' and rm.ratingstartdate<=k.Arcdate)

             and rownum = 1

            )RDA1,

          NVL(ZOO.PK_GetRatingParam.getratingarz(k.customerid,k.ArcDate2),' ') as RVA2,

            (  Select rc.RATINGSTARTDATE

             From creator.RatingContragent_log rc

             Where ratinginstrument<>'KDSEGBASEL'

             and rc.contragentid=k.customerid

             and ratingstartdate=(select max(ratingstartdate) from creator.RatingContragent_log rm where rm.contragentid=rc.contragentid and rm.ratinginstrument<>'KDSEGBASEL' and rm.ratingstartdate<=k.Arcdate2)

             and rownum = 1

            )RDA2,

          --------SEGMENT------------------------------------------

            (  Select rc.ratingvalue

             From creator.RatingContragent_log rc

             Where ratinginstrument='KDSEGBASEL'

             and rc.contragentid=k.customerid

             and ratingstartdate=(select max(ratingstartdate) from creator.RatingContragent_log rm where rm.contragentid=rc.contragentid and rm.ratinginstrument='KDSEGBASEL' and rm.ratingstartdate<=k.Arcdate)

             and rownum = 1

            )segment1,

            (  Select rc.ratingvalue

             From creator.RatingContragent_log rc

             Where ratinginstrument='KDSEGBASEL'

             and rc.contragentid=k.customerid

             and ratingstartdate=(select max(ratingstartdate) from creator.RatingContragent_log rm where rm.contragentid=rc.contragentid and rm.ratinginstrument='KDSEGBASEL' and rm.ratingstartdate<=k.Arcdate2)

             and rownum = 1

            )segment2,

          --------MaxOverDayPeriod------------------------------------------ 

            md.MaxDayOver,

          --------BorrowerParams------------------------------------------ 

             Case when k.ArcDate is not null Then

             Case when (KD1.RESAMOUNTEQ978)>=250000 Then 'NonLocal' Else 'Local'

             end

             End as WriteCompetence,

             Case when k.ArcDate2 is not null Then

             Case when (KD2.RESAMOUNTEQ978)>=250000 Then 'NonLocal' Else 'Local'

             end

             End as WriteCompetence2,

          ------------------------------

             Case when k.ArcDate is not null Then

             Case when (KD1.RESAMOUNTEQ978)>=100000 Then 'Significant' Else 'NonSignificant'

             end

             End as Significant,

             Case when k.ArcDate2 is not null Then 

             Case when (KD2.RESAMOUNTEQ978)>=100000 Then 'Significant' Else 'NonSignificant'

             end

             End as Significant2,

          ------------------------------

             Case when k.ArcDate is not null Then

             Case when  kd1.Rating in ('4E','5A','5B','5C','5D','5E','XX')

             or kd1.dayovermax>90

             then  'RestructuringCase'

             when  kd1.Rating in ('4C','4D')

             or kd1.dayovermax between 31 and 90

             then  'WatchCase' 

             else  'NormalCase' 

             end

             End as ListType,

             Case when k.ArcDate2 is not null Then 

             Case when kd2.Rating in ('4E','5A','5B','5C','5D','5E','XX')

             or kd2.dayovermax>90

             then  'RestructuringCase'

             when  kd2.Rating in ('4C','4D')

             or kd2.dayovermax between 31 and 90

             then  'WatchCase' 

             else  'NormalCase' 

             end

             End as ListType2,

          ------------------------------------------------------------------------------------------------------ 

             to_date(creator.PKG_DEALUTIL.GetDealParamValue(k.ownerid,'elRestrDate'),'dd.mm.yyyy') RestrDate,

          ------------------------------------------------------------------------------------------------------ 

            (Select  ca.paramvalue

             from  creator.contragentaddparam_history ca

             where  ca.paramname='Дата ост. мон. фін.стану'

             and  ca.contragentid=k.customerid

             and  k.ArcDate between ca.validfrom and ca.validtill

            )LastFinMonitoring1,

            (Select  ca.paramvalue

             from  creator.contragentaddparam_history ca

             where  ca.paramname='Дата ост. мон. фін.стану'

             and  ca.contragentid=k.customerid

             and  k.ArcDate2 between ca.validfrom and ca.validtill

            )LastFinMonitoring2,

           

           

            (Select  ca.paramvalue

             from  creator.contragentaddparam_history ca

             where  ca.paramname='Гран. дата наст. мон. фін'

             and  ca.contragentid=k.customerid

             and  k.ArcDate between ca.validfrom and ca.validtill

            )MarginFinMonitoring1,

            (Select  ca.paramvalue

             from  creator.contragentaddparam_history ca

             where  ca.paramname='Гран. дата наст. мон. фін'

             and  ca.contragentid=k.customerid

             and  k.ArcDate2 between ca.validfrom and ca.validtill

            )MarginFinMonitoring2,

           

           

            (Select  (select vll.name

             from  creator.vbua_level_loanrisk vll

             where  to_number(ca.paramvalue)=vll.id)

             from  creator.contragentaddparam_history ca

             where  ca.paramname='Рівень кредитного ризику'

             and  ca.contragentid=k.customerid

             and  k.ArcDate between ca.validfrom and ca.validtill

            )CreditRiskLevel1,

            (Select  (select vll.name

             from  creator.vbua_level_loanrisk vll

             where  to_number(ca.paramvalue)=vll.id)

             from  creator.contragentaddparam_history ca

             where  ca.paramname='Рівень кредитного ризику'

             and  ca.contragentid=k.customerid

             and  k.ArcDate2 between ca.validfrom and ca.validtill

            )CreditRiskLevel2,

          ------------------------------------------------------------------------------------------------------ 

            (Select ls.name

             From  creator.dealcommercialloan dcl, creator.commercialloanstate ls

             where  dcl.dealid=k.dealid

             and  dcl.loanstateid=ls.id) loanstate

          ------------------------------------------------------------------------------------------------------ 

          from  (

          select

            v.ArcDate,

            v2.Arcdate as Arcdate2,

            v.ToboName,

            v.customerid,

            v.customeridentifycode,

            v.customerType,

            v.customername,

            v.OwnerId,

            v.DealId,

            v.dealno,

            v.direct,

            v.product,

            v.dealtype,

            v.currency,

            v.interestrate,

           

             case when  'позабаланс' ='баланс'

             then v.restamount

             when  'позабаланс' ='позабаланс'

             then v.Z_H

             else v.restamount+v.Z_H

             end restamount,

           

             case when  'позабаланс' ='баланс'

             then v.restamounteq

             when  'позабаланс' ='позабаланс'

             then v.Z_H_E

             else v.restamounteq+v.Z_H_E

             end restamounteq,

           

          /*

            nvl(v2.overdueamount,0) 

            +

            nvl(

            (

            select nvl(sum(z_h),0)

            from exprep_h u

            where u.arcdate =

            to_date(

            decode ( date '2013-09-30' , --prompt end

            date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

            date '2013-09-30' 

            )

            )

            and u.contragentid = v2.customerid

            and u.dealid = v2.dealid

            ),

            0)

            as overdueamount2,

          */

             nvl(v.overdueamount,0)

            +

             nvl(

            (

             select nvl(sum(z_h),0)

             from exprep_h u

             where u.arcdate =

             to_date(

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

            )

             and u.contragentid = v.customerid

             and u.dealid = v.dealid

            ),

             0

            )

             as overdueamount,

          /*

            nvl(v2.overdueamounteq,0)

            +

            nvl(

            (

            select nvl(sum(z_h_e),0)

            from exprep_h u

            where u.arcdate =

            to_date(

            decode ( date '2013-09-30' , --prompt end

            date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

            date '2013-09-30' 

            )

            )

            and u.contragentid = v2.customerid

            and u.dealid = v2.dealid

            ),

            0)

            as overdueamounteq2,

          */

             nvl(v.overdueamounteq,0)

            +

             nvl(

            (

             select nvl(sum(z_h_e),0)

             from exprep_h u

             where u.arcdate =

             to_date(

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

            )

             and u.contragentid = v.customerid

             and u.dealid = v.dealid

            ),

             0)

             as overdueamounteq,

           

             case when  'позабаланс' ='баланс'

             then v.intaccrualamount

             when  'позабаланс' ='позабаланс'

             then v.Z_H_PRC

             else v.intaccrualamount+v.Z_H_PRC

             end intaccrualamount,

             case when  'позабаланс' ='баланс'

             then v.intaccrualamounteq

             when  'позабаланс' ='позабаланс'

             then v.Z_H_PRC_E

             else v.intaccrualamounteq+v.Z_H_PRC_E

             end intaccrualamounteq,

          /*

            nvl(v2.intoveramount,0) 

            +

            nvl(

            (

            select nvl(sum(z_h_prc),0)

            from exprep_h u

            where u.arcdate =

            to_date(

            decode ( date '2013-09-30' , --prompt end

            date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

            date '2013-09-30' 

            )

            )

            and u.contragentid = v2.customerid

            and u.dealid = v2.dealid

            ),

            0)

            as intoveramount2,

          */

             nvl(v.intoveramount,0)

            +

             nvl(

            (

             select nvl(sum(z_h_prc),0)

             from exprep_h u

             where u.arcdate =

             to_date(

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

            )

             and u.contragentid = v.customerid

             and u.dealid = v.dealid

            ),

             0)

            intoveramount,

          /*

            nvl(v2.INTOVERAMOUNTEQ,0)

            +

            nvl(

            (

            select nvl(sum(z_h_prc_e),0)

            from exprep_h u

            where u.arcdate =

            to_date(

            decode ( date '2013-09-30' , --prompt end

            date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

            date '2013-09-30' 

            )

            )

            and u.contragentid = v2.customerid

            and u.dealid = v2.dealid

            ),0)

            as INTOVERAMOUNTEQ2,

          */

             nvl(v.INTOVERAMOUNTEQ,0)

            +

             nvl(

            (

             select nvl(sum(z_h_prc_e),0)

             from exprep_h u

             where u.arcdate =

             to_date(

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

            )

             and u.contragentid = v.customerid

             and u.dealid = v.dealid

            ),0)

             as INTOVERAMOUNTEQ,

           

            v.dayoverrest,

            v.dayoverint,

            v.dayovermax,

            v.RESAMOUNTEQ978,

            v2.interestrate  as interestrate2,

             case when  'позабаланс' ='баланс'

             then v2.restamount

             when  'позабаланс' ='позабаланс'

             then v2.Z_H

             else v2.restamount+v2.Z_H

             end restamount2,

           

           

             case when  'позабаланс' ='баланс'

             then v2.restamounteq

             when  'позабаланс' ='позабаланс'

             then v2.Z_H_E

             else v2.restamounteq+v2.Z_H_E

             end restamounteq2,

           

             nvl(v2.overdueamount,0)

           

            +

             nvl(

            (

             select nvl(sum(z_h),0)

             from exprep_h u

             where u.arcdate =

           

             to_date(

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

            )

           

           

             and u.contragentid = v2.customerid

             and u.dealid = v2.dealid

            ),

             0)

           

             as overdueamount2,

           

             nvl(v2.overdueamounteq,0)

           

            +

             nvl(

            (

             select nvl(sum(z_h_e),0)

             from exprep_h u

             where u.arcdate =

           

             to_date(

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

            )

           

             and u.contragentid = v2.customerid

             and u.dealid = v2.dealid

            ),

             0)

           

             as overdueamounteq2,

           

             case when  'позабаланс' ='баланс'

             then v2.intaccrualamount

             when  'позабаланс' ='позабаланс'

             then v2.Z_H_PRC

             else v2.intaccrualamount+v2.Z_H_PRC

             end intaccrualamount2,

             case when  'позабаланс' ='баланс'

             then v2.intaccrualamounteq

             when  'позабаланс' ='позабаланс'

             then v2.Z_H_PRC_E

             else v2.intaccrualamounteq+v2.Z_H_PRC_E

             end intaccrualamounteq2,

           

             nvl(v2.intoveramount,0)

           

            +

             nvl(

            (

             select nvl(sum(z_h_prc),0)

             from exprep_h u

             where u.arcdate =

           

             to_date(

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

            )

           

             and u.contragentid = v2.customerid

             and u.dealid = v2.dealid

            ),

             0)

           

             as intoveramount2,

           

             nvl(v2.INTOVERAMOUNTEQ,0)

           

            +

             nvl(

            (

             select nvl(sum(z_h_prc_e),0)

             from exprep_h u

             where u.arcdate =

           

             to_date(

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

            )

           

             and u.contragentid = v2.customerid

             and u.dealid = v2.dealid

            ),0)

           

             as INTOVERAMOUNTEQ2,

           

            v2.dayoverrest as dayoverrest2,

            v2.dayoverint as dayoverint2,

            v2.dayovermax as dayovermax2,

            v2.RESAMOUNTEQ978 as RESAMOUNTEQ9782

          from tdealreport1 v inner join tdealreport2 v2 on v.dealId=v2.dealid

          where v.arcdate=

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

          and v2.arcdate=

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

          UNION ALL

          select v.Arcdate,

             null ArcDate2,

            v.ToboName,

            v.customerid,

            v.customeridentifycode,

            v.customerType,

            v.customername,

            v.OwnerId,

            v.DealId,

            v.dealno,

            v.direct,

            v.product,

            v.dealtype,

            v.currency,

            v.interestrate,

             case when  'позабаланс' ='баланс'

             then v.restamount

             when  'позабаланс' ='позабаланс'

             then v.Z_H

             else v.restamount+v.Z_H

             end restamount,

             case when  'позабаланс' ='баланс'

             then v.restamounteq

             when  'позабаланс' ='позабаланс'

             then v.Z_H_E

             else v.restamounteq+v.Z_H_E

             end restamounteq,

            v.overdueamount,

            v.overdueamounteq,

             case when  'позабаланс' ='баланс'

             then v.intaccrualamount

             when  'позабаланс' ='позабаланс'

             then v.Z_H_PRC

             else v.intaccrualamount+v.Z_H_PRC

             end intaccrualamount,

             case when  'позабаланс' ='баланс'

             then v.intaccrualamounteq

             when  'позабаланс' ='позабаланс'

             then v.Z_H_PRC_E

             else v.intaccrualamounteq+v.Z_H_PRC_E

             end intaccrualamounteq,

            v.intoveramount,

            v.INTOVERAMOUNTEQ,

            v.dayoverrest,

            v.dayoverint,

            v.dayovermax,

            v.RESAMOUNTEQ978,

             null as interestrate2,

             0 as restamount2,

             0 as restamounteq2,

             0 as overdueamount2,

             0 as overdueamounteq2,

             0 as intaccrualamount2,

             0 as intaccrualamounteq2,

             0 as intoveramount2,

             0 as INTOVERAMOUNTEQ2,

             0 as dayoverrest2,

             0 as dayoverint2,

             0 as dayovermax2,

             0 as RESAMOUNTEQ9782

          from tdealreport1 v

          where v.arcdate=

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

           

          and  not exists (select 1 from tdealreport2 v2 where v.dealId=v2.dealid and v2.arcdate=

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

          )

           

           

          UNION ALL

          select null Arcdate,

            v.Arcdate Arcdate2,

            v.ToboName,

            v.customerid,

            v.customeridentifycode,

            v.customerType,

            v.customername,

            v.Ownerid,

            v.DealId,

            v.dealno,

            v.direct,

            v.product,

            v.dealtype,

            v.currency,

             null as interestrate,

             0 as restamount,

             0 as restamounteq,

             0 as overdueamount,

             0 as overdueamounteq,

             0 as intaccrualamount,

             0 as intaccrualamounteq,

             0 as intoveramount,

             0 as INTOVERAMOUNTEQ,

             0 as dayoverrest,

             0 as dayoverint,

             0 as dayovermax,

             0 as RESAMOUNTEQ978,

            v.interestrate as interestrate2,

             case when  'позабаланс' ='баланс'

             then v.restamount

             when  'позабаланс' ='позабаланс'

             then v.Z_H

             else v.restamount+v.Z_H

             end restamount2,

             case when  'позабаланс' ='баланс'

             then v.restamounteq

             when  'позабаланс' ='позабаланс'

             then v.Z_H_E

             else v.restamounteq+v.Z_H_E

             end restamounteq2,

            v.overdueamount as overdueamount2,

            v.overdueamounteq as overdueamounteq2,

             case when  'позабаланс' ='баланс'

             then v.intaccrualamount

             when  'позабаланс' ='позабаланс'

             then v.Z_H_PRC

             else v.intaccrualamount+v.Z_H_PRC

             end intaccrualamount2,

             case when  'позабаланс' ='баланс'

             then v.intaccrualamounteq

             when  'позабаланс' ='позабаланс'

             then v.Z_H_PRC_E

             else v.intaccrualamounteq+v.Z_H_PRC_E

             end intaccrualamounteq2,

            v.intoveramount as intoveramount2,

            v.INTOVERAMOUNTEQ as INTOVERAMOUNTEQ2,

            v.dayoverrest as dayoverrest2,

            v.dayoverint as dayoverint2,

            v.dayovermax as dayovermax2,

            v.RESAMOUNTEQ978 as RESAMOUNTEQ9782

          from tdealreport1 v

          where v.arcdate=

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

           

          and  not exists (select 1 from tdealreport2 v2 where v.dealId=v2.dealid and v2.arcdate=

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

          )

           

           

          --Order by customerid,dealid

          )K,

          (

          select 0 dealid, 0 maxdayover from dual

          /*

          Select

            ar.dealid,max(greatest(nvl(ar.arcdate-ar.FACTACTIVEOVERDUEDATE+1,0),nvl(ar.arcdate-ar.FACTACTIVEOVERDUEDATE_INT+1,0)))MaxDayOver

            From creator.ar_loanportfolio ar

            Where ar.siteid=325213

            and ( ar.sessionid in

            --(select maxsessionid from tmaxsessions)

            (select sessionId from CREATOR.BI_LOADSESSION ss where ss.activesign=1)

            OR

            ar.sessionid in

            (select maxsessionid from tmaxsessionsOLD)

            )

          -- and ar.sessionid in

            --(select maxsessionid from tmaxsessions)

          -- (select sessionId from CREATOR.BI_LOADSESSION ss where ss.activesign=1)

            --(select max(sessionid) from creator.ar_loanportfolio

            -- where arcdate = date '2012-12-29'

            --

            -- union all

            --

            -- select max(sessionid) from creator.ar_loanportfolio

            -- where arcdate = date '2013-09-30' 

            --)

            --(select sessionId from CREATOR.BI_LOADSESSION ss where ss.activesign=1)

           

            and ( (ar.arcdate between

            

            decode ( date '2012-12-29' , --prompt begin

            date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

            date '2012-12-29' 

            ) 

            

            and

            

            decode ( date '2013-09-30' , --prompt end

            date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

            date '2013-09-30' 

            )

            

            and 'Так' ='Так')

            or

            ar.arcdate=to_date('01011900','ddmmyyyy') 

            )

            Group By dealid

          */

          )MD,

          (Select Customerid,

            ( Select SUM(l.Z_EUR + l.Z_Over_EUR +l.Z_Prc_EUR+l.Z_Prc_Over_EUR+l.z_limit_eur+

            l.z_h_eur+l.z_h_prc_eur+l.z_h_com_eur + l.z_com_over_eur +

             case when l.hopelessdate is not null or(l.hopelessdate is null and (l.Z_COM_EUR-l.commission_offbalance_ekv_eur)<0)

             then  0

             when l.hopelessdate is null and (l.Z_COM_EUR-l.commission_offbalance_ekv_eur)>=0

             then l.Z_COM_EUR-l.commission_offbalance_ekv_eur

             else l.Z_COM_EUR

             end)

             from  ZOO.Z129ExpRep l

             where l.contragentid=p.customerid and l.arcdate=p.arcdate) as resamounteq978,

            dayovermax, nvl(TRIM(UPPER(ZOO.PK_GetRatingParam.getratingarz(customerid,ArcDate))),'XX') Rating

          From  (

             Select arcdate,CustomerId,

             sum(RESAMOUNTEQ978) RESAMOUNTEQ978,

             max(dayovermax)dayovermax

             from tdealreport1

             where arcdate=

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

             group by CustomerId, arcdate

            )p

          )KD1,

          (Select CustomerId,sum(COMMAMOUNTEQ978)COMMAMOUNTEQ978

             from tdealcommitment1

            where arcdate=

           

             decodedate '2012-12-29' , --prompt begin

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate)-7 ),

             date '2012-12-29' 

            )

           

          group by CustomerId

          )KC1,

          (Select Customerid,

            ( Select SUM(l.Z_EUR + l.Z_Over_EUR +l.Z_Prc_EUR+l.Z_Prc_Over_EUR+l.z_limit_eur+

            l.z_h_eur+l.z_h_prc_eur+l.z_h_com_eur + l.z_com_over_eur +

             case when l.hopelessdate is not null or(l.hopelessdate is null and (l.Z_COM_EUR-l.commission_offbalance_ekv_eur)<0)

             then  0

             when l.hopelessdate is null and (l.Z_COM_EUR-l.commission_offbalance_ekv_eur)>=0

             then l.Z_COM_EUR-l.commission_offbalance_ekv_eur

             else l.Z_COM_EUR

             end)

             from  ZOO.Z129ExpRep l

             where l.contragentid=p.customerid and l.arcdate=p.arcdate) as resamounteq978,

            dayovermax, nvl(TRIM(UPPER(ZOO.PK_GetRatingParam.getratingarz(customerid,ArcDate))),'XX') Rating

          From  (

             Select arcdate,CustomerId,sum(RESAMOUNTEQ978)RESAMOUNTEQ978, max(dayovermax)dayovermax

             from tdealreport1

             where arcdate=

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

             group by CustomerId, arcdate

            )p

          )KD2,

          (Select CustomerId,sum(COMMAMOUNTEQ978)COMMAMOUNTEQ978

             from tdealcommitment2

            where arcdate=

           

             decodedate '2013-09-30' , --prompt end

             date '1800-01-01',

            ( select max(arcdate) from creator.calendar where dayoff = 0 and arcdate < trunc(sysdate) ),

             date '2013-09-30' 

            )

           

          group by CustomerId

          )KC2

          Where --DEALTYPE in @prompt('c. Тип угоди:','A','Типи договорів\Тип',Multi,Constrained,Persistent,,User:2)

            --and

            k.dealid=md.DealId(+)

            and k.customerid=kd1.customerid(+)

            and k.customerid=kd2.customerid(+)

            and k.customerid=kc1.customerid(+)

            and k.customerid=kc2.customerid(+)

          )

           

          Message was edited by: BluShadow to change code to courier new font.

          • 2. Re: sql request optimizing
            marchello-OC

            explain plan:

             

            Plan hash value: 571922976

             

            --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

            | Id | Operation | Name  | Rows | Bytes |TempSpc| Cost (%CPU)| Time  | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |

            --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

            0 | SELECT STATEMENT  | | 16337125M| |  1253K (9)| 00:00:20 | | | | | |

            |*  1COUNT STOPKEY | | | | | | | | | | | |

            |*  2TABLE ACCESS BY INDEX ROWID | POSTANOVA23NBU |  122 | |  4  (0)| 00:00:01 | | | | | |

            |*  3INDEX RANGE SCAN | I_DATE_OWNER_POST23 |  1 | | |  3  (0)| 00:00:01 | | | | | |

            |*  4COUNT STOPKEY | | | | | | | | | | | |

            |*  5TABLE ACCESS BY INDEX ROWID | POSTANOVA23NBU |  125 | |  4  (0)| 00:00:01 | | | | | |

            |*  6INDEX RANGE SCAN | I_DATE_OWNER_POST23 |  1 | | |  3  (0)| 00:00:01 | | | | | |

            |*  7COUNT STOPKEY | | | | | | | | | | | |

            |*  8TABLE ACCESS BY INDEX ROWID | POSTANOVA23NBU |  136 | |  4  (0)| 00:00:01 | | | | | |

            |*  9INDEX RANGE SCAN | I_DATE_OWNER_POST23 |  1 | | |  3  (0)| 00:00:01 | | | | | |

            |* 10COUNT STOPKEY | | | | | | | | | | | |

            |* 11TABLE ACCESS BY INDEX ROWID | POSTANOVA23NBU |  122 | |  4  (0)| 00:00:01 | | | | | |

            |* 12INDEX RANGE SCAN | I_DATE_OWNER_POST23 |  1 | | |  3  (0)| 00:00:01 | | | | | |

            |* 13COUNT STOPKEY | | | | | | | | | | | |

            |* 14TABLE ACCESS BY INDEX ROWID | POSTANOVA23NBU |  125 | |  4  (0)| 00:00:01 | | | | | |

            |* 15INDEX RANGE SCAN | I_DATE_OWNER_POST23 |  1 | | |  3  (0)| 00:00:01 | | | | | |

            |* 16COUNT STOPKEY | | | | | | | | | | | |

            |* 17TABLE ACCESS BY INDEX ROWID | POSTANOVA23NBU |  136 | |  4  (0)| 00:00:01 | | | | | |

            |* 18INDEX RANGE SCAN | I_DATE_OWNER_POST23 |  1 | | |  3  (0)| 00:00:01 | | | | | |

            19 | SORT AGGREGATE  | |  117 | | | | | | | | |

            20TABLE ACCESS BY INDEX ROWID | REP130_CANCELLATION |  1482516 | |  208  (8)| 00:00:01 | | | | | |

            |* 21INDEX SKIP SCAN | REP130_CANCELLATION1 |  148 | | |  204  (8)| 00:00:01 | | | | | |

            22 | SORT AGGREGATE  | |  117 | | | | | | | | |

            23TABLE ACCESS BY INDEX ROWID | REP130_CANCELLATION |  1482516 | |  208  (8)| 00:00:01 | | | | | |

            |* 24INDEX SKIP SCAN | REP130_CANCELLATION1 |  148 | | |  204  (8)| 00:00:01 | | | | | |

            25 | SORT AGGREGATE  | |  117 | | | | | | | | |

            26TABLE ACCESS BY INDEX ROWID | REP130_CANCELLATION |  1482516 | |  208  (8)| 00:00:01 | | | | | |

            |* 27INDEX SKIP SCAN | REP130_CANCELLATION1 |  148 | | |  204  (8)| 00:00:01 | | | | | |

            28 | SORT AGGREGATE  | |  117 | | | | | | | | |

            29TABLE ACCESS BY INDEX ROWID | REP130_CANCELLATION |  1482516 | |  208  (8)| 00:00:01 | | | | | |

            |* 30INDEX SKIP SCAN | REP130_CANCELLATION1 |  148 | | |  204  (8)| 00:00:01 | | | | | |

            31 | SORT AGGREGATE  | |  117 | | | | | | | | |

            32TABLE ACCESS BY INDEX ROWID | REP130_CANCELLATION |  1482516 | |  208  (8)| 00:00:01 | | | | | |

            |* 33INDEX SKIP SCAN | REP130_CANCELLATION1 |  148 | | |  204  (8)| 00:00:01 | | | | | |

            34 | SORT AGGREGATE  | |  117 | | | | | | | | |

            35TABLE ACCESS BY INDEX ROWID | REP130_CANCELLATION |  1482516 | |  208  (8)| 00:00:01 | | | | | |

            |* 36INDEX SKIP SCAN | REP130_CANCELLATION1 |  148 | | |  204  (8)| 00:00:01 | | | | | |

            37 | SORT AGGREGATE  | |  117 | | | | | | | | |

            38TABLE ACCESS BY INDEX ROWID | REP130_CANCELLATION |  1482516 | |  208  (8)| 00:00:01 | | | | | |

            |* 39INDEX SKIP SCAN | REP130_CANCELLATION1 |  148 | | |  204  (8)| 00:00:01 | | | | | |

            40 | SORT AGGREGATE  | |  117 | | | | | | | | |

            41TABLE ACCESS BY INDEX ROWID | REP130_CANCELLATION |  1482516 | |  208  (8)| 00:00:01 | | | | | |

            |* 42INDEX SKIP SCAN | REP130_CANCELLATION1 |  148 | | |  204  (8)| 00:00:01 | | | | | |

            43 | SORT AGGREGATE  | |  117 | | | | | | | | |

            44TABLE ACCESS BY INDEX ROWID | REP130_CANCELLATION |  1482516 | |  208  (8)| 00:00:01 | | | | | |

            |* 45INDEX SKIP SCAN | REP130_CANCELLATION1 |  148 | | |  204  (8)| 00:00:01 | | | | | |

            46 | SORT AGGREGATE  | |  117 | | | | | | | | |

            47TABLE ACCESS BY INDEX ROWID | REP130_CANCELLATION |  1482516 | |  208  (8)| 00:00:01 | | | | | |

            |* 48INDEX SKIP SCAN | REP130_CANCELLATION1 |  148 | | |  204  (8)| 00:00:01 | | | | | |

            49 | SORT AGGREGATE  | |  117 | | | | | | | | |

            50TABLE ACCESS BY INDEX ROWID | REP130_CANCELLATION |  1482516 | |  208  (8)| 00:00:01 | | | | | |

            |* 51INDEX SKIP SCAN | REP130_CANCELLATION1 |  148 | | |  204  (8)| 00:00:01 | | | | | |

            52 | SORT AGGREGATE  | |  117 | | | | | | | | |

            53TABLE ACCESS BY INDEX ROWID | REP130_CANCELLATION |  1482516 | |  208  (8)| 00:00:01 | | | | | |

            |* 54INDEX SKIP SCAN | REP130_CANCELLATION1 |  148 | | |  204  (8)| 00:00:01 | | | | | |

            |* 55COUNT STOPKEY | | | | | | | | | | | |

            56 | NESTED LOOPS OUTER | |  135 | |  5  (0)| 00:00:01 | | | | | |

            |* 57TABLE ACCESS BY INDEX ROWID | CONTRAGENTRATING_LOG |  124 | |  4  (0)| 00:00:01 | | | | | |

            |* 58INDEX RANGE SCAN | UN_CONTRAGENTRATING_LOG |  1 | | |  3  (0)| 00:00:01 | | | | | |

            59TABLE ACCESS BY INDEX ROWID | CRATINGNBU |  111 | |  1  (0)| 00:00:01 | | | | | |

            |* 60INDEX UNIQUE SCAN | PKCRATINGNBU |  1 | | |  0  (0)| 00:00:01 | | | | | |

            |* 61COUNT STOPKEY | | | | | | | | | | | |

            62 | NESTED LOOPS OUTER | |  135 | |  5  (0)| 00:00:01 | | | | | |

            |* 63TABLE ACCESS BY INDEX ROWID | CONTRAGENTRATING_LOG |  124 | |  4  (0)| 00:00:01 | | | | | |

            |* 64INDEX RANGE SCAN | UN_CONTRAGENTRATING_LOG |  1 | | |  3  (0)| 00:00:01 | | | | | |

            65TABLE ACCESS BY INDEX ROWID | CRATINGNBU |  111 | |  1  (0)| 00:00:01 | | | | | |

            |* 66INDEX UNIQUE SCAN | PKCRATINGNBU |  1 | | |  0  (0)| 00:00:01 | | | | | |

            |* 67COUNT STOPKEY | | | | | | | | | | | |

            68 | NESTED LOOPS OUTER | |  140 | |  5  (0)| 00:00:01 | | | | | |

            |* 69TABLE ACCESS BY INDEX ROWID | CONTRAGENTRATING_LOG |  124 | |  4  (0)| 00:00:01 | | | | | |

            |* 70INDEX RANGE SCAN | UN_CONTRAGENTRATING_LOG |  1 | | |  3  (0)| 00:00:01 | | | | | |

            71TABLE ACCESS BY INDEX ROWID | RISKTYPE |  116 | |  1  (0)| 00:00:01 | | | | | |

            |* 72INDEX UNIQUE SCAN | PK_RISKTYPE |  1 | | |  0  (0)| 00:00:01 | | | | | |

            73 | SORT AGGREGATE  | |  117 | | | | | | | | |

            74TABLE ACCESS BY INDEX ROWID | ARC_RESERVATIONREPORT |  117 | |  4  (0)| 00:00:01 | | | | | |

            |* 75INDEX RANGE SCAN | PK_ARC_RESERVATIONREPORT |  1 | | |  3  (0)| 00:00:01 | | | | | |

            76 | SORT AGGREGATE  | |  111 | | | | | | | | |

            |* 77TABLE ACCESS BY INDEX ROWID | RESERVEARCDATES |  111 | |  2  (0)| 00:00:01 | | | | | |

            |* 78INDEX RANGE SCAN | PK_RESERVEARCDATES |  1 | | |  1  (0)| 00:00:01 | | | | | |

            |* 79COUNT STOPKEY | | | | | | | | | | | |

            80 | NESTED LOOPS OUTER | |  140 | |  5  (0)| 00:00:01 | | | | | |

            |* 81TABLE ACCESS BY INDEX ROWID | CONTRAGENTRATING_LOG |  124 | |  4  (0)| 00:00:01 | | | | | |

            |* 82INDEX RANGE SCAN | UN_CONTRAGENTRATING_LOG |  1 | | |  3  (0)| 00:00:01 | | | | | |

            83TABLE ACCESS BY INDEX ROWID | RISKTYPE |  116 | |  1  (0)| 00:00:01 | | | | | |

            |* 84INDEX UNIQUE SCAN | PK_RISKTYPE |  1 | | |  0  (0)| 00:00:01 | | | | | |

            85 | SORT AGGREGATE  | |  117 | | | | | | | | |

            86TABLE ACCESS BY INDEX ROWID | ARC_RESERVATIONREPORT |  117 | |  4  (0)| 00:00:01 | | | | | |

            |* 87INDEX RANGE SCAN | PK_ARC_RESERVATIONREPORT |  1 | | |  3  (0)| 00:00:01 | | | | | |

            88 | SORT AGGREGATE  | |  111 | | | | | | | | |

            |* 89TABLE ACCESS BY INDEX ROWID | RESERVEARCDATES |  111 | |  2  (0)| 00:00:01 | | | | | |

            |* 90INDEX RANGE SCAN | PK_RESERVEARCDATES |  1 | | |  1  (0)| 00:00:01 | | | | | |

            91 | SORT AGGREGATE  | |  117 | | | | | | | | |

            92TABLE ACCESS BY INDEX ROWID | REP130_CANCELLATION |  1482516 | |  208  (8)| 00:00:01 | | | | | |

            |* 93INDEX SKIP SCAN | REP130_CANCELLATION1 |  148 | | |  204  (8)| 00:00:01 | | | | | |

            94 | SORT AGGREGATE  | |  117 | | | | | | | | |

            95TABLE ACCESS BY INDEX ROWID | REP130_CANCELLATION |  1482516 | |  208  (8)| 00:00:01 | | | | | |

            |* 96INDEX SKIP SCAN | REP130_CANCELLATION1 |  148 | | |  204  (8)| 00:00:01 | | | | | |

            97 | SORT AGGREGATE  | |  179 | | | | | | | | |

            98 | NESTED LOOPS | | | | | | | | | | | |

            99 | NESTED LOOPS | |  179 | |  158  (0)| 00:00:01 | | | | | |

            | 100 | NESTED LOOPS | |  143 | |  9  (0)| 00:00:01 | | | | | |

            | 101TABLE ACCESS BY INDEX ROWID | DEALCOMMERCIALTRANCHE |  112 | |  3  (0)| 00:00:01 | | | | | |

            |*102INDEX RANGE SCAN | IN_DEALCOMMERCIALTRANCHE_LOAN |  1 | | |  2  (0)| 00:00:01 | | | | | |

            |*103TABLE ACCESS BY INDEX ROWID | DEALDOCTRANSACTION |  131 | |  6  (0)| 00:00:01 | | | | | |

            |*104INDEX RANGE SCAN | I_DDT_DEALDOCTP |  3 | | |  3  (0)| 00:00:01 | | | | | |

            | 105PARTITION RANGE ALL  | |  1 | | |  148  (0)| 00:00:01174 | | | |

            |*106INDEX RANGE SCAN | PK_ARC_DOCUMENT |  1 | | |  148  (0)| 00:00:01174 | | | |

            |*107TABLE ACCESS BY LOCAL INDEX ROWID | ARC_DOCUMENT |  136 | |  149  (0)| 00:00:0111 | | | |

            | 108 | SORT AGGREGATE  | |  1104 | | | | | | | | |

            | 109 | NESTED LOOPS | | | | | | | | | | | |

            | 110 | NESTED LOOPS | |  1104 | |  159  (0)| 00:00:01 | | | | | |

            | 111 | NESTED LOOPS | |  168 | |  10  (0)| 00:00:01 | | | | | |

            | 112 | NESTED LOOPS | |  147 | |  9  (0)| 00:00:01 | | | | | |

            | 113TABLE ACCESS BY INDEX ROWID | DEALCOMMERCIALTRANCHE |  116 | |  3  (0)| 00:00:01 | | | | | |

            |*114INDEX RANGE SCAN | IN_DEALCOMMERCIALTRANCHE_LOAN |  1 | | |  2  (0)| 00:00:01 | | | | | |

            |*115TABLE ACCESS BY INDEX ROWID | DEALDOCTRANSACTION |  131 | |  6  (0)| 00:00:01 | | | | | |

            |*116INDEX RANGE SCAN | I_DDT_DEALDOCTP |  3 | | |  3  (0)| 00:00:01 | | | | | |

            |*117INDEX UNIQUE SCAN | PK_CURRENCYRATEALL |  121 | |  1  (0)| 00:00:01 | | | | | |

            | 118PARTITION RANGE ALL  | |  1 | | |  148  (0)| 00:00:01174 | | | |

            |*119INDEX RANGE SCAN | PK_ARC_DOCUMENT |  1 | | |  148  (0)| 00:00:01174 | | | |

            |*120TABLE ACCESS BY LOCAL INDEX ROWID | ARC_DOCUMENT |  136 | |  149  (0)| 00:00:0111 | | | |

            |*121HASH JOIN | |  129 | |  6  (17)| 00:00:01 | | | | | |

            |*122INDEX RANGE SCAN | I_CONTR_CRL_CLIENTLEVELID |  113 | |  2  (0)| 00:00:01 | | | | | |

            | 123 | SORT AGGREGATE  | |  118 | | | | | | | | |

            |*124TABLE ACCESS BY INDEX ROWID | CRL_CLIENTLEVEL |  118 | |  3  (0)| 00:00:01 | | | | | |

            |*125INDEX RANGE SCAN | I_CONTR_CRL_CLIENTLEVEL |  2 | | |  1  (0)| 00:00:01 | | | | | |

            | 126TABLE ACCESS FULL | CRL_RISKLEVEL |  8128 | |  3  (0)| 00:00:01 | | | | | |

            | 127TABLE ACCESS BY INDEX ROWID | CRL_DEALLEVEL |  114 | |  3  (0)| 00:00:01 | | | | | |

            |*128INDEX RANGE SCAN | I_DEALID_CRL_DEALLEVELID |  1 | | |  2  (0)| 00:00:01 | | | | | |

            | 129 | SORT AGGREGATE  | |  119 | | | | | | | | |

            |*130TABLE ACCESS BY INDEX ROWID | CRL_DEALLEVEL |  119 | |  3  (0)| 00:00:01 | | | | | |

            |*131INDEX RANGE SCAN | I_DEALID_CRL_DEALLEVEL |  2 | | |  1  (0)| 00:00:01 | | | | | |

            | 132TABLE ACCESS BY INDEX ROWID | CRL_DEALLEVEL |  114 | |  3  (0)| 00:00:01 | | | | | |

            |*133INDEX RANGE SCAN | I_DEALID_CRL_DEALLEVELID |  1 | | |  2  (0)| 00:00:01 | | | | | |

            | 134 | SORT AGGREGATE  | |  119 | | | | | | | | |

            |*135TABLE ACCESS BY INDEX ROWID | CRL_DEALLEVEL |  119 | |  3  (0)| 00:00:01 | | | | | |

            |*136INDEX RANGE SCAN | I_DEALID_CRL_DEALLEVEL |  2 | | |  1  (0)| 00:00:01 | | | | | |

            | 137MERGE JOIN CARTESIAN | |  124 | |  4  (0)| 00:00:01 | | | | | |

            | 138TABLE ACCESS BY INDEX ROWID | CRL_DEALLEVEL |  113 | |  3  (0)| 00:00:01 | | | | | |

            |*139INDEX RANGE SCAN | I_DEALID_CRL_DEALLEVELID |  1 | | |  2  (0)| 00:00:01 | | | | | |

            | 140 | SORT AGGREGATE  | |  119 | | | | | | | | |

            |*141TABLE ACCESS BY INDEX ROWID | CRL_DEALLEVEL |  119 | |  3  (0)| 00:00:01 | | | | | |

            |*142INDEX RANGE SCAN | I_DEALID_CRL_DEALLEVEL |  2 | | |  1  (0)| 00:00:01 | | | | | |

            | 143 | BUFFER SORT | |  111 | |  1  (0)| 00:00:01 | | | | | |

            | 144TABLE ACCESS BY INDEX ROWID | CRL_PARAM |  111 | |  1  (0)| 00:00:01 | | | | | |

            |*145INDEX RANGE SCAN | I_ARCDATE_CRL_PARAM |  1 | | |  0  (0)| 00:00:01 | | | | | |

            | 146 | SORT AGGREGATE  | |  18 | | | | | | | | |

            | 147 | FIRST ROW  | |  18 | |  1  (0)| 00:00:01 | | | | | |

            |*148INDEX RANGE SCAN (MIN/MAX) | I_ARCDATE_CRL_PARAM |  18 | |  1  (0)| 00:00:01 | | | | | |

            |*149HASH JOIN | |  129 | |  6  (17)| 00:00:01 | | | | | |

            |*150INDEX RANGE SCAN | I_CONTR_CRL_CLIENTLEVELID |  113 | |  2  (0)| 00:00:01 | | | | | |

            | 151 | SORT AGGREGATE  | |  118 | | | | | | | | |

            |*152TABLE ACCESS BY INDEX ROWID | CRL_CLIENTLEVEL |  118 | |  3  (0)| 00:00:01 | | | | | |

            |*153INDEX RANGE SCAN | I_CONTR_CRL_CLIENTLEVEL |  2 | | |  1  (0)| 00:00:01 | | | | | |

            | 154TABLE ACCESS FULL | CRL_RISKLEVEL |  8128 | |  3  (0)| 00:00:01 | | | | | |

            | 155TABLE ACCESS BY INDEX ROWID | CRL_DEALLEVEL |  114 | |  3  (0)| 00:00:01 | | | | | |

            |*156INDEX RANGE SCAN | I_DEALID_CRL_DEALLEVELID |  1 | | |  2  (0)| 00:00:01 | | | | | |

            | 157 | SORT AGGREGATE  | |  119 | | | | | | | | |

            |*158TABLE ACCESS BY INDEX ROWID | CRL_DEALLEVEL |  119 | |  3  (0)| 00:00:01 | | | | | |

            |*159INDEX RANGE SCAN | I_DEALID_CRL_DEALLEVEL |  2 | | |  1  (0)| 00:00:01 | | | | | |

            | 160TABLE ACCESS BY INDEX ROWID | CRL_DEALLEVEL |  114 | |  3  (0)| 00:00:01 | | | | | |

            |*161INDEX RANGE SCAN | I_DEALID_CRL_DEALLEVELID |  1 | | |  2  (0)| 00:00:01 | | | | | |

            | 162 | SORT AGGREGATE  | |  119 | | | | | | | | |

            |*163TABLE ACCESS BY INDEX ROWID | CRL_DEALLEVEL |  119 | |  3  (0)| 00:00:01 | | | | | |

            |*164INDEX RANGE SCAN | I_DEALID_CRL_DEALLEVEL |  2 | | |  1  (0)| 00:00:01 | | | | | |

            | 165MERGE JOIN CARTESIAN | |  124 | |  4  (0)| 00:00:01 | | | | | |

            | 166TABLE ACCESS BY INDEX ROWID | CRL_DEALLEVEL |  113 | |  3  (0)| 00:00:01 | | | | | |

            |*167INDEX RANGE SCAN | I_DEALID_CRL_DEALLEVELID |  1 | | |  2  (0)| 00:00:01 | | | | | |

            | 168 | SORT AGGREGATE  | |  119 | | | | | | | | |

            |*169TABLE ACCESS BY INDEX ROWID | CRL_DEALLEVEL |  119 | |  3  (0)| 00:00:01 | | | | | |

            |*170INDEX RANGE SCAN | I_DEALID_CRL_DEALLEVEL |  2 | | |  1  (0)| 00:00:01 | | | | | |

            | 171 | BUFFER SORT | |  111 | |  1  (0)| 00:00:01 | | | | | |

            | 172TABLE ACCESS BY INDEX ROWID | CRL_PARAM |  111 | |  1  (0)| 00:00:01 | | | | | |

            |*173INDEX RANGE SCAN | I_ARCDATE_CRL_PARAM |  1 | | |  0  (0)| 00:00:01 | | | | | |

            | 174 | SORT AGGREGATE  | |  18 | | | | | | | | |

            | 175 | FIRST ROW  | |  18 | |  1  (0)| 00:00:01 | | | | | |

            |*176INDEX RANGE SCAN (MIN/MAX) | I_ARCDATE_CRL_PARAM |  18 | |  1  (0)| 00:00:01 | | | | | |

            |*177COUNT STOPKEY | | | | | | | | | | | |

            |*178INDEX RANGE SCAN | I_RATINGCONT_LOG_CONTRDATE |  121 | |  3  (0)| 00:00:01 | | | | | |

            | 179 | SORT AGGREGATE  | |  121 | | | | | | | | |

            |*180INDEX RANGE SCAN | I_RATINGCONT_LOG_CONTRDATE |  121 | |  3  (0)| 00:00:01 | | | | | |

            |*181COUNT STOPKEY | | | | | | | | | | | |

            |*182INDEX RANGE SCAN | I_RATINGCONT_LOG_CONTRDATE |  121 | |  3  (0)| 00:00:01 | | | | | |

            | 183 | SORT AGGREGATE  | |  121 | | | | | | | | |

            |*184INDEX RANGE SCAN | I_RATINGCONT_LOG_CONTRDATE |  121 | |  3  (0)| 00:00:01 | | | | | |

            |*185COUNT STOPKEY | | | | | | | | | | | |

            | 186TABLE ACCESS BY INDEX ROWID | RATINGCONTRAGENT_LOG |  125 | |  4  (0)| 00:00:01 | | | | | |

            |*187INDEX RANGE SCAN | I_RATINGCONT_LOG_CONTRDATE |  1 | | |  3  (0)| 00:00:01 | | | | | |

            | 188 | SORT AGGREGATE  | |  121 | | | | | | | | |

            |*189INDEX RANGE SCAN | I_RATINGCONT_LOG_CONTRDATE |  121 | |  3  (0)| 00:00:01 | | | | | |

            |*190COUNT STOPKEY | | | | | | | | | | | |

            | 191TABLE ACCESS BY INDEX ROWID | RATINGCONTRAGENT_LOG |  125 | |  4  (0)| 00:00:01 | | | | | |

            |*192INDEX RANGE SCAN | I_RATINGCONT_LOG_CONTRDATE |  1 | | |  3  (0)| 00:00:01 | | | | | |

            | 193 | SORT AGGREGATE  | |  121 | | | | | | | | |

            |*194INDEX RANGE SCAN | I_RATINGCONT_LOG_CONTRDATE |  121 | |  3  (0)| 00:00:01 | | | | | |

            | 195 | SORT AGGREGATE  | |  147 | | | | | | | | |

            | 196TABLE ACCESS BY INDEX ROWID | Z129EXPREP |  147 | |  38  (0)| 00:00:01 | | | | | |

            | 197 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | | | |

            | 198 | BITMAP AND  | | | | | | | | | | | |

            | 199 | BITMAP CONVERSION FROM ROWIDS | | | | | | | | | | | |

            |*200INDEX RANGE SCAN | I_CONTRAGENTID_Z129EXPREP |  519 | | |  4  (0)| 00:00:01 | | | | | |

            | 201 | BITMAP CONVERSION FROM ROWIDS | | | | | | | | | | | |

            |*202INDEX RANGE SCAN | I_ARCDATE_Z129EXPREP |  519 | | |  34  (0)| 00:00:01 | | | | | |

            | 203 | SORT AGGREGATE  | |  147 | | | | | | | | |

            | 204TABLE ACCESS BY INDEX ROWID | Z129EXPREP |  147 | |  38  (0)| 00:00:01 | | | | | |

            | 205 | BITMAP CONVERSION TO ROWIDS | | | | | | | | | | | |

            | 206 | BITMAP AND  | | | | | | | | | | | |

            | 207 | BITMAP CONVERSION FROM ROWIDS | | | | | | | | | | | |

            |*208INDEX RANGE SCAN | I_CONTRAGENTID_Z129EXPREP |  519 | | |  4  (0)| 00:00:01 | | | | | |

            | 209 | BITMAP CONVERSION FROM ROWIDS | | | | | | | | | | | |

            |*210INDEX RANGE SCAN | I_ARCDATE_Z129EXPREP |  519 | | |  34  (0)| 00:00:01 | | | | | |

            |*211TABLE ACCESS BY INDEX ROWID | CONTRAGENTADDPARAM_HISTORY |  144 | |  4  (0)| 00:00:01 | | | | | |

            |*212INDEX RANGE SCAN | PK_CONTRAGENTADDPARAM_HISTORY |  1 | | |  3  (0)| 00:00:01 | | | | | |

            |*213TABLE ACCESS BY INDEX ROWID | CONTRAGENTADDPARAM_HISTORY |  144 | |  4  (0)| 00:00:01 | | | | | |

            |*214INDEX RANGE SCAN | PK_CONTRAGENTADDPARAM_HISTORY |  1 | | |  3  (0)| 00:00:01 | | | | | |

            |*215TABLE ACCESS BY INDEX ROWID | CONTRAGENTADDPARAM_HISTORY |  144 | |  4  (0)| 00:00:01 | | | | | |

            |*216INDEX RANGE SCAN | PK_CONTRAGENTADDPARAM_HISTORY |  1 | | |  3  (0)| 00:00:01 | | | | | |

            |*217TABLE ACCESS BY INDEX ROWID | CONTRAGENTADDPARAM_HISTORY |  144 | |  4  (0)| 00:00:01 | | | | | |

            |*218INDEX RANGE SCAN | PK_CONTRAGENTADDPARAM_HISTORY |  1 | | |  3  (0)| 00:00:01 | | | | | |

            |*219TABLE ACCESS FULL | VBUA_LEVEL_LOANRISK |  114 | |  3  (0)| 00:00:01 | | | | | |

            |*220TABLE ACCESS BY INDEX ROWID | CONTRAGENTADDPARAM_HISTORY |  144 | |  4  (0)| 00:00:01 | | | | | |

            |*221INDEX RANGE SCAN | PK_CONTRAGENTADDPARAM_HISTORY |  1 | | |  3  (0)| 00:00:01 | | | | | |

            |*222TABLE ACCESS FULL | VBUA_LEVEL_LOANRISK |  114 | |  3  (0)| 00:00:01 | | | | | |

            |*223TABLE ACCESS BY INDEX ROWID | CONTRAGENTADDPARAM_HISTORY |  144 | |  4  (0)| 00:00:01 | | | | | |

            |*224INDEX RANGE SCAN | PK_CONTRAGENTADDPARAM_HISTORY |  1 | | |  3  (0)| 00:00:01 | | | | | |

            | 225 | NESTED LOOPS | |  129 | |  3  (0)| 00:00:01 | | | | | |

            | 226TABLE ACCESS BY INDEX ROWID | DEALCOMMERCIALLOAN |  110 | |  2  (0)| 00:00:01 | | | | | |

            |*227INDEX UNIQUE SCAN | PK_DEALCOMMERCIALLOAN |  1 | | |  1  (0)| 00:00:01 | | | | | |

            | 228TABLE ACCESS BY INDEX ROWID | COMMERCIALLOANSTATE |  11209 | |  1  (0)| 00:00:01 | | | | | |

            |*229INDEX UNIQUE SCAN | PK_COMMERCIALLOANSTATE |  1 | | |  0  (0)| 00:00:01 | | | | | |

            | 230VIEW  | | 16337125M| |  1253K (9)| 00:00:20 | | | | | |

            | 231 | TEMP TABLE TRANSFORMATION | | | | | | | | | | | |

            | 232 | LOAD AS SELECT  | SYS_TEMP_0FD9D8FB4_E5C550EF | | | | | | | | | | |

            | 233 | NESTED LOOPS | |  110 | |  3  (0)| 00:00:01 | | | | | |

            |*234INDEX UNIQUE SCAN | PK_CALENDAR |  18 | |  1  (0)| 00:00:01 | | | | | |

            | 235 | COLLECTION ITERATOR PICKLER FETCH  | FN_VDEALREPORT |  12 | |  2  (0)| 00:00:01 | | | | | |

            | 236 | LOAD AS SELECT  | SYS_TEMP_0FD9D8FB5_E5C550EF | | | | | | | | | | |

            | 237 | NESTED LOOPS | |  110 | |  3  (0)| 00:00:01 | | | | | |

            |*238INDEX UNIQUE SCAN | PK_CALENDAR |  18 | |  1  (0)| 00:00:01 | | | | | |

            | 239 | COLLECTION ITERATOR PICKLER FETCH  | FN_VDEALREPORT |  12 | |  2  (0)| 00:00:01 | | | | | |

            | 240 | LOAD AS SELECT  | SYS_TEMP_0FD9D8FB6_E5C550EF | | | | | | | | | | |

            | 241 | INLIST ITERATOR | | | | | | | | | | | |

            | 242TABLE ACCESS BY INDEX ROWID | Z129EXPREP |  31605 | |  4  (0)| 00:00:01 | | | | | |

            |*243INDEX RANGE SCAN | I_ARCDATE_Z129EXPREP | 17074 | | |  3  (0)| 00:00:01 | | | | | |

            | 244 | NESTED LOOPS OUTER | | 16337105M| |  1253K (9)| 00:00:20 | | | | | |

            | 245 | NESTED LOOPS OUTER | | 16337105M| |  1249K (9)| 00:00:20 | | | | | |

            | 246 | NESTED LOOPS OUTER | | 16337105M| |  737K (12)| 00:00:12 | | | | | |

            | 247 | NESTED LOOPS OUTER | | 16337105M| |  226K (28)| 00:00:04 | | | | | |

            | 248 | NESTED LOOPS OUTER | | 1633773M| |  113K (28)| 00:00:02 | | | | | |

            | 249VIEW  | | 1633741M| |  1129  (1)| 00:00:01 | | | | | |

            | 250UNION-ALL  | | | | | | | | | | | |

            | 251 | SORT AGGREGATE  | |  148 | | | | | | | | |

            | 252 | PX COORDINATOR | | | | | | | | | | | |

            | 253 | PX SEND QC (RANDOM) | :TQ10000 |  148 | | | | | | Q1,00 | P->S | QC (RAND) |

            | 254 | SORT AGGREGATE  | |  148 | | | | | | Q1,00 | PCWP | |

            |*255VIEW  | | 15877744K| |  177  (2)| 00:00:01 | | | Q1,00 | PCWP | |

            | 256 | PX BLOCK ITERATOR | | 158778295K| |  177  (2)| 00:00:01 | | | Q1,00 | PCWC | |

            | 257TABLE ACCESS FULL | SYS_TEMP_0FD9D8FB6_E5C550EF | 158778295K| |  177  (2)| 00:00:01 | | | Q1,00 | PCWP | |

            | 258 | SORT AGGREGATE  | |  148 | | | | | | | | |

            | 259 | PX COORDINATOR | | | | | | | | | | | |

            | 260 | PX SEND QC (RANDOM) | :TQ20000 |  148 | | | | | | Q2,00 | P->S | QC (RAND) |

            | 261 | SORT AGGREGATE  | |  148 | | | | | | Q2,00 | PCWP | |

            |*262VIEW  | | 15877744K| |  177  (2)| 00:00:01 | | | Q2,00 | PCWP | |

            | 263 | PX BLOCK ITERATOR | | 158778295K| |  177  (2)| 00:00:01 | | | Q2,00 | PCWC | |

            | 264TABLE ACCESS FULL | SYS_TEMP_0FD9D8FB6_E5C550EF | 158778295K| |  177  (2)| 00:00:01 | | | Q2,00 | PCWP | |

            | 265 | SORT AGGREGATE  | |  148 | | | | | | | | |

            | 266 | PX COORDINATOR | | | | | | | | | | | |

            | 267 | PX SEND QC (RANDOM) | :TQ30000 |  148 | | | | | | Q3,00 | P->S | QC (RAND) |

            | 268 | SORT AGGREGATE  | |  148 | | | | | | Q3,00 | PCWP | |

            |*269VIEW  | | 15877744K| |  177  (2)| 00:00:01 | | | Q3,00 | PCWP | |

            | 270 | PX BLOCK ITERATOR | | 158778295K| |  177  (2)| 00:00:01 | | | Q3,00 | PCWC | |

            | 271TABLE ACCESS FULL | SYS_TEMP_0FD9D8FB6_E5C550EF | 158778295K| |  177  (2)| 00:00:01 | | | Q3,00 | PCWP | |

            | 272 | SORT AGGREGATE  | |  148 | | | | | | | | |

            | 273 | PX COORDINATOR | | | | | | | | | | | |

            | 274 | PX SEND QC (RANDOM) | :TQ40000 |  148 | | | | | | Q4,00 | P->S | QC (RAND) |

            | 275 | SORT AGGREGATE  | |  148 | | | | | | Q4,00 | PCWP | |

            |*276VIEW  | | 15877744K| |  177  (2)| 00:00:01 | | | Q4,00 | PCWP | |

            | 277 | PX BLOCK ITERATOR | | 158778295K| |  177  (2)| 00:00:01 | | | Q4,00 | PCWC | |

            | 278TABLE ACCESS FULL | SYS_TEMP_0FD9D8FB6_E5C550EF | 158778295K| |  177  (2)| 00:00:01 | | | Q4,00 | PCWP | |

            | 279 | SORT AGGREGATE  | |  148 | | | | | | | | |

            | 280 | PX COORDINATOR | | | | | | | | | | | |

            | 281 | PX SEND QC (RANDOM) | :TQ50000 |  148 | | | | | | Q5,00 | P->S | QC (RAND) |

            | 282 | SORT AGGREGATE  | |  148 | | | | | | Q5,00 | PCWP | |

            |*283VIEW  | | 15877744K| |  177  (2)| 00:00:01 | | | Q5,00 | PCWP | |

            | 284 | PX BLOCK ITERATOR | | 158778295K| |  177  (2)| 00:00:01 | | | Q5,00 | PCWC | |

            | 285TABLE ACCESS FULL | SYS_TEMP_0FD9D8FB6_E5C550EF | 158778295K| |  177  (2)| 00:00:01 | | | Q5,00 | PCWP | |

            | 286 | SORT AGGREGATE  | |  148 | | | | | | | | |

            | 287 | PX COORDINATOR | | | | | | | | | | | |

            | 288 | PX SEND QC (RANDOM) | :TQ60000 |  148 | | | | | | Q6,00 | P->S | QC (RAND) |

            | 289 | SORT AGGREGATE  | |  148 | | | | | | Q6,00 | PCWP | |

            |*290VIEW  | | 15877744K| |  177  (2)| 00:00:01 | | | Q6,00 | PCWP | |

            | 291 | PX BLOCK ITERATOR | | 158778295K| |  177  (2)| 00:00:01 | | | Q6,00 | PCWC | |

            | 292TABLE ACCESS FULL | SYS_TEMP_0FD9D8FB6_E5C550EF | 158778295K| |  177  (2)| 00:00:01 | | | Q6,00 | PCWP | |

            | 293 | SORT AGGREGATE  | |  148 | | | | | | | | |

            | 294 | PX COORDINATOR | | | | | | | | | | | |

            | 295 | PX SEND QC (RANDOM) | :TQ70000 |  148 | | | | | | Q7,00 | P->S | QC (RAND) |

            | 296 | SORT AGGREGATE  | |  148 | | | | | | Q7,00 | PCWP | |

            |*297VIEW  | | 15877744K| |  177  (2)| 00:00:01 | | | Q7,00 | PCWP | |

            | 298 | PX BLOCK ITERATOR | | 158778295K| |  177  (2)| 00:00:01 | | | Q7,00 | PCWC | |

            | 299TABLE ACCESS FULL | SYS_TEMP_0FD9D8FB6_E5C550EF | 158778295K| |  177  (2)| 00:00:01 | | | Q7,00 | PCWP | |

            | 300 | SORT AGGREGATE  | |  148 | | | | | | | | |

            | 301 | PX COORDINATOR | | | | | | | | | | | |

            | 302 | PX SEND QC (RANDOM) | :TQ80000 |  148 | | | | | | Q8,00 | P->S | QC (RAND) |

            | 303 | SORT AGGREGATE  | |  148 | | | | | | Q8,00 | PCWP | |

            |*304VIEW  | | 15877744K| |  177  (2)| 00:00:01 | | | Q8,00 | PCWP | |

            | 305 | PX BLOCK ITERATOR | | 158778295K| |  177  (2)| 00:00:01 | | | Q8,00 | PCWC | |

            | 306TABLE ACCESS FULL | SYS_TEMP_0FD9D8FB6_E5C550EF | 158778295K| |  177  (2)| 00:00:01 | | | Q8,00 | PCWP | |

            |*307HASH JOIN | |  128052144K|  1106  (1)| 00:00:01 | | | | | |

            |*308VIEW  | |  81682042K| |  6  (17)| 00:00:01 | | | | | |

            | 309TABLE ACCESS FULL | SYS_TEMP_0FD9D8FB5_E5C550EF |  8168 | 81680 | |  6  (17)| 00:00:01 | | | | | |

            |*310VIEW  | |  816819M| |  6  (17)| 00:00:01 | | | | | |

            | 311TABLE ACCESS FULL | SYS_TEMP_0FD9D8FB4_E5C550EF |  8168 | 81680 | |  6  (17)| 00:00:01 | | | | | |

            |*312HASH JOIN RIGHT ANTI | |  816819M| |  12  (17)| 00:00:01 | | | | | |

            | 313VIEW  | VW_SQ_1 |  8168103K| |  6  (17)| 00:00:01 | | | | | |

            |*314VIEW  | |  8168175K| |  6  (17)| 00:00:01 | | | | | |

            | 315TABLE ACCESS FULL | SYS_TEMP_0FD9D8FB5_E5C550EF |  8168 | 81680 | |  6  (17)| 00:00:01 | | | | | |

            |*316VIEW  | |  816819M| |  6  (17)| 00:00:01 | | | | | |

            | 317TABLE ACCESS FULL | SYS_TEMP_0FD9D8FB4_E5C550EF |  8168 | 81680 | |  6  (17)| 00:00:01 | | | | | |

            |*318HASH JOIN RIGHT ANTI | |  816819M| |  12  (17)| 00:00:01 | | | | | |

            | 319VIEW  | VW_SQ_2 |  8168103K| |  6  (17)| 00:00:01 | | | | | |

            |*320VIEW  | |  8168175K| |  6  (17)| 00:00:01 | | | | | |

            | 321TABLE ACCESS FULL | SYS_TEMP_0FD9D8FB5_E5C550EF |  8168 | 81680 | |  6  (17)| 00:00:01 | | | | | |

            |*322VIEW  | |  816819M| |  6  (17)| 00:00:01 | | | | | |

            | 323TABLE ACCESS FULL | SYS_TEMP_0FD9D8FB4_E5C550EF |  8168 | 81680 | |  6  (17)| 00:00:01 | | | | | |

            |*324VIEW  | |  12041 | |  7  (29)| 00:00:01 | | | | | |

            | 325VIEW  | |  135 | |  7  (29)| 00:00:01 | | | | | |

            | 326 | SORT GROUP BY  | |  148 | |  7  (29)| 00:00:01 | | | | | |

            |*327VIEW  | |  8168382K| |  6  (17)| 00:00:01 | | | | | |

            | 328TABLE ACCESS FULL | SYS_TEMP_0FD9D8FB4_E5C550EF |  8168 | 81680 | |  6  (17)| 00:00:01 | | | | | |

            |*329VIEW  | |  12041 | |  7  (29)| 00:00:01 | | | | | |

            | 330VIEW  | |  135 | |  7  (29)| 00:00:01 | | | | | |

            | 331 | SORT GROUP BY  | |  148 | |  7  (29)| 00:00:01 | | | | | |

            |*332VIEW  | |  8168382K| |  6  (17)| 00:00:01 | | | | | |

            | 333TABLE ACCESS FULL | SYS_TEMP_0FD9D8FB4_E5C550EF |  8168 | 81680 | |  6  (17)| 00:00:01 | | | | | |

            |*334VIEW  | |  113 | |  31  (4)| 00:00:01 | | | | | |

            | 335 | SORT GROUP BY  | |  82820 | |  31  (4)| 00:00:01 | | | | | |

            | 336 | NESTED LOOPS | |  82820 | |  30  (0)| 00:00:01 | | | | | |

            |*337INDEX UNIQUE SCAN | PK_CALENDAR |  18 | |  1  (0)| 00:00:01 | | | | | |

            |*338 | COLLECTION ITERATOR PICKLER FETCH| FN_VDEALCOMMITMENT |  82164 | |  29  (0)| 00:00:01 | | | | | |

            |*339VIEW  | |  113 | |  31  (4)| 00:00:01 | | | | | |

            | 340 | SORT GROUP BY  | |  82820 | |  31  (4)| 00:00:01 | | | | | |

            | 341 | NESTED LOOPS | |  82820 | |  30  (0)| 00:00:01 | | | | | |

            |*342INDEX UNIQUE SCAN | PK_CALENDAR |  18 | |  1  (0)| 00:00:01 | | | | | |

            |*343 | COLLECTION ITERATOR PICKLER FETCH | FN_VDEALCOMMITMENT |  82164 | |  29  (0)| 00:00:01 | | | | | |

            |*344TABLE ACCESS FULL | DUAL |  12 | |  0  (0)| 00:00:01 | | | | | |

            --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

             

            Predicate Information (identified by operation id):

            ---------------------------------------------------

             

               1 - filter(ROWNUM=1)

               2 - filter("U1"."CONTRAGENTID"=:B1)

               3 - access("U1"."ARCDATE"=:B1 AND "U1"."OWNERID"=:B2)

               4 - filter(ROWNUM=1)

               5 - filter("U1"."CONTRAGENTID"=:B1)

               6 - access("U1"."ARCDATE"=:B1 AND "U1"."OWNERID"=:B2)

               7 - filter(ROWNUM=1)

               8 - filter("U1"."CONTRAGENTID"=:B1)

               9 - access("U1"."ARCDATE"=:B1 AND "U1"."OWNERID"=:B2)

              10 - filter(ROWNUM=1)

              11 - filter("U1"."CONTRAGENTID"=:B1)

              12 - access("U1"."ARCDATE"=:B1 AND "U1"."OWNERID"=:B2)

              13 - filter(ROWNUM=1)

              14 - filter("U1"."CONTRAGENTID"=:B1)

              15 - access("U1"."ARCDATE"=:B1 AND "U1"."OWNERID"=:B2)

              16 - filter(ROWNUM=1)

              17 - filter("U1"."CONTRAGENTID"=:B1)

              18 - access("U1"."ARCDATE"=:B1 AND "U1"."OWNERID"=:B2)

              21 - access("D"."ARCDATE">=TO_DATE(TO_DATE(' 2012-12-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))+1 AND "D"."DEALID"=:B1 AND "D"."ARCDATE"<=TO_DATE(' 2013-09-30

              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

              filter("D"."DEALID"=:B1)

              24 - access("D"."ARCDATE">=TO_DATE(TO_DATE(' 2012-12-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))+1 AND "D"."DEALID"=:B1 AND "D"."ARCDATE"<=TO_DATE(' 2013-09-30

              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

              filter("D"."DEALID"=:B1)

              27 - access("D"."ARCDATE">=TO_DATE(TO_DATE(' 2012-12-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))+1 AND "D"."DEALID"=:B1 AND "D"."ARCDATE"<=TO_DATE(' 2013-09-30

              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

              filter("D"."DEALID"=:B1)

              30 - access("D"."ARCDATE">=TO_DATE(TO_DATE(' 2012-12-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))+1 AND "D"."DEALID"=:B1 AND "D"."ARCDATE"<=TO_DATE(' 2013-09-30

              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

              filter("D"."DEALID"=:B1)

              33 - access("D"."ARCDATE">=TO_DATE(TO_DATE(' 2012-12-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))+1 AND "D"."DEALID"=:B1 AND "D"."ARCDATE"<=TO_DATE(' 2013-09-30

              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

              filter("D"."DEALID"=:B1)

              36 - access("D"."ARCDATE">=TO_DATE(TO_DATE(' 2012-12-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))+1 AND "D"."DEALID"=:B1 AND "D"."ARCDATE"<=TO_DATE(' 2013-09-30

              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

              filter("D"."DEALID"=:B1)

              39 - access("D"."ARCDATE">=TO_DATE(TO_DATE(' 2012-12-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))+1 AND "D"."DEALID"=:B1 AND "D"."ARCDATE"<=TO_DATE(' 2013-09-30

              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

              filter("D"."DEALID"=:B1)

              42 - access("D"."ARCDATE">=TO_DATE(TO_DATE(' 2012-12-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))+1 AND "D"."DEALID"=:B1 AND "D"."ARCDATE"<=TO_DATE(' 2013-09-30

              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

              filter("D"."DEALID"=:B1)

              45 - access("D"."ARCDATE">=TO_DATE(TO_DATE(' 2012-12-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))+1 AND "D"."DEALID"=:B1 AND "D"."ARCDATE"<=TO_DATE(' 2013-09-30

              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

              filter("D"."DEALID"=:B1)

              48 - access("D"."ARCDATE">=TO_DATE(TO_DATE(' 2012-12-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))+1 AND "D"."DEALID"=:B1 AND "D"."ARCDATE"<=TO_DATE(' 2013-09-30

              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

              filter("D"."DEALID"=:B1)

              51 - access("D"."ARCDATE">=TO_DATE(TO_DATE(' 2012-12-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))+1 AND "D"."DEALID"=:B1 AND "D"."ARCDATE"<=TO_DATE(' 2013-09-30

              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

              filter("D"."DEALID"=:B1)

              54 - access("D"."ARCDATE">=TO_DATE(TO_DATE(' 2012-12-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))+1 AND "D"."DEALID"=:B1 AND "D"."ARCDATE"<=TO_DATE(' 2013-09-30

              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

              filter("D"."DEALID"=:B1)

              55 - filter(ROWNUM=1)

              57 - filter("CR"."VALIDTILL">=:B1)

              58 - access("CR"."CONTRAGENTID"=:B1 AND "CR"."VALIDFROM"<=:B2)

              filter("CR"."VALIDFROM"<=:B1)

              60 - access("CR"."CRATINGNBUID"="CN"."ID"(+))

              61 - filter(ROWNUM=1)

              63 - filter("CR"."VALIDTILL">=:B1)

              64 - access("CR"."CONTRAGENTID"=:B1 AND "CR"."VALIDFROM"<=:B2)

              filter("CR"."VALIDFROM"<=:B1)

              66 - access("CR"."CRATINGNBUID"="CN"."ID"(+))

              67 - filter(ROWNUM=1)

              69 - filter("CR"."VALIDTILL">=:B1)

              70 - access("CR"."CONTRAGENTID"=:B1 AND "CR"."VALIDFROM"<=:B2)

              filter("CR"."VALIDFROM"<=:B1)

              72 - access("CR"."RISKTYPEID"="RT"."ID"(+))

              75 - access("R"."ARCDATE"= (SELECT MAX("H"."ARCDATE") FROM "CREATOR"."RESERVEARCDATES" "H" WHERE "H"."ARCDATE"=:B1 AND "H"."ISFIXED"=1) AND "R"."DEALID"=:B2)

              77 - filter("H"."ISFIXED"=1)

              78 - access("H"."ARCDATE"=:B1)

              79 - filter(ROWNUM=1)

              81 - filter("CR"."VALIDTILL">=:B1)

              82 - access("CR"."CONTRAGENTID"=:B1 AND "CR"."VALIDFROM"<=:B2)

              filter("CR"."VALIDFROM"<=:B1)

              84 - access("CR"."RISKTYPEID"="RT"."ID"(+))

              87 - access("R"."ARCDATE"= (SELECT MAX("H"."ARCDATE") FROM "CREATOR"."RESERVEARCDATES" "H" WHERE "H"."ARCDATE"=:B1 AND "H"."ISFIXED"=1) AND "R"."DEALID"=:B2)

              89 - filter("H"."ISFIXED"=1)

              90 - access("H"."ARCDATE"=:B1)

              93 - access("D"."ARCDATE">=TO_DATE(TO_DATE(' 2012-12-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))+1 AND "D"."DEALID"=:B1 AND "D"."ARCDATE"<=TO_DATE(' 2013-09-30

              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

              filter("D"."DEALID"=:B1)

              96 - access("D"."ARCDATE">=TO_DATE(TO_DATE(' 2012-12-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))+1 AND "D"."DEALID"=:B1 AND "D"."ARCDATE"<=TO_DATE(' 2013-09-30

              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

              filter("D"."DEALID"=:B1)

            102 - access("D"."LOANDEALID"=:B1)

            103 - filter("T"."ARCDATE">=TO_DATE(TO_DATE(' 2012-12-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))+1 AND "T"."ARCDATE"<=TO_DATE(' 2013-09-30 00:00:00', 'syyyy-mm-dd

              hh24:mi:ss'))

            104 - access("T"."DEALID"="D"."DEALID" AND "T"."DEALDOCUMENTTYPEID"=2)