优化笔记:pfyhparopenfundinfotest_D_20140916.gz

性能瓶颈在函数的乱用。原代码黄色部分。

 12分钟->35秒

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

1.对两个函数调用多次，而且两个函数之间还有调用关系。（优化器是可以自动把函数体拆出来，拼到主查询里面一起优化的。但是太复杂了它也蒙。）

IsSpecial和gettradedate函数都是从1010表拿数据。

IsSpecial扫2次1010表

Gettradedate扫1次1010表

而4603表有25万多数据，对这个查询用了2次IsSpecial和3次Gettradedate，最糟糕的情况对1010表的扫描次数大约25万*（2+3）=125万次

用with把1010逻辑提出来，然后通过cross join连接到主查询，只扫一次。

2.这个案例跟昨天那个代码结构相似，但是情况正好相反。

   如果把or改成union，性能反而会变慢。（昨天的把or改成union性能会变快）

 开始判断性能瓶颈的时候发生了点悲剧。4603表有25万的数据，唯一性索引在OB_OBJECT_ID上面（错看成在f1_4603上了，o(╯□╰)o），而我们需要的只有2500左右，而且与1090相连用的是f1_4603.

 所以错认为了走4603的索引效率会变高。

 其实这个案例，不管索引在OB_OBJECT_ID还是f1_4603上面都没有用。因为真正把数据从25万筛选成2500的条件是在f3_4603上面，而f3上面没有索引，所以必须全表扫描。

 所以，如果把or改成了union，本来对4603表的全扫描只扫1次，就会变成2次，效率反而降低。（昨天的案例把or改为union，从1次全表扫描变为2次索引扫描，效率提升）。

3.其他相似子查询，可按之前的办法合并。

-------------------------------优化后代码----------------------------------------------

withtdas

 (selectmax(f1_1010) -min(f1_1010) special,

        max(f1_1010) ed,

        min(f1_1010) sd

   from(selectf1_1010

           fromwind.tb_object_1010

          wheref1_1010 < to_char(&PlanTime,'yyyymmdd')

          orderbyf1_1010desc)

  whererownum<=2)

select*

 from(Selectcase

                whenf16_1090like'%!%'then

                 substr(f16_1090,1,instr(f16_1090,'!') -1)

                else

                  F16_1090

              endZqdm,

              case

                whennvl(F6_4603,0) =0then

                 nvl(F4_4603,0)

                else

                 nvl(F6_4603,0)

              endNet_value,

              0Begin_Net,

              case

                whennvl(f4_4603,0) <>0then

                 nvl(nvl(F5_4603, f4_4603),0)

                whennvl(f4_4603,0) =0then

                 nvl(F5_4603,0)

              endTotal_net,

              ---除货币基金累计未公布时，取单位净值[20140507]

              0Adjust_net,

              0Adjust_Int,

              Case

                Whenexists(select'1'

                        fromwind.tb_object_1101

                       wheref14_1101 = f2_1090

                         andf13_1101 = f3_4603

                         andf15_1101 =1)Then

                 'Y'

                Else

                 'N'

              Enddiv_date,

               F3_4603 data_date,

              Case

                WhenExists(Select'1'

                        FromWind.tb_object_4521

                       Wheref1_4521 = f16_1090

                         Andf14_4521 = F3_4603)Then

                  (Selectf9_4521

                    FromWind.tb_object_4521

                   Wheref1_4521 = f16_1090

                     Andf14_4521 = F3_4603)

                Whenf5_1090 ='上海'Then

                 'H'

                Whenf5_1090 ='深圳'Then

                 'S'

                Else

                 'Y'

              Endmarket,

              Case

                WhenExists(Select'1'

                        FromWind.tb_object_4521

                       Wheref1_4521 = f16_1090

                         Andf14_4521 = F3_4603)Then

                  (Selectf15_4521

                    FromWind.tb_object_4521

                   Wheref1_4521 = f16_1090

                     Andf14_4521 = F3_4603)

                ELSE

                  F100_1099

              ENDfund_type,

               (select(F5_1115 *10000)

                 fromwind.tb_object_1115 a

                wheref1_1115 = f2_1090

                  anda.f2_1115 = (SelectMax(b.F2_1115)

                                     Fromwind.tb_object_1115 b

                                    Wherea.f1_1115 = b.f1_1115

                                      Andb.f2_1115 <= F3_4603)) Total_amt,

              NullAsBataValue

         Fromwind.tb_object_1090

        innerjoinwind.tb_object_4603 b

           onF1_4603 = f2_1090

        innerjoinwind.tb_object_1099

           onf1_1099 = F2_1090

        crossjointd

        Where(f19_1090 ='0'orf18_1090 >= to_char(&PlanTime,'yyyymmdd'))

          and((td.special =1andtd.ed = F3_4603)or

               (td.special !=1andF3_4603 > td.sdandF3_4603 <= td.ed)))

 wherelength(zqdm) <=6

unionall

Selectcase

        whenf16_1090 ='TZ0001'then

         'TZ0002'

        whenf16_1090 ='TZ0002'then

         'TZ0001'

      ---小写转大写---

        elSe

         UPPER(f16_1090)

      endZqdm,

      nvl(F3_4141,0) Net_value,

      0Begin_Net,

      case

        whenexists(select'1'

                fromwind.tb_object_1744

               wheref1_1744 = f1_4141

                  andf6_1744 ='货币市场型')then

         0

        else

         nvl(nvl(F4_4141, F3_4141),0)

      endTotal_net,

      0Adjust_net,

      0Adjust_Int,

      'N'div_date,

       f2_4141 data_date,

      'Y'market,

      nvl((selectcase

                   whenf6_1744 ='货币市场型'then

                    '货币型'

                   else

                     f6_1744

                 end

            fromwind.tb_object_1744

           wheref1_1744 = f1_4141

             andf6_1744 < >'FOF'),

          '债券型') fund_type,

      nvl((Selectf7_1772

            Fromwind.tb_object_1772 b

           Wheref1_1772 = f2_1090

             Andf2_1772 = (SelectMax(f2_1772)

                              Fromwind.tb_object_1772

                             Whereb.f1_1772 = f1_1772)),

          0) Total_amt,

      nullBataValue

 Fromwind.tb_object_1090, wind.tb_object_4141 a

 Wheref1_4141 = f2_1090

  Andf2_4141 = (SelectMax(f1_0012)

                   Fromwind.tb_object_0012

                  Wheref1_0012 < to_char(&PlanTime,'yyyymmdd')

                    Andf3_0012 ='CN'

                    Andf4_0012 =1

                    AndF2_0012 ='SSE')

  ANDF4_1090 ='LC'

union

SelectF16_1090 Zqdm,

      nvl(F4_1449,0) Net_value,

      1Begin_Net,

      0Total_net,

      0Adjust_net,

      0Adjust_Int,

      'N'div_date,

       F2_1449 data_date,

      Case

        Whenf5_1090 ='上海'Then

         'H'

        Whenf5_1090 ='深圳'Then

         'S'

        Else

         'Y'

      Endmarket,

      case

        whenexists(select'1'

                fromwind.tb_object_1400

               wheref2_1400 = f2_1090

                 andf6_1400 ='1'

                 andf3_1400 ='2001010503')then

         'QDII'

        else

          (selectdecode(F100_1099,'货币市场型','货币型', f100_1099)

            fromwind.tb_object_1099

           wheref1_1099 = f2_1090)

      endfund_type,

       (selectF5_1115 *10000

          fromwind.tb_object_1115 b

        wheref1_1115 = f2_1090

          Andb.f2_1115 = (SelectMax(F2_1115)

                             Fromwind.tb_object_1115

                            Wheref1_1115 = f2_1090

                              Andf2_1115 < = f6_1449)) Total_amt,

       (SELECTF4_5052

         FROMWIND.TB_OBJECT_5052

        WHEREF3_5052 ='613007000'

          ANDF1_5052 = F2_1090

          ANDF2_5052 =

               (SELECTMAX(F2_5052)

                 FROMWIND.TB_OBJECT_5052

                WHEREF1_5052 = F2_1090

                  ANDF3_5052 ='613007000'

                  ANDF2_5052 <= TO_CHAR(&PlanTime,'YYYYMMDD'))) BataValue

 Fromwind.tb_object_1090, wind.tb_object_1449 a

 WhereF1_1449 = f2_1090

  Andf19_1090 ='0'

  andlength(f16_1090) < =6

  andF3_1449 =

       (selectmax(F16_1101)

         fromwind.tb_object_1101

        whereF16_1101 <= to_char(&PlanTime,'yyyymmdd'))

  ANDNOTEXISTS(SELECT'1 '

         fromWIND.TB_OBJECT_4603

         WHEREF3_4603 = F2_1449

          ANDF1_4603 = F1_1449)

------------------------原代码-------------------------------------------------------------

函数1：

createorreplacefunctionwind.GetTradeDate(p_startvarchar2,p_incnumber)returnvarchar2is

 Resultvarchar2(10);

begin

 ifp_startisnullthen

   returnnull;

 endif;

 ifp_inc<0then

   selectmin(f1_1010)intoResult

     from(selectf1_1010fromwind.tb_object_1010

     wheref1_1010<p_startorderbyf1_1010desc)

     whererownum<=abs(p_inc);

 elsifp_inc>0then

   selectmax(f1_1010)intoResult

     from(selectf1_1010fromwind.tb_object_1010

     wheref1_1010>p_startorderbyf1_1010)

     whererownum<=abs(p_inc);

 else

   Result:=p_start;

 endif;

 return(Result);

endGetTradeDate;

函数2：

createorreplacefunctionwind.IsSpecial(strDatevarchar2)returnnumberis

  lastTradeDayvarchar2(8);

  nCountnumber;

begin

  lastTradeDay := gettradeDate(strDate,-1);

 selectcount(1)intonCount

   fromwind.tb_object_1010

  wheref1_1010 = to_char((to_date(lastTradeDay,'yyyymmdd') -1),'yyyymmdd');

 return1- nCount;

select*

 from(Selectcase

                whenf16_1090like'%!%'then

                 substr(f16_1090,1,instr(f16_1090,'!') -1)

                else

                  F16_1090

              endZqdm,

              case

                whennvl(F6_4603,0) =0then

                 nvl(F4_4603,0)

                else

                 nvl(F6_4603,0)

              endNet_value,

              0Begin_Net,

              case

                whennvl(f4_4603,0) <>0then

                 nvl(nvl(F5_4603, f4_4603),0)

                whennvl(f4_4603,0) =0then

                 nvl(F5_4603,0)

              endTotal_net,

              ---除货币基金累计未公布时，取单位净值[20140507]

              0Adjust_net,

              0Adjust_Int,

              Case

                Whenexists(select'1'

                         fromwind.tb_object_1101

                       wheref14_1101 = f2_1090

                         andf13_1101 = f3_4603

                         andf15_1101 =1)Then

                 'Y'

                Else

                 'N'

              Enddiv_date,

               F3_4603 data_date,

              Case

                WhenExists(Select'1'

                        FromWind.tb_object_4521

                       Wheref1_4521 = f16_1090

                         Andf14_4521 = F3_4603)Then

                  (Selectf9_4521

                    FromWind.tb_object_4521

                   Wheref1_4521 = f16_1090

                     Andf14_4521 = F3_4603)

                Whenf5_1090 ='上海'Then

                 'H'

                Whenf5_1090 ='深圳'Then

                 'S'

                Else

                 'Y'

              Endmarket,

              Case

                WhenExists(Select'1'

                        FromWind.tb_object_4521

                       Wheref1_4521 = f16_1090

                         Andf14_4521 = F3_4603)Then

                  (Selectf15_4521

                    FromWind.tb_object_4521

                   Wheref1_4521 = f16_1090

                      Andf14_4521 = F3_4603)

                ELSE

                  F100_1099

              ENDfund_type,

               (select(F5_1115 *10000)

                 fromwind.tb_object_1115 a

                wheref1_1115 = f2_1090

                  anda.f2_1115 = (SelectMax(b.F2_1115)

                                     Fromwind.tb_object_1115 b

                                    Wherea.f1_1115 = b.f1_1115

                                      Andb.f2_1115 <= F3_4603)) Total_amt,

              NullAsBataValue

         Fromwind.tb_object_1090,

               wind.tb_object_4603 b,

               wind.tb_object_1099

        WhereF1_4603 = f2_1090

          Andf1_1099 = F2_1090

          And(f19_1090 ='0'orf18_1090 >= to_char(&PlanTime,'yyyymmdd'))

          and(((IsSpecial(to_char(&PlanTime,'yyyymmdd'))) =0and

               gettradedate(to_char(&PlanTime,'yyyymmdd'), -1) = F3_4603)or

               (IsSpecial(to_char(&PlanTime,'yyyymmdd')) =1and

               F3_4603 >

               gettradedate(gettradedate(to_char(&PlanTime,'yyyymmdd'),

                                           -1),

                              -1)and

               F3_4603 <= gettradedate(to_char(&PlanTime,'yyyymmdd'), -1))))

 wherelength(zqdm) <=6

unionall

Selectcase

        whenf16_1090 ='TZ0001'then

         'TZ0002'

        whenf16_1090 ='TZ0002'then

         'TZ0001'

      ---小写转大写---

        elSe

         UPPER(f16_1090)

      endZqdm,

      nvl(F3_4141,0) Net_value,

      0Begin_Net,

      case

        whenexists(select'1'

                fromwind.tb_object_1744

               wheref1_1744 = f1_4141

                 andf6_1744 ='货币市场型')then

         0

        else

         nvl(nvl(F4_4141, F3_4141),0)

      endTotal_net,

      0Adjust_net,

      0Adjust_Int,

      'N'div_date,

       f2_4141 data_date,

      'Y'market,

      nvl((selectcase

                   whenf6_1744 ='货币市场型'then

                    '货币型'

                   else

                     f6_1744

                 end

            fromwind.tb_object_1744

           wheref1_1744 = f1_4141

             andf6_1744 < >'FOF'),

          '债券型') fund_type,

      nvl((Selectf7_1772

            Fromwind.tb_object_1772 b

           Wheref1_1772 = f2_1090

             Andf2_1772 = (SelectMax(f2_1772)

                              Fromwind.tb_object_1772

                             Whereb.f1_1772 = f1_1772)),

          0) Total_amt,

      nullBataValue

 Fromwind.tb_object_1090, wind.tb_object_4141 a

 Wheref1_4141 = f2_1090

  Andf2_4141 = (SelectMax(f1_0012)

                   Fromwind.tb_object_0012

                  Wheref1_0012 < to_char(&PlanTime,'yyyymmdd')

                    Andf3_0012 ='CN'

                    Andf4_0012 =1

                    AndF2_0012 ='SSE')

  ANDF4_1090 ='LC'

union

SelectF16_1090 Zqdm,

      nvl(F4_1449,0) Net_value,

      1Begin_Net,

      0Total_net,

      0Adjust_net,

      0Adjust_Int,

      'N'div_date,

       F2_1449 data_date,

      Case

        Whenf5_1090 ='上海'Then

         'H'

        Whenf5_1090 ='深圳'Then

         'S'

        Else

         'Y'

      Endmarket,

      case

        whenexists(select'1'

                fromwind.tb_object_1400

               wheref2_1400 = f2_1090

                 andf6_1400 ='1'

                 andf3_1400 ='2001010503')then

         'QDII'

        else

          (selectdecode(F100_1099,'货币市场型','货币型', f100_1099)

            fromwind.tb_object_1099

           wheref1_1099 = f2_1090)

      endfund_type,

       (selectF5_1115 *10000

         fromwind.tb_object_1115 b

        wheref1_1115 = f2_1090

          Andb.f2_1115 = (SelectMax(F2_1115)

                             Fromwind.tb_object_1115

                            Wheref1_1115 = f2_1090

                              Andf2_1115 < = f6_1449)) Total_amt,

       (SELECTF4_5052

         FROMWIND.TB_OBJECT_5052

        WHEREF3_5052 ='613007000'

          ANDF1_5052 = F2_1090

          ANDF2_5052 =

               (SELECTMAX(F2_5052)

                 FROMWIND.TB_OBJECT_5052

                WHEREF1_5052 = F2_1090

                  ANDF3_5052 ='613007000'

                  ANDF2_5052 <= TO_CHAR(&PlanTime,'YYYYMMDD'))) BataValue

 Fromwind.tb_object_1090, wind.tb_object_1449 a

 WhereF1_1449 = f2_1090

  Andf19_1090 ='0'

  andlength(f16_1090) < =6

  andF3_1449 =

       (selectmax(F16_1101)

         fromwind.tb_object_1101

        whereF16_1101 <= to_char(&PlanTime,'yyyymmdd'))

  ANDNOTEXISTS(SELECT'1 '

         fromWIND.TB_OBJECT_4603

        WHEREF3_4603 = F2_1449

          ANDF1_4603 = F1_1449)