---
title: "这样的语句如何优化Temp空间 现在它要用30G哦"
author: "winzenghua"
date: "2009-06-02T17:39:00Z"
tags: []
slug: "temp-30g"
draft: false
lastmod: "2009-06-02T17:39:00Z"
id: 987882
url: "https://java4gis.iteye.com/blog/987882"
---

select/\*+PARALLET(T\_BASE\_USER\_BUY\_INFO,4)\*/ a.f\_yearweek,a.f\_lotbigclass,b.ExpeNum,a.salay,b.countrysalay,a.bonus,b.countrybonus  
from  
(  
select TO\_CHAR(b.f\_DATE,'YYYYMMWW') as f\_yearweek,c.f\_lotbigclass,F\_FlatName,  
sum(F\_BuyMoney) Salay,  
sum(F\_TaxBefforBonus) Bonus  
from T\_Base\_User\_Buy\_Info b  
left join T\_Base\_Lot\_Send\_Bonus s  
on b.F\_LotID=s.f\_lotid and s.f\_wtype=b.f\_wtypeand s.f\_expect=b.f\_expect  
inner join T\_base\_Flat\_Type t  
on t.f\_buymodename=b.F\_BuyChannel and t.F\_DEPARTMENT=1 and f\_flatname='主站'  
inner join ( select distinct f\_lotbigclass,f\_lottype from t\_base\_lotclass t where f\_classsort=1) C  
on b.F\_LotID=c.f\_lottype  
where b.f\_date between to\_date('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to\_date('2007-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss')  
and s.f\_date between to\_date('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to\_date('2007-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss')  
group by TO\_CHAR(b.f\_DATE,'YYYYMMWW'),c.f\_lotbigclass,F\_FlatName  
) a inner join  
(  
select TO\_CHAR(b.f\_DATE,'YYYYMMWW') as f\_yearweek,c.f\_lotbigclass,  
count(distinct b.F\_Expect) ExpeNum,  
sum(b.F\_BuyMoney) countrySalay,  
sum(s.F\_TaxBefforBonus) countryBonus  
from T\_Base\_User\_Buy\_Info b  
left join T\_Base\_Lot\_Send\_Bonus s  
on b.F\_LotID=s.f\_lotid and s.f\_wtype=b.f\_wtypeand s.f\_expect=b.f\_expect  
inner join T\_base\_Flat\_Type t  
on t.f\_buymodename=b.F\_BuyChannel and t.F\_DEPARTMENT=1  
inner join ( select distinct f\_lotbigclass,f\_lottype from t\_base\_lotclass t where f\_classsort=1) C  
on b.F\_LotID=c.f\_lottype  
where b.f\_date between to\_date('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to\_date('2007-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss')  
and s.f\_date between to\_date('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to\_date('2007-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss')  
group by TO\_CHAR(b.f\_DATE,'YYYYMMWW'),c.f\_lotbigclass,F\_FlatName  
) b on a.f\_yearweek=b.f\_yearweek and a.f\_lotbigclass=b.f\_lotbigclass ;

执行计划

![](http://p.blog.csdn.net/images/p_blog_csdn_net/ZengMuAnSha/EntryImages/20090602/%E6%9C%AA%E5%91%BD%E5%90%8D.JPG)

最后把两个大表现统计后在LEFT JOIN 那样占临时表空间就少了

select a.f\_yearweek,c.f\_lotbigclass,sum(ExpeNum) ExpeNum ,sum(countrySalay) countrySalay,sum(countryBonus) countryBonus  
from  
(  
select TO\_CHAR(f\_DATE,'YYYYMMWW') as f\_yearweek ,F\_LotID, count(distinct F\_Expect) ExpeNum, sum(F\_BuyMoney) countrySalay  
from T\_Base\_User\_Buy\_Info  
where f\_date between to\_date('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to\_date('2007-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss')  
group by TO\_CHAR(f\_DATE,'YYYYMMWW'),F\_LotID  
) a  
left join  
(  
select TO\_CHAR(f\_date,'YYYYMMWW') as f\_yearweek ,F\_LotID,sum(F\_TaxBefforBonus) countryBonus  
from T\_Base\_Lot\_Send\_Bonus  
where f\_date between to\_date('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to\_date('2007-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss')  
group by TO\_CHAR(f\_date,'YYYYMMWW'),F\_LotID  
)b on a.f\_yearweek=b.f\_yearweek and a.f\_lotid=b.f\_lotid  
inner join  
(  
select distinct f\_lottype,f\_lotbigclass  
from t\_base\_lotclass  
where f\_classsort=1  
)c on a.f\_lotid=c.f\_lottype  
group by a.f\_yearweek,c.f\_lotbigclass

select a.f\_yearweek,a.f\_lotbigclass,ExpeNum ,locatesalay,countryBonus  
from  
(  
select TO\_CHAR(f\_DATE,'YYYYMMWW') as f\_yearweek ,f\_lotbigclass,  
count(distinct F\_Expect) ExpeNum, sum(F\_BuyMoney) locatesalay  
from T\_Base\_User\_Buy\_Info  
inner join T\_base\_Flat\_Type t on f\_buymodename=F\_BuyChannel and F\_DEPARTMENT=1 and f\_flatname='主站'  
inner join ( select distinct f\_lotbigclass,f\_lottype from t\_base\_lotclass t where f\_classsort=1) C  
on F\_LotID=c.f\_lottype  
where f\_date between to\_date('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to\_date('2007-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss')  
group by TO\_CHAR(f\_DATE,'YYYYMMWW'),f\_lotbigclass  
) a  
left join  
(  
select TO\_CHAR(f\_date,'YYYYMMWW') as f\_yearweek ,f\_lotbigclass,sum(F\_TaxBefforBonus) countryBonus  
from T\_Base\_Lot\_Send\_Bonus s  
inner join T\_base\_Flat\_Type t on s.f\_buymodeid=t.f\_buymodeid and F\_DEPARTMENT=1 and f\_flatname='主站'  
inner join ( select distinct f\_lotbigclass,f\_lottype from t\_base\_lotclass t where f\_classsort=1) C  
on F\_LotID=c.f\_lottype  
where f\_date between to\_date('2007-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to\_date('2007-01-10 00:00:00','yyyy-mm-dd hh24:mi:ss')  
group by TO\_CHAR(f\_date,'YYYYMMWW'),f\_lotbigclass  
)b on a.f\_yearweek=b.f\_yearweek and a.f\_lotbigclass=b.f\_lotbigclass