Select Left join 很慢！

最新推荐文章于 2024-08-20 23:04:08 发布

Shark曾_小凡仙

最新推荐文章于 2024-08-20 23:04:08 发布

阅读量6.4k

点赞数

分类专栏： Oracle 开发文章标签： join date wap insert null exception

本文链接：https://blog.csdn.net/ZengMuAnSha/article/details/4593340

版权

Oracle 开发专栏收录该内容

128 篇文章 0 订阅

订阅专栏

8:34 跑到01：24 约6个小时产生61万4009行

a: 1775 2 second
b:200 34
c:200 23
d:200 205
e:200 42
a left join b left join c 1775行54秒
a left join b left join c left join d 1775行306秒

create or replace procedure P_WAP_WEK_I_FINANCE_ANALYZ is

v_StartTime   Date default Sysdate;                                --开始时间
v_StartNum    number default dbms_utility.get_time; --计算结束时间
v_RowNum      Number Default 0;                             --影响行数
v_maxtime     Varchar2(6);
v_enddate     Date;
v_startday    Date;

Begin

Select Max(F_YEARWEEK) Into v_maxtime From   T_WAP_FINANCE_ANALYZ;

   v_enddate := trunc(Sysdate,'d')+1;

If V_MAXTIME Is Not Null Then
     v_startday := to_date(udf_weektodate(v_maxtime),'yyyy-mm-dd');
Else
     v_startday := to_date('2008-10-01','yyyy-mm-dd');
End If;


      Insert Into T_WAP_FINANCE_ANALYZ
      Select a.f_yearweek,a.f_week,a.f_day,a.f_Regchannel,f_fillUserNum,F_FillMoney,
             decode(f_fillUserNum,0,Null, F_FillMoney/f_fillUserNum) As f_fill_avg,
             f_fillNum,decode(f_fillUserNum,0,Null, f_fillNum/f_fillUserNum) As f_fill_avg_num,
             f_GetOutUserNum,f_GetOutMoney,f_GetOutNum,f_salary,f_bonus,
             decode(f_salary,0,Null, f_bonus/f_salary) As f_bonus_rate,
             Sysdate
      From
      (
          Select X.*, e.f_Regchannel
          From
          (
              select udf_weekofyear( v_startday + (level-1), 'yyyyiw') f_yearweek,
                            To_char( v_startday + (level-1), 'day') f_week,
                            (v_startday + (level-1)) As f_day
              from dual
              connect by level < v_enddate+1 - v_startday

          )X,(Select F_REGCHANNEL From T_base_wap_REGChannel g Where g.f_Regchannel <> '500wan' ) e
           Order By f_yearweek,f_week
      ) a

      Left Join
      (
          Select
                             udf_weekofyear(F_Date,'yyyyiw') f_yearweek,to_char(F_Date,'day') f_week,wr.f_Regchannel,
                  Count(Distinct fu.f_username) As f_fillUserNum,
                  Count(fu.f_Username) As f_fillNum,
                  Sum(F_FillMoney) As F_FillMoney

          From T_Base_User_Fill_Burse fu
          Inner Join T_Base_Userinfo ui       On fu.f_Username   = ui.f_Username
          Inner Join T_base_wap_RegChannel wr On ui.f_Regchannel = wr.f_Regchannel
          Where wr.f_Regchannel != '500wan'
          And   F_Date Between v_startday And v_enddate
          And   F_FillSuccess = 1
          Group By   udf_weekofyear(F_Date,'yyyyiw'),to_char(F_Date,'day'),f_Regchannel

       ) b On a.f_yearweek=b.f_yearweek And a.f_week=b.f_week And a.f_Regchannel=b.f_Regchannel

      Left Join
      (
        Select
                        udf_weekofyear(F_Date,'yyyyiw') f_yearweek,to_char(F_Date,'day') f_week,f_Regchannel,
               count(Distinct bug.f_username ) f_GetOutUserNum,
               Count( bug.f_username) As f_GetOutNum,
               Sum(F_GetOutMoney) As f_GetOutMoney
         From t_Base_User_GetOut_Money bug
         Inner Join t_Base_Userinfo ui On bug.f_Username=ui.f_Username
         Inner Join T_base_wap_REGChannel wr On ui.f_Regchannel = wr.f_Regchannel
         Where wr.f_Regchannel != '500wan'
         And   F_Date Between v_startday And v_enddate
         Group By udf_weekofyear(F_Date,'yyyyiw') ,to_char(F_Date,'day'),f_Regchannel
      ) c
      On    a.f_yearweek=c.f_yearweek And a.f_week = c.f_week And a.f_Regchannel = c.f_Regchannel

      Left Join
      (
          Select
                            udf_weekofyear(F_ExpectEndDate,'yyyyiw') f_yearweek,to_char(F_ExpectEndDate,'day') f_week,f_Regchannel,
                  Sum(F_TicketOutMoney) As f_salary
          From       T_Base_ProJ bpj
          Inner Join t_Base_Userinfo ui On bpj.f_Username=ui.f_Username
          Inner Join T_base_wap_REGChannel wr On ui.f_Regchannel = wr.f_Regchannel
          Where wr.f_Regchannel != '500wan'
          And   F_ExpectEndDate Between v_startday And v_enddate
          And   f_issuc = 1
          Group By   udf_weekofyear(F_ExpectEndDate,'yyyyiw'),to_char(F_ExpectEndDate,'day'),f_Regchannel
      ) d
        On    a.f_yearweek=d.f_yearweek And a.f_week=d.f_week And a.f_Regchannel=d.f_Regchannel

      Left Join
      (
          Select
                            udf_weekofyear(F_ExpectEndDate,'yyyyiw') f_yearweek,to_char(F_ExpectEndDate,'day') f_week,f_Regchannel,
                  Sum(Case When f_lotid = 9 Then F_Tax_After_Bonus Else F_Tax_Before_Bonus End ) As f_bonus
          From T_Base_Lot_Send_Bonus blsb
          Inner Join t_Base_Userinfo ui On blsb.f_Username=ui.f_Username
          Inner Join   T_base_wap_REGChannel wr On ui.f_Regchannel = wr.f_Regchannel
          Where wr.f_Regchannel != '500wan'
          And   F_ExpectEndDate Between v_startday And v_enddate
          And   F_Issuc = 1
          Group By   udf_weekofyear(F_ExpectEndDate,'yyyyiw'),to_char(F_ExpectEndDate,'day'),f_Regchannel
      ) e
        On    a.f_yearweek=d.f_yearweek And a.f_week=d.f_week And a.f_Regchannel=d.f_Regchannel

      Order By a.f_yearweek,a.f_week,a.f_Regchannel;


    v_RowNum := sql%rowcount;
    Commit;


P_BASE_DAY_I_SPRUN_LOG('WAP','P_WAP_WEK_I_FINANCE_ANALYZ',v_StartTime,sysdate,'成功','INSERT',v_RowNum,v_StartNum);
    commit;
   EXCEPTION
     WHEN OTHERS THEN
       ROLLBACK;
       v_RowNum := sql%rowcount;

       P_BASE_DAY_I_SPRUN_LOG('WAP','P_WAP_WEK_I_FINANCE_ANALYZ',v_StartTime,sysdate,sqlerrm,'INSERT',v_RowNum,v_StartNum);

end ;

执行计划：