最近在读剑破冰山这本书,写写博文,当做做笔记。感谢那些出书的人,感谢他们分享的知识。
1、构造测试表 d1、d2
CREATE TABLE d1 AS SELECT * FROM scott.dept;
CREATE TABLE d2 AS SELECT * FROM scott.dept;
delete from d2 where deptno in (40, 20);
SQL> select * from d1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from d2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING FU JIAN
30 SALES CHICAGO
2、使用d1表的记录去更新d2表的记录
MERGE INTO d2
USING d1
ON (d1.deptno = d2.deptno)
WHEN MATCHED THEN
UPDATE SET d2.loc = d1.loc || '...'
WHEN NOT MATCHED THEN
INSERT VALUES(d1.deptno, d1.dname, d1.loc)
SQL> select * from d2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK...
30 SALES CHICAGO...
40 OPERATIONS BOSTON
20 RESEARCH DALLAS
3、查看 merge 语句的执行计划
--将环境变到原来
SQL> rollback;
Rollback complete.
SQL> select * from d1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from d2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING FU JIAN
30 SALES CHICAGO
--实行merge操作并查看执行计划
EXPLAIN PLAN FOR
MERGE INTO d2
USING d1
ON (d1.deptno = d2.deptno)
WHEN MATCHED THEN
UPDATE SET d2.loc = d1.loc || '...'
WHEN NOT MATCHED THEN
INSERT VALUES(d1.deptno, d1.dname, d1.loc);
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Plan hash value: 872405927
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 4 | 240 | 8 (13)| 00:00:01 |
| 1 | MERGE | D2 | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN OUTER | | 4 | 288 | 8 (13)| 00:00:01 |
| 4 | TABLE ACCESS FULL| D1 | 4 | 120 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| D2 | 2 | 84 | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D1"."DEPTNO"="D2"."DEPTNO"(+))
Note
-----
- dynamic sampling used for this statement (level=2)
21 rows selected.
4、merge只执行update 或 只执行insert
SQL> rollback;
Rollback complete.
SQL> select * from d1;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from d2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING FU JIAN
30 SALES CHICAGO
MERGE INTO d2
USING d1
ON (d1.deptno = d2.deptno)
WHEN MATCHED THEN
UPDATE SET d2.loc = d1.loc || '...';
SQL> select * from d2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK...
30 SALES CHICAGO...
SQL> rollback;
Rollback complete.
Wrote file afiedt.buf
select * from d2
MERGE INTO d2
USING d1
ON (d1.deptno = d2.deptno)
WHEN NOT MATCHED THEN
INSERT VALUES(d1.deptno, d1.dname, d1.loc);
SQL> select * from d2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING FU JIAN
30 SALES CHICAGO
40 OPERATIONS BOSTON
20 RESEARCH DALLAS
5、merge语句添加条件更新,只更新deptno = 10的行(提示:下面的操作都是基于开始的d1、和d2表数据)
MERGE INTO d2
USING d1
ON (d1.deptno = d2.deptno)
WHEN MATCHED THEN
UPDATE SET d2.loc = d1.loc || '...'
WHERE d1.deptno = 10
SQL> select * from d2;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK...
30 SALES CHICAGO
6、在merge中使用delete子句清除行,只删除deptno = 10的行
MERGE INTO d2
USING d1
ON (d1.deptno = d2.deptno)
WHEN MATCHED THEN
UPDATE SET d2.loc = d1.loc || '...'
DELETE WHERE (d2.deptno = 10)
SQL> select * from d2;
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO...
7、merge无条件insert
MERGE INTO d2
USING d1
ON (1 = 2)
WHEN NOT MATCHED THEN
INSERT VALUES (d1.deptno, d1.dname, d1.loc);
SQL> select * from d2;
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO...
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON