Oracle Join Methods

Hash Join

过程

通过一个例子来说明

SQL_ID  gtmmr9gaxqhfr, child number 0
-------------------------------------
SELECT /*+ USE_HASH(d e) */e.ename, d.dname FROM emp e, dept d where
e.deptno=d.deptno

Plan hash value: 1123238657

-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |   106 (100)|          |       |       |          |
|*  1 |  HASH JOIN         |      |     15 |   360 |   106   (1)| 00:00:02 |  1206K|  1206K|  661K (0)|
|   2 |   TABLE ACCESS FULL| EMP  |     15 |   135 |     3   (0)| 00:00:01 |       |       |          |
|   3 |   TABLE ACCESS FULL| DEPT |    100K|  1465K|   103   (1)| 00:00:02 |       |       |          |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPTNO"="D"."DEPTNO")

1. Oracle将emp以deptno为hash key在PGA中建立一个hash table，这个hash table是分区的，假如PGA的hash area不够大，将会以分区为单位写入temp表空间。Oracle总是使用较小的结果集建立hash table。在执行计划中，紧跟着Hash join的就是probed hash table。

2. Oracle扫描dept，同样以deptno位hash key去probe内存/temp中的hash table。

3. 返回结果。

由于Hash join使用PGA，是私有内存，不需要latch保护。这一点与buffer cache中内容读写不一样。

Zero pass, One pass and Multiple pass

hash area size能够存储整个probed hash table时，不需要使用temp，性能最优，为zero pass。在执行机会中称之为0mem

当hash area size能够放下至少一个分区时，执行计划中为1mem. 

hash area size连一个完整分区都不能存储时，为最差情况，这种情况称为multiple pass.

Nested Loop Join

Nested loop工作原理类似双重循环。通常用于两个小的结果集。

特点：

outer table比较小，inner table可以用连接键快速定位，比如使用索引。也就是说,inner table的连接键上有索引。在执行计划里，

NESTED LOOPS
  outer_loop
  inner_loop

Oracle 11g引入了新的实现方式，在执行计划里有体现。具体请看Performance Tuning Guide Page 300。