Oracle 查询优化器 -- 表连接方法

-- Start

循环嵌套连接（Nested Loop Joins）

首先，看看下面的 SQL 语句。

SELECT e.first_name, e.last_name, e.salary, d.department_nameFROM hr.employees e, hr.departments dWHERE d.department_name IN ('Marketing', 'Sales')AND e.department_id = d.department_id;

对于循环嵌套，你可以把表想象成数组，Oracle 会采用如下的方式执行查询。

String[] departments = {};String[] employees = {};// 外层循环for(String dep: departments) {// 内层循环for(String emp: employees) {}}

很明显，如果 employees 很大且没有索引，外层循环每执行一次都需要全表扫描 employees，这是不可接受的。所以循环嵌套表连接方式适合那些内层循环数据量少且有索引的情形。

当然，你也可以通过下面的方式建议 Oracle 采用循环嵌套连接方式。

-- USE_NLSELECT /*+ USE_NL(e d) */ e.first_name, e.last_name, e.salary, d.department_nameFROM hr.employees e, hr.departments dWHERE d.department_name IN ('Marketing', 'Sales')AND e.department_id = d.department_id;--USE_NL_WITH_INDEX，指定 e 为内层循环表SELECT /*+ USE_NL_WITH_INDEX(e) */ e.first_name, e.last_name, e.salary, d.department_nameFROM hr.employees e, hr.departments dWHERE d.department_name IN ('Marketing', 'Sales')AND e.department_id = d.department_id;--USE_NL_WITH_INDEX，指定 e 为内层循环表，同时指定索引SELECT /*+ USE_NL_WITH_INDEX(e emp_dep_id_idx) */ e.first_name, e.last_name, e.salary, d.department_nameFROM hr.employees e, hr.departments dWHERE d.department_name IN ('Marketing', 'Sales')AND e.department_id = d.department_id;

你还可以通过下面的方式建议 Oracle 不要采用循环嵌套连接方式。

SELECT /*+ NO_USE_NL(e d) */ e.first_name, e.last_name, e.salary, d.department_nameFROM hr.employees e, hr.departments dWHERE d.department_name IN ('Marketing', 'Sales')AND e.department_id = d.department_id;

哈希连接（Hash Joins）

首先，看看下面的 SQL 语句。

SELECT o.customer_id, l.unit_price * l.quantityFROM orders o ,order_items lWHERE l.order_id = o.order_id;

哈希连接会采用如下步骤。

第一步，根据小表在内存中创建一个 Hash 表。类似下面的 Java 代码。

// Hash 表Map hash_join = new HashMap();// 订单表Integer[] order_ids = {};// 填充 Hash 表for(Integer id: order_ids) {hash_join.put(id, -999);}

第二步，扫描大表，建立对应关系。

// Hash 表Map hash_join = new HashMap();// order_items 表Integer[] order_items = {};// 扫描 order_items 表，建立对应关系for(Integer id: order_items) {// 注意这里是重点：根据 Hash 算法检索数据Integer value = hash_join.get(id);if(value == null) {continue;} else {hash_join.put(id, value); // 建立对应关系}}

第三步，根据 Hash 表的对应关系返回数据。

哈希连接适合大表连接，但是连接条件必须是等价条件。你可以通过下面的方式建议 Oracle 采用哈希连接方式。

SELECT /*+ USE_HASH(o l) */ o.customer_id, l.unit_price * l.quantityFROM orders o ,order_items lWHERE l.order_id = o.order_id;

你也可以通过下面的方式建议 Oracle 不要采用哈希连接方式。

SELECT /*+ NO_USE_HASH(o l) */ o.customer_id, l.unit_price * l.quantityFROM orders o ,order_items lWHERE l.order_id = o.order_id;

排序合并连接（Sort Merge Joins）

排序合并连接分两步，第一步把两个表根据连接条件排序，第二步把排序后的表合并。通常，哈希连接的性能要比排序合并好，但是哈希连接条件必须是等价条件，而排序合并连接条件可以是任何条件。此外，在下面的情况下会考虑使用排序合并连接。

1.要连接的两个集合已经排序了
2.结果集需要排序

你可以通过下面的方式建议 Oracle 采用排序合并连接。

SELECT /*+ USE_MERGE(employees departments) */ *FROM employees, departmentsWHERE employees.department_id = departments.department_id;

你也可以通过下面的方式建议 Oracle 不要采用排序合并连接。

SELECT /*+ NO_USE_MERGE(employees departments) */ *FROM employees, departmentsWHERE employees.department_id = departments.department_id;

笛卡儿连接（Cartesian Joins）

如果两个表没有指定连接条件，Oracle 会使用笛卡儿连接。结果集是两个表行数的乘积。

外连接（Outer Joins）

外连接包括左外连接，右外连接，全外连接。对于左外连接和右外连接，Oracle 根据统计信息来决定连接方式，包括循环嵌套外连接（Nested Loop Outer Joins），哈希外连接（Hash Join Outer Joins）， 排序合并外连接（Sort Merge Outer Joins）。但是对于全外连接，从11g开始，Oracle 默认采用基于哈希算法的本地化执行方法（native execution method），你也可以通过下面的方式提示 Oracle 采用或不采用本地化执行方法。

NATIVE_FULL_OUTER_JOINNO_NATIVE_FULL_OUTER_JOIN

--更多参见：Oracle SQL 优化精萃

-- 声明：转载请注明出处

-- Last edited on 2015-07-21

-- Created by ShangBo on 2015-07-21

-- End