update和merge只用于更新时,俩种方式是可互更换的,但在一些特殊情况下,俩种方式的执行效率差距很大!
update适用于
1、
更改单表
速度快稳定性好
;
2、某字段即是过滤条件又是更新字段,且该字段有选择性很强的索引时“update A set status=1 where id=1 and status=2 and idc in (表)”
merge适用于
1、
根据一张表或多表联合查询的连接条件对另外一张表进行查询,连接条件匹配上的进行UPDATE,无法匹配的执行INSERT可直接用merge实现,执行效率要高于INSERT+UPDATE;
2、“update a set i=(select i from b where a.id=b.id) where not exists (select 1 from b where a.id=b.id)” not exists 部分需要额外消耗,可以用 merge 避免;
2、“update a set i=(select i from b where a.id=b.id) where not exists (select 1 from b where a.id=b.id)” not exists 部分需要额外消耗,可以用 merge 避免;
3、“例子1“ 情况
merge缺点:
不支持更改字段放入on【会报错
ORA-38104: 无法更新 ON 子句中引用的列: XX】;
有些情况merge 过滤条件不放入on可能会有其他性能问题(例子2、3)
例子1、
SQL:
update bill_asn t
set t.source_no =
( select nvl (max(b.wmsnos), '-')
from bill_asn a
inner join bill_syn_out b
on b.sys_no || b.nos = a.bill_no
and a.bizs_type = 4
where a.bill_no = t.bill_no);
执行计划:
Description |
Object_owner |
Object_name |
Cost |
Cardinality |
Bytes |
UPDATE STATEMENT, GOAL = ALL_ROWS | 3386798772 | 682546 | 11603282 | ||
UPDATE | USR_LMP | BILL_ASN | |||
TABLE ACCESS FULL | USR_LMP | BILL_ASN | 5520 | 682546 | 11603282 |
SORT AGGREGATE | 1 | 54 | |||
NESTED LOOPS | 4961 | 976 | 52704 | ||
INDEX RANGE SCAN | USR_LMP | I_BILL_ASN_FPNO | 3 | 1 | 16 |
TABLE ACCESS FULL
| USR_LMP_JK
| BILL_SYN_OUT
| 4958
| 9957
| 378366
|
分析:
该sql执行时间>2小时;更新条目: 682546
执行计划
I_BILL_ASN_FPNO(索引)+
BILL_SYN_OUT(表) 做
NESTED LOOPS循环每次
Cardinality=1取一条数据和
BILL_ASN表
进行update,这样的操作执行
682546次(表行数);
cost消耗:
682546(表行) *4961(消耗)=
3386110706 接近
3386798772
优化方案:
SQL:
merge into BILL_ASN T
using (SELECT NVL( MAX(b.WMSNOS), '-' ) WMSNOS, A.BILL_NO
FROM BILL_ASN A
INNER JOIN BILL_SYN_OUT B
ON B.SYS_NO || B.NOS = A.BILL_NO
AND A.BIZS_TYPE = 4
group by A.BILL_No) T1
on (T1.BILL_NO = T.BILL_NO )
when matched then
update set T.SOURCE_NO = t1.WMSNOS;
执行计划:
Description |
Object_owner |
Object_name |
Cost |
Cardinality |
Bytes |
MERGE STATEMENT, GOAL = ALL_ROWS | 20254 | 66902 | 1338040 | ||
MERGE | USR_LMP | BILL_ASN | |||
VIEW | USR_LMP | ||||
HASH JOIN | 20254 | 66902 | 19000168 | ||
VIEW | USR_LMP | 7440 | 66902 | 5285258 | |
SORT GROUP BY | 7440 | 66902 | 3612708 | ||
HASH JOIN | 6409 | 97597 | 5270238 | ||
INDEX FAST FULL SCAN | USR_LMP | I_BILL_ASN_FPNO | 1451 | 66902 | 1070432 |
TABLE ACCESS FULL | USR_LMP_JK | BILL_SYN_OUT | 4955 | 995696 | 37836448 |
TABLE ACCESS FULL
| USR_LMP
| BILL_ASN
| 5520
| 682546
| 139921930
|
** merge 表之间选择的HASH JOIN,且过滤后批量更新~
例子2、
MERGE 顺序:
多表on关联后[关联的执行计划已经选好]--> 筛选where条件...所以就算where取主键定值也不会走索引(如下)
原SQL:
MERGE INTO CS_BATCH_SEND_EVA EVA
USING (SELECT ORDER_ID, NAT_MON FROM ES_INS_REC REC) T
ON (T.ORDER_ID = EVA.ORDER_ID )
WHEN MATCHED THEN
UPDATE
SET EVA.IS_LEGAL = 0
WHERE EVA.BATCH_SEND_EVA_ID = :B1/*主键字段*/;
执行计划:
![](http://img.blog.itpub.net/blog/attachment/201604/6/28602568_1459911000xnC8.png?x-oss-process=style/bb)
优化方案:
SQL:
MERGE INTO CS_BATCH_SEND_EVA EVA
USING (SELECT ORDER_ID, NAT_MON FROM ES_INS_REC REC) T
ON (T.ORDER_ID = EVA.ORDER_ID and EVA.BATCH_SEND_EVA_ID = :B1/*主键字段*/)
WHEN MATCHED THEN
UPDATE
SET EVA.IS_LEGAL = 0;
执行计划:
![](http://img.blog.itpub.net/blog/attachment/201604/6/28602568_1459911016ooXx.png?x-oss-process=style/bb)
结果:
由原来的2表全扫,改为走pk_batch_send_eve_id 主键 先筛选后是1条记录,关联条件T.ORDER_ID = EVA.ORDER_ID使ES_INS_REC 也选择order_id索引。
例子3、
之前遇到过merge 的where 过滤部分写查询语句,执行计划中并没有体现子查询语句中关联的表;
【如图】
![](http://img.blog.itpub.net/blog/attachment/201510/22/28602568_1445519908uDF8.png?x-oss-process=style/bb)
若您sql及执行计划同例子3的情况,且执行很久都执行不出来,可尝试将子查询部分放入on中避免此原因引起的性能问题;
【如图】
![](http://img.blog.itpub.net/blog/attachment/201510/22/28602568_1445519922FF9e.png?x-oss-process=style/bb)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28602568/viewspace-1815737/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28602568/viewspace-1815737/