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
t1contains a data row that meets thet1.c1=src_tbl.c1condition:- If the
src_tbl.c2 > '100'condition is met, the value oft1.c2is updated to that ofsrc_tbl.c2. Each row in the destination table is updated only once. - Otherwise, the row is not updated.
- If the
If
t1does not contain a data row that meets thet1.c1=src_tbl.c1condition:- If a data row meets the
src_tbl.c1 > 10condition, the(src_tbl.c1,src_tbl.c2)row is inserted tot1. - Otherwise, no row is inserted.
- If a data row meets the
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
t2contains a data row that meets thet2.c1=src_tbl.c1condition:- The value of
t2.c2is updated to that ofsubstr(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.
- The value of
If
t2does not contain a data row that meets thet2.c1 = src_tbl.c1condition,(src_tbl.c1, src_tbl.c2)is inserted intot2.
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.