Select Left join 很慢！

 

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 ;

执行计划：

 

问题找出来后的执行计划