这样的语句如何优化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_wtype  and 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_wtype  and 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