嵌套循环Nested Loop Joins
Nested loop joins are useful when the following conditions are true:
The database joins small subsets(子集) of data.(数据库连接小部分数据)
The join condition is an efficient method of accessing the second table.(连接条件是访问第二个表的有效方法)

It is important to ensure that the inner table is driven from (dependent on) the outer table. If the inner table's access path is independent of the outer table, then the same rows are retrieved(检索) for every iteration(循环) of the outer loop, degrading performance considerably. In such cases, hash joins joining the two independent row sources perform better.
如果内表的访问路径独立于外部表，则对于外部循环的每次循环都会检索相同的行，从而大大降低性能

A nested loop join involves(涉及) the following steps:
The optimizer determines the driving table and designates(指定) it as the outer table.
The other table is designated as the inner table.
For every row in the outer table, Oracle Database accesses all the rows in the inner table. The outer loop is for every row in the outer table and the inner loop is for every row in the inner table. The outer loop appears before the inner loop in the execution plan, as follows:NESTED LOOPS、outer_loop、inner_loop 

The optimizer uses nested loop joins when joining small number of rows, with a good driving condition between the two tables. You drive from the outer loop to the inner loop, so the order of tables in the execution plan is important

The outer loop is the driving row source. It produces a set of rows for driving the join condition. The row source can be a table accessed using an index scan or a full table scan. Also, the rows can be produced from any other operation. For example, the output from a nested loop join can serve as a row source for another nested loop join.

The inner loop is iterated(循环) for every row returned from the outer loop, ideally by an index scan. If the access path for the inner loop is not dependent on the outer loop, then you can end up with a Cartesian(笛卡尔积) product; for every iteration of the outer loop, the inner loop produces the same set of rows. Therefore, you should use other join methods when two independent row sources are joined together.
两表查询没有任何关联条件如select * from t1,t2就是典型的笛卡尔积

Nested loop join的个人理解和总结

1、工作原理：
select ...from t1, t2 where t1.id=t2.id;
for i in 1..t1.rowcount loop
   s_t1_id := t1[i].id;
   for j in 1..t2.rowcount loop
      s_t2_id := t2[j].id;
      if s_t1_id = s_t2_id then
         .....
      end if;
   end loop;
end loop;
Driving Table（驱动表）： 该表又称为外层表（OUTER TABLE）。通俗的理解就是执行计划中NESTED LOOPS先执行的那个表
Probed Table（匹配表）： 该表又称为内层表（INNER TABLE）

2、通过以上工作原理我们知道，T1表（即驱动表）每返回一条记录，都要去T2表去轮询一次，得到与其匹配的数据，推送到结果集中。所以在嵌套循环的使用中，必须设置返回记录少的表作为驱动表，可以通过以下两点来提高嵌套循环的速度。

   第一：尽量提高T1表取记录的速度(T1表的连接列上创建索引)
   第二：尽量提高T2表记录匹配速度(T2表的连接列上创建索引)

哈希连接 Hash Joins
The database uses hash joins to join large data sets. The optimizer uses the smaller of two tables or data sources to build a hash table on the join key in memory. It then scans the larger table, probing(探测) the hash table to find the joined rows.This method is best when the smaller table fits in available memory. The cost is then limited to a single read pass over the data for the two tables.（对两个表的数据进行单次读取）
The optimizer uses a hash join to join two tables if they are joined using an equijoin(等值连接) and if either of(任意一个) the following conditions are true:
A large amount of data must be joined.
A large fraction of a small table must be joined.

hash join的个人理解和总结
- 小表关联大表
- 全表扫描小表，并把关联字段的值取出，在pga里构建hash table。
- 遍历大表，根据扫描的结果按照关联字段执行相同的hash函数，得到hash value，去pga的hash table里找匹配记录。
优点
- 逻辑读很少，因为两表读只读一次。
缺点：
- 如果小表的hash table不能完全放在PGA里的话，则性能会极具下降。

摘录引用一个关于Hash join原理的例子
假如两个数据集：
S={1,1,1,3,3,4,4,4,4,5,8,8,8,8,10}
B={0,0,1,1,1,1,2,2,2,2,2,2,3,8,9,9,9,10,10,11}
Hash Join的第一步就是判定小表（即build input）是否能完全存放在hash area内存中
如果能完全存放在内存中，则在内存中建立hash table，这是最简单的hash join
如果不能全部存放在内存中，则build input必须分区。Oracle采用内部一个hash函数作用于连接键上，将S和B分割成多个分区,这样产生十个分区，如下表:

经过这样的分区之后，只需要相应的分区之间做join即可
    如果有一个分区为NULL的话，则相应的分区join即可忽略,在将S表读入内存分区时，oracle即记录连接键的唯一值，构建成所谓的位图向量,它需要占hash area内存的5%左右。在这里即为{1,3,4,5,8,10}
   当对B表进行分区时，将每一个连接键上的值与位图向量相比较，如果不在其中，则将其记录丢弃
   在我们这个例子中，B表中以下数据将被丢弃{0,0,2,2,2,2,2,2,9,9,9,9,11},这个过程就是位图向量过滤,当S1,B1做完连接后，接着对Si,Bi进行连接

排序合并Sort Merge Joins
Sort merge joins can join rows from two independent sources. Hash joins generally perform better than sort merge joins. However, sort merge joins can perform better than hash joins if both of the following conditions exist:
The row sources are sorted already.
A sort operation does not have to be done.

However, if a sort merge join involves choosing a slower access method (an index scan as opposed to a full table scan), then the benefit of using a sort merge might be lost.

Sort merge joins are useful when the join condition between two tables is an inequality condition such as <, <=, >, or >=. Sort merge joins perform better than nested loop joins for large data sets. You cannot use hash joins unless there is an equality condition.

In a merge join, there is no concept of a driving table. The join consists of two steps:
Sort join operation: Both the inputs are sorted on the join key.
Merge join operation: The sorted lists are merged together.

If the input is sorted by the join column, then a sort join operation is not performed for that row source. However, a sort merge join always creates a positionable sort buffer for the right side of the join so that it can seek back to the last match in the case where duplicate join key values come out of the left side of the join.

The optimizer can choose a sort merge join over a hash join for joining large amounts of data if any of the following conditions are true:
The join condition between two tables is not an equijoin.
Because of sorts required by other operations, the optimizer finds it is cheaper to use a sort merge than a hash join.

总结三种连接方式差异如下

访问次数|嵌套循环|驱动表返回几条，被驱动表访问多少次
              |—————————————————————————————————————— 
              |哈希连接|表都最多访问一次
              |--------------------------------------------------------------------------
              |排序合并|表都最多访问一次
---------------------------------------------------------------------------
适应场景|嵌套循环|小量数据的连接或小表大表连接
              |—————————————————————————————————————— 
              |哈希连接|大数据的连接或小表大表连接
              |--------------------------------------------------------------------------
              |排序合并|大数据的连接且有排序操作
---------------------------------------------------------------------------
排序差异|嵌套循环|无排序
              |-------------------------------------------------------------
              |哈希连接|无排序，但会消耗内存建立Hash表
              |--------------------------------------------------------------
              |排序合并|有排序
----------------------------------------------------------------
限制场景|嵌套循环|无任何限制，<>,>,<,like都可以用
              | ——————————————————————————
              |哈希连接|只能使用=
              | -----------------------------------------------------
              |排序合并|无任何限制，<>,>,<,like都可以用
----------------------------------------------------
索引使用|嵌套循环|驱动表的限制条件和被驱动表的连接条件上创建索引
              | ------------------------------------------------------------
              |哈希连接|索引列无要求，与单表情况无异
              | -----------------------------------------------------------
              |排序合并|索引可以消除排序
              ---------------------------------------------------------