MERGE演算子は、ソーステーブルのデータ行を更新または挿入する方法でターゲットテーブルにマージします。
OceanBaseデータベースがサポートするMERGE演算子にはMERGEとMULTI PARTITION MERGEが含まれます。
説明
MERGE演算子は、OceanBaseデータベースのOracleモード専用です。
MERGE
MERGE 演算子は、データテーブルの単一パーティション内のデータをマージするために使用されます。
以下の例のように、Q1クエリは src_tbl テーブルのデータ行を t1 テーブルにマージします。src_tbl 内の各データ行について、次の方法でマージが実行されます：
t1テーブルにt1.c1=src_tbl.c1の条件を満たすデータ行が存在する場合：src_tbl.c2 > '100'を満たす場合、更新操作を実行し、t1.c2の値をsrc_tbl.c2の値に設定します（ターゲットテーブルの各行は1回しか更新されません）。src_tbl.c2 > '100'を満たさない場合、更新操作は実行されません。
t1テーブルにt1.c1 = src_tbl.c1の条件を満たすデータ行が存在しない場合：src_tbl.c1 > 10を満たす場合、挿入操作を実行し、(src_tbl.c1,src_tbl.c2)をt1テーブルに挿入します。src_tbl.c1 > 10を満たさない場合、挿入操作は指定されません。
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 はマージ機能の実装に依存する1回の結合操作です。MERGE 演算子を使用する場合、必ず source_table と target_table に対して1回の外部結合が実行されます。これは、どの行が一致し、どの行が一致しないかを区別するためです。
実行計画表示の outputs & filters では、MERGE 演算子の出力情報が詳細に示されています。
| 情報名 | 意味 |
|---|---|
| output | この演算子が出力する式。 |
| filter | この演算子のフィルター条件。例ではMERGE演算子にfilterがないため、nilとなります。 |
| columns | 挿入操作に関連するデータテーブルの列。 |
| partitions | 挿入操作に関連するデータテーブルのパーティション。 |
| update | 更新操作におけるすべての代入式。 |
| match_conds | ソーステーブルとターゲットテーブルのマッチング条件。 |
| insert_conds | 挿入操作で満たす必要がある条件。 |
| update_conds | 更新操作で満たす必要がある条件。 |
| delete_conds | 削除操作で満たす必要がある条件。 |
MULTI PARTITION MERGE
MULTI PARTITION MERGE 演算子は、データテーブルの複数のパーティションにあるデータをマージするために使用されます。
以下の例のように、Q2クエリは src_tbl テーブルのデータ行をパーティションテーブル t2 にマージします。src_tbl テーブル内の各データ行について、次の方法でマージが実行されます：
t2テーブルにt2.c1 = src_tbl.c1の条件を満たすデータ行が存在する場合：- 更新操作を実行し、
t2.c2の値をsubstr(src_tbl.c2, 1, 5)の値に設定します（ターゲットテーブル内の各行は1回しか更新されません）。 - 更新完了後、
t2.c2 > '80000'を満たす場合、対応するデータ行を削除します。
- 更新操作を実行し、
t2テーブルにt2.c1 = src_tbl.c1の条件を満たすデータ行が存在しない場合、挿入操作を実行し、(src_tbl.c1, src_tbl.c2)を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])
上記の例の実行計画表示における outputs & filters は、MULTI PARTITION MERGE 演算子の出力情報を詳細に示しています。フィールドの意味は MERGE 演算子と同じです。