select /*parallel(a, 12)*/
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 /*parallel(b, 12)*/
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
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 /*parallel(b, 12)*/
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
第一个语句比第2个语句查询满有几个原因。
1、2楼说的第一个sql语句中使用了distinct。是其中之一。
2、第一个语句中使用嵌套查询也是原因之一
3、最主要的原因应该是你使用的执行计划关键字提示。
从你的数据来看:
TB_DW_NS_READ_USER_ACT_LIST a 10030
TB_MK_SC_USER_DTAL b 64756033
明显a表比b表的数据小。
你使用的执行计划关键字提示为parallel(表, 12)
这个意思把指定的表分成12个部分并行执行。
你这2个语句在效率上的差距就在你选择的表上。
本来你a表的数据量就不大。你强制为并行执行,效率也高不了多少。
但当你改为b表后,因为b表的数据量大,分成12份并行执行,每一分的数据量就要小得多。
从而提高了执行效率。
在加关键字的时候要注意数据量。