All Products
Search
Document Center

MERGE

Last Updated: Jun 18, 2021

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 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 table t1 does not contain a data row that meets the t1.c1=src_tbl.c1 condition:

    • If the src_tbl.c1 > 10 condition is met, the (src_tbl.c1,src_tbl.c2) row is inserted to table t1.

    • Otherwise, no row is inserted.

    obclient>CREATE TABLE src_tbl (c1 INT PRIMARY KEY, c2 VARCHAR2(10));
    Query OK, 0 rows affected (0.12 sec)
    
    obclient>CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 VARCHAR2(10));
    Query OK, 0 rows affected (0.12 sec)
    
    obclient>CREATE TABLE t2 (c1 INT PRIMARY KEY, c2 VARCHAR2(10)) PARTITION BY HASH(c1) 
             PARTITIONS 10;
    Query OK, 0 rows affected (0.12 sec)
    
    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\G;
    *************************** 1. row ***************************
    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)

    In the preceding code, the OUTER JOIN clause 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 in the execution plan demo shows in detail the output information of the MERGE operator.

    Field

    Description

    output

    The output expression of the operator.

    filter

    The filter conditions of the operator.

    In this example, the condition is set to nil because no filter 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 to 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 table 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 tablet2 does not contain a data row that meets the t2.c1 = src_tbl.c1 condition, (src_tbl.c1, src_tbl.c2) is inserted into table 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)\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.