CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
INSERT INTO t1 VALUES(0, 0);
INSERT INTO t1 VALUES(1, null);
INSERT INTO t1 VALUES(2, null);
INSERT INTO t2 VALUES(1, 1);
INSERT INTO t2 VALUES(2, 20);
INSERT INTO t2 VALUES(3, 3);
INSERT INTO t2 VALUES(4, 40);
根据 T2 的数据更新 T1 的数据:
如果 t1.c1 在 t2 中能够找到 c1 取值相同的行,如果 t1.c2 取值为空,那么使用 t2.c2 更新 t1.c2;如果更新之后t1.c2 >= 10,那么删除该行。
如果 t2.c1 在 t1 中找不到取值相同的行,将 t2 中的这行插入到 t1 中,且只插入满足 t2.c2 < 10 的行。
obclient>MERGE INTO t1 USING t2 ON (t1.c1 = t2.c1)
WHEN MATCHED THEN UPDATE SET c2 = t2.c2 where t1.c2 IS NULL DELETE
WHERE t1.c2 >= 10 WHEN NOT MATCHED THEN INSERT VALUES(t2.c1, t2.c2)
WHERE t2.c2 < 10;
Query OK, 3 rows affected (0.02 sec)
obclient>SELECT * FROM t1;
+------+------+
| C1 | C2 |
+------+------+
| 0 | 0 |
| 1 | 1 |
| 3 | 3 |
+------+------+
3 rows in set (0.00 sec)
在 Oracle 中,其串行计划和并行计划有什么区别呢?
SQL> @?/rdbms/admin/utlxplp
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 4 | 260 | 6 (0)| 00:00:01 |
| 1 | MERGE | T1 | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN OUTER | | 4 | 256 | 6 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T2 | 4 | 104 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T1 | 3 | 114 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"(+)="T2"."C1")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
21 rows selected.
SQL> alter session force parallel dml parallel 10;
SQL> @?/rdbms/admin/utlxplp
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
Plan hash value:
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 4 | 260 | 4 (0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10003 | 4 | 256 | 4 (0)| 00:00:01 | Q1,03 | P->S | QC (RAND) |
| 3 | MERGE | T1 | | | | | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 4 | 256 | 4 (0)| 00:00:01 | Q1,03 | PCWP | |
| 5 | PX SEND (ROWID RANDOM) | :TQ10002 | 4 | 256 | 4 (0)| 00:00:01 | Q1,02 | P->P | (ROWID RAND|
| 6 | VIEW | | | | | | Q1,02 | PCWP | |
|* 7 | HASH JOIN OUTER BUFFERED| | 4 | 256 | 4 (0)| 00:00:01 | Q1,02 | PCWP | |
| 8 | PX RECEIVE | | 4 | 104 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10000 | 4 | 104 | 2 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | 4 | 104 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 11 | TABLE ACCESS FULL | T2 | 4 | 104 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 12 | PX RECEIVE | | 3 | 114 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 13 | PX SEND HASH | :TQ10001 | 3 | 114 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 14 | PX BLOCK ITERATOR | | 3 | 114 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 15 | TABLE ACCESS FULL | T1 | 3 | 114 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("T1"."C1"(+)="T2"."C1")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
31 rows selected.
如果在 t1.c2 上添加一个唯一索引,则计划变成:
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 4 | 260 | 2 (0)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 4 | 256 | 2 (0)| 00:00:01 | | | Q1,02 | P->S | QC (RAND) |
| 3 | INDEX MAINTENANCE | T1 | | | | | | | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 4 | 256 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 5 | PX SEND RANGE | :TQ10001 | 4 | 256 | 2 (0)| 00:00:01 | | | Q1,01 | P->P | RANGE |
| 6 | MERGE | T1 | | | | | | | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 4 | 256 | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX SEND HYBRID (ROWID PKEY) | :TQ10000 | 4 | 256 | 2 (0)| 00:00:01 | | | Q1,00 | P->P | HYBRID (ROW|
| 9 | VIEW | | | | | | | | Q1,00 | PCWP | |
| 10 | NESTED LOOPS OUTER | | 4 | 256 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 11 | PX BLOCK ITERATOR | | | | | | | | Q1,00 | PCWC | |
| 12 | TABLE ACCESS FULL | T2 | 4 | 104 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 13 | TABLE ACCESS BY GLOBAL INDEX ROWID| T1 | 1 | 38 | 0 (0)| 00:00:01 | ROWID | ROWID | Q1,00 | PCWP | |
|* 14 | INDEX UNIQUE SCAN | SYS_C00132058 | 1 | | 0 (0)| 00:00:01 | | | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------