Oracle 4种Join 类型

要想了解如何分析执行计划就必须先了解join 的种类及机制，下面对oracle的4中join 方法做个总结。

一、查询优化器如何执行join 语句

在有join的语句中优化器是如何选择执行计划的呢？优化器需要考虑选择什么访问路径？选择什么join 方法？选择什么join顺序？

那么优化器是如何选择执行计划的呢？

1. 优化器首先要判断是join两张表还是多张表。对于多张表，优化器会把有唯一约束或主键的表放在前面，然后处理后面的表。

2. 对于有outer join条件的语句，有outer join条件的表将放在后面。当一个子查询已经转化为一个表时，子查询的表必须在outer join的后面。

优化器根据可能的join顺序,join 方法和可用的访问路径会产生很多的执行计划后，优化器需要评估每个执行计划的成本，并选择最低的一个作为最终执行计划，对于成本的评估方法是：

1. nested loop join的成本是基于读取外表的每一行并和在内存里的内表一一匹配的成本，优化器通过统计信息来评估该成本。

2. sort merge join的成本主要在读所有的数据到内存并进行排序。

3.hash join的成本主要在建立hash表。

同时优化器还会考虑小的sort area size将增加sort merge join的成本，因为在小的sort area里面将花费更多的CPU和IO。相比与nest loop join大multiblock读将提高sort merge join的性能。

二、join 的种类

1. nested loop join

当小数据量的表被join时，并且join的条件能非常有效和和第二个表建立关联，使用nest loop join将非常有效。

使用nest loop join的关键是inner表必须依赖于outer表，否则使用hash join将更加高效。

nest loop join涉及到以下步骤：

A. 优化器决定驱动表并指定它为outer表

B. 其它表被指定为inner表

C. oracle 先取outer表一行数据再和inner表的每一行数据进行匹配，再取outer表第二行和inner表的每一行进行匹配，知道内外都循环完。

在执行计划中outer loop在上面，inner loop在下面:

NESTED LOOPS

outer_loopinner_loop

Nest Loop Join的关键是：小数据量的join, Join表之间存在依赖关系，内表依赖于外表。比如雇员表的部门号依赖与部门表的部门号。因此雇员表作为inner表，部门表做为outer表。在扫描中outer可以是索引扫描也可以是全表扫描，对于inner表是采用索引扫描。使用USE_NL(table1 table2) hint来强制使用nest loop join方法。

2. hash join

对于数据量大的join使用hash join 更有效。优化器选择两个join表中小的表在内存中通过join key来建立hash表。然后扫描大表来匹配哈希表从而找到join的行。什么时候采用hash join呢？如果这个join是equijoin或满足以下条件之一：

1. 大量的数据需要join

2. 一个小表的大部分需要被join使用USE_HASHhint来强制使用hash join.

 

3. sort merge join

sort merge join用于join两个没有依赖关系的表。

一般来说hash join比sort merge join的性能好，但是在以下条件中，并不见得：

1. 数据源已经被排序了

2. 一个排序操作不得不执行对于join条件是非equijoin(不包括不等于),如>,>=,<,<=时，sort merge join比较适用。

对于数据量比较大是sort merge join要比nest loop join性能好。在merge join中，并没有驱动表的概念，join由以下两步组成：

1. sort join操作：两个表都在join key上排序

2. merge join操作：排序的列表被合并到一起对于join大数据量时，以下情况使用sort merge join，而不使用hash join

1. 两个表的join条件不是euqijoin

2. 操作中需要排序，优化器发现使用sort merge join成本更低可以使用USE_MERGE hint来强制使用sort merge join

 

4. Cartesian join

对于没有任何join条件的语句将采用笛卡尔积join.使用ORDEREDhint强制执行cartesian join.