oracle left join 速度慢,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 ;

执行计划：

问题找出来后的执行计划