这样的语句如何优化Temp空间 现在它要用30G哦

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 ;

执行计划

最后把两个大表现统计后在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