转载

Oracle 如何选择update、merge

链接：http://blog.itpub.net/28602568/viewspace-1815737/

标题：Oracle 如何选择update、merge

update和merge只用于更新时，俩种方式是可互更换的，但在一些特殊情况下，俩种方式的执行效率差距很大！

之前Oracle SQL优化总结有提到“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避免；

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