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番オペレーターの反復終了まで、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演算子であり、1番と3番の演算子という2つの子ノードを持っています。この演算子は左右の子ノードのデータをマージ結合するため、左右の子ノードのデータが結合列に対して順序付けられている必要があります。
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オペレーターであり、1番と2番の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 のようなものです。 |