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。
参考