---
title: "Select Left join 很慢！"
author: "winzenghua"
date: "2009-09-25T11:24:00Z"
tags: []
slug: "select-left-join"
draft: false
lastmod: "2009-09-25T11:24:00Z"
id: 987844
url: "https://java4gis.iteye.com/blog/987844"
---

&lt;!-- \[if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt; &lt;w:View&gt;Normal&lt;/w:View&gt; &lt;w:Zoom&gt;0&lt;/w:Zoom&gt; &lt;w:PunctuationKerning/&gt; &lt;w:DrawingGridVerticalSpacing&gt;7.8 磅&lt;/w:DrawingGridVerticalSpacing&gt; &lt;w:DisplayHorizontalDrawingGridEvery&gt;0&lt;/w:DisplayHorizontalDrawingGridEvery&gt; &lt;w:DisplayVerticalDrawingGridEvery&gt;2&lt;/w:DisplayVerticalDrawingGridEvery&gt; &lt;w:ValidateAgainstSchemas/&gt; &lt;w:SaveIfXMLInvalid&gt;false&lt;/w:SaveIfXMLInvalid&gt; &lt;w:IgnoreMixedContent&gt;false&lt;/w:IgnoreMixedContent&gt; &lt;w:AlwaysShowPlaceholderText&gt;false&lt;/w:AlwaysShowPlaceholderText&gt; &lt;w:Compatibility&gt; &lt;w:SpaceForUL/&gt; &lt;w:BalanceSingleByteDoubleByteWidth/&gt; &lt;w:DoNotLeaveBackslashAlone/&gt; &lt;w:ULTrailSpace/&gt; &lt;w:DoNotExpandShiftReturn/&gt; &lt;w:AdjustLineHeightInTable/&gt; &lt;w:BreakWrappedTables/&gt; &lt;w:SnapToGridInCell/&gt; &lt;w:WrapTextWithPunct/&gt; &lt;w:UseAsianBreakRules/&gt; &lt;w:DontGrowAutofit/&gt; &lt;w:UseFELayout/&gt; &lt;/w:Compatibility&gt; &lt;w:BrowserLevel&gt;MicrosoftInternetExplorer4&lt;/w:BrowserLevel&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;!\[endif]--&gt;&lt;!-- \[if gte mso 9]&gt;&lt;xml&gt; &lt;w:LatentStyles DefLockedState="false" LatentStyleCount="156"&gt; &lt;/w:LatentStyles&gt; &lt;/xml&gt;&lt;!\[endif]--&gt;&lt;!-- \[if !mso]&gt; &lt; classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui&gt; &lt;/object&gt; &lt;mce:style&gt;&lt;!-- st1\\:\*{behavior:url(#ieooui) } --&gt; &lt;!-- \[endif]--&gt; &lt;!-- \[if gte mso 10]&gt; &lt;mce:style&gt;&lt;!-- /* Style Definitions \*/ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} --&gt; &lt;!-- \[endif]--&gt;

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 &lt; v\_enddate+1 - v\_startday

)X,(Select F\_REGCHANNEL From T\_base\_wap\_REGChannel g Where g.f\_Regchannel &lt;&gt; '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 ;

执行计划：

![](http://p.blog.csdn.net/images/p_blog_csdn_net/ZengMuAnSha/EntryImages/20090925/leftjon.PNG)

问题找出来后的执行计划

![](http://p.blog.csdn.net/images/p_blog_csdn_net/ZengMuAnSha/EntryImages/20090925/leftjoin_01.PNG)