Oracle Merge into中修改表的限制条件位置的区别
Oracle Merge into中修改表的限制条件位置的区别
前言
版本：11.2.0.4.0
说明：不讨论有insert子句的情况
对于样例语句如下：
merge into t1 using t2 on (t1.id = t2.id) when matched then update set t1.name = t2.name;
想对t1做限制只更新t1.id=1的语句，那么下边3条SQL有什么区别:
merge into (select * from t1 where t1.id=1) tt1 | merge into t1 | merge into t1 using t2 | using t2 | using t2 on (tt1.id = t2.id) | on (t1.id = t2.id) | on (t1.id = t2.id and t1.id=1) when matched then | when matched then | when matched then update set t1.name = t2.name; | update set t1.name = t2.name where t1.id=1; | update set t1.name = t2.name;
构造环境
按照https://www.cnblogs.com/wangrui1587165/p/9844979.html里边的表做案例吧。
create table student (stu_id varchar2(4),stu_name varchar2(4),sex varchar2(1),credit varchar2(2)); insert into student values('0001','大王','2','88'); insert into student values('0002','刘一','1','88'); insert into student values('0003','陈二','2','66'); insert into student values('0004','张三','0','66'); create table student_temp (stu_id varchar2(4),stu_name varchar2(4),sex varchar2(1),credit varchar2(2)); insert into student_temp values('0001','大王','2','77'); insert into student_temp values('0002','刘一','1','77'); commit;
表数据如下：
17:04:52 ZKM@zkm(413)> select * from student; STU_ID STU_NAME SEX CREDIT ------------ ------------ --- ------ 0001 大王 2 88 0002 刘一 1 88 0003 陈二 2 66 0004 张三 0 66 Elapsed: 00:00:00.00 17:04:56 ZKM@zkm(413)> select * from student_temp; STU_ID STU_NAME SEX CREDIT ------------ ------------ --- ------ 0001 大王 2 77 0002 刘一 1 77 Elapsed: 00:00:00.00
对如下三条语句做比对：
1 2 3
merge into (select * from student_temp where stu_id = '0001') t1 | merge into student_temp t1 | merge into student_temp t1 using (select stu_id, stu_name, sex, credit from student) t2 | using (select stu_id, stu_name, sex, credit from student) t2 | using (select stu_id, stu_name, sex, credit from student) t2 on (t1.stu_id = t2.stu_id) | on (t1.stu_id = t2.stu_id) | on (t1.stu_id = t2.stu_id and t1.stu_id = '0001') when matched then | when matched then | when matched then update set t1.credit = t2.credit ; | update set t1.credit = t2.credit where t1.stu_id = '0001'; | update set t1.credit = t2.credit;
执行计划和执行后表student_temp数据结果如下：
1.
16:01:46 ZKM@zkm(392)> select * from student_temp; STU_ID STU_NAME SEX CREDIT ------------ ------------ --- ------ 0001 大王 2 88 0002 刘一 1 77
-------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 1 | | 0 |00:00:00.01 | 16 | | | | | 1 | MERGE | STUDENT_TEMP | 1 | | 0 |00:00:00.01 | 16 | | | | | 2 | VIEW | | 1 | | 1 |00:00:00.01 | 14 | | | | |* 3 | HASH JOIN | | 1 | 1 | 1 |00:00:00.01 | 14 | 1421K| 1421K| 695K (0)| |* 4 | TABLE ACCESS FULL| STUDENT | 1 | 1 | 1 |00:00:00.01 | 7 | | | | |* 5 | TABLE ACCESS FULL| STUDENT_TEMP | 1 | 1 | 1 |00:00:00.01 | 7 | | | | -------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("STUDENT_TEMP"."STU_ID"="STU_ID") 4 - filter("STU_ID"='0001') 5 - filter("STU_ID"='0001')
2.
16:02:24 ZKM@zkm(392)> select * from student_temp; STU_ID STU_NAME SEX CREDIT ------------ ------------ --- ------ 0001 大王 2 88 0002 刘一 1 77 -------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 1 | | 0 |00:00:00.01 | 16 | | | | | 1 | MERGE | STUDENT_TEMP | 1 | | 0 |00:00:00.01 | 16 | | | | | 2 | VIEW | | 1 | | 2 |00:00:00.01 | 14 | | | | |* 3 | HASH JOIN | | 1 | 2 | 2 |00:00:00.01 | 14 | 1185K| 1185K| 795K (0)| | 4 | TABLE ACCESS FULL| STUDENT_TEMP | 1 | 2 | 2 |00:00:00.01 | 7 | | | | | 5 | TABLE ACCESS FULL| STUDENT | 1 | 4 | 4 |00:00:00.01 | 7 | | | | -------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."STU_ID"="STU_ID")
3.
16:03:05 ZKM@zkm(392)> select * from student_temp; STU_ID STU_NAME SEX CREDIT ------------ ------------ --- ------ 0001 大王 2 88 0002 刘一 1 77 -------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 1 | | 0 |00:00:00.01 | 16 | | | | | 1 | MERGE | STUDENT_TEMP | 1 | | 0 |00:00:00.01 | 16 | | | | | 2 | VIEW | | 1 | | 1 |00:00:00.01 | 14 | | | | |* 3 | HASH JOIN | | 1 | 1 | 1 |00:00:00.01 | 14 | 1421K| 1421K| 695K (0)| |* 4 | TABLE ACCESS FULL| STUDENT | 1 | 1 | 1 |00:00:00.01 | 7 | | | | |* 5 | TABLE ACCESS FULL| STUDENT_TEMP | 1 | 1 | 1 |00:00:00.01 | 7 | | | | -------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("T1"."STU_ID"="STU_ID") 4 - filter("STU_ID"='0001') 5 - filter("T1"."STU_ID"='0001')
对于结果，可以得到：
1 2 3 merge into (select * from student_temp where stu_id = '0001') t1 | merge into student_temp t1 | merge into student_temp t1 using (select stu_id, stu_name, sex, credit from student) t2 | using (select stu_id, stu_name, sex, credit from student) t2 | using (select stu_id, stu_name, sex, credit from student) t2 on (t1.stu_id = t2.stu_id) | on (t1.stu_id = t2.stu_id) | on (t1.stu_id = t2.stu_id and t1.stu_id = '0001') when matched then | when matched then | when matched then update set t1.credit = t2.credit ; | update set t1.credit = t2.credit where t1.stu_id = '0001'; | update set t1.credit = t2.credit;
执行结果均相同，若是HASH JOIN前提下，对于第2条语句消耗的内存更多，这是因为第2条在id4和id5处没有对student和student_temp表先做过滤。而第1条和第3条则一样的性能
建议使用第1种写法。原因是第3种写法从理解上看没有第一种好理解。
而且如果在when not matched when insert子句下，意义就完全不一样了，参考https://www.cnblogs.com/wangrui1587165/p/9844979.html。
参考
分类:
Oracle
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？