Understanding Joins (理解联接)
【目录】
CBO 如何执行联接指令
CBO 如何选择联接方法
联接方法
嵌套循环联接
优化器何时使用嵌套循环联接
嵌套循环联接提示
多重嵌套循环
嵌套循环外联接
优化器何时使用嵌套循环外联接
嵌套循环外联接提示
哈希联接
优化器何时使用哈希联接
哈希联接提示
哈希外联接
优化器何时使用哈希外联接
哈希外联接提示
排序归并联接
优化器何时使用排序归并联接
排序归并联接提示
排序归并外联接
优化器何时使用排序归并外联接
排序归并外联接提示
笛卡尔联接
优化器何时使用笛卡尔联接
笛卡尔联接提示
完全外联接
CBO 如何为联接选择执行计划
CBO 如何执行反联接
CBO 如何执行半联接
CBO 如何执行星型查询
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
联接（join）是用于从多个表中获取数据的指令。联接的表现在于 FROM 子句中有多于一个表，且这些表之间的关系由 WHERE 子句中的联接条件来定义。
本节讨论 Oracle 优化器如何执行包含联接（join）、反联接（anti-join）、半联接（semi-join）的 SQL 指令。另外还将描述优化器如何使用位图索引（bitmap index）执行星型查询（star query）——将一个事实表（fact table）联接到多个维度表（dimension table）的查询。
参见：
“性能示例模型”（附录 A）
CBO 如何执行联接指令
要为一个联接指令选择执行计划，优化器必须先做出这些相关决定。
访问路径 对于简单指令，优化器必须选择一个访问路径（access path）来获取联接指令中每个表的数据。
联接方法 要联接数据源的每一对记录，Oracle 必须执行下列操作之一：
n 嵌套循环联接 （Nested loop join）
n 排序归并联接 （Sort merge join）
n 哈希联接 （Hash join，不能用于 RBO）
n 群集联接 （Cluster join）
联接次序 要执行一个指令联接多于两个的表，Oracle 联接其中的两个表，然后联接结果集到另一个表，如此持续到所有的表联接好输出结果。
参见：
“理解 RBO 的访问路径”（8-2）
“理解 CBO 的访问路径”（1-24）
CBO 如何选择联接方法
优化器评估每一个联接方法的成本，并选择其中成本最低的方法。如果一个联接返回很多记录，优化器会考察以下三个因素：
1. 当一个联接返回大量的记录（作为代表，超过 10000 个记录就认定为“大量”）时，嵌套循环联接效率较低，优化器会尽量不选它。嵌套循环联接的成本可由下式计算：
cost = access_cost_of_A + (access_cost_of_B * number_of_rows_from_A)
2. 如果您使用CBO，当联接返回大量的记录时，哈希联接是最有效的联接。哈希联接的成本可由下式计算：
cost = (access_cost_of_A * number_of_hash_partitions_of_B) + access_cost_of_B
3. 如果您使用RBO，当联接返回大量的记录时，归并联接是最有效的联接。归并联接的成本可由下式计算：
cost = access_cost_of_A + access_cost_of_B + (sort_cost_of_A + sort_cost_of_B)
若数据已预先排好序，则排序成本都为 0。
|
注意：Oracle 公司强烈建议使用 CBO 优化，RBO 在后续版本中将不再受到重视。 |
联接方法
联接方法包括：
n 嵌套循环联接 （Nested Loop Joins）
n 嵌套循环外联接 （Nested Loop Outer Joins）
n 哈希联接 （Hash Joins）
n 哈希外联接 （Hash Join Outer Joins）
n 排序归并联接 （Sort Merge Joins）
n 排序归并外联接 （Sort Merge Outer Joins）
n 笛卡尔联接 （Cartesian Joins）
n 完全外联接 （Full Outer Joins）
嵌套循环联接
当联接小型数据集且到第二个表的联接条件可直接生效时，嵌套循环联接是很实用的。
重要的一点是要确保内表（inner table）是依赖于外表（outer table）的（即受外表驱动）。如果内表的访问路径独立于外表，每次循环都要读取同一记录，从而丧失太多的性能。这种情况下，用哈希联接联接两个独立的表会好一些。
参见：
“笛卡尔联接”
嵌套循环联接包括如下步骤：
1. 优化器确定驱动表，并指定其为外表。
2. 指定另一个表为内表。
3. 对外表的每一记录，Oracle 访问内表的每一记录。外循环就是遍历外表的每一记录，内循环就是遍历内表的每一记录。外循环先于内循环出现在执行计划中，如下：
NESTED LOOP
<Outer Loop>
<Inner Loop>
例 1–24 嵌套循环联接
考虑下面这个查询的执行计划
SELECT a.unit_price * a.quantity
FROM order_items a,orders b
WHERE b.customer_id = :b1
AND a.order_id = b.order_id
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID ORDERS
INDEX RANGE SCAN CUSTOMER_ID
TABLE ACCESS BY INDEX ROWID ORDER_ITEMS
INDEX RANGE SCAN ORDER_ID
外循环 在这个执行计划中，外循环和等价指令如下：
TABLE ACCESS BY INDEX ROWID ORDERS
INDEX RANGE SCAN CUSTOMER_ID
SELECT <some columns>
FROM orders b
WHERE b.customer_id = :b1
完成指令 因此，获取一个客户的所有交易额的指令可以分解为两个循环：
l 外循环获取给定客户的所有订单。
l 对于外循环获取的每个订单，内循环返回交易额。
优化器何时使用嵌套循环联接
当联接少量记录，且两个表之间有一个较好的驱动条件时，优化器使用嵌套循环联接。外循环驱动内循环，因此执行计划中表的次序很重要。
外循环是驱动记录源，它为驱动联接条件而产生一个记录集。记录源可以是通过索引扫描或全表扫描访问的一个表，也可以由其他操作产生。例如，一个嵌套循环联接的输出可以当作另一个嵌套循环联接的记录源。
内循环对每一个外循环返回的记录执行一次，理想的情况是执行索引扫描。如果内循环的访问路径不依赖于外循环，可以直接用一个笛卡尔乘积来结束；对于外循环的每一次步进，内循环产生相同的记录集。因此当两个独立记录源联接时，您可以用其他的联接方法。
嵌套循环联接提示
如果优化器要选择其他联接方法，使用 USE_NL(a b) 提示，其中 a 和 b 是要联接的表的别名。
在例 1–25 中，数据量很少，优化器更愿意作全表扫描并选择哈希联接。
例 1–25 嵌套循环联接提示
初始查询
SELECT l.unit_price * l.quantity
FROM order_items l, orders h
WHERE h.customer_id = :b1
AND l.order_id = h.order_id;
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
HASH JOIN
TABLE ACCESS FULL ORDERS
TABLE ACCESS FULL ORDER_ITEMS
使用嵌套联接提示查询
下面的例子使用了一个提示，变换联接方法为嵌套循环。在这个指令中，orders 被全表扫描，过滤条件 customer_id =:b1 被施加到每一个记录。对于每一个符合过滤条件的记录，order_items 被经由索引 order_id 访问。
SELECT /*+ USE_NL(l h) */ l.unit_price * l.quantity
FROM order_items l, orders h
WHERE h.customer_id = :b1
AND l.order_id = h.order_id;
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS FULL ORDERS
TABLE ACCESS BY INDEX ROWID ORDER_ITEMS
INDEX RANGE SCAN ORDER_ID
避免全表扫描
这里增加另一个提示，避免对 orders 的全表扫描。这个提示可以使得执行计划类似于大型系统（尽管它在这里效果并不显著）。
SELECT /*+ USE_NL(l h) INDEX(h orders_n1) */ l.unit_price * l.quantity
FROM order_items l, orders h
WHERE h.customer_id = :b1
AND l.order_id = h.order_id;
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID ORDERS
INDEX RANGE SCAN CUSTOMER_ID
TABLE ACCESS BY INDEX ROWID ORDER_ITEMS
INDEX RANGE SCAN ORDER_ID
多重嵌套循环
一个嵌套循环的外循环也可以是一个嵌套循环。如果需要，可以嵌套两个或更多的外循环以联接多个表，每一层循环就是一个数据访问方法，如下：
SELECT STATEMENT
NESTED LOOP 3
NESTED LOOP 2 (OUTER LOOP 3.1)
NESTED LOOP 1 (OUTER LOOP 2.1)
OUTER LOOP 1.1 - #1
INNER LOOP 1.2 - #2
INNER LOOP 2.2 - #3
INNER LOOP 3.2 - #4
嵌套循环外联接
当两个表需要外联接的时候会使用该操作。外连接返回外表（要保留的）记录，即使内表中没有对应记录。
在正常的外联接中，优化器基于成本选择表（驱动和被驱动的）的次序。然而在嵌套循环外联接中，表的次序由联接条件决定。外表（只包含要保留的记录）用于驱动内表。
例 1–26 查询了前 30 天内创建的所有客户，看他们的活跃性如何。这里需要一个外联接，以免错过任何没有订单的客户。
例 1–26 嵌套循环外联接
SELECT cust_last_name, 86(nvl2(h.customer_id,0,1)) "Count"
FROM customers c, orders h
WHERE c.<creation_date> > SYSDATE -30
AND c.customer_id = h.customer_id(+)
GROUP BY cust_last_name;
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
SORT GROUP BY
NESTED LOOPS OUTER
TABLE ACCESS BY INDEX ROWID CUSTOMERS
INDEX RANGE SCAN <creation_date>
INDEX RANGE SCAN CUSTOMER_ID
本例中，外联接条件如下：
customers.customer_id = orders.customer_id(+)
该外联接条件中：
n 外表是 customers。
n 内表是 orders。
n 联接保留了 customers 的记录，包括了 orders 中没有对应数据的记录。
n 联接只能用 customers 驱动 orders。
优化器何时使用嵌套循环外联接
以下情况中，优化器会使用嵌套循环联接来处理外联接：
n 由外表驱动内表有可行性。
n 数据量足够小，使得嵌套循环较为有效。
嵌套循环外联接提示
要令优化器在联接中使用嵌套循环算法，使用 USE_NL 提示。
哈希联接
哈希联接用于联接大型数据集。优化器采用两个表或数据源中较小的一个在内存中建立基于联接键（join key）的哈希表。然后扫描较大的表，检测哈希表以找到要联接的记录。
这个方法最适用于较小的表能完全读入内存的情况。其后的成本只受限于两个表中一次读遍历的速度。
然而，如果哈希表增长得太大，导致内存放不下时，优化器会将表分为若干分区。由于这些分区的大小超过了分配到的内存，因此部分分区会被写入磁盘临时段。临时扩展段越大，会导致块写入磁盘时的 I/O 增长；推荐临时扩展段大小为 1MB。临时扩展段的大小由 INITIAL 指定，NEXT 指定常驻表空间，UNIFORMSIZE 指定临时表空间。
哈希表创建完成后，将进行如下操作：
1. 第二个表（较大的的表）被扫描。
2. 它被分为若干分区，像若干较小的表。
3. 各分区被写入磁盘
当哈希表建立好时，可能会出现这样的状况：整个哈希表占用了全部内存。此时不需给后一个（较大的）表建立相应的分区。后一个表被扫描时，相应记录对应到常驻哈希表中需要联接的记录，并立即返回结果。
每个哈希表分区被一次读入内存，然后执行如下的操作：
1. 扫描第二个表的相应分区。
2. 探测哈希表，返回联接后的记录。
这个过程一直重复，直到剩下的分区处理完。相成本会增加到两个读遍历和一个写遍历。
如果哈希表超过内存大小，可能某些部分会反复被 I/O 交换，出现这一情况的概率取决于第二个表中获取的记录数。该情况下，性能也显得很差。
优化器何时使用哈希联接
当两个表采用“＝”联接时，优化器会使用哈希联接来联接两个表。或者下列任一情况出现，同样采用哈希联接：
1. 大量数据要被联接。
2. 一个表的大部分需要联接。
在例 1-27 中，orders 表被用来建立哈希表，order_items 是较大的表，其后被扫描。
例 1–27 哈希联接
SELECT h.customer_id, l.unit_price * l.quantity
FROM orders h ,order_items l
WHERE l.order_id = h.order_id
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
HASH JOIN
TABLE ACCESS FULL ORDERS
TABLE ACCESS FULL ORDER_ITEMS
哈希联接提示
应用 USE_HASH 提示以建议优化器在联接两个表到一起时使用哈希联接。如果优化器不能正常进行哈希联接，请检查参数 HASH_AREA_SIZE 和 HASH_JOIN_ENABLED 的值。
|
注意：Oracle 不建议使用 HASH_AREA_SIZE 参数，除非是作为共享服务器配置。Oracle 建议通过设置 PGA_AGGREGATE_TARGET 启用 SQL 工作区的自动内存管理。保留 HASH_AREA_SIZE 参数只是为了向后兼容。 |
例 1–28 应用 USE_HASH 提示
初始查询
SELECT l.<promise_date>,l.product_id, SUM(l2.quantity)
FROM order_items l, order_items l2
WHERE l.product_id = l2.product_id
AND l.warehouse_id = l2.warehouse_id
AND l2.<promise_date>< l.<promise_date>
GROUP BY l.product_id, l.<promise_date>, l.order_id
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
SORT GROUP BY
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL ORDER_ITEMS
FILTER
SORT JOIN
TABLE ACCESS FULL ORDER_ITEMS
使用提示查询
SELECT /*+USE_HASH(l l2) */ l.<promise_date>,l.product_id, SUM(l2.quantity)
FROM order_items l, order_items l2
WHERE l.product_id = l2.product_id
AND l.warehouse_id = l2.warehouse_id
AND l2.<promise_date>< l.<promise_date>
GROUP BY l.product_id, l.<promise_date>, l.order_id
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
SORT GROUP BY
HASH JOIN
TABLE ACCESS FULL ORDER_ITEMS
TABLE ACCESS FULL ORDER_ITEMS
哈希外联接
当优化器认为数据量大到需要进行哈希联接，或外表不能驱动内表的时候，它会使用该操作。
与外联接类似，表的次序不是由成本决定，而是联接条件。（含保留记录的）外表被用于建立哈希表，内表被用来匹配哈希表。
例 1–29 显示了一个典型的哈希外联接查询。
例 1–29 哈希外联接
SELECT cust_last_name
FROM customers c, orders h
WHERE c.<creation_date> < SYSDATE-365
AND h.customer_id(+) = c.customer_id
AND h.customer_id IS NULL;
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
FILTER
HASH JOIN OUTER
TABLE ACCESS BY INDEX ROWID CUSTOMERS
INDEX RANGE SCAN <creation_date>
INDEX FAST FULL SCAN CUSTOMER_ID
该查询查找不活跃的用户（一年前创建，且没有订单）。内表沿着外表记录查找，当它没有在内表找到相应记录时，返回 NULL。该操作找出所有没有订单记录的客户记录。
本例中，外联接条件如下：
customers.customer_id = orders.customer_id(+)
条件表示的意思如下：
n 外表是 customers。
n 内表是 orders。
n 联接保留了客户记录，包括在 orders 中没有对应记录的记录。
n 哈希表通过 customers 建立。
n orders 被用于匹配哈希表。
您可以使用一个 NOT EXISTS 子查询来返回这些记录，然而，由于您是在表中查询所有记录，哈希联接效果更佳（除非 NOT EXISTS 子查询不嵌套）。
优化器何时使用哈希外联接
当数据量很大使得哈希联接较为有效，或外表不可能驱动内表的时候，优化器使用哈希联接来处理一个外联接。
例 1–30 外联接到一个多表视图。优化器不能像一般联接那样驱动到视图，或压入谓词，所以它建立了视图中的全部记录集。
例 1–30 外联接到多表视图
SELECT c.cust_last_name, sum(<revenue>)
FROM customers c, orders h
WHERE c.<creation_date> > sysdate-30
AND h.customer_id(+) = c.customer_id
GROUP BY c.cust_last_name;
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
SORT GROUP BY
HASH JOIN OUTER
TABLE ACCESS BY INDEX ROWID CUSTOMERS
INDEX RANGE SCAN <creation_date>
VIEW ORDERS
SORT GROUP BY
HASH JOIN
TABLE ACCESS FULL ORDERS
TABLE ACCESS FULL ORDER_ITEMS
该视图的定义如下：
CREATE OR REPLACE view orders AS
SELECT h.order_id, SUM(l.<revenue_amount>) <revenue>, h.order_id, h.customer_id
FROM orders h, order_items l
WHERE h.order_id = l.order_id
GROUP BY h.order_id, h.order_id, h.customer_id;
哈希外联接提示
要让一个外联接使用哈希联接，使用 USE_HASH 提示。
排序归并联接
排序归并联接可用于联接两个独立源。一般情况下，哈希联接的性能优于排序归并联接。另一方面，如果存在以下两个情况，排序归并联接的性能优于哈希联接：
n 记录源已排好序。
n 排序操作不需要完全完成。
然而，如果排序归并联接选择了一个较慢的访问方法（索引扫描的对立面——全表扫描），排序归并联接的优势可能就会失去。
当两个表之间的联接条件是不等条件（但不是不等式）时，如<、<=、> 或 >=，排序归并联接是实用的。排序归并联接应用于大数据集的性能优于嵌套循环联接（哈希联接只能用于等式条件）
在归并联接中，没有驱动表的概念。联接由两个步骤组成：
1. 排序联接操作：对两个输入的联接键排序。
2. 归并联接操作：顺序链表被归并到一起。
如果输入的联接字段已经排好序，排序操作则不会施加于记录源。
例 1–31 跟踪了 revenue 的生成，没有同等联接（equijoin）操作存在。因此这里既可以使用嵌套循环也可以使用排序归并来联接数据。对于更大的数据量，排序归并是更好的选择（事实上优化器会选择这个）。
例 1–31 无同等联接操作的排序归并联接
SELECT SUM(l.<revenue_amount>), l2.<creation_date>
FROM order_items l, order_items l2
WHERE l.<creation_date> < l2.<creation_date>
AND l.order_id <> l2.order_id
GROUP BY l2.<creation_date>, l2.line_item_id
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
SORT GROUP BY
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL ORDER_ITEMS
FILTER
SORT JOIN
TABLE ACCESS FULL ORDER_ITEMS
优化器何时使用排序归并联接
在联接大量数据时，如果以下条件为真，优化器将选择排序归并联接而不是哈希联接：
n 两个表之间的联接条件不能是同等联接。
n OPTIMIZER_MODE 被设置为 RULE。
n HASH_JOIN_ENABLED 为假。
n 由于其他操作已经要求排序，优化器发现使用排序归并比哈希联接成本更低。
n 优化器认为基于 HASH_AREA_SIZE 和 SORT_AREA_SIZE 的设置，使用哈希联接的成本较高。
|
注意：Oracle 不建议使用 HASH_AREA_SIZE 和 SORT_AREA_SIZE 参数，除非使用环境是作为共享服务器配置。Oracle 建议通过设置 PGA_AGGREGATE_TARGET 参数启用 SQL 工作区的自动内存管理。保留 HASH_AREA_SIZE 和 SORT_AREA_SIZE参数只是为了向后兼容。 |
例 1–32 跟踪了消费清单，选用排序归并操作以后，优化器避免了为 GROUP BY 作的一个排序。
例 1–32 排序归并联接避免 GROUP BY 排序
SELECT inv.product_id, SUM(l.quantity)
FROM inventories inv, order_items l
WHERE inv.product_id = l.product_id
AND inv.product_id BETWEEN :b1 AND :b2
GROUP BY inv.product_id
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
SORT GROUP BY NOSORT
MERGE JOIN
TABLE ACCESS BY INDEX ROWID ORDER_ITEMS
INDEX RANGE SCAN PRODUCT_ID
SORT JOIN
INDEX RANGE SCAN PRODUCT_ID
排序归并联接提示
应用 USE_MERGE 提示以建议优化器使用排序归并联接。您可能还需要给一个提示以指定访问路径。
例 1–33 显示了 USE_MERGE 的两个应用。
例 1–33 排序归并联接提示
初始查询
SELECT h.customer_id, l.unit_price * l.quantity
FROM orders h ,order_items l
WHERE l.order_id = h.order_id
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS FULL ORDER_ITEMS
TABLE ACCESS BY INDEX ROWID ORDERS
INDEX UNIQUE SCAN ORDER_ID
在初始查询中，优化器选择了对 order_items 作全扫描以避免排序操作。然而，这增加了成本。一个大的表通过索引和单块读入来访问，要比通过全表扫描的访问速度快。
带 USE_MERGE 的查询
SELECT /*+USE_MERGE(h l) */ h.customer_id, l.unit_price * l.quantity
FROM orders h ,order_items l
WHERE l.order_id = h.order_id
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
MERGE JOIN
TABLE ACCESS BY INDEX ROWID ORDER_ITEMS
INDEX FULL SCAN ORDER_ID
SORT JOIN
TABLE ACCESS FULL ORDERS
带 USE_MERGE 和 FULL 的查询
下面的例子对两个表都做了全扫描，且结果集在归并之前已排序。
SELECT /*+USE_MERGE(h l) FULL(l) */ h.customer_id, l.unit_price * l.quantity
FROM orders h ,order_items l
WHERE l.order_id = h.order_id
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL ORDERS
SORT JOIN
TABLE ACCESS FULL ORDER_ITEMS
排序归并外联接
当外联接无法用外表驱动内表时，它不能使用哈希联接或嵌套循环联接。因此，它会使用排序归并联接完成联接操作。
优化器何时使用排序归并外联接
当嵌套循环联接显得低效时，优化器使用排序归并联接。嵌套循环联接低效的原因可能是数据量（较大）、或者因为其他操作已经要求排序，优化器发现使用排序归并联接的成本低于哈希联接。
例 1–34 显示了一个存货用量报告，其中优化器通过排序归并操作避免了 GROUP BY 的一次排序。
例 1–34 排序归并外联接
SELECT inv.product_id, SUM(l.quantity)
FROM inventories inv, order_items l
WHERE inv.product_id = l.product_id(+)
GROUP BY inv.product_id;
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
SORT GROUP BY NOSORT
MERGE JOIN OUTER
SORT JOIN
INDEX FAST FULL SCAN PRODUCT_ID
SORT JOIN
TABLE ACCESS FULL ORDER_ITEMS
排序归并外联接提示
要使用排序归并做联接，使用 USE_MERGE 提示。
笛卡尔联接
笛卡尔联接用于一个或多个表的联接条件与其他表无关的情况。优化器将一个数据源的每一记录与另一数据源的每一记录联接，创建两个集合的笛卡尔乘积。
有时，两个表之间一个普通的过滤条件会被优化器当作一个可能的联接条件。这尤其危险，因为这种联接可能不会在执行计划中被标为一个笛卡尔乘积。
笛卡尔联接的通常起因是不恰当的 SQL 书写方式。例 1–35 的 FROM 子句中有三个表，但只有一个联接条件联接其中的两个表。
例 1–35 带 DISTINCT 子句的笛卡尔联接
SELECT DISTINCT h.order_id, l.line_item_id, l.quantity
FROM order_items l, orders h, order_items l2
WHERE h.customer_id = :b1
AND l.order_id = h.order_id;
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
SORT UNIQUE
MERGE JOIN CARTESIAN
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID ORDERS
INDEX RANGE SCAN ORDER_ID
TABLE ACCESS BY INDEX ROWID ORDER_ITEMS
INDEX RANGE SCAN ORDER_ID
SORT JOIN
INDEX FAST FULL SCAN ORDER_ID
虽然简单，本例中的情形会发生在用户写一个包括很多表的查询，某个额外的表被写入 FROM 子句却没有被写入 WHERE 子句的时候。这种查询当中，用 DISTINCT 子句可以剔除重复的记录。
优化器通过索引 order_items_n1 获取 order_items 的 rowids 字段。这是因为 order_id 是个 NOT NULL 字段，索引中因此包含所有的 rowids。结果被按照联接键排序。
优化器通过嵌套循环联接联接表 l 和 h。对每一个返回的记录，它返回 order_items 表的所有记录。执行计划显示，笛卡尔乘积中应用了一个归并联接。
|
注意：本例并不是值得推荐的方法。您应该指定联接标准，从而避免笛卡尔乘积。 在本例中，DISTINCT 移除了笛卡尔乘积生成的额外的元组（tuple），但性能急剧降低。 |
例 1–36 显示了一个带隐式笛卡尔乘积的嵌套循环。
例 1–36 带隐式笛卡尔乘积的嵌套循环
SELECT a.<attribute4>,b.last_name
FROM employees b, order_items a
WHERE a.order_id = :b1
AND b.employee_id = a.<created_by>
AND b.department_id = :b2;
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID EMPLOYEES
INDEX RANGE SCAN EMPLOYEES
TABLE ACCESS BY INDEX ROWID ORDER_ITEMS
INDEX RANGE SCAN ORDER_ID
如果嵌套循环操作的内表不是由外表而是由一个独立的记录源驱动，记录也将像笛卡尔乘积中那样被访问。由于联接条件存在且访问表以后被应用，因此结果不是笛卡尔乘积。然而，二者的访问成本是基本相同的。
检查例 1–36 的指令，你可能认为优化器会做如下工作：
1. 通过 order_id 访问 order_items。
2. 对每一个从 order_items 返回的记录，通过 mployee_id 联接到 employees
3. 对 employees 施加 department_id 过滤。
然而，执行计划显示某些部分很不相同。employee_id 的数据类型是 VARCHAR2，而 <created_by> 是数值，这导致了对 employee_id 的隐式类型转换，索引因此被禁用。结果是两个独立的源。
1. 所有记录依据 department_id 从 employees 获取。
2. 所有记录依据 order_id 从 order_items 获取。
根据 1 中的每一个记录，访问 2 中的每一个记录，但只返回满足下面条件的记录：
TO_NUMBER(b.employee_id) = a.<created_by>
因为有一个联接条件，所以结果并没有显示为一个笛卡尔乘积，但访问记录的次数是相同的。
优化器何时使用笛卡尔联接
当要求无条件联接两个表时，优化器使用笛卡尔联接。
笛卡尔联接提示
ORDERED 提示可能会导致笛卡尔联接。By specifying a table before its join table is specified, the optimizer does a Cartesian join. （这句话比较晦涩，不知道该如何翻译）
例 1–37 使用 ORDERED 提示之前：
SELECT h.<purchase_order_num>, sum(l.<revenue_amount>)
FROM customers c, order_items l, orders h
WHERE c.cust_last_name = :b1
AND h.customer_id = c.customer_id
AND h.order_id = l.order_id
GROUP BY h.<purchase_order_num>;
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
SORT GROUP BY
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID CUSTOMERS
INDEX RANGE SCAN CUSTOMER_ID
TABLE ACCESS BY INDEX ROWID ORDERS
INDEX RANGE SCAN ORDER_ID
TABLE ACCESS BY INDEX ROWID ORDER_ITEMS
INDEX RANGE SCAN ORDER_ID
计划显示优化器是按如下步骤执行的：
1. 通过 cust_last_name 访问 customers。
2. 通过 customer_id 联接到 orders。
3. 通过 line_item_id 联接到 order_items。
例 1–38 使用 ORDERED 提示之后：
SELECT /*+ORDERED */ h.<purchase_order_num>, sum(l.<revenue_amount>)
FROM customers c, order_items l, orders h
WHERE c.cust_last_name = :b1
AND h.customer_id = c.customer_id
AND h.order_id = l.order_id
GROUP BY h.<purchase_order_num>;
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
SORT GROUP BY
MERGE JOIN
SORT JOIN
MERGE JOIN CARTESIAN
TABLE ACCESS BY INDEX ROWID CUSTOMERS
INDEX RANGE SCAN CUST_LAST_NAME
SORT JOIN
TABLE ACCESS FULL ORDER_ITEMS
SORT JOIN
TABLE ACCESS FULL ORDERS
优化器现在是按照如下步骤执行的：
1. 通过 cust_last_name 访问 customers。
2. 跟 order_items 作一个笛卡尔乘积，因为它是 FROM 子句的下一字段。
3. 来自 customers 的记录乘上（逻辑意义上的）order_items 中的数百万记录。
4. 对结果集按（customer_id，order_id）的顺序排列。
5. orders 表按（customer_id，order_id）排序后与前面的结果集做归并联接。
若 order_items 和 customers 之间有一个非选择性的过滤条件：
SELECT /*+ORDERED */ h.<purchase_order_num>, SUM(l.<revenue_amount>)
FROM customers c, order_items l, orders h
WHERE c.cust_last_name = :b1
AND h.customer_id = c.customer_id
AND h.order_id = l.order_id
AND c.price_list_id = l.price_list_id
GROUP BY h.<purchase_order_num>;
Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
SORT GROUP BY
MERGE JOINSORT JOIN
NESTED LOOPS
TABLE ACCESS BY INDEX ROWID CUSTOMERS
INDEX RANGE SCAN CUST_LAST_NAME
TABLE ACCESS FULL ORDERS
SORT JOIN
TABLE ACCESS FULL ORDERS
执行计划很相似，但现在因为优化器在两个表之间找到了一个联接条件，它移除了笛卡尔乘积生成的额外的元组。尽管没有建立实际的笛卡尔乘积，执行计划在块访问中仍然显得效率低下，因为嵌套循环联接对于大型数据集的表现并不好。
完全外联接
一个完全外联接就像左、右外联接的联合。在内联接的基础上，会保留内联接没有返回的两个表中的各个记录，并以 null 填充。
换言之，完全外联接让您把表联接到一起，还会把没有对应记录的记录也放入联接后的表中。
例 1–39 的查询获取所有部门以及部门内的所有雇员，另外还包括：
n 不在部门内的雇员。
n 没有雇员的部门。
例 1–39 完全外联接
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;
该指令产生如下输出：
DEPARTMENT_ID EMPLOYEE_ID
------------------------ --------------------
10 255200
20 255202
20 255201
30
40 255203
255199
255204
255206
255205
CBO 如何为联接选择执行计划
应用基于成本和基于准则的途径都需要考虑以下因素：
n 优化器首先确认联接的两个或更多的表是否是明确定义的最多一个记录的记录源（即常表）。优化器基于表中的 UNIQUE 和 PRIMARY KEY 约束来认定这一情况。如果存在，优化器会将这些表放在联接次序的前面。然后优化器再优化剩余的其他表。
n 对于带外联接条件的联接指令，联接条件中联接符的前后必须各有一个表。优化器不考虑违反改规则的联接次序。
CBO 方法中，优化器根据可能的联接次序、联接方法及可用的访问路径产生一系列执行计划。然后，优化器评估每一个计划的成本，并选择最低的那一个。评估成本有如下途径：
n 嵌套循环操作的成本是基于读取每一个外表中的选中记录及内表中的每一个匹配记录进入内存的成本。优化器使用数据词典中的统计表来评估成本。
n 排序归并联接的成本主要是基于读取所有记录源到内存并排序的成本。
在确定每个操作成本的时候，优化器还会考虑其他因素。例如：
n 一个较小的排序区会增加排序归并联接的成本，因为排序会花更多的 CPU 时间和 I/O。排序区大小由初始参数 SORT_AREA_SIZE 指定。
|
注意：Oracle 不建议使用 SORT_AREA_SIZE 参数，除非是作为共享服务器配置。Oracle 建议通过设置 PGA_AGGREGATE_TARGET 启用 SQL 工作区的自动内存管理。保留 SORT_AREA_SIZE 参数只是为了向后兼容。 |
n 一个较大的多块读取计数（multiblock read count）可能会降低排序归并联接及嵌套循环联接的成本。如果单次I/O 可以从磁盘读取大量连续块，嵌套循环联接的内表索引不太可能提高全表扫描的性能。多块读取计数由初始参数 DB_FILE_ MULTIBLOCK_READ_COUNT 指定。
使用 CBO，优化器对联接次序的选择可以被 ORDERED 提示覆盖。如果 ORDERED 提示指定的联接次序违反了外联接的规则，优化器会忽略该提示而选择（合理的）次序。当然，你也可以使用提示覆盖优化器选择的联接方法。
参见：
第5章，“优化器提示”以获取更多关于优化器提示的信息
CBO 如何执行反联接
反联接返回谓词左侧（的表）拥有而右侧（的表）中没有对应记录的记录集。就是说，它返回不匹配右侧记录子查询（NOT IN subquery）的记录。举例来说，一个反联接会选出不在特定部门的雇员列表：
SELECT *
FROM employees
WHERE department_id NOT IN
(SELECT department_id
FROM departments
WHERE loc = ’HEADQUARTERS’);
优化器对 NOT IN 子查询默认使用嵌套循环算法。然而，如果使用了 MERGE_AJ、HASH_AJ 或 NL_AJ 提示并且存在各种需要的条件，NOT IN 子查询可以变成排序归并或哈希反联接。
CBO 如何执行半联接
半联接（又称准联接）返回的记录集是匹配 EXISTS 的一组记录，但当谓词右侧有多个记录满足子查询标准时，不复制（双份）左侧记录。例如：
SELECT *
FROM departments
WHERE EXISTS
(SELECT * FROM employees
WHERE departments.employee_id = employees.employee_id
AND employees.salary > 25000);
该查询中，即使 employees 中有多个记录匹配子查询，也只从 departments 中返回一次记录。如果 salary 字段没有索引，将会使用半联接以提高查询性能。
对不能跟主查询归并的 IN 或 EXISTS 子查询，优化器默认采用嵌套循环算法。然而，如果指定 MERGE_SJ、HASH_SJ 或 NL_SJ 提示，且满足对应的条件需求，子查询可以转换为排序归并或哈希半联接。
参见：
第5章，“优化器提示”获取更多关于优化器提示的信息
CBO 如何执行星型查询
有些数据仓库被设计为星型结构，包含一个超大的事实表和若干较小的维度表（也称查找表）。事实表存放主要信息，每个维度表存放事实表某一属性的相关信息。
星型查询用于联接事实表与若干维度表。每个维度表通过自己的主键联接到对应事实表的外来键（foreign key），但维度表不互相联接。
CBO 识别星型查询并为之生成高效的执行计划（RBO 不能识别星型查询）。
一个典型的事实表包括键字和测度（measures）。例如，一个简单的事实表可能包含测度 Sales 和键字 Time、Product 和 Market。因此，应该相应存在 Time、Product 和 Market 的维度表。以 Product 维度表为例，典型的情况应该包含事实表中每个 product 的数量信息。
星型联接利用事实表的外来键和对应维度表的主键作联接。事实表一般有一个外来键字段的连接索引以使这种类型的联接更容易，也可能为每个外来键字段建立单独的位图索引。
参见：
《Oracle9i 数据仓库指南》以获取更多星型查询的信息
