1、oracle merge详细介绍
merge into是oracle9I新增的语法，主要是用于简化更新的执行效率，后面有具体改写的案例分析
具体语法如下：
merge into tab01 a
using tab02 b
on (a.id=b.id)
when matched then
update set a.name=b.name
when not matched then
insert values(b.id,d,name)
用tab02表来对tab01更新，tab01的所有记录行中如果有符合的数据行就执行update操作a.id=b.id的就对tab01的name更新为tab02的name，如果没有满足a.id=b.id的数据行就对tab01表执行insert操作，注意此时matched只能update，not matched也只能insert或者不操作。
需要注意的是update是不能对关联条件update的：
merge into tab01 a
using tab02 b
on (a.id=b.id)
when matched then
update set a.id=b.id，a.name=b.name
when not matched then
insert values(b.id,d,name)
此时oracle无法对连接条件的column进行update set
2、merge into优化器执行计划更加灵活，减少表关联次数
上面提到简化更新的执行效率，到底是如何简化的，何种情况下可以改写为merge来简化
SQL>drop table t_objects;
SQL>drop table t_tables;
SQL>create table t_objects as select * from dba_objects;
SQL>create table t_tables as select * from dba_tables;
SQL>alter table t_objects add tablespace_name varchar2(32);
2.1 错误更新的SQL，语义错误
现在需要把t_tables的tablespace_name同步到t_objects的tablespace_name中
UPDATE t_objects a
SET
a.tablespace_name = (select b.tablespace_name
FROM
t_tables b
WHERE
b.table_name = a.object_name and b.owner =a.owner
)
WHERE exists(select 1 from t_tables b where
b.table_name = a.object_name
AND b.owner = a.owner
)
UPDATE t_objects a
SET
a.tablespace_name = (select b.tablespace_name
FROM
t_tables b
WHERE
b.table_name = a.object_name and b.owner =a.owner
)
注意很多开发人员喜欢用第二种SQL去更新，这样oracle会把t_objects表中的所有tablespace_name都更新掉,这个一定要特别注意，如下例子
SQL> select * from t01;
ID NAME
---------- --------------------------------
1 awk
2 awk
3 afg
SQL> select * from t02;
ID EADDR
---------- ----------
1 beijing
2 shanghai
SQL> update t01 set name=(select eaddr from t02 where t01.id=t02.id);
3 rows updated.
SQL> select * from t01;
ID NAME
---------- --------------------------------
1 beijing
2 shanghai
3
SQL> rollback;
rollback complete.
SQL> update t01 set name=(select eaddr from t02 where t01.id=t02.id) where exists(select 1 from t02 where t01.id=t02.id);
2 rows updated.
SQL> select * from t01;
ID NAME
---------- --------------------------------
1 beijing
2 shanghai
3 afg
2、merge join优化
回到原SQL案例中认真分析下，原SQL执行计划如下：
SQL> UPDATE t_objects a
SET a.tablespace_name=
(SELECT b.tablespace_name
FROM t_tables b
WHERE b.table_name=a.object_name
AND b.owner =a.owner
)
WHERE EXISTS
(SELECT 1 FROM t_tables b WHERE b.table_name=a.object_name AND b.owner=a.owner
);
3174 rows updated.
Plan hash value: 2130220419
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | | 0 |00:00:03.64 | 327K| | | |
| 1 | UPDATE | T_OBJECTS | 1 | | 0 |00:00:03.64 | 327K| | | |
|* 2 | HASH JOIN RIGHT SEMI| | 1 | 76604 | 3174 |00:00:00.22 | 1352 | 1229K| 1229K| 1335K (0)|
| 3 | VIEW | VW_SQ_1 | 1 | 2989 | 2959 |00:00:00.01 | 107 | | | |
| 4 | TABLE ACCESS FULL | T_TABLES | 1 | 2989 | 2959 |00:00:00.01 | 107 | | | |
| 5 | TABLE ACCESS FULL | T_OBJECTS | 1 | 76604 | 86958 |00:00:00.01 | 1245 | | | |
|* 6 | TABLE ACCESS FULL | T_TABLES | 3015 | 1 | 3015 |00:00:03.02 | 322K| | | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ITEM_1"="A"."OBJECT_NAME" AND "ITEM_2"="A"."OWNER")
6 - filter(("B"."TABLE_NAME"=:B1 AND "B"."OWNER"=:B2)
Statistics
----------------------------------------------------------
0 recursive calls
3238 db block gets
323957 consistent gets
0 physical reads
754468 redo size
841 bytes sent via SQL*Net to client
1014 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3174 rows processed
该SQL的执行计划在于需要对表T_OBJECTS扫描两次，并且还有标量子查询部分（可以参考上面的T_TABLES表的循环查询了3015次）
改写为merge来优化：
merge into t_objects a
using t_tables b on(a.owner=b.owner and a.object_name=b.table_name)
when matched then update
set a.tablespace_name=b.tablespace_name;
Plan hash value: 1970127410
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | | 0 |00:00:00.51 | 4590 | | | |
| 1 | MERGE | T_OBJECTS | 1 | | 0 |00:00:00.51 | 4590 | | | |
| 2 | VIEW | | 1 | | 3174 |00:00:00.03 | 1352 | | | |
|* 3 | HASH JOIN | | 1 | 3915 | 3174 |00:00:00.03 | 1352 | 1598K| 984K| 2440K (0)|
| 4 | TABLE ACCESS FULL| T_TABLES | 1 | 2989 | 2959 |00:00:00.01 | 107 | | | |
| 5 | TABLE ACCESS FULL| T_OBJECTS | 1 | 76604 | 86958 |00:00:00.02 | 1245 | | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."OWNER"="B"."OWNER" AND "A"."OBJECT_NAME"="B"."TABLE_NAME")
Statistics
----------------------------------------------------------
66 recursive calls
3238 db block gets
1714 consistent gets
0 physical reads
754388 redo size
844 bytes sent via SQL*Net to client
947 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
3174 rows processed
从执行计划来看merge改写的SQL已经没有标量子查询部分，逻辑读降低到了1714多
需要注意的merge into改写包含when matched和when not matched需要涉及到hash join outer也就是外连接
SQL> merge into t_objects a
using t_tables b on(a.owner=b.owner and a.object_name=b.table_name)
when matched then update
set a.tablespace_name=b.tablespace_name
when not matched then insert (owner,object_name) values(b.owner,b.table_name);
3090 rows merged.
SQL> select * from table(dbms_xplan.display_cursor(null));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID d6npj8t745y0d, child number 0
-------------------------------------
merge into t_objects a using t_tables b on(a.owner=b.owner and
a.object_name=b.table_name) when matched then update set
a.tablespace_name=b.tablespace_name when not matched then insert
(owner,object_name) values(b.owner,b.table_name)
Plan hash value: 555198445
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | | | | 1513 (100)| |
| 1 | MERGE | T_OBJECTS | | | | | |
| 2 | VIEW | | | | | | |
|* 3 | HASH JOIN OUTER | | 3279 | 2491K| 1312K| 1513 (1)| 00:00:19 |
| 4 | TABLE ACCESS FULL| T_TABLES | 2417 | 1276K| | 32 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T_OBJECTS | 90835 | 20M| | 347 (1)| 00:00:05 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."OBJECT_NAME"="B"."TABLE_NAME" AND "A"."OWNER"="B"."OWNER")
Note
-----
- dynamic sampling used for this statement (level=2)
29 rows selected.
3、merge join改写含有聚合函数的update
生产环境的案例1：
SQL Monitoring Report
SQL Text
------------------------------
UPDATE bs_reso_060451_vc a
SET
( a.msisdn,a.callnumber ) = (
SELECT DISTINCT
b.msisdn,
b.calling_number from js_ro_settle_detail_vco_04 b
WHERE
substr(a.inv_id,0,17) = b.serial_number and a.mon_number = '4'
AND a.status_id = '401'
)
WHERE
a.mon_number = '4'
AND a.status_id = '401'
Global Information
------------------------------
Status : EXECUTING
Instance ID : 1
Session : AUD (3265:53793)
SQL ID : 61c4x36kjcr22
SQL Execution ID : 16777216
Execution Started : 11/18/2017 20:15:54
First Refresh Time : 11/18/2017 20:16:04
Last Refresh Time : 11/18/2017 22:10:03
Duration : 6850s
Module/Action : PL/SQL Developer/Command Window - New
Service : SYS$USERS
Program : plsqldev.exe
Global Stats
===============================================================================
| Elapsed | Cpu | IO | Cluster | Other | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes |
===============================================================================
| 6853 | 4199 | 131 | 0.40 | 2522 | 179M | 26569 | 446MB |
===============================================================================
SQL Plan Monitoring Details (Plan Hash Value=1926918616)
=======================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) | |
=======================================================================================================================================================================================================
| 0 | UPDATE STATEMENT | | | | | | 1 | | | | | | | |
| -> 1 | UPDATE | BS_RESO_060451_VC | | | 6844 | +10 | 1 | 0 | 3 | 49152 | | 0.04 | Cpu (1) | |
| | | | | | | | | | | | | | buffer exterminate (2) | |
| -> 2 | PARTITION RANGE SINGLE | | 6M | 2M | 6844 | +10 | 1 | 4846 | | | | | | |
| -> 3 | TABLE ACCESS FULL | BS_RESO_060451_VC | 6M | 2M | 6844 | +10 | 1 | 4846 | 17 | 368KB | | 0.01 | db file scattered read (1) | 0% |
| -> 4 | SORT UNIQUE | | 36610 | 14241 | 6844 | +10 | 4846 | 3071 | | | | 0.01 | Cpu (1) | |
| -> 5 | FILTER | | | | 6844 | +10 | 4846 | 3071 | | | | | | |
| -> 6 | TABLE ACCESS FULL | JS_RO_SETTLE_DETAIL_VCO_04 | 36610 | 14023 | 6853 | +1 | 4846 | 3071 | 26549 | 446MB | | 99.93 | Cpu (6685) | 100% |
| | | | | | | | | | | | | | resmgr:cpu quantum (1) | |
| | | | | | | | | | | | | | db file scattered read (9) | |
| | | | | | | | | | | | | | db file sequential read (125) | |
=======================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(("A"."MON_NUMBER"=4 AND "A"."STATUS_ID"='401'))
5 - filter((:B1='401' AND :B2=4))
6 - filter("B"."SERIAL_NUMBER"=SUBSTR(:B1,0,17))
merge /*+parallel 4*/
into bs_reso_060451_vc a
using (select distinct msisdn, calling_number, serial_number
from js_ro_settle_detail_vco_04) b
on (substr(a.inv_id, 0, 17) = b.serial_number and a.mon_number = '4' and a.status_id = '401') /*主表条件可以写到on后面*/
when matched then
update
set a.msisdn = b.msisdn, a.callnumber = b.calling_number
Plan hash value: 3425559647
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 214G| 75T| | 7180 (93)| 00:01:41 | | | | | |
| 1 | MERGE | BS_RESO_060451_VC | | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10003 | 214G| 76T| | 7180 (93)| 00:01:41 | | | Q1,03 | P->S | QC (RAND) |
| 4 | VIEW | | | | | | | | | Q1,03 | PCWP | |
|* 5 | HASH JOIN BUFFERED | | 214G| 76T| | 7180 (93)| 00:01:41 | | | Q1,03 | PCWP | |
| 6 | PX RECEIVE | | 3661K| 125M| | 32 (7)| 00:00:01 | | | Q1,03 | PCWP | |
| 7 | PX SEND HASH | :TQ10001 | 3661K| 125M| | 32 (7)| 00:00:01 | | | Q1,01 | P->P | HASH |
| 8 | VIEW | | 3661K| 125M| | 32 (7)| 00:00:01 | | | Q1,01 | PCWP | |
| 9 | HASH UNIQUE | | 3661K| 125M| 168M| 32 (7)| 00:00:01 | | | Q1,01 | PCWP | |
| 10 | PX RECEIVE | | 3661K| 125M| | 32 (7)| 00:00:01 | | | Q1,01 | PCWP | |
| 11 | PX SEND HASH | :TQ10000 | 3661K| 125M| | 32 (7)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 12 | HASH UNIQUE | | 3661K| 125M| 168M| 32 (7)| 00:00:01 | | | Q1,00 | PCWP | |
| 13 | PX BLOCK ITERATOR | | 3661K| 125M| | 30 (0)| 00:00:01 | | | Q1,00 | PCWC | |
| 14 | TABLE ACCESS FULL| JS_RO_SETTLE_DETAIL_VCO_04 | 3661K| 125M| | 30 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 15 | PX RECEIVE | | 5869K| 1998M| | 4838 (91)| 00:01:08 | | | Q1,03 | PCWP | |
| 16 | PX SEND HASH | :TQ10002 | 5869K| 1998M| | 4838 (91)| 00:01:08 | | | Q1,02 | P->P | HASH |
| 17 | PX BLOCK ITERATOR | | 5869K| 1998M| | 4838 (91)| 00:01:08 | 4 | 4 | Q1,02 | PCWC | |
|* 18 | TABLE ACCESS FULL | BS_RESO_060451_VC | 5869K| 1998M| | 4838 (91)| 00:01:08 | 4 | 4 | Q1,02 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("B"."SERIAL_NUMBER"=SUBSTR("A"."INV_ID",0,17))
18 - filter("A"."MON_NUMBER"=4 AND "A"."STATUS_ID"='401')
Note
-----
- dynamic sampling used for this statement (level=2)
- automatic DOP: skipped because of IO calibrate statistics are missing
概述 ：merge join改写后oracle可以选择在nested loops结合Hash join的连接方式进行选择，如果采取原update的方式优化器并不会选择hash join的连接方式，而是选择了nested loops的连接方式，此连接方式对于大表更新执行成本会非常高，特别相关业务表没有索引时会执行多次全表扫描。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
