oracle use_merge,oracle hint use_merge提示的hint优化select insert性能

如下sql运行非常慢

insert /*+APPEND*/ into deal_order_user_choose_tmp nologging

(

ORDER_ID,--订单ID  varchar2(100)  VARCHAR2(80)

ORDER_DETAILID,--  订单明细ID  varchar2(800)  VARCHAR2(80)

CHOOSE_TYPE,--  选择项类型  CHAR(40)  VARCHAR2(200)

CHOOSE_ID,--  选择项ID  CHAR(40)  VARCHAR2(200)

CHOOSE_NAME,--  选择项名称  INTEGER  VARCHAR2(200)

REMARK--  备注  varchar2(800)  VARCHAR2(600)

)

select

/*+ use_merge (psg,po) */

psg.order_no,--duc.ORDER_ID,--订单ID  varchar2(100)  VARCHAR2(80)

psg.id,--duc.ORDER_DETAILID,--  订单明细ID  varchar2(800)  VARCHAR2(80)

(case when upper(psg.goods_table_name) in('PORTAL_BANKCARDPAY_PRICE','PORTAL_PAYCARD','PORTAL_TERM_SPEC') then '99'

when upper(psg.goods_table_name) in('PORTAL_PREP_PHONENUMBER') then '7'

--套餐

when upper(psg.goods_table_name) in('PORTAL_PPM_PROD_OFFER')  and (select ppo.offer_type from portal_ppm_prod_offer ppo where ppo.id=psg.goods_id)=1 then   '1'--duc.CHOOSE_TYPE,--  选择项类型  CHAR(40)  VARCHAR2(200)

--补贴

when upper(psg.goods_table_name) in('PORTAL_PPM_PROD_OFFER')  and (select ppo.offer_type from portal_ppm_prod_offer ppo where ppo.id=psg.goods_id)=3 then  '2' --duc.CHOOSE_TYPE,--  选择项类型  CHAR(40)  VARCHAR2(200)

--预存款

when upper(psg.goods_table_name) in('PORTAL_PPM_PROD_OFFER')  and (select ppo.offer_type from portal_ppm_prod_offer ppo where ppo.id=psg.goods_id) in (4,5) then  '3'--duc.CHOOSE_TYPE,--  选择项类型  CHAR(40)  VARCHAR2(200)

--主可选包

when upper(psg.goods_table_name) in('PORTAL_PPM_PROD_OFFER')  and (select ppo.offer_type from portal_ppm_prod_offer ppo where ppo.id=psg.goods_id) in (2,6) then  '4'--duc.CHOOSE_TYPE,--  选择项类型  CHAR(40)  VARCHAR2(200)

end) as choose_type,--

--duc.CHOOSE_ID,--  选择项ID  CHAR(40)  VARCHAR2(200)

(case when upper(psg.goods_table_name) in('PORTAL_BANKCARDPAY_PRICE') then (select pbp.id from PORTAL_BANKCARDPAY_PRICE pbp where pbp.id=psg.goods_id and pbp.rec_status=1)

when upper(psg.goods_table_name) in('PORTAL_PAYCARD') then (select pp.id from PORTAL_PAYCARD pp where pp.rec_status=1 and psg.goods_id=pp.id )

when upper(psg.goods_table_name) in('PORTAL_TERM_SPEC') then (select pts.id from PORTAL_TERM_SPEC pts where pts.rec_status=1 and pts.id=psg.goods_id)

when upper(psg.goods_table_name) in('PORTAL_PREP_PHONENUMBER') then  (select ppp.phone_number from PORTAL_PREP_PHONENUMBER ppp where ppp.id=psg.goods_id and ppp.rec_status=1)

when upper(psg.goods_table_name) in('PORTAL_PPM_PROD_OFFER')  then   (select pppo.id from PORTAL_PPM_PROD_OFFER pppo where pppo.rec_status=1 and pppo.id=psg.goods_id)--duc.CHOOSE_TYPE,--  选择项类型  CHAR(40)  VARCHAR2(200)

end) as choose_id,

--duc.CHOOSE_NAME,--  选择项名称  INTEGER  VARCHAR2(200)

(case when upper(psg.goods_table_name) in('PORTAL_BANKCARDPAY_PRICE') then (select pbp.remark from PORTAL_BANKCARDPAY_PRICE pbp where pbp.id=psg.goods_id and pbp.rec_status=1)

when upper(psg.goods_table_name) in('PORTAL_PAYCARD') then (select pp.cardname from PORTAL_PAYCARD pp where pp.rec_status=1 and psg.goods_id=pp.id )

when upper(psg.goods_table_name) in('PORTAL_TERM_SPEC') then (select pts.mkt_res_name from PORTAL_TERM_SPEC pts where pts.rec_status=1 and pts.id=psg.goods_id)

when upper(psg.goods_table_name) in('PORTAL_PREP_PHONENUMBER') then  (select ppp.phone_number from PORTAL_PREP_PHONENUMBER ppp where ppp.id=psg.goods_id and ppp.rec_status=1)

when upper(psg.goods_table_name) in('PORTAL_PPM_PROD_OFFER')  then   (select pppo.prod_offer_name from PORTAL_PPM_PROD_OFFER pppo where pppo.rec_status=1 and pppo.id=psg.goods_id)--duc.CHOOSE_TYPE,--  选择项类型  CHAR(40)  VARCHAR2(200)

end)  as    CHOOSE_NAME,

null--duc.REMARK--  备注  varchar2(800)  VARCHAR2(600)

from

portal_shopping_goodslist psg,

portal_orderbill po

where psg.rec_status=1 and

po.rec_status=1  and

psg.order_no=po.ext_cust_order_id;

and

upper(psg.goods_table_name) not in('PORTAL_STANDARD_COMBINATION','Portal_Seckilling_infos') and

po.ORDERBILL_STATUS  in (1,2,4,5,6,7,8,9,10,103,111,222,500,501,502,505,555,601) and

psg.goods_id is not null;

分析后，select部分查询太慢

看看sql的执行计划如下：

此sql消耗资源最多就是最下端的hash join

--查询from２表的记录数

select count(id) from portal_orderbill ;

select count(id) from portal_shopping_goods;

结果集各为100w左右

hash join用于大表和小表的关联，性能明显

此时想到merge join适用于两个表记录量差不多

为上述的sql添加hint /*+ use_merge (psg,po) */

再次查看执行计划如下：

变成了merge join

二者的区别是merge join的cpu成本比hash join要少很多

关于hash join与merge join在cpu资源和io资源及内存资源消耗方面，还要再研究下