Select/* + parallet (t_base_user_buy_info, 4) */a. f_yearweek, A. f_lotbigclass, B. interval num, 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_regular Ct = B. f_regular CT
Inner join t_base_flat_type t
On t. f_buymodename = B. F_BuyChannel and t. F_DEPARTMENT = 1 and f_flatname = 'webmaster'
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 ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') and to_date ('2017-01-10 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')
And s. f_date between to_date ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') and to_date ('2017-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_regular CT) interval num,
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_regular Ct = B. f_regular CT
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 ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') and to_date ('2017-01-10 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')
And s. f_date between to_date ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') and to_date ('2017-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;
Execution Plan
After the two big performance statistics are made, select a is missing for the temporary tablespace that occupies the left join. f_yearweek, c. f_lotbigclass, sum (interval num) interval num, sum (countrySalay) countrySalay, sum (countryBonus) countryBonus
From
(
Select TO_CHAR (f_DATE, 'yyyymmww ') as f_yearweek, F_LotID, count (distinct f_0000ct) interval num, sum (F_BuyMoney) countrySalay
From T_Base_User_Buy_Info
Where f_date between to_date ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') and to_date ('2017-01-10 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')
Group by TO_CHAR (f_DATE, 'yyyymmww '), F_LotID
)
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 ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') and to_date ('2017-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, interval num, locatesalay, countrybonus
From
(
Select to_char (f_date, 'yyyymmww ') as f_yearweek, f_lotbigclass,
Count (distinct f_0000ct) interval num, 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 = 'origin site'
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 ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') and to_date ('2017-01-10 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss ')
Group by TO_CHAR (f_DATE, 'yyyymmww '), f_lotbigclass
)
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 = 'webmaster'
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 ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: MI: ss') and to_date ('2017-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