Oracle Parallel Merge Into Plan

以下示例表和数据来自 OceanBase 官网：

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
--------------------------------------------------------------------------------
Plan hash value: 1849147112

-----------------------------------------------------------------------------
| 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: 253587341

------------------------------------------------------------------------------------------------------------------------
| 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 |            |
-----------------------------------------------------------------------------------------------------------------------------------------------------------