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资源及内存资源消耗方面，还要再研究下

 

non_use_merge.gif

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-750248/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-750248/