Oracle Joins

IT小Chen 2021-04-14

309

Oracle® Database SQL Tuning Guide

Part IV SQL Operators: Access Paths and Joins

---8 Optimizer Access Paths

---9 Joins

9Joins

Oracle数据库为连接行集提供了几种优化方式。

9.1关于连接

连接将来自两个行源（如表或视图）的输出合并起来，并返回一个行源。返回的行源是数据集。

连接的特征是在WHERE（非ANSI）或FROM中多个表的JOIN（ANSI）子语。当FROM子句中存在多个表时，Oracle数据库将执行连接。

连接条件使用表达式比较两个行源。连接条件定义表之间的关系。如果语句未指定连接条件，则数据库执行笛卡尔连接(Cartesian join)，将一个表中的每一行与另一个表中的每一行匹配。

9.1.1连接树

通常，连接树表示为倒树结构。

如下图所示，table1为左表，table2为右表。优化器从左到右处理连接。例如，如果这个图形描述了一个嵌套循环连接(nested loops join,)，那么table1是外部循环(outer loop)，table2是内部循环(inner loop)。

联接的输入可以是前一个联接的结果集。如果联接树的每个内部节点的右子节点是一个表，那么该树就是一个左深联接树，如下面的示例所示。大多数连接树都是左深连接。

如果联接树的每个内部节点的左子节点是一个表，则该树称为右深联接树，如下图所示。

如果联接树的内部节点的左子节点或右子节点可以是联接节点，则该树称为浓密联接树。在下面的示例中，table4是一个联接节点的右子节点，table1是一个联接节点的左子节点，table2是一个联接节点的左子节点。

在另一个变体中，联接的两个输入都是前一个联接的结果。

9.1.2优化器如何执行连接语句

数据库连接行源对。当FROM子句中存在多个表时，优化器必须确定每对表中哪一个联接操作最有效。

优化器必须做出下表所示的相关决策。

Table 9-1Join Operations

操作：访问路径

解释：对于简单语句，优化器必须选择一个访问路径来从连接语句中的每个表检索数据。例如，优化器可能会在全表扫描或索引扫描之间进行选择。

操作：连接方式

解释：要联接每一对行源，Oracle数据库必须决定如何联接。“how”是连接方法。可能的连接方法有嵌套循环(nested loop)、排序合并(sort merge)和哈希连接( hash joins)。笛卡尔连接(Cartesian join)需要前面的连接方法之一。每种连接方法都有特定的情况，在这些情况下，它比其他方法更适合。

操作：连接类型

解释：联接条件确定联接类型。例如，内部联接仅检索与联接条件匹配的行。外部联接检索与联接条件不匹配的行。

操作：连接顺序

解释：要执行连接两个以上表的语句，Oracle数据库先连接两个表，然后将产生的行源连接到下一个表。这个过程将一直持续下去，直到所有表都连接到结果中为止。例如，数据库连接两个表，然后将结果连接到第三个表，然后将这个结果连接到第四个表，依此类推。

9.1.3优化器如何为连接选择执行计划

在确定连接顺序和方法时，优化器的目标是尽早减少行数，以便在整个SQL语句执行过程中执行更少的工作。

优化器根据可能的连接顺序、连接方法和可用的访问路径生成一组执行计划。然后，优化器估计每个计划的成本，并选择成本最低的一个。在选择执行计划时，优化器会考虑以下因素:

•优化器首先确定连接两个或多个表是否会导致一个至多包含一行的行源。

优化器根据表上惟一的主键约束来识别这种情况。如果存在这种情况，那么优化器将首先按照连接顺序放置这些表。然后优化器优化其余表集的连接。

•对于带有外部连接条件的连接语句，带有外部连接操作符的表通常按照连接顺序位于条件中的另一个表之后。

通常，优化器不会考虑违反此准则的连接顺序，尽管在某些情况下，优化器会覆盖此顺序条件。类似地，当一个子查询被转换成反连接或半连接时，子查询中的表必须位于它们所连接或关联的外部查询块中的那些表之后。然而，哈希反连接和半连接能够在某些情况下覆盖这个排序条件。

优化器通过计算估计的I/Os和CPU来估计查询计划的成本。这些I/Os具有与之相关的特定成本:单个块I/O的成本，以及多个块I/Os的成本。另外，不同的函数和表达式都有与之相关的CPU成本。优化器使用这些指标确定查询计划的总成本。这些指标可能会受到许多初始化参数和编译时的会话设置的影响，比如DB_FILE_MULTI_BLOCK_READ_COUNT设置、系统统计信息等等。

例如，优化估计成本的方式如下:

•嵌套循环联接的成本取决于将外部表的每个选定行及其内部表的每个匹配行读入内存的成本。优化器使用数据字典中的统计信息来估计这些成本。

•排序合并连接的成本很大程度上取决于将所有源读入内存并进行排序的成本。

•哈希连接的成本很大程度上取决于在连接的一个输入端构建哈希表，并使用连接另一端的行探测它的成本。

例9-1估计连接顺序和方法的成本

从概念上讲，优化器构造了一个连接顺序和方法的矩阵，以及与每个连接顺序和方法相关的成本。例如，优化器必须确定如何在查询中最好地联接date_dim和lineorder表。下表显示了方法和订单的可能变化，以及每种方法和订单的成本。在本例中，嵌套循环联接的顺序是date_dim, lineorder的成本最低。

表9-2 date_dim和lineorder表连接的示例成本

9.2连接方式

连接方式是连接两个行源的机制。

根据统计数据，优化器选择估计成本最低的方法。如图9-5所示，每个连接方法有两个子方法：驱动（也称为外部）行源和被驱动（也称为内部）行源。

9.2.1 嵌套循环连接(Nested Loops Joins)

嵌套循环将外部数据集连接到内部数据集。

对于与单表谓词匹配的外部数据集中的每一行，数据库检索内部数据集中满足连接谓词的所有行。如果索引可用，那么数据库可以使用它来访问rowid的内部数据集。

9.2.1.1优化器何时考虑嵌套循环连接

当数据库连接小的数据子集时，嵌套循环连接非常有用;当数据库连接大的数据集时，优化器模式设置为FIRST_ROWS，或者连接条件是访问内部表的有效方法时，嵌套循环连接非常有用。

注意:

连接所期望的行数是驱动优化器决策的因素，而不是底层表的大小。例如，一个查询可能连接两个各有10亿行的表，但是由于过滤器的原因，优化器期望每个数据集有5行。

一般来说，嵌套循环联接在具有联接条件索引的小表上最有效。如果行源只有一行，如对主键值进行相等查找（例如，employee_id=101），则联接是一个简单的查找。优化器总是试图将最小的行源放在第一位，使其成为驱动表。

优化器决定使用嵌套循环的因素有很多。例如，数据库可以在一个批处理中从外部行源读取几行。根据检索到的行数，优化器可以选择到内部行源的嵌套循环或哈希联接。例如，如果查询将部门联接到驱动表employees，并且谓词在employees.last_name中指定了一个值，则数据库可能会读取last_name索引中的足够条目，以确定是否传递了内部阈值。如果阈值未通过，优化器将选择到部门的嵌套循环联接，如果阈值通过，则数据库将执行哈希联接，这意味着读取其余员工，将其哈希到内存中，然后加入到部门。

如果内部循环的访问路径不依赖于外部循环，则结果可以是笛卡尔积：对于外部循环的每次迭代，内部循环生成相同的行集。若要避免此问题，请使用其他联接方法联接两个独立的行源。

9.2.1.2嵌套循环联接工作原理

从概念上讲，嵌套循环相当于两个嵌套的for循环。

例如，如果一个查询连接了员工表和部门表，那么伪代码中的嵌套循环可能是:

内部循环对外部循环的每一行执行。employees表是“外部”数据集，因为它位于外部for循环中。外部表有时称为驱动表。departments表是“内部”数据集，因为它位于内部的for循环中。

嵌套循环连接涉及以下基本步骤:

1.优化器确定驱动行源并将其指定为外部循环。

外层循环产生一组用于驱动连接条件的行。行源可以是使用索引扫描、全表扫描或任何其他生成行的操作访问的表。

内部循环的迭代次数取决于外部循环中检索到的行数。例如，如果从外部表检索10行，那么数据库必须在内部表中执行10次查找。如果从外部表检索到10,000,000行，那么数据库必须在内部表中执行10,000,000个查找。

2.优化器将另一个行源指定为内部循环。

外部循环出现在执行计划的内部循环之前，具体如下:

NESTED LOOPS

outer_loop

inner_loop

3.对于客户端的每次取数请求，基本过程如下：

a、从外部行源获取行

b、探测内部行源以查找与谓词条件匹配的行

c、重复前面的步骤，直到获取请求获得所有行

有时数据库会对rowid进行排序，以获得更有效的缓冲区访问模式。

9.2.1.3 嵌套嵌套循环

嵌套循环的外部循环本身可以是由不同的嵌套循环生成的行源。

数据库可以嵌套两个或更多的外部循环，以便根据需要联接尽可能多的表。每个循环都是一个数据访问方法。下面的模板展示了数据库是如何遍历三个嵌套循环的:

数据库对循环的排序如下:

1.数据库遍历嵌套循环1:

嵌套循环1的输出是一个行源。

2.数据库遍历嵌套循环2，使用嵌套循环1生成的行源作为它的外循环:

嵌套循环2的输出是另一个行源。

3.数据库遍历嵌套循环3，使用嵌套循环2生成的行源作为它的外循环:

例9-2嵌套嵌套循环联接

假设您按如下方式连接employees和departments表：

SELECT /*+ ORDERED USE_NL(d) */

e.last_name, e.first_name, d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id

AND e.last_name like 'A%';

该计划显示，优化器选择了两个嵌套循环(步骤1和步骤2)访问数据:

在本例中，基本过程如下：

1.数据库开始遍历内部嵌套循环（步骤2），如下所示：

a.数据库在emp_name_ix中搜索rowids，查找以a开头的所有姓氏（步骤4）。

例如：

Abel,employees_rowid

Ande,employees_rowid

Atkinson,employees_rowid

Austin,employees_rowid

b.使用前一步中的rowid，数据库从employees表中检索一批行(步骤3)，例如:

Abel,Ellen,80

Abel,John,50

这些行成为最内层嵌套循环的外部行源。

批处理步骤通常是自适应执行计划的一部分。要确定嵌套循环是否优于哈希连接，优化器需要确定从行源返回的许多行。如果返回了太多行，那么优化器将切换到不同的连接方法。

c.对于外部行源中的每一行，数据库扫描dept_id_pk索引以获得匹配部门ID的部门中的rowid(步骤5)，并将其连接到employees行。例如：

Abel,Ellen,80,departments_rowid

Ande,Sundar,80,departments_rowid

Atkinson,Mozhe,50,departments_rowid

Austin,David,60,departments_rowid

这些行成为外部嵌套循环的外部行源(步骤1)。

2.数据库遍历外部嵌套循环，如下所示：

a.数据库读取外部行源中的第一行。例如：

Abel,Ellen,80,departments_rowid

b、数据库使用departments rowid从departments检索相应的行（步骤6），然后连接结果以获取请求的值（步骤1）。

例如：

Abel,Ellen,80,Sales

c、数据库读取外部行源中的下一行，使用departments rowid从departments检索相应的行（步骤6），并遍历循环，直到检索到所有行。

结果集的格式如下：

Abel,Ellen,80,Sales

Ande,Sundar,80,Sales

Atkinson,Mozhe,50,Shipping

Austin,David,60,IT

9.2.1.4嵌套循环联接的当前实现

Oracle Database 11g引入了一种新的嵌套循环实现，它减少了物理I/O的总体延迟。

当索引或表块不在缓冲区缓存中并且需要处理连接时，需要物理I/O。数据库可以批处理多个物理I/O请求，并使用向量I/O（数组）而不是一次处理一个。数据库向执行读取的操作系统发送一个rowid数组。

作为新实现的一部分，两个嵌套循环连接行源可能出现在执行计划中，而在以前的版本中只有一个会出现在执行计划中。在这种情况下，Oracle数据库会分配一个嵌套循环连接行源，将连接外部表中的值与内部的索引连接起来。第二行源被分配来联接第一个联接的结果，其中包括存储在索引中的rowid，表位于联接的内侧。

考虑“嵌套循环联接的原始实现”中的查询。在当前实现中，此查询的执行计划可能如下：

在本例中，hr.departments表中的行构成内部嵌套循环（步骤2）的外部行源（步骤3）。索引emp_department_ix是内部嵌套循环的内部行源（步骤4）。内部嵌套循环的结果构成外部嵌套循环（第1行）的外部行源（第2行）。employees表是外部嵌套循环的外部行源（第5行）。

对于每个fetch请求，基本过程如下：

1.数据库遍历内部嵌套循环（步骤2）以获得获取中请求的行：

a、数据库读取第一行部门以获取名为Marketing或Sales的部门的部门ID（步骤3）。例如：

Marketing,20

此行集合是外部循环。数据库将数据缓存在PGA中。

b、数据库扫描emp_department_ix（employees表上的一个索引），以查找与此department ID相对应的employees行ID（步骤4），然后连接结果集（步骤2）。

结果集的格式如下：

Marketing,20,employees_rowid

c、数据库读取下一行部门，扫描emp_department_ix以找到与此部门ID相对应的员工行ID，然后遍历循环，直到满足客户端请求。

在本例中，数据库只在外部循环中迭代两次，因为只有来自部门的两行满足谓词过滤器。

从概念上讲，结果集具有以下形式：

Marketing,20,employees_rowid

...

Sales,80,employees_rowid

...

这些行成为外部嵌套循环的外部行源（步骤1）。

此行集缓存在PGA中。

2.数据库将上一步获得的rowid组织起来，以便在缓存中更有效地访问它们。

3.数据库开始遍历外部嵌套循环，如下所示：

a、数据库从上一步获得的行集合中检索第一行，如下例所示：

Marketing,20,employees_rowid

b、使用rowid，数据库从employees检索一行以获取请求的值（步骤1），如下例所示：

Michael,Hartstein,13000,Marketing

c、数据库从行集中检索下一行，使用rowid探测匹配行的雇员，并在循环中迭代，直到检索到所有行。

结果集的格式如下：

Michael,Hartstein,13000,Marketing

Pat,Fay,6000,Marketing

John,Russell,14000,Sales

Karen,Partners,13500,Sales

Alberto,Errazuriz,12000,Sales

...

在某些情况下，未分配第二个联接行源，执行计划与Oracle Database 11g之前的执行计划相同。以下列表描述了这些情况：

•索引中存在连接内侧所需的所有列，不需要表访问。在这种情况下，Oracle数据库只分配一个连接行源。

•返回的行的顺序可能与Oracle Database 12c之前版本中返回的顺序不同。因此，当Oracle Database尝试保留行的特定顺序（例如，为了消除按排序的顺序的需要）时，Oracle Database可能会使用嵌套循环联接的原始实现。

•OPTIMIZER_FEATURES_ENABLE 初始化参数设置为Oracle Database 11g之前的版本。在这种情况下，Oracle Database使用原始实现进行嵌套循环联接。

9.2.1.5嵌套循环联接的原始实现

在当前版本中，嵌套循环的新实现和原始实现都是可能的。

对于原始实现的示例，请考虑

hr.employees和hr.departments表：

SELECT e.first_name, e.last_name, e.salary, d.department_name

FROM hr.employees e, hr.departments d

WHERE d.department_name IN ('Marketing', 'Sales')

AND e.department_id = d.department_id;

在Oracle Database 11g之前的版本中，此查询的执行计划可能如下所示：

对于每个fetch请求，基本过程如下：

1.数据库遍历循环以获取请求的行：

a、数据库读取第一行部门以获取名为Marketing或Sales的部门的部门ID（步骤3）。例如：

Marketing,20

此行集合是外部循环。数据库缓存PGA中的行。

b、数据库扫描emp_department_ix（employees.department_id列上的索引），以查找与此部门id相对应的employees行id（步骤4），然后连接结果集（步骤2）。

从概念上讲，结果集具有以下形式：

Marketing,20,employees_rowid

c、数据库读取下一行部门，扫描emp_department_ix以找到与此部门ID对应的员工行ID，并在循环中迭代，直到满足客户端请求。

在本例中，数据库只在外部循环中迭代两次，因为只有来自部门的两行满足谓词过滤器。

从概念上讲，结果集具有以下形式：

Marketing,20,employees_rowid

...

Sales,80,employees_rowid

...

2.根据具体情况，数据库可以组织上一步中获得的缓存的rowid，以便更有效地访问它们。

3.对于嵌套循环生成的结果集中的每个employees rowid，数据库从employees检索一行以获取请求的值（步骤1）。

因此，基本过程是读取一个rowid并检索匹配的employees行，读取下一个rowid并检索匹配的employees行，等等。从概念上讲，结果集具有以下形式：

Michael,Hartstein,13000,Marketing

Pat,Fay,6000,Marketing

John,Russell,14000,Sales

Karen,Partners,13500,Sales

Alberto,Errazuriz,12000,Sales

...

9.2.1.6嵌套循环控制

可以添加USE_NL提示，指示优化器使用指定表作为内部表，使用嵌套循环联接将每个指定表联接到另一行源。

相关提示USE_NL_WITH_INDEX（table INDEX）提示指示优化器将指定表连接到另一个具有嵌套循环的行源，使用指定表作为内部表连接。索引是可选的。如果未指定索引，则嵌套循环联接使用至少有一个联接谓词的索引作为索引键。

示例9-3嵌套循环提示

假设优化器为以下查询选择哈希联接：

SELECT e.last_name, d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id;

执行计划如下：

要使用部门作为内部表强制嵌套循环连接，请添加USE_NL提示，如下面的查询所示:

SELECT /*+ ORDERED USE_NL(d) */

e.last_name, d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id;

执行计划如下：

数据库得到的结果集如下:

1.在嵌套循环中，数据库读取员工表以获取员工的姓和部门ID(步骤2)，例如:

De Haan,90

2.对于上一步获得的行，数据库扫描department，查找与employees department ID匹配的部门名称(步骤3)，并连接结果(步骤1)，例如:

De Haan,Executive

3.数据库检索employee中的下一行，从department中检索匹配的行，然后重复这个过程，直到检索到所有行。结果集的形式如下:

De Haan,Executive

Kochnar,Executive

Baer,Public Relations

King,Executive

...

9.2.2 Hash Joins

数据库使用哈希联接来联接较大的数据集。

优化器使用两个数据集中较小的一个在内存中的联接键上构建哈希表，使用确定性哈希函数指定哈希表中存储每一行的位置。然后，数据库扫描较大的数据集，探测哈希表以查找满足联接条件的行。

9.2.2.1优化器何时会考虑哈希连接

通常，当必须连接相对较大的数据量(或必须连接小表的很大一部分)时，并且连接是等值连接，优化器会考虑哈希连接，。

当较小的数据集适合于内存时，哈希连接的成本效率最高。在这种情况下，成本限制在两个数据集上的一次读取传递。

因为哈希表在PGA中，所以Oracle数据库可以访问行而不需要锁定它们。这种技术通过避免重复锁存和读取数据库缓冲区缓存中的块来减少逻辑I/O。

如果数据集在内存中不合适，那么数据库将对行源进行分区，而连接将按分区进行分区。这可能会使用大量的排序区域内存和I/O到临时表空间。这种方法仍然是最经济有效的，特别是当数据库使用并行查询服务器时。

9.2.2.2哈希联接的工作原理

哈希算法接受一组输入并应用确定性哈希函数来生成随机哈希值。

在哈希联接中，输入值是联接键。输出值是数组（哈希表）中的索引（slots）。

9.2.2.2.1哈希表

为了演示哈希表，假设数据库对部门表和员工表通过部门id关联进行哈希运算。

前5行部门表数据如下：

SQL> select * from departments where rownum < 6;

数据库对表中的每个department_id应用哈希函数，为每个department生成一个哈希值。在这个例子中，哈希表有5个槽(可以多一点，也可以少一点)。因为n是5，所以可能的哈希值的范围是从1到5。哈希函数可能会为部门id生成以下值:

f(10) = 4

f(20) = 1

f(30) = 4

f(40) = 2

f(50) = 5

注意，哈希函数恰好为部门10和30生成相同的哈希值4。这就是所谓的哈希冲突。在本例中，数据库使用一个链表将部门10和部门30的记录放在同一个槽中。

从概念上看，哈希表如下:

1 20,Marketing,201,1800

2 40,Human Resources,203,2400

4 10,Administration,200,1700 -> 30,Purchasing,114,1700

5 50,Shipping,121,1500

9.2.2.2.2哈希连接:基本步骤

优化器使用较小的数据源在内存中的连接键上构建哈希表，然后扫描较大的表来查找连接的行。

基本步骤如下：

1.数据库对较小的数据集(称为构建表)执行完整扫描，然后对每一行中的连接键应用哈希函数，以构建PGA中的哈希表。

在伪代码中，算法可能如下所示:

2.数据库使用成本最低的访问机制探测第二个数据集(称为探测表)。

通常，数据库会对更小和更大的数据集进行全面扫描。伪代码中的算法可能如下所示:

对于从较大的数据集中检索到的每一行，数据库执行以下操作:

a.将相同的哈希函数应用于连接列或多个列，以计算哈希表中相关槽的数目。

例如，为了探测部门ID 30的哈希表，数据库将哈希函数应用到30，它将生成哈希值4。

b.探测哈希表以确定槽中是否存在行。

如果不存在行，则数据库处理较大数据集中的下一行。如果存在行，则数据库继续进行下一步。

c.检查联接列或多个列是否匹配。如果发生匹配，那么数据库要么报告这些行，要么将它们传递到计划的下一步，然后处理更大数据集中的下一行。

如果哈希表槽中存在多行，则数据库遍历行链表，检查每一行。例如，如果department 30哈希到slot 4，那么数据库将检查每一行，直到找到30为止。

示例9-4 Hash Joins

应用程序查询oe.orders和oe.order_items表，关联列是order_id。

SELECT o.customer_id, l.unit_price * l.quantity

FROM orders o, order_items l

WHERE l.order_id = o.order_id;

执行计划如下：

因为orders表相对于order_items表比较小，而order_items表要大6倍，所以数据库会哈希orders。在哈希连接中，构建表的数据集总是首先出现在操作列表中(步骤2)。

9.2.2.3当哈希表不适合PGA时，哈希连接如何工作

当哈希表不能完全适合PGA时，数据库必须使用另一种技术。在这种情况下，数据库使用一个临时空间来保存哈希表的部分(称为分区)，有时还保存探测哈希表的较大表的部分。

基本流程如下:

1.数据库对较小的数据集执行完整的扫描，然后在PGA和磁盘上构建一个散列桶数组。

当PGA哈希区填满时，数据库会找到哈希表中最大的分区，并将其写入磁盘上的临时空间。数据库将属于这个磁盘上分区的任何新行存储在磁盘上，以及PGA中的所有其他行。因此，哈希表的一部分在内存中，另一部分在磁盘上。

2.数据库第一次读取其他数据集。对于每一行，数据库执行以下操作:

a.将相同的散列函数应用于连接列，以计算相关散列桶的数目。

b.探测哈希表，以确定内存中的桶中是否存在行。

如果散列值指向内存中的一行，那么数据库将完成连接并返回该行。但是，如果该值指向磁盘上的散列分区，那么数据库将这一行存储在临时表空间中，使用与原始数据集相同的分区方案。

3.数据库逐个读取每个磁盘上的临时分区

4.数据库将每个分区行连接到相应的磁盘上临时分区中的行。

9.2.2.4 Hash Joins控制

USE_HASH提示优化器在将两个表连接在一起时使用哈希联接。

9.2.3 排序合并连接

排序合并联接是嵌套循环联接的变体。

如果联接中的两个数据集尚未排序，则数据库将对它们进行排序。这些是排序连接操作。对于第一个数据集中的每一行，数据库将探测第二个数据集以查找匹配的行并将它们连接起来，它的起始位置基于前一个迭代中进行的匹配。这是合并连接操作。

9.2.3.1优化器何时考虑使用排序合并连接

哈希连接需要一个散列表和这个表的一个探测，而排序合并连接需要两个排序。

当下列条件为真时，优化器可以选择排序合并连接而不是散列连接来连接大量数据:

•两个表之间的联接条件不是等值联接，即使用了诸如<、<=、>或>=这样的不等式条件。

与排序合并不同，散列连接需要一个相等条件。

•由于其他操作需要排序，优化器发现使用排序合并成本更低。

如果存在索引，则数据库可以避免对第一个数据集进行排序。但是，无论索引如何，数据库总是对第二个数据集进行排序。

与嵌套循环连接相比，排序合并具有与散列连接相同的优点:数据库访问PGA中的行，而不是SGA中的行，通过避免重复锁存和读取数据库缓冲区缓存中的块，减少了逻辑I/O。通常，散列连接比排序合并连接执行得更好，因为排序是昂贵的。但是，与散列连接相比，排序合并连接具有以下优点:

•在初始排序之后，合并阶段进行了优化，从而更快地生成输出行。

•当哈希表不能完全装入内存时，排序合并可能比哈希连接更划算。

内存不足的散列连接需要将散列表和其他数据集复制到磁盘。在这种情况下，数据库可能需要多次从磁盘读取数据。在排序合并中，如果内存不能保存这两个数据集，那么数据库将它们都写入磁盘，但是每个数据集的读取次数不超过一次。

9.2.3.2排序归并联接的工作原理

与嵌套循环联接一样，sort merge联接读取两个数据集，但在它们还没有排序时对它们进行排序。

对于第一个数据集中的每一行，数据库在第二个数据集中找到一个起始行，然后读取第二个数据集，直到找到一个不匹配的行。在伪代码中，排序归并的高级算法可能如下所示:

例如，下表显示了两个数据集中的排序值:temp_ds1和temp_ds2。

如下表所示，数据库首先读取temp_ds1中的10，然后读取temp_ds2中的第一个值。因为temp_ds2中的20大于temp_ds1中的10，所以数据库停止读取temp_ds2。

数据库继续处理temp_ds1中的下一个值，即20。数据库通过temp_ds2执行，如下表所示。

数据库继续到temp_ds1中的下一行，即30。数据库从最后一次匹配的数目(20)开始，然后通过temp_ds2查找匹配，如下表所示。

数据库继续到temp_ds1中的下一行，即40。如下表所示，数据库从temp_ds2中最后一个匹配项的数量开始，即20个，然后通过temp_ds2查找匹配项。

数据库以这种方式继续运行，直到它与temp_ds2中的最后70个匹配为止。这个场景表明，当数据库读取temp_ds1时，不需要读取temp_ds2中的每一行。与嵌套循环连接相比，这是一个优势。

例9-5使用索引对合并连接进行排序

下面的查询将连接上的employees和departments表

department_id列，对department_id上的行按如下顺序排序:

SELECT e.employee_id,

e.last_name,

e.first_name,

e.department_id,

d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id

ORDER BY department_id;

查询DBMS_XPLAN.DISPLAY_CURSOR显示排序合并联接执行计划：

这两个数据集是departments表和employees表。由于索引按部门id对departments表进行排序，因此数据库可以读取此索引并避免排序（步骤3）。数据库只需要对employees表进行排序（步骤4），这是CPU最密集的操作。

例9-6没有索引的排序合并连接

在department_id列上联接employees和departments表，按如下方式对department_id上的行进行排序。在本例中，您指定NO_INDEX和USE_MERGE来强制优化器选择排序合并:

SELECT /*+ USE_MERGE(d e) NO_INDEX(d) */

e.employee_id,

e.last_name,

e.first_name,

e.department_id,

d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id

ORDER BY department_id;

查询DBMS_XPLAN.DISPLAY_CURSOR显示排序合并联接执行计划：

由于忽略了departments.department_id索引，优化器将执行排序，这将使步骤2和步骤3的总成本增加67%(从3增加到5)。

9.2.3.3合并排序连接控制

USE_MERGE提示指示优化器使用排序合并连接。

在某些情况下，使用USE_MERGE提示覆盖优化器是有意义的。例如，优化器可以选择对表进行全扫描，并避免在查询中执行排序操作。但是，这样做会增加成本，因为通过索引和单个块读取来访问大表，而不是通过全表扫描进行更快的访问。

9.3连接类型

连接类型由连接条件的类型决定。

9.3.1 内连接

内部连接(有时称为简单连接)是只返回满足连接条件的行的连接。内连接可以是等连接，也可以是非等连接。

9.3.1.1 等值连接

等值连接是一个内部连接，其连接条件包含一个等式运算符。

下面的例子是一个等值连接，因为连接条件只包含一个等式运算符:

SELECT e.employee_id, e.last_name, d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id;

在前面的查询中，联接条件是e.department_id=d.department_id。如果employees表中的某一行的部门ID与部门表中某一行的值匹配，则数据库返回连接的结果;否则，数据库不会返回结果。

9.3.1.2 非等值连接

非等值连接是一个内部连接，其连接条件包含一个非等值操作符。

下面的查询列出了所有在员工176(因为他在2007年换了工作，所以被列在job_history中)在公司工作的员工的雇佣日期:

SELECT e.employee_id, e.first_name, e.last_name, e.hire_date

FROM employees e, job_history h

WHERE h.employee_id = 176

AND e.hire_date BETWEEN h.start_date AND h.end_date;

在前面的示例中，连接employees和job_history的条件不包含相等操作符，因此它是一个非相等连接。非等值连接的情况比较少见。

请注意，散列连接至少需要部分等值连接。下面的SQL脚本包含一个相等连接条件 (e1.empno = e2.empno)和一个不相等条件:

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT *

FROM scott.emp e1

JOIN scott.emp e2

ON (e1.empno = e2.empno AND e1.hiredate BETWEEN e2.hiredate - 1 AND

e2.hiredate + 1)

优化器为前面的查询选择一个散列连接，如下面的计划所示:

9.3.1.3 Band带连接

带连接是一种特殊类型的非等值连接，其中一个数据集中的键值必须位于第二个数据集的指定范围(“带”)内。同一个表可以同时用作第一个和第二个数据集。

从Oracle数据库12c版本2(12.2)开始，数据库可以更有效地评估带连接。该优化避免了对超出定义频带的行进行不必要的扫描。

优化器使用成本评估来选择连接方法(散列、嵌套循环或排序合并)和并行数据分布方法。在大多数情况下，优化的性能与等效连接相当。

以下示例查询的雇员的工资比每个雇员的工资少100美元到多100美元。因此，带宽为200美元。本例假设可以将每个员工的工资与自身进行比较。下面的查询包括部分样本输出:

SELECT e1.last_name || ' has salary between 100 less and 100 more than ' ||

e2.last_name AS "SALARY COMPARISON"

FROM employees e1, employees e2

WHERE e1.salary BETWEEN e2.salary - 100 AND e2.salary + 100;

例9-7查询没有带连接优化

在不进行band join优化的情况下，数据库使用以下查询计划:

在这个计划中，步骤2对e1行源进行排序，步骤5对e2行源进行排序。下表说明了排序后的行源。

连接首先遍历排序输入（e1），它是连接的左分支，对应于计划的步骤2。原始查询包含两个谓词：

•e1.sal>=e2.sal–100，这是第5步过滤器

•e1.sal>=e2.sal+100，这是第4步过滤器

对于已排序行源e1的每次迭代，数据库都会迭代行源e2，根据步骤5 filter e1.sal>=e2.sal–100检查每一行。如果行通过了步骤5筛选器，则数据库将其发送到步骤4筛选器，然后继续针对步骤5筛选器测试e2中的下一行。但是，如果一行未能通过步骤5筛选，那么e2的扫描将停止，数据库将继续执行e1的下一次迭代。

下表显示了e1的第一次迭代，从数据集e1中的24000（King）开始。数据库确定e2中的第一行24000（king）通过步骤5过滤器。然后，数据库将行发送到步骤4过滤器e1.sal<=w2.sal+100，后者也通过。数据库将此行发送到合并行源。接下来，数据库检查17000（Kochhar）与步骤5过滤器，后者也通过了。但是，该行无法通过步骤4筛选器，因此被丢弃。数据库继续根据步骤5过滤器测试17000（De Haan）。

如上表所示，每个e2行都必须通过步骤5筛选，因为e2工资是按降序排序的。因此，步骤5过滤器总是将行发送到步骤4过滤器。因为e2工资是按降序排序的，所以步骤4过滤器必然会失败，从17000开始的每一行（Kochhar）。之所以效率低下，是因为数据库会针对步骤5筛选器（必须通过）测试e2中的每个后续行，然后针对步骤4筛选器（必须失败）进行测试。

示例9-8带连接优化的查询

从Oracle Database 12c Release 2（12.2）开始，数据库使用以下计划优化带区联接，该计划没有单独的筛选操作：

区别在于，步骤4对两个谓词使用布尔值和逻辑来创建单个筛选器。数据库不对一个筛选器检查一行，然后将其发送到另一个行源以检查第二个筛选器，而是对一个筛选器执行一次检查。如果检查失败，则停止处理。

在本例中，查询从e1的第一次迭代开始，e1以24000（King）开头。下图表示范围。23900以下和24100以上的e2值超出范围。

下表显示，数据库根据步骤4过滤器测试e2的第一行，即24000（King）。行通过测试，因此数据库发送要合并的行。e2的下一行是17000（Kochhar）。该行超出范围（范围），因此不满足筛选器谓词，因此数据库在此迭代中停止测试e2行。数据库停止测试，因为e2的降序排序确保e2中的所有后续行都无法通过筛选测试。因此，数据库可以进行e1的第二次迭代。

这样，带连接优化消除了不必要的处理。与在未优化的情况下扫描e2中的每一行不同，数据库只扫描至少两行。

9.3.2 Outer Joins外连接

外部联接返回满足联接条件的所有行，还返回一个表中没有来自另一个表的行满足联接条件的行。因此，外部联接的结果集是内部联接的超集。

在ANSI语法中，OUTER JOIN子句指定一个外部联接。在FROM子句中，左表显示在外部联接关键字的左侧，右表显示在这些关键字的右侧。左表也称为外表，右表也称为内表。例如，在下面的语句中employees表是左表或外表：

SELECT employee_id, last_name, first_name

FROM employees

LEFT OUTER JOIN departments

ON (employees.department_id = departments.departments_id);

外连接要求外连接表作为驱动表。在前面的例子中，employees是驱动表，departments是被驱动表。

9.3.2.1嵌套循环外连接

数据库使用此操作来循环两个表之间的外部连接。外部连接返回外部(保留的)表行，即使内部(可选)表中没有对应的行。

在一个标准的嵌套循环中，优化器根据成本选择表的顺序，即驱动表和被驱动表。但是，在嵌套循环外部连接中，连接条件决定表的顺序。数据库使用外部的行保留表来驱动内部表。

在以下情况下，优化器使用嵌套循环连接来处理外部连接:

•可以从外部表驱动到内部表。

•数据量足够低，使嵌套循环方法有效。

对于嵌套循环外部连接的一个示例，您可以将USE_NL提示添加到示例9-9中，以指示优化器使用嵌套循环。例如:

SELECT /*+ USE_NL(c o) */

cust_last_name, SUM(NVL2(o.customer_id, 0, 1)) "Count"

FROM customers c, orders o

WHERE c.credit_limit > 1000

AND c.customer_id = o.customer_id(+)

GROUP BY cust_last_name;

9.3.2.2散列连接外部连接

当数据量大到足以使散列连接有效时，或者无法从外部表驱动到内部表时，优化器将使用散列连接来处理外部连接。

成本决定了表格的顺序。外部表(包括保留的行)可以用来构建哈希表，也可以用来探测哈希表。

例9-9散列连接外部连接

这个示例展示了一个典型的散列连接外部连接查询及其执行计划。在这个例子中，所有信用限额大于1000的客户都被查询。需要一个外部连接，以便查询捕获没有订单的客户。

•外部表是customers。

•内部表是orders。

•联接保留customers数据，包括orders中没有对应行的那些行。

您可以使用不存在的子查询来返回行。但是，由于要查询表中的所有行，所以散列连接的性能更好(除非不存在的子查询没有嵌套)。

SELECT cust_last_name, SUM(NVL2(o.customer_id, 0, 1)) "Count"

FROM customers c, orders o

WHERE c.credit_limit > 1000

AND c.customer_id = o.customer_id(+)

GROUP BY cust_last_name;

查询查找满足各种条件的客户。当外部联接在内部表中没有找到任何对应的行时，它将为内部表列和外部(保留的)表行返回NULL。此操作查找没有任何订单行的所有客户行。

在这种情况下，外部连接条件如下:

customers.customer_id = orders.customer_id(+)

该条件的组成部分如下:

例9-10 外连接到多表视图

在本例中，外部连接是一个多表视图。优化器不能像普通连接或谓词推进那样插入视图，因此它构建视图的整个行集。

SELECT c.cust_last_name, sum(revenue)

FROM customers c, v_orders o

WHERE c.credit_limit > 2000

AND o.customer_id(+) = c.customer_id

GROUP BY c.cust_last_name;

视图定义如下：

CREATE OR REPLACE view v_orders AS

SELECT l.product_id,

SUM(l.quantity * unit_price) revenue,

o.order_id,

o.customer_id

FROM orders o, order_items l

WHERE o.order_id = l.order_id

GROUP BY l.product_id, o.order_id, o.customer_id;

9.3.2.3排序合并外部连接

当外部联接无法从外部（保留的）表驱动到内部（可选）表时，它不能使用哈希联接或嵌套循环联接。

在这种情况下，它使用sort-merge外部联接。

优化器在以下情况下对外部联接使用排序合并：

•嵌套循环连接效率低下。由于数据量的原因，嵌套循环联接可能效率低下。

•优化器发现在散列连接上使用排序合成本更低，因为其他操作需要排序。

9.3.2.4全外连接

完全外部联接是左外部联接和右外部联接的组合。

除了内部联接之外，两个表中未在内部联接结果中返回的行也将保留并扩展为空。换句话说，完全外部联接将表联接在一起，但在联接的表中显示没有相应行的行。

示例9-11完全外部连接

以下查询检索每个部门中的所有部门和所有员工，但也包括：

•任何没有部门的员工

•任何没有员工的部门

SELECT d.department_id, e.employee_id

FROM employees e

FULL OUTER JOIN departments d

ON e.department_id = d.department_id

ORDER BY d.department_id;

结果如下：

例9-12完整外部连接的执行计划

从Oracle Database 11g开始，Oracle Database将自动使用基于散列连接的本机执行方法来执行完整的外部连接。当数据库使用新方法执行完整外部连接时，查询的执行计划包含散列连接完整外部。例9-11中的查询使用以下执行计划:

在前面的计划(步骤3)中包含了散列连接完整外部，这表明查询使用散列完整外部连接执行方法。通常，当两个表之间的全外连接条件是等值连接时，可以使用散列全外连接执行方法，Oracle数据库会自动使用它。

要指示优化器考虑使用散列全外连接执行方法，请应用NATIVE_FULL_OUTER_JOIN提示。要指示优化器不要考虑使用散列完整外连接执行方法，请应用NO_NATIVE_FULL_OUTER_JOIN提示。NO_NATIVE_FULL_OUTER_JOIN提示指示优化器在连接每个指定表时排除本机执行方法。相反，完整的外部连接是作为左外部连接和反连接的联合执行的。

9.3.2.5外部连接左侧有多个表

在Oracle数据库12c中，一个外连接表的左侧可能存在多个表。

此增强功能使Oracle数据库能够合并包含多个表的视图，并显示在外部连接的左侧。在Oracle数据库12c之前的版本中，像下面这样的查询是无效的，并且会触发ORA-01417错误消息:

SELECT t1.d, t3.c

FROM t1, t2, t3

WHERE t1.z = t2.z

AND t1.x = t3.x(+)

AND t2.y = t3.y(+);

从Oracle数据库12c开始，前面的查询是有效的。

9.3.3 半连接Semijoins

半联接是两个数据集之间的联接，当子查询数据集中存在匹配的行时，该联接返回第一个数据集中的行。

数据库在第一次匹配时停止处理第二个数据集。因此，当第二个数据集中的多行满足子查询条件时，优化不会复制第一个数据集中的行。

注：

半联接Semijoins和反联接antijoins被视为联接类型，即使导致它们的SQL构造是子查询。它们是内部算法，优化器使用它们来展开子查询结构，以便它们可以以类联接的方式进行解析。

9.3.3.1优化器何时考虑半联接

当查询只需要确定匹配是否存在时，半联接避免返回大量行。

对于大型数据集，这种优化可以大大节省嵌套循环联接的时间，嵌套循环联接必须遍历内部查询为外部查询中的每一行返回的每条记录。优化器可以将半联接优化应用于嵌套循环联接、哈希联接和排序合并联接。

优化器可以在以下情况下选择半联接：

•语句使用IN或EXISTS子句。

•语句在in或EXISTS子句中包含子查询。

•IN或EXISTS子句不包含在or分支中。

9.3.3.2半连接的工作原理

根据使用的连接类型，半连接优化的实现方式不同。

以下伪代码显示嵌套循环联接的半联接：

在前面的伪代码中，ds1是第一个数据集，ds2_subquery是子查询数据集。代码从第一个数据集获取第一行，然后循环子查询数据集以查找匹配项。代码一旦找到匹配项就退出内部循环，然后开始处理第一个数据集中的下一行。

示例9-13使用WHERE EXISTS的半联接

以下查询使用WHERE EXISTS子句仅列出包含员工的部门：

SELECT department_id, department_name

FROM departments

WHERE EXISTS

(SELECT 1

FROM employees

WHERE employees.department_id = departments.department_id)

执行计划在步骤1中显示嵌套循环半连接：

对于构成外部循环的departments中的每一行，数据库获取department ID，然后探测employees.department_ID索引以查找匹配条目。从概念上讲，索引如下：

10,rowid

30,rowid

...

如果departments表中的第一个条目是department 30，则数据库将对索引执行范围扫描，直到找到第一个30条目，然后停止读取索引并从departments返回匹配的行。如果外层循环中的下一行是department 20，那么数据库将扫描索引以查找20条目，但找不到任何匹配项，然后执行外层循环的下一次迭代。数据库以这种方式进行，直到返回所有匹配的行。

示例9-14在中使用的半联接

以下查询使用IN子句仅列出包含员工的部门：

SELECT department_id, department_name

FROM departments

WHERE department_id IN (SELECT department_id FROM employees);

执行计划在步骤1中显示嵌套循环半连接：

该计划与例9-13中的计划相同。

9.3.4反连接 Antijoins

反联接是两个数据集之间的联接，当子查询数据集中不存在匹配的行时，反联接返回第一个数据集中的行。

与半联接一样，反联接在找到第一个匹配项时停止处理子查询数据集。与半联接不同，反联接只在找不到匹配项时返回行。

9.3.4.1 优化器何时考虑反联接

当查询只需要在不存在匹配时返回行时，反联接避免了不必要的处理。

对于大型数据集，这种优化可以比嵌套循环连接节省大量时间。后者通过外部查询中每一行的内部查询返回的每条记录进行循环。优化器可以将反联接优化应用于嵌套循环联接、哈希联接和排序合并联接

优化器可以在以下情况下选择反联接：

•语句使用NOT IN或NOT EXISTS子句。

•语句在NOT in或NOT EXISTS子句中有一个子查询。

•NOT IN或NOT EXISTS子句未包含在或分支中。

语句执行外连接并对联接列应用IS NULL条件，如下例所示：

SET AUTOTRACE TRACEONLY EXPLAIN

SELECT emp.*

FROM emp, dept

WHERE emp.deptno = dept.deptno(+)

AND dept.deptno IS NULL

9.3.4.2反连接的工作原理

根据使用的连接类型，反连接优化的实现方式不同。

以下伪代码显示嵌套循环联接的反联接：

在前面的伪码中，ds1是第一个数据集，ds2是第二个数据集。代码从第一个数据集获取第一行，然后循环通过第二个数据集寻找匹配项。代码一旦找到匹配项就退出内部循环，并开始处理第一个数据集中的下一行。

示例9-15使用WHERE EXISTS的半联接

以下查询使用WHERE EXISTS子句仅列出包含员工的部门：

SELECT department_id, department_name

FROM departments

WHERE EXISTS

(SELECT 1

FROM employees

WHERE employees.department_id = departments.department_id)

显示了嵌套循环半操作在步骤1的执行计划:

对于形成外部循环的部门中的每一行，数据库获取部门ID，然后探测employee .department_id索引，以查找匹配的条目。从概念上看，索引数据如下:

10,rowid

30,rowid

...

如果departments表中的第一个记录是department 30，那么数据库将对索引执行一次范围扫描，直到找到第一个30项为止，这时它将停止读取索引并返回来自部门的匹配行。如果外层循环中的下一行是department 20，那么数据库将扫描索引中的20项，并没有找到任何匹配项，从而执行外层循环的下一次迭代。数据库以这种方式处理，直到返回所有匹配的行。

9.3.4.3反连接如何处理空值

对于半连接，IN和EXISTS在功能上是等价的。但是，由于为空，NOT IN和NOT EXISTS在功能上是不等价的。

如果将空值返回给NOT IN操作符，则该语句将不返回任何记录。要查看原因，请考虑以下WHERE子句:

WHERE department_id NOT IN (null, 10, 20)

数据库对前面的表达式进行如下测试:

WHERE (department_id != null)

AND(department_id != 10)

AND (department_id != 20)

为了使整个表达式为真，每个条件都必须为真。但是，一个空值不能与另一个值进行比较，所以department_id !=null条件不能为真，因此整个表达式总是假的。即使在NOT IN操作符返回空值时，下列技术也可以使语句返回记录:

•对子查询返回的列应用NVL函数。

•在子查询中添加一个非空谓词。

实现NOT NULL约束。

与NOT In不同，NOT EXISTS子句只考虑返回匹配项存在的谓词，而忽略任何不匹配或由于null而无法确定的行。如果子查询中至少有一行与来自外部查询的行匹配，则NOT EXISTS返回false。如果没有元组匹配，则NOT EXISTS返回true。子查询中是否存在空值并不影响对匹配记录的搜索。

在Oracle Database 11g之前的版本中，当可以通过子查询返回空值时，优化器不能使用反连接优化。然而，从Oracle数据库11g开始，下面几节中描述的 ANTI NA (和 ANTI SNA) 优化使优化器能够在可能为空的情况下使用反连接。

例9-16反连接使用NOT IN

假设用户使用NOT IN子句发出以下查询，以列出不包含雇员的部门:

SELECT department_id, department_name

FROM departments

WHERE department_id NOT IN (SELECT department_id FROM employees);

前面的查询不返回行，即使几个部门不包含雇员。由于employees.department_id列可为空，因此发生了用户不希望出现的此结果。

执行计划在步骤2中显示嵌套循环反SNA操作：

ANTI-SNA代表“单个支持空值的antijoin”，ANTI-NA代表“支持空值的antijoin”，支持空值的操作使优化器可以在可空列上使用antijoin优化。在Oracle Database 11g之前的版本中，当可能为空时，数据库无法对不在查询执行反联接。

假设用户通过对子查询应用IS NOT NULL条件重写查询：

SELECT department_id, department_name

FROM departments

WHERE department_id NOT IN

(SELECT department_id FROM employees WHERE department_id IS NOT NULL);

前面的查询返回16行，这是预期的结果。计划中的步骤1显示标准嵌套循环反联接，而不是反NA或反SNA联接，因为子查询不能返回空：

例9-17反连接使用NOT EXISTS

假设用户使用一个NOT EXISTS子句发出以下查询，以列出不包含雇员的部门:

SELECT department_id, department_name

FROM departments d

WHERE NOT EXISTS

(SELECT null FROM employees e WHERE e.department_id = d.department_id)

前面的查询避免了NOT IN子句的null问题。因此,尽管department_id列可为空，该语句返回所需的结果。

执行计划的第1步揭示了一个嵌套的循环反操作，而不是反NA，这是在可能为空时不进入所必需的：

9.3.5 笛卡尔连接 Cartesian Joins

当一个或多个表对语句中的任何其他表没有任何联接条件时，数据库使用笛卡尔联接。

优化器将来自一个数据源的每一行与来自另一个数据源的每一行连接起来，创建这两个集合的笛卡尔积。因此，使用以下公式计算联接产生的行总数，其中rs1是第一行集中的行数，rs2是第二行集中的行数：

rs1 X rs2 = total rows in result set

9.3.5.1优化器何时考虑笛卡尔连接时

优化器仅在特定情况下对两行源使用笛卡尔连接。通常，情况如下：

•不存在连接条件。

在某些情况下，优化器可以在两个表之间选取一个公共筛选条件作为可能的联接条件。

注：

如果笛卡尔连接出现在查询计划中，则可能是由于不小心忽略了连接条件。一般来说，如果查询连接n个表，则需要n-1连接条件来避免笛卡尔连接。

•笛卡尔连接是一种有效的方法。

例如，优化器可能决定生成两个非常小的表的笛卡尔积，这两个表都连接到同一个大表。

•ORDERED提示在指定联接表之前指定表。

9.3.5.2笛卡尔连接的工作原理

笛卡尔连接使用嵌套循环。

在较高级别上，笛卡尔连接的算法如下所示，其中ds1通常是较小的数据集，ds2是较大的数据集：

示例9-18笛卡尔连接

在本例中，用户打算执行employees和departments表的内部联接，但意外地忽略了联接条件：

SELECT e.last_name, d.department_name FROM employees e, departments d

执行计划如下：

在前面计划的步骤1中，笛卡尔关键字指示笛卡尔连接的存在。行数（2889）是27和107的乘积。

在步骤3中，缓冲区排序操作指示数据库正在将通过扫描emp_name_ix获得的数据块从SGA复制到PGA。此策略避免了对数据库缓冲区缓存中相同块的多次扫描，这将生成许多逻辑读取并允许资源争用。

9.3.5.3笛卡尔连接的控制

ORDERED提示指示优化器按照表在FROM子句中出现的顺序连接表。通过在两个没有直接连接的行源之间强制连接，优化器必须执行笛卡尔连接。

示例9-19 ORDERED Hint

在以下示例中，ORDERED提示指示优化器联接employees 和locations，但没有联接条件连接这两个行源：

SELECT /*+ORDERED*/

e.last_name, d.department_name, l.country_id, l.state_province

FROM employees e, locations l, departments d

WHERE e.department_id = d.department_id

AND d.location_id = l.location_id

下面的执行计划显示了位置（步骤6）和员工（步骤4）之间的笛卡尔产品（步骤3），然后将其连接到部门表（步骤2）：

9.4连接优化

连接优化使连接更加有效。

9.4.1Bloom过滤器

Bloom filter以其创建者Burton Bloom的名字命名，是一种用于测试集合成员资格的低内存数据结构。

一个Bloom filter可以正确地指示一个元素何时不在集合中，但也可以不正确地指示一个元素何时在集合中。因此，不可能出现漏报，但可能出现误报。

9.4.1.1 Bloom过滤器的用途

Bloom过滤器测试一组值，以确定它们是否是另一组值的成员。

例如，一个集合是(10,20,30,40)，另一个集合是(10,30,60,70)。一个Bloom filter可以确定60和70被排除在第一个集合之外，10和30可能是成员。当存储筛选器所需的内存相对于数据集中的数据量比较小，并且大多数数据预期无法通过成员资格测试时，Bloom筛选器特别有用。

Oracle数据库使用Bloom filter来实现各种特定的目标，包括:

•减少在并行查询中传输到从属进程的数据量，特别是当数据库因为没有满足联接条件而丢弃大多数行时

•在连接中构建分区访问列表时，删除不需要的分区，这称为分区修剪

•测试数据是否存在于服务器结果缓存中，从而避免磁盘读取

•Exadata单元中的筛选器成员，特别是在将一个大的事实表和一个星型模式中的小维度表连接起来时，可以同时进行并行处理和串行处理。

9.4.1.2 Bloom过滤器的工作原理

Bloom过滤器使用一个比特数组来表示集合中的包含。

例如，数组中的8个元素(本例中使用的任意数字)最初设置为0:

e1 e2 e3 e4 e5 e6 e7 e8

0 0 0 0 0 0 0 0

这个数组表示一个集合。为了表示这个数组中的输入值i，对i应用了三个独立的哈希函数(三个是任意的)，每个函数生成1到8之间的哈希值:

f1(i) = h1

f2(i) = h2

f3(i) = h3

例如，为了在这个数组中存储值17，哈希函数将i设置为17，然后返回以下哈希值:

f1(17) = 5

f2(17) = 3

f3(17) = 5

在前面的示例中，两个哈希函数碰巧返回了相同的值5，这称为哈希冲突。因为不同的哈希值是5和3，所以数组中的第5和第3个元素被设置为1:

e1 e2 e3 e4 e5 e6 e7 e8

0 0 1 0 1 0 0 0

测试集合中17个成员的成员资格将逆转该过程。要测试集合是否排除值17，元素3或元素5必须包含0。如果任何一个元素中都有0，那么集合就不能包含17。不可能有假阴性。

为了测试集合是否包含17，元素3和元素5都必须包含1个值。但是，如果测试表明两个元素都是1，那么集合仍然可能不包含17。误报是可能的。例如，下面的数组可能表示值22，其中元素3和元素5都有一个1:

e1 e2 e3 e4 e5 e6 e7 e8

1 0 1 0 1 0 0 0

9.4.1.3 Bloom过滤器控制

优化器会自动决定是否使用Bloom过滤器。要覆盖优化器决策，请使用提示PX_JOIN_FILTER和NO_PX_JOIN_FILTER。

9.4.1.4Bloom滤元数据

V$ views包含关于Bloom过滤器的元数据。您可以查询以下视图:

•V$SQL_JOIN_FILTER

这个视图显示了由活动的Bloom过滤器过滤出(过滤列)和测试(探测列)的行数。

•V$PQ_TQSTAT

此视图显示在执行树的每个阶段，通过每个并行执行服务器处理的行数。您可以使用它来监视Bloom过滤器减少了多少并行进程之间的数据传输。

在执行计划中，Bloom过滤器由Operation列中的关键字JOIN filter和Name列中的前缀:BF表示，如下面的计划片段的第9步所示:

在计划的谓词信息部分，包含以字符串SYS_OP_BLOOM_FILTER开头的函数的过滤器表示使用了Bloom过滤器。

9.4.1.5 Bloom过滤器:场景

在本例中，一个并行查询将sales事实表连接到产品和时间维度表，并在第18财政周进行筛选。

SELECT /*+ parallel(s) */

p.prod_name, s.quantity_sold

FROM sh.sales s, sh.products p, sh.times t

WHERE s.prod_id = p.prod_id

AND s.time_id = t.time_id

AND t.fiscal_week_number = 18;

查询DBMS_XPLAN.DISPLAY_CURSOR提供以下输出:

SELECT *

FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'BASIC,+PARALLEL,+PREDICATE'));

单个服务器进程扫描时间表（步骤13），然后使用混合哈希分布方法将行发送到并行执行服务器（步骤12）。集合Q1,03中的过程创建一个bloom过滤器（步骤9）。集合Q1,02中的进程并行扫描sales（步骤18），然后使用Bloom过滤器丢弃sales中的行（步骤16），然后使用混合散列分布将它们发送到集合Q1,03（步骤15）。集合Q1,03散列中的进程将时间行连接到筛选的销售行（步骤8）。集合Q1,01中的进程扫描产品（步骤7），然后将行发送到Q1,03（步骤5）。最后，Q1,03中的进程将产品行与前一个散列联接生成的行联接（步骤3）。