JOIN演算子は、特定の条件に基づいて2つのテーブルのデータを結合するために使用されます。
JOINのタイプには主に内部結合（Inner Join）、外部結合（Outer Join）、およびセミ/アンチ結合（Semi/Anti Join）の3種類があります。
OceanBaseデータベースがサポートするJOIN演算子には、主にNESTED LOOP JOIN (NLJ)、MERGE JOIN (MJ)、およびHASH JOIN (HJ)が含まれます。
NESTED LOOP JOIN (NLJ)
以下の例では、Q1とQ2のクエリでHintを使用してNLJを指定しています。ここで、0番演算子はNLJ演算子です。この演算子には2つのサブノードがあり、それぞれ1番演算子と2番演算子です。その実行ロジックは以下の通りです：
1番演算子から1行を読み取ります。
2番演算子を開き、すべての行を読み取ります。
1番と2番の演算子の出力結果を結合し、フィルタ条件を実行して出力結果を出力します。
1番演算子の反復が終了するまで、最初のステップを繰り返します。
obclient> CREATE TABLE t1 (c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> CREATE TABLE t2 (d1 INT, d2 INT, PRIMARY KEY (d1));
Query OK, 0 rows affected
Q1:
obclient> EXPLAIN SELECT /*+USE_NL(t1, t2)*/ t1.c2 + t2.d2 FROM t1, t2 WHERE c2 = d2;
Query Plan:
===========================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
-------------------------------------------
|0 |NESTED-LOOP JOIN| |9782 |411238|
|1 | TABLE FULL SCAN |T1 |999 |647 |
|2 | MATERIAL | |999 |1519 |
|3 | TABLE FULL SCAN |T2 |999 |647 |
===========================================
Outputs & filters:
-------------------------------------
0 - output([T1.C2 + T2.D2]), filter(nil),
conds([T1.C2 = T2.D2]), nl_params_(nil)
1 - output([T1.C2]), filter(nil),
access([T1.C2]), partitions(p0)
2 - output([T2.D2]), filter(nil)
3 - output([T2.D2]), filter(nil),
access([T2.D2]), partitions(p0)
ここで、MATERIAL演算子は下層演算子の出力データをマテリアライズするために使用されます。詳細については、MATERIALを参照してください。
Q2:
obclient> EXPLAIN SELECT /*+USE_NL(t1, t2)*/ t1.c2 + t2.d2 FROM t1, t2 WHERE c1 = d1;
Query Plan:
| ==========================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
------------------------------------------
|0 |NESTED-LOOP JOIN| |990 |37346|
|1 | TABLE FUll SCAN |T1 |999 |669 |
|2 | TABLE GET |T2 |1 |36 |
==========================================
Outputs & filters:
-------------------------------------
0 - output([T1.C2 + T2.D2]), filter(nil),
conds(nil), nl_params_([T1.C1])
1 - output([T1.C1], [T1.C2]), filter(nil),
access([T1.C1], [T1.C2]), partitions(p0)
2 - output([T2.D2]), filter(nil),
access([T2.D2]), partitions(p0)
上記の例では、実行計画表示のoutputs & filtersにおいて、NESTED LOOP JOIN演算子の具体的な出力情報が以下のように詳細に示されています：
| 情報名 | 意味 |
|---|---|
| output | この演算子が出力する式。 |
| filter | この演算子のフィルタ条件。例ではNLJ演算子にfilterが設定されていないため、nilとなります。 |
| conds | 接続条件。例えばQ1クエリのt1.c2 = t2.d2接続条件。 |
| nl_params_ | NLJ左テーブルのデータに基づいて生成されるプッシュダウンパラメータ。例えばQ2クエリのt1.c1。NLJは左テーブルの各行をイテレーションするたびに、nl_paramsに基づいてパラメータを構築し、このパラメータと元の接続条件c1 = d1に基づいて、右テーブル上のフィルタ条件を構築します：d1 = ?。このフィルタ条件は右テーブルにプッシュダウンされ、インデックス上のクエリ範囲、つまりどの範囲のデータをスキャンする必要があるかを抽出します。Q2クエリでは、プッシュダウン条件d1 = ?が存在するため、2番目の演算子はTABLE GET演算子です。 |
以下の例では、Q3クエリでは結合条件が一切指定されておらず、0番演算子はNESTED-LOOP JOIN CARTESIANとして表示されています。論理的には、それは依然としてNLJ演算子であり、結合条件のないNLJを表します。
Q3:
obclient> EXPLAIN SELECT t1.c2 + t2.d2 FROM t1, t2;
*************************** 1. row ***************************
Query Plan:
| =====================================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
-----------------------------------------------------
|0 |NESTED-LOOP JOIN CARTESIAN| |998001 |747480|
|1 | TABLE FULL SCAN |T1 |999 |647 |
|2 | MATERIAL | |999 |1519 |
|3 | TABLE FULL SCAN |T2 |999 |647 |
=====================================================
Outputs & filters:
-------------------------------------
0 - output([T1.C2 + T2.D2]), filter(nil),
conds(nil), nl_params_(nil)
1 - output([T1.C2]), filter(nil),
access([T1.C2]), partitions(p0)
2 - output([T2.D2]), filter(nil)
3 - output([T2.D2]), filter(nil),
access([T2.D2]), partitions(p0)
MERGE JOIN (MJ)
以下の例では、Q4クエリで USE_MERGE ヒントを使用して、クエリがMJを使用するように指定されています。ここで、0番演算子はMJ演算子であり、2つのサブノード、すなわち1番と3番の演算子を持っています。この演算子は左右のサブノードのデータをマージ結合するため、左右のサブノードのデータは結合列に対して順序付けられている必要があります。
Q4クエリを例にとると、結合条件は t1.c2 = t2.d2 であり、テーブル t1 のデータは c2 列に従ってソートされ、テーブル t2 のデータは d2 列に従ってソートされることを要求します。Q4クエリでは、2番演算子の出力は無秩序であり、4番演算子の出力は d1 列に従ってソートされており、どちらもMJの順序要件を満たしていません。そのため、1番と3番の演算子にソートを割り当てています。
Q4:
obclient> EXPLAIN SELECT /*+USE_MERGE(t1, t2)*/ t1.c2 + t2.d2 FROM t1, t2 WHERE c2 = d2 AND c1 + d1 > 10;
Query Plan:
| ======================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
--------------------------------------
|0 |MERGE JOIN | |3261 |14199|
|1 | SORT | |999 |4505 |
|2 | TABLE FULL SCAN|T1 |999 |669 |
|3 | SORT | |999 |4483 |
|4 | TABLE FULL SCAN|T2 |999 |647 |
======================================
Outputs & filters:
-------------------------------------
0 - output([T1.C2 + T2.D2]), filter(nil),
equal_conds([T1.C2 = T2.D2]), other_conds([T1.C1 + T2.D1 > 10])
1 - output([T1.C2], [T1.C1]), filter(nil), sort_keys([T1.C2, ASC])
2 - output([T1.C2], [T1.C1]), filter(nil),
access([T1.C2], [T1.C1]), partitions(p0)
3 - output([T2.D2], [T2.D1]), filter(nil), sort_keys([T2.D2, ASC])
4 - output([T2.D2], [T2.D1]), filter(nil),
access([T2.D2], [T2.D1]), partitions(p0)
以下の例では、Q5クエリの結合条件は t1.c1 = t2.d1 であり、テーブル t1 のデータは c1 列に従ってソートされ、テーブル t2 のデータは d1 列に従ってソートされることを要求します。この実行計画では、テーブル t2 はメインテーブルスキャンを選択しており、結果は d1 列に従ってソートされているため、追加の SORT 演算子を割り当てる必要はありません。理想的な場合、JOIN の左右のテーブルに適切なインデックスが選択され、インデックスが提供するデータの順序がMJの要件を満たしている場合、SORT 演算子を割り当てる必要はありません。
Q5:
obclient> EXPLAIN SELECT /*+USE_MERGE(t1, t2)*/ t1.c2 + t2.d2 FROM t1, t2 WHERE c1 = d1;
Query Plan:
| =====================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-------------------------------------
|0 |MERGE JOIN | |990 |6096|
|1 | SORT | |999 |4505|
|2 | TABLE FULL SCAN|T1 |999 |669 |
|3 | TABLE FULL SCAN |T2 |999 |647 |
=====================================
Outputs & filters:
-------------------------------------
0 - output([T1.C2 + T2.D2]), filter(nil),
equal_conds([T1.C1 = T2.D1]), other_conds(nil)
1 - output([T1.C2], [T1.C1]), filter(nil), sort_keys([T1.C1, ASC])
2 - output([T1.C1], [T1.C2]), filter(nil),
access([T1.C1], [T1.C2]), partitions(p0)
3 - output([T2.D1], [T2.D2]), filter(nil),
access([T2.D1], [T2.D2]), partitions(p0)
上記の例では、実行計画に表示される outputs & filters において、MERGE JOIN 演算子の具体的な出力情報が詳細に示されています。
| 情報名 | 意味 |
|---|---|
| output | この演算子が出力する式。 |
| filter | この演算子におけるフィルタ条件。MJ演算子ではfilterが設定されていないため、nilとなります。 |
| equal_conds | マージ結合時に使用される等価結合条件であり、左右のサブノードの結果セットは結合列に対して順序付けられていなければなりません。 |
| other_conds | その他の結合条件。例えばQ4クエリのt1.c1 + t2.d1 > 10などです。 |
HASH JOIN (HJ)
以下の例では、Q6クエリは USE_HASH ヒントを使用して、クエリがHJを使用することを指定しています。ここで、0番演算子はHJ演算子であり、それには2つのサブノード、すなわち1番と2番の演算子があります。この演算子の実行ロジックステップは以下のとおりです：
左サブノードのデータを読み取り、結合列に基づいてハッシュ値（例えば
t1.c1）を計算し、ハッシュテーブルを構築します。右サブノードのデータを読み取り、結合列に基づいてハッシュ値（例えば
t2.d1）を計算し、対応するハッシュテーブル内のt1のデータと結合を試みます。
Q6:
obclient> EXPLAIN SELECT /*+USE_HASH(t1, t2)*/ t1.c2 + t2.d2 FROM t1, t2 WHERE c1 = d1 AND c2 + d2 > 1;
Query Plan:
| ====================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
------------------------------------
|0 |HASH JOIN | |330 |4850|
|1 | TABLE FULL SCAN|T1 |999 |669 |
|2 | TABLE FULL SCAN|T2 |999 |647 |
====================================
Outputs & filters:
-------------------------------------
0 - output([T1.C2 + T2.D2]), filter(nil),
equal_conds([T1.C1 = T2.D1]), other_conds([T1.C2 + T2.D2 > 1])
1 - output([T1.C1], [T1.C2]), filter(nil),
access([T1.C1], [T1.C2]), partitions(p0)
2 - output([T2.D1], [T2.D2]), filter(nil),
access([T2.D1], [T2.D2]), partitions(p0)
上記の例では、実行計画表示の outputs & filters は、HASH JOIN 演算子の出力情報を詳細に示しています。
| 情報名 | 意味 |
|---|---|
| output | この演算子が出力する式。 |
| filter | この演算子におけるフィルタ条件。 HJ 演算子では filter が設定されていないため、nil となります。 |
| equal_conds | 等価結合であり、左右両側の結合列を用いてハッシュ値を計算します。 |
| other_conds | その他の結合条件。 例えばQ6クエリの t1.c2 + t2.d2 > 1 のような条件です。 |