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进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
786次阅读
2025-04-18 14:18:38
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
664次阅读
2025-04-15 17:24:06
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
591次阅读
2025-04-20 10:07:02
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
548次阅读
2025-04-22 00:13:51
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
532次阅读
2025-04-17 17:02:24
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
510次阅读
2025-04-22 00:20:37
一页概览：Oracle GoldenGate
甲骨文云技术
496次阅读
2025-04-30 12:17:56
火焰图--分析复杂SQL执行计划的利器
听见风的声音
470次阅读
2025-04-17 09:30:30
OR+DBLINK的关联SQL优化思路
布衣
382次阅读
2025-05-05 19:28:36
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
382次阅读
2025-04-15 14:48:05
