update 改写 merge into

update语句改写成merge into有时会提高运行速度

看两个案例

1.根据业务将两个嵌套子查询改写成max，速度有3min提升到3s

UPDATE OPER_792.LL_SCB_YDKB_20120730 A
SET A.DCP =
(SELECT B.PROD_OFFER_NAME
FROM OPER_792.YD_TC B
WHERE A.SERV_ID = B.SERV_ID
AND B.TC_TYPE = '合约计划'
AND ROWNUM = 1),
A.JCTC =
(SELECT B.PROD_OFFER_NAME
FROM OPER_792.YD_TC B
WHERE A.SERV_ID = B.SERV_ID
AND B.TC_TYPE = '基础套餐'
AND ROWNUM = 1)
WHERE A.DAY_ID = 20150125


merge into OPER_792.LL_SCB_YDKB_20120730 A
using (select c.SERV_ID,
max(case
when c.TC_TYPE = '合约计划' then
c.PROD_OFFER_NAME
end) col1,
max(case
when c.TC_TYPE = '基础套餐' then
c.PROD_OFFER_NAME
end) col2
from OPER_792.YD_TC c
group by c.SERV_ID) b
on (A.SERV_ID = B.SERV_ID and a.day_id = 20150125)
when matched then
update set A.DCP = b.col1, A.JCTC = b.col2

 

2.正常的将update 改写成merge into

update tb_result r set r.vote_count=nvl((

select temp_.vote_count

from(

　　select result_id,

　　count(rv_id) as vote_count

　　from tb_result_vote

　　group by result_id

) temp_

where temp_.result_id=r.result_id),

r.vote_count

);

 

merge into tb_result r

using(

select result_id,

　　count(rv_id) as vote_count

　　from tb_result_vote

　　group by result_id) temp_

on(temp_.result_id=r.result_id)

when matched then

update set r.vote_count=nvl(temp_.vote_count,r.vote_count);