oracle两查询差异,两个Oracle查询语句，执行计划如何有这么大的差异

当前位置:我的异常网» Oracle管理 » 两个Oracle查询语句，执行计划如何有这么大的差异

两个Oracle查询语句，执行计划如何有这么大的差异

www.myexceptions.net  网友分享于：2013-03-15  浏览：42次

两个Oracle查询语句，执行计划怎么有这么大的差异？

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

------解决方案--------------------

你上面的查询里面有子查询，执行的三次全表扫面，第二次里面只有两次吧

文章评论