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);
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步