Oracle Merge into中修改表的限制条件位置的区别
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle Merge into中修改表的限制条件位置的区别，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9229字，纯文字阅读大概需要14分钟。
内容图文
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。
参考
https://www.cnblogs.com/wangrui1587165/p/9844979.html
ORACLE sql merge into update where条件位置与性能消耗
内容总结
以上是互联网集市为您收集整理的Oracle Merge into中修改表的限制条件位置的区别全部内容，希望文章能够帮你解决Oracle Merge into中修改表的限制条件位置的区别所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】