select
d.statis_date,
d.area_code,
d.cell_sale_id,
count(c.serv_number)
from (select distinct (case
when(a.whole_fee > 0 or a.word_fee>0) then
a.msisdn
when(a.msisdn = b.msisdn and (a.book_chpt_cnt > 0 or a.book_download_cnt > 0)) then
a.msisdn
else
null
end) serv_number
from masadw.tb_dw_ns_read_user_act_list a,
masadw.tb_dw_ns_read_user_order_list b
where a.msisdn = b.msisdn(+)
and a.deal_date between vd_first_date and vd_date
and b.deal_date between vd_first_date and vd_date) c
masadw.tb_mk_sc_user_dtal d
where c.serv_number = d.serv_number
and c.user_status_id <> 'H'
and c.statis_date = vd_date
group by d.statis_date,
d.area_code,
d.cell_sale_id;
执行计划：
对象 耗费
-select statement,goal=all rows
-sort group by
-view VM_NWVW_1
-sort unique
-filter
-filter
-hash join outer
-merge join cartesian
-partition list iterator
table access all TB_DW_NS_READ_USER_ACT_LIST 10030
-buffer sort
-partition range single
-partition list all
table access all TB_MK_SC_USER_DTAL 64756033
-partition list iterator
table access full TB_DW_NS_READ_USER_ORDER_LIST 94268
select
b.statis_date,
b.area_code,
b.cell_sale_id,
count(case
when a.new_stdt_count = '1' then
a.serv_number
else
null
end)
from masadw.tb_kr_campus_user_dtal a,
masadw.tb_mk_sc_user_dtal b
where a.serv_number = b.serv_number
and b.user_status_id <> 'H'
and a.statis_date = vd_date
and b.statis_date = vd_date
group by b.statis_date,
b.area_code,
b.cell_sale_id;
执行计划：
对象 耗费
-select statement,goal=all rows
-PX COORDINATOR
-PX SEND QC(RANDOW) :TQ10003 98152
-SORT GROUP BY
-PX RECEIVE
-PX SEND HASH :TQ10002 98152
-SORT GROUP BY
-HASH JOIN
-BUFFER SORT
-PX RECEIVE
-PX SEND HASH :TQ10000 31066
-PARTITION LIST SINGLE
TABLE ACCESS FULL TB_KR_CAMPUS_USER_DTAL 31066
-PX RECEIVE
-PX SEND HASH :TQ10001 67082
-PX BLOCK ITERATOR
TABLE ACCESS FULL TB_MK_SC_USER_DTAL 67082