The JOIN operator is used to combine data from two tables based on specific conditions.
The main types of JOIN include Inner Join, Outer Join, and Semi/Anti Join.
OceanBase AI Database supports the following JOIN operators: NESTED LOOP JOIN (NLJ), MERGE JOIN (MJ), and HASH JOIN (HJ).
NESTED LOOP JOIN (NLJ)
In the following example, the Q1 and Q2 queries use hints to specify that NLJ should be used. Operator 0 is an NLJ operator. This operator has two child nodes, namely Operator 1 and Operator 2. Its execution logic is as follows:
Read a row from Operator 1.
Open Operator 2 and read all its rows.
Join the output results of Operators 1 and 2, apply the filter conditions, and output the result.
Repeat step 1 until the iteration of Operator 1 is complete.
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)
In this plan, the MATERIAL operator is used to materialize the data output by the lower-level operator. For more information, see 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)
In the preceding example, the outputs & filters section of the execution plan shows the output information of the NESTED LOOP JOIN operator as follows:
Information name |
Description |
|---|---|
| output | The output columns of this operator. |
| filter | The filter condition of this operator. In the example, no filter is set for the NLJ operator, so the value is nil. |
| conds | The join condition. For example, the join condition in query Q1 is t1.c2 = t2.d2. |
| nl_params_ | The pushdown parameters generated based on the data in the left table of the NLJ. For example, in query Q2, t1.c1. Each time the NLJ iterates a row of the left table, it constructs a parameter based on nl_params. Based on this parameter and the original join condition c1 = d1, a filter condition d1 = ? is constructed on the right table. This filter condition is pushed down to the right table and extracts the query range of the index, indicating which range of index data needs to be scanned. In query Q2, because the pushed-down condition d1 = ? exists, operator 2 is the TABLE GET operator. |
In the following example, the Q3 query does not specify any join conditions. Operator 0 is displayed as a NESTED-LOOP JOIN CARTESIAN. Logically, it is still an NLJ operator, representing an NLJ without any join conditions.
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)
In the following example, the Q4 query uses the USE_MERGE hint to specify that MJ should be used. Operator 0 is an MJ operator, which has two child nodes, namely Operators 1 and 3. This operator performs a merge join on the data of the left and right child nodes; therefore, the data of the child nodes must be sorted relative to the join columns.
Taking the Q4 query as an example, the join condition is t1.c2 = t2.d2, which requires the data in table t1 to be sorted by the c2 column and the data in table t2 to be sorted by the d2 column. In the Q4 query, the output of Operator 2 is unordered, and the output of Operator 4 is sorted by the d1 column, neither of which meets the sorting requirement for MJ. Therefore, Operators 1 and 3 are assigned to perform sorting.
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)
In the following example, the join condition in the Q5 query is t1.c1 = t2.d1, which requires the data in table t1 to be sorted by the c1 column and the data in table t2 to be sorted by the d1 column. In this execution plan, table t2 is scanned as the primary table, and the result is sorted by the d1 column, so no additional SORT operator is needed. Ideally, if the left and right tables of a JOIN have appropriate indexes whose data order meets the requirements of MJ, no SORT operator needs to be allocated.
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)
In the preceding example, the outputs & filters section of the execution plan shows the output information of the MERGE JOIN operator as follows:
Information name |
Description |
|---|---|
| output | The output columns of this operator. |
| filter | The filter condition of this operator. Since no filter is set for the MJ operator, the value is nil. |
| equal_conds | The equality join conditions used in a merge join. The result sets of the left and right child nodes must be ordered with respect to the join columns. |
| other_conds | Other join conditions. For example, t1.c1 + t2.d1 > 10 in query Q4. |
HASH JOIN (HJ)
In the following example, the Q6 query uses the USE_HASH hint to specify that the query should use HJ. Operator 0 is an HJ operator, which has two child nodes: operators 1 and 2. The execution logic of this operator is as follows:
Read the data from the left child node, calculate a hash value based on the join column (for example,
t1.c1), and build a hash table.Read the data from the right child node, calculate a hash value based on the join column (for example,
t2.d1), and attempt to join with the data corresponding tot1in the hash table.
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)
In the preceding example, the outputs & filters section of the execution plan shows the output information of the HASH JOIN operator as follows:
Information name |
Description |
|---|---|
| output | The output columns of this operator. |
| filter | The filter condition of this operator. Since no filter is set for the HJ operator, the value is nil. |
| equal_conds | For an equi-join, the join columns on both sides are used to compute hash values. |
| other_conds | Other join conditions. For example, t1.c2 + t2.d2 > 1 in query Q6. |