Select Left join 很慢！

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

iteye_3691

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

阅读量109

点赞数

文章标签： ui

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 ;

执行计划：