OceanBase JOIN

JOIN 算子用于将两张表的数据，按照特定的条件进行联接。

JOIN 的类型主要包括内联接（INNER JOIN）、外联接（OUTER JOIN）和半联接（SEMI/ANTI JOIN）三种。

OceanBase 数据库支持的 JOIN 算子主要有 NESTED LOOP JOIN (NLJ)、MERGE JOIN (MJ) 和 HASH JOIN (HJ)。

NESTED LOOP JOIN (NLJ)

如下示例中，Q1 和 Q2 查询使用 HINT 指定了查询使用 NLJ。其中，0 号算子是一个 NLJ 算子。这个算子存在两个子节点，分别是 1 号算子和 2 号算子，它的执行逻辑为：

从 1 号算子读取一行。
打开 2 号算子，读取所有的行。
联接接 1和 2 号算子的输出结果，并执行过滤条件，输出结果。
重复第一步，直到 1 号算子迭代结束。

obclient>CREATE TABLE t1 (c1 INT, c2 INT);
Query OK, 0 rows affected (0.12 sec)

obclient>CREATE TABLE t2 (d1 INT, d2 INT, PRIMARY KEY (d1));
Query OK, 0 rows affected (0.12 sec)

Q1: 
obclient>EXPLAIN SELECT /*+USE_NL(t1, t2)*/ t1.c2 + t2.d2 FROM t1, t2 
     WHERE c2 = d2\G;
*************************** 1. row ***************************
Query Plan:
===========================================
|ID|OPERATOR        |NAME|EST. ROWS|COST  |
-------------------------------------------
|0 |NESTED-LOOP JOIN|    |9782     |411238|
|1 | TABLE SCAN     |T1  |999      |647   |
|2 | MATERIAL       |    |999      |1519  |
|3 |  TABLE 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\G;
*************************** 1. row ***************************
Query Plan:
| ==========================================
|ID|OPERATOR        |NAME|EST. ROWS|COST |
------------------------------------------
|0 |NESTED-LOOP JOIN|    |990      |37346|
|1 | TABLE 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\G;
*************************** 1. row ***************************
Query Plan:
| =====================================================
|ID|OPERATOR                  |NAME|EST. ROWS|COST  |
-----------------------------------------------------
|0 |NESTED-LOOP JOIN CARTESIAN|    |998001   |747480|
|1 | TABLE SCAN               |T1  |999      |647   |
|2 | MATERIAL                 |    |999      |1519  |
|3 |  TABLE 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 的 HINT 指定了查询使用 MJ。其中，0 号算子是一个 MJ 算子，它有两个子节点，分别是 1 和 3 号算子。该算子会对左右子节点的数据进行归并联接，因此，要求左右子节点的数据相对于联接列是有序的。

以 Q4 查询为例，联接条件为 t1.c2 = t2.d2，它要求 t1 的数据是按照 c2 排序的，t2 的数据是按照 d2 排序的。在 Q4 查询中，2 号算子的输出是无序的；4 号算子的输出是按照 d2 排序的，均不满足 MERGE JOIN 对序的要求，因此，分配了 1 和 3 号算子进行排序。

Q4: 
obclient>EXPLAIN SELECT /*+USE_MERGE(t1, t2)*/ t1.c2 + t2.d2 FROM t1, t2 
                WHERE c2 = d2 AND c1 + d1 > 10\G;
*************************** 1. row ***************************
Query Plan:
| ======================================
|ID|OPERATOR    |NAME|EST. ROWS|COST |
--------------------------------------
|0 |MERGE JOIN  |    |3261     |14199|
|1 | SORT       |    |999      |4505 |
|2 |  TABLE SCAN|T1  |999      |669  |
|3 | SORT       |    |999      |4483 |
|4 |  TABLE 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\G;
*************************** 1. row ***************************
Query Plan:
| =====================================
|ID|OPERATOR    |NAME|EST. ROWS|COST|
-------------------------------------
|0 |MERGE JOIN  |    |990      |6096|
|1 | SORT       |    |999      |4505|
|2 |  TABLE SCAN|T1  |999      |669 |
|3 | TABLE 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 的 HINT 指定了查询使用 HJ。其中，0 号算子是一个 HJ 算子，它有两个子节点，分别是 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\G;
*************************** 1. row ***************************
Query Plan:
| ====================================
|ID|OPERATOR   |NAME|EST. ROWS|COST|
------------------------------------
|0 |HASH JOIN  |    |330      |4850|
|1 | TABLE SCAN|T1  |999      |669 |
|2 | TABLE 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`。

w3cschool 编程狮，随时随地学编程

OceanBase JOIN

NESTED LOOP JOIN (NLJ)

MERGE JOIN (MJ)

HASH JOIN (HJ)

OceanBase 产品简介

OceanBase 快速入门

OceanBase 教程

OceanBase 控制台指南

OceanBase 集群工作台

OceanBase 租户

OceanBase 监控

OceanBase 诊断

OceanBase 备份恢复管理

OceanBase 安全设置

OceanBase 参数管理

OceanBase 数据传输

OceanBase 产品简介

OceanBase 产品架构

OceanBase 产品功能

OceanBase 快速入门

OceanBase 迁移项目管理

OceanBase MySQL数据库迁移

OceanBase Oracle数据库迁移

OceanBase 常见问题

OceanBase ODC 使用指南

OceanBase Web 版 ODC

OceanBase 连接数据库

OceanBase 使用工作台

OceanBase 使用工具

OceanBase 数据导出和导入

OceanBase 任务管理

OceanBase 数据库对象

OceanBase 表对象

OceanBase 视图对象

OceanBase 函数对象

OceanBase 存储过程对象

OceanBase 序列对象

OceanBase 程序包对象

OceanBase 触发器对象

OceanBase 类型对象

OceanBase 同义词对象

OceanBase 客户端版 ODC

OceanBase 连接数据库

OceanBase 使用工作台

OceanBase 使用工具

OceanBase 数据导出和导入

OceanBase 任务管理

OceanBase 数据库对象

OceanBase 表对象

OceanBase 视图对象

OceanBase 函数对象

OceanBase 存储过程对象

OceanBase 序列对象

OceanBase 程序包对象

OceanBase 触发器对象

OceanBase 类型对象

OceanBase 同义词对象

OceanBase Connector/J 开发者指南

OceanBase 什么是OceanBase Connector/J

OceanBase Connector/J 使用指南

OceanBase 基本操作

OceanBase Java数据流

OceanBase 使用LOB

OceanBase 数据源和URL

OceanBase 结果集

OceanBase 语句缓存

OceanBase 参考信息

OceanBase Oracle 模式特有功能

OceanBase 分布式事务

OceanBase SQL 参考（MySQL 模式）

OceanBase 基本元素

OceanBase 数据类型

OceanBase 运算符

OceanBase 函数

OceanBase 单行函数

OceanBase 查询和子查询

OceanBase SQL语句

OceanBase SQL 参考（Oracle 模式）

OceanBase 基本元素