当优化器认为数据量大到需要进行哈希联接，或外表不能驱动内表的时候，它会使用该操作。
与外联接类似，表的次序不是由成本决定，而是联接条件。（含保留记录的）外表被用于建立哈希表，内表被用来匹配哈希表。
例
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;
--------------------------------------------------------------------------------
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;
--------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------
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
的设置，使用哈希联接的成本较高。
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
--------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------
SELECT STATEMENT
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL ORDERS
SORT JOIN
TABLE ACCESS FULL ORDER_ITEMS
笛卡尔联接用于一个或多个表的联接条件与其他表无关的情况。优化器将一个数据源的每一记录与另一数据源的每一记录联接，创建两个集合的笛卡尔乘积。
有时，两个表之间一个普通的过滤条件会被优化器当作一个可能的联接条件。这尤其危险，因为这种联接可能不会在执行计划中被标为一个笛卡尔乘积。
笛卡尔联接的通常起因是不恰当的
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;
--------------------------------------------------------------------------------
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
表的所有记录。执行计划显示，笛卡尔乘积中应用了一个归并联接。
--------------------------------------------------------------------------------
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>;
--------------------------------------------------------------------------------
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>;
--------------------------------------------------------------------------------
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>;
--------------------------------------------------------------------------------
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
40 255203
255199
255204
255206
255205
CBO
如何为联接选择执行计划
应用基于成本和基于准则的途径都需要考虑以下因素：
n
优化器首先确认联接的两个或更多的表是否是明确定义的最多一个记录的记录源（即常表）。优化器基于表中的
UNIQUE
和
PRIMARY KEY
约束来认定这一情况。如果存在，优化器会将这些表放在联接次序的前面。然后优化器再优化剩余的其他表。
n
对于带外联接条件的联接指令，联接条件中联接符的前后必须各有一个表。优化器不考虑违反改规则的联接次序。
CBO
方法中，优化器根据可能的联接次序、联接方法及可用的访问路径产生一系列执行计划。然后，优化器评估每一个计划的成本，并选择最低的那一个。评估成本有如下途径：
n
嵌套循环操作的成本是基于读取每一个外表中的选中记录及内表中的每一个匹配记录进入内存的成本。优化器使用数据词典中的统计表来评估成本。
n
排序归并联接的成本主要是基于读取所有记录源到内存并排序的成本。
在确定每个操作成本的时候，优化器还会考虑其他因素。例如：
n
一个较小的排序区会增加排序归并联接的成本，因为排序会花更多的
CPU
时间和
I/O
。排序区大小由初始参数
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
数据仓库指南》以获取更多星型查询的信息
