A MERGE operator merges 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.
MERGE operators supported by OceanBase Database include MERGE and MULTI PARTITION MERGE.
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 table
t1contains a data row that meets thet1.c1=src_tbl.c1condition:
- If table
t1does not contain a data row that meets thet1.c1=src_tbl.c1condition:
In the preceding code, the
OUTER JOINclause is necessary for theMERGEoperator. When theMERGEoperator 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 & filterssection in the execution plan demo shows in detail the output information of theMERGEoperator.
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 table
t2contains a data row that meets thet2.c1=src_tbl.c1condition:
- If table
t2does not contain a data row that meets thet2.c1 = src_tbl.c1condition,(src_tbl.c1, src_tbl.c2)is inserted into tablet2.
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)\G;
*************************** 1. row ***************************
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 in the execution plan demo shows in detail the output information of the MULTI PARTITION MERGE operator. Fields of the operator have the same meaning as those of the MERGE operator.