优化笔记:pfyhparopenfundinfotest_D_20140916.gz

2019年11月12日 阅读数:127
这篇文章主要向大家介绍优化笔记:pfyhparopenfundinfotest_D_20140916.gz,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

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

 

 

 

 

 12分钟->35秒函数

---------------------------------------------------------------------------------------------性能

 

1.对两个函数调用屡次,并且两个函数之间还有调用关系。(优化器是能够自动把函数体拆出来,拼到主查询里面一块儿优化的。可是太复杂了它也蒙。)优化

 

IsSpecialgettradedate函数都是从1010表拿数据。spa

 

IsSpecial 21010code

 

Gettradedate 11010orm

 

4603表有25万多数据,对这个查询用了2IsSpecial 3 Gettradedate最糟糕的状况对1010表的扫描次数大约25*2+3 =125万次索引

 

with1010逻辑提出来,而后经过cross join链接到主查询,只扫一次。ci

 

 

 

2.这个案例跟昨天那个代码结构类似,可是状况正好相反。element

 

   若是把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.其余类似子查询,可按以前的办法合并。

 

 

 

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

 

with td as

 

 (select max(f1_1010) - min(f1_1010) special,

 

         max(f1_1010) ed,

 

         min(f1_1010) sd

 

    from (select f1_1010

 

            from wind.tb_object_1010

 

           where f1_1010 < to_char(&PlanTime, 'yyyymmdd')

 

           order by f1_1010 desc)

 

   where rownum <= 2)

 

select *

 

  from (Select case

 

                 when f16_1090 like '%!%' then

 

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

 

                 else

 

                  F16_1090

 

               end Zqdm,

 

               case

 

                 when nvl(F6_4603, 0) = 0 then

 

                  nvl(F4_4603, 0)

 

                 else

 

                  nvl(F6_4603, 0)

 

               end Net_value,

 

               0 Begin_Net,

 

               case

 

                 when nvl(f4_4603, 0) <> 0 then

 

                  nvl(nvl(F5_4603, f4_4603), 0)

 

                 when nvl(f4_4603, 0) = 0 then

 

                  nvl(F5_4603, 0)

 

               end Total_net,

 

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

 

               0 Adjust_net,

 

               0 Adjust_Int,

 

               Case

 

                 When exists (select '1'

 

                         from wind.tb_object_1101

 

                        where f14_1101 = f2_1090

 

                          and f13_1101 = f3_4603

 

                          and f15_1101 = 1) Then

 

                  'Y'

 

                 Else

 

                  'N'

 

               End div_date,

 

               F3_4603 data_date,

 

               Case

 

                 When Exists (Select '1'

 

                         From Wind.tb_object_4521

 

                        Where f1_4521 = f16_1090

 

                          And f14_4521 = F3_4603) Then

 

                  (Select f9_4521

 

                     From Wind.tb_object_4521

 

                    Where f1_4521 = f16_1090

 

                      And f14_4521 = F3_4603)

 

                 When f5_1090 = '上海' Then

 

                  'H'

 

                 When f5_1090 = '深圳' Then

 

                  'S'

 

                 Else

 

                  'Y'

 

               End market,

 

               Case

 

                 When Exists (Select '1'

 

                         From Wind.tb_object_4521

 

                        Where f1_4521 = f16_1090

 

                          And f14_4521 = F3_4603) Then

 

                  (Select f15_4521

 

                     From Wind.tb_object_4521

 

                    Where f1_4521 = f16_1090

 

                      And f14_4521 = F3_4603)

 

                 ELSE

 

                  F100_1099

 

               END fund_type,

 

               (select (F5_1115 * 10000)

 

                  from wind.tb_object_1115 a

 

                 where f1_1115 = f2_1090

 

                   and a.f2_1115 = (Select Max(b.F2_1115)

 

                                      From wind.tb_object_1115 b

 

                                     Where a.f1_1115 = b.f1_1115

 

                                       And b.f2_1115 <= F3_4603)) Total_amt,

 

               Null As BataValue

 

          From wind.tb_object_1090

 

         inner join wind.tb_object_4603 b

 

            on F1_4603 = f2_1090

 

         inner join wind.tb_object_1099

 

            on f1_1099 = F2_1090

 

         cross join td

 

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

 

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

 

               (td.special != 1 and F3_4603 > td.sd and F3_4603 <= td.ed)))

 

 where length(zqdm) <= 6

 

union all

 

Select case

 

         when f16_1090 = 'TZ0001' then

 

          'TZ0002'

 

         when f16_1090 = 'TZ0002' then

 

          'TZ0001'

 

       ---小写转大写---

 

         elSe

 

          UPPER(f16_1090)

 

       end Zqdm,

 

       nvl(F3_4141, 0) Net_value,

 

       0 Begin_Net,

 

       case

 

         when exists (select '1'

 

                 from wind.tb_object_1744

 

                where f1_1744 = f1_4141

 

                  and f6_1744 = '货币市场型') then

 

          0

 

         else

 

          nvl(nvl(F4_4141, F3_4141), 0)

 

       end Total_net,

 

       0 Adjust_net,

 

       0 Adjust_Int,

 

       'N' div_date,

 

       f2_4141 data_date,

 

       'Y' market,

 

       nvl((select case

 

                    when f6_1744 = '货币市场型' then

 

                     '货币型'

 

                    else

 

                     f6_1744

 

                  end

 

             from wind.tb_object_1744

 

            where f1_1744 = f1_4141

 

              and f6_1744 < > 'FOF'),

 

           '债券型') fund_type,

 

       nvl((Select f7_1772

 

             From wind.tb_object_1772 b

 

            Where f1_1772 = f2_1090

 

              And f2_1772 = (Select Max(f2_1772)

 

                               From wind.tb_object_1772

 

                              Where b.f1_1772 = f1_1772)),

 

           0) Total_amt,

 

       null BataValue

 

  From wind.tb_object_1090, wind.tb_object_4141 a

 

 Where f1_4141 = f2_1090

 

   And f2_4141 = (Select Max(f1_0012)

 

                    From wind.tb_object_0012

 

                   Where f1_0012 < to_char(&PlanTime, 'yyyymmdd')

 

                     And f3_0012 = 'CN'

 

                     And f4_0012 = 1

 

                     And F2_0012 = 'SSE')

 

   AND F4_1090 = 'LC'

 

union

 

 

 

Select F16_1090 Zqdm,

 

       nvl(F4_1449, 0) Net_value,

 

       1 Begin_Net,

 

       0 Total_net,

 

       0 Adjust_net,

 

       0 Adjust_Int,

 

       'N' div_date,

 

       F2_1449 data_date,

 

       Case

 

         When f5_1090 = '上海' Then

 

          'H'

 

         When f5_1090 = '深圳' Then

 

          'S'

 

         Else

 

          'Y'

 

       End market,

 

       case

 

         when exists (select '1'

 

                 from wind.tb_object_1400

 

                where f2_1400 = f2_1090

 

                  and f6_1400 = '1'

 

                  and f3_1400 = '2001010503') then

 

          'QDII'

 

         else

 

          (select decode(F100_1099, '货币市场型', '货币型', f100_1099)

 

             from wind.tb_object_1099

 

            where f1_1099 = f2_1090)

 

       end fund_type,

 

       (select F5_1115 * 10000

 

          from wind.tb_object_1115 b

 

         where f1_1115 = f2_1090

 

           And b.f2_1115 = (Select Max(F2_1115)

 

                              From wind.tb_object_1115

 

                             Where f1_1115 = f2_1090

 

                               And f2_1115 < = f6_1449)) Total_amt,

 

       (SELECT F4_5052

 

          FROM WIND.TB_OBJECT_5052

 

         WHERE F3_5052 = '613007000'

 

           AND F1_5052 = F2_1090

 

           AND F2_5052 =

 

               (SELECT MAX(F2_5052)

 

                  FROM WIND.TB_OBJECT_5052

 

                 WHERE F1_5052 = F2_1090

 

                   AND F3_5052 = '613007000'

 

                   AND F2_5052 <= TO_CHAR(&PlanTime, 'YYYYMMDD'))) BataValue

 

  From wind.tb_object_1090, wind.tb_object_1449 a

 

 Where F1_1449 = f2_1090

 

   And f19_1090 = '0'

 

   and length(f16_1090) < = 6

 

   and F3_1449 =

 

       (select max(F16_1101)

 

          from wind.tb_object_1101

 

         where F16_1101 <= to_char(&PlanTime, 'yyyymmdd'))

 

   AND NOT EXISTS (SELECT '1 '

 

          from WIND.TB_OBJECT_4603

 

         WHERE F3_4603 = F2_1449

 

           AND F1_4603 = F1_1449)

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

函数1

 

create or replace function wind.GetTradeDate(p_start varchar2,p_inc number) return varchar2 is

 

  Result varchar2(10);

 

begin

 

  if p_start is null then

 

    return null;

 

  end if;

 

 

 

  if p_inc<0 then

 

    select min(f1_1010) into Result

 

      from (select f1_1010 from wind.tb_object_1010

 

      where f1_1010<p_start order by f1_1010 desc)

 

      where rownum<=abs(p_inc);

 

  elsif p_inc>0 then

 

    select max(f1_1010) into Result

 

      from (select f1_1010 from wind.tb_object_1010

 

      where f1_1010>p_start order by f1_1010)

 

      where rownum<=abs(p_inc);

 

  else

 

    Result:=p_start;

 

  end if;

 

 

 

  return(Result);

 

end GetTradeDate;

 

 

 

函数2

 

 

 

create