MERGE|V4.3.5|OceanBase Database| docs|Distributed Database

MERGE operators merge rows from the source table into the destination table by updating the rows in the destination table or inserting rows from the source table into the destination table.

OceanBase Database supports the following types of MERGE operators: MERGE and MULTI PARTITION MERGE.

Note

MERGE operators are supported only in OceanBase Database in Oracle mode.

MERGE

The MERGE operator merges data in a single partition of the table.

In the following example, query Q1 merges the rows of table src_tbl to table t1. Each data row in table src_tbl is merged by using the following method:

If t1 contains a data row that meets the t1.c1=src_tbl.c1 condition:
- If the src_tbl.c2 > '100' condition is met, the value of t1.c2 is updated to that of src_tbl.c2. Each row in the destination table is updated only once.
- Otherwise, the row is not updated.
If t1 does not contain a data row that meets the t1.c1=src_tbl.c1 condition:
- If a data row meets the src_tbl.c1 > 10 condition, the (src_tbl.c1,src_tbl.c2) row is inserted to t1.
- Otherwise, no row is inserted.

obclient> CREATE TABLE src_tbl (c1 INT PRIMARY KEY, c2 VARCHAR2(10));
Query OK, 0 rows affected

obclient> CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR2(10));
Query OK, 0 rows affected

obclient> CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 VARCHAR2(10)) PARTITION BY HASH(c1)
         PARTITIONS 10;
Query OK, 0 rows affected

Q1:
obclient> EXPLAIN MERGE INTO t1 USING src_tbl ON (t1.c1 = src_tbl.c1)
        WHEN MATCHED THEN
        UPDATE SET t1.c2 = src_tbl.c2 WHERE src_tbl.c2 > '100'
        WHEN NOT MATCHED THEN
        INSERT (t1.c1, t1.c2)  VALUES (src_tbl.c1,src_tbl.c2) WHERE src_tbl.c1 > 10;

Query Plan:
===============================================
|ID|OPERATOR         |NAME   |EST. ROWS|COST  |
-----------------------------------------------
|0 |MERGE            |       |100001   |100001|
|1 | MERGE OUTER JOIN|       |100001   |219005|
|2 |  TABLE SCAN     |SRC_TBL|100000   |61860 |
|3 |  TABLE SCAN     |T1     |100000   |61860 |
===============================================

Outputs & filters:
-------------------------------------
  0 - output([column_conv(DECIMAL,PS:(38,0),NOT NULL,SRC_TBL.C1)], [column_conv(VARCHAR,utf8mb4_bin,length:10,NULL,SRC_TBL.C2)]), filter(nil),
      columns([{T1: ({T1: (T1.C1, T1.C2)})}]), partitions(p0),
      update([T1.C2=column_conv(VARCHAR,utf8mb4_bin,length:10,NULL,SRC_TBL.C2)]),
      match_conds([T1.C1 = SRC_TBL.C1]), insert_conds([SRC_TBL.C1 > 10]),
      update_conds([SRC_TBL.C2 > '100']), delete_conds(nil)
  1 - output([SRC_TBL.C1], [SRC_TBL.C2], [T1.C1], [T1.C1 = SRC_TBL.C1], [T1.C2]), filter(nil),
      equal_conds([T1.C1 = SRC_TBL.C1]), other_conds(nil)
  2 - output([SRC_TBL.C1], [SRC_TBL.C2]), filter(nil),
      access([SRC_TBL.C1], [SRC_TBL.C2]), partitions(p0)
  3 - output([T1.C1], [T1.C2]), filter(nil),
      access([T1.C1], [T1.C2]), partitions(p0)

OUTER JOIN is necessary for the MERGE operator. When the MERGE operator is used, an outer join operation must be performed on the source and destination tables to distinguish matched rows from unmatched ones.

In the preceding example, the Outputs & filters section shows in detail the output information of the MERGE operator.

Field	Description
output	The output expressions of the operator.
filter	The filter conditions of the operator. In this example, `filter` is set to `nil` because no filter condition is configured for the `MERGE` operator.
columns	The table columns involved in the insert operation.
partitions	The partition of the source table that contains the data to be inserted.
update	The assignment expressions in the update operation.
match_conds	The condition for matching the source table with the destination table.
insert_conds	The condition for inserting data into the destination table.
update_conds	The condition for updating data in the destination table.
delete_conds	The condition for deleting data.

MULTI PARTITION MERGE

The MULTI PARTITION MERGE operator merges data in multiple partitions of a table.

In the following example, query Q2 merges the rows of table src_tbl to partitioned table t2. Each data row in table src_tbl is merged by using the following method:

If t2 contains a data row that meets the t2.c1=src_tbl.c1 condition:
- The value of t2.c2 is updated to that of substr(src_tbl.c2, 1, 5). Each row in the destination table is updated only once.
- After the update, if the t2.c2 > '80000' condition is met, the corresponding data row is deleted.
If t2 does not contain a data row that meets the t2.c1 = src_tbl.c1 condition, (src_tbl.c1, src_tbl.c2) is inserted into t2.

Q2:
obclient> EXPLAIN MERGE INTO t2 USING SRC_TBL ON (t2.c1 = src_tbl.c1)
        WHEN MATCHED THEN
        UPDATE SET t2.c2 = SUBSTR(src_tbl.c2, 1, 5)
        DELETE WHERE t2.c2 > '80000'
        WHEN NOT MATCHED THEN
        INSERT (t2.c1, t2.c2) VALUES (src_tbl.c1,src_tbl.c2);

Query Plan:
============================================================

|ID|OPERATOR                      |NAME    |EST. ROWS|COST   |
--------------------------------------------------------------
|0 |MULTI PARTITION MERGE         |        |100000   |100000 |
|1 | PX COORDINATOR               |        |100000   |956685 |
|2 |  EXCHANGE OUT DISTR          |:EX10001|100000   |899889 |
|3 |   MERGE OUTER JOIN           |        |100000   |899889 |
|4 |    EXCHANGE IN DISTR         |        |100000   |90258  |
|5 |     EXCHANGE OUT DISTR (PKEY)|:EX10000|100000   |61860  |
|6 |      TABLE SCAN              |SRC_TBL |100000   |61860  |
|7 |    SORT                      |        |1000000  |5447108|
|8 |     PX PARTITION ITERATOR    |        |1000000  |618524 |
|9 |      TABLE SCAN              |T2      |1000000  |618524 |
==============================================================

Outputs & filters:
-------------------------------------
  0 - output([column_conv(DECIMAL,PS:(38,0),NOT NULL,SRC_TBL.C1)], [column_conv(VARCHAR,utf8mb4_bin,length:10,NULL,SRC_TBL.C2)]), filter(nil),
      columns([{T2: ({T2: (T2.C1, T2.C2)})}]), partitions(p[0-9]),
      update([T2.C2=column_conv(VARCHAR,utf8mb4_bin,length:10,NULL,SUBSTR(SRC_TBL.C2, 1, 5))]),
      match_conds([T2.C1 = SRC_TBL.C1]), insert_conds(nil),
      update_conds(nil), delete_conds([T2.C2 > '80000'])
  1 - output([SRC_TBL.C1], [SRC_TBL.C2], [T2.C1], [T2.C1 = SRC_TBL.C1], [T2.C2]), filter(nil)
  2 - output([SRC_TBL.C1], [SRC_TBL.C2], [T2.C1], [T2.C1 = SRC_TBL.C1], [T2.C2]), filter(nil), dop=1
  3 - output([SRC_TBL.C1], [SRC_TBL.C2], [T2.C1], [T2.C1 = SRC_TBL.C1], [T2.C2]), filter(nil),
      equal_conds([T2.C1 = SRC_TBL.C1]), other_conds(nil)
  4 - output([SRC_TBL.C1], [SRC_TBL.C2]), filter(nil)
  5 - (#keys=1, [SRC_TBL.C1]), output([SRC_TBL.C1], [SRC_TBL.C2]), filter(nil), is_single, dop=1
  6 - output([SRC_TBL.C1], [SRC_TBL.C2]), filter(nil),
      access([SRC_TBL.C1], [SRC_TBL.C2]), partitions(p0)
  7 - output([T2.C1], [T2.C2]), filter(nil), sort_keys([T2.C1, ASC]), local merge sort
  8 - output([T2.C1], [T2.C2]), filter(nil)
  9 - output([T2.C1], [T2.C2]), filter(nil),
      access([T2.C1], [T2.C2]), partitions(p[0-9])

In the preceding example, the Outputs & filters section shows in detail the output information of the MULTI PARTITION MERGE operator. The fields of the operator have the same meaning as those of the MERGE operator.

OceanBase

Customer Stories

Documentation

MERGE

Note

MERGE

MULTI PARTITION MERGE