HASH JOIN的模式
hash join有三种工作模式，分别是optimal模式，onepass模式和multipass模式，分别在v$sysstat里面有对应的统计信息：
SQL> select name, value from v$sysstat where name like '%workarea executions%';
optimal模式
optimal模式就是从build table上获取的结果集比较小，可以把整个hash table都建立在用户可以使用的内存区域里。下面这张图就是用来描述optimal的hash join的：
optimal hash join大致上分为以下几步：
1. 首先利用做连接的列上的hash函数，把build table的结果集做成内存里的hash table，这里的hash bucket总是2的n次方，比如1024或4096。可以简单的把hash table看做内存里的一个大正方形，你面有很多小格子，而build table上的数据则是分散的分布在这些格子里面，而这些小格子就是hash bucket。
2. oracle开始读取probe table, 针对每一条数据都对做连接的列上使用hash函数，定位对应build table的相同值的hash bucket，找到相应的hash bucket后就到那个位置是去看有没有匹配的数据。这个过程叫做probing hash table。
3. 在检查bush bucket的时候，如果bucket里面没有数据，那么马上丢掉probe table的这一行。如果Bucket里面有数据，就要进一步检查里面的数据是否和proble table当前的这个数据匹配，这一步是很必要的。在我们前面介绍filter和hash cluster table的时候我们提过，hash函数存在一个冲突的问题，也就是不同的hash key完全有可能对应到相同的hash bucket里，所以当我们为probe table上的一个值定位到了一个hash bucket的时候，我们需要做进一步的检查，来看看这个bucket里面哪些数据是我们需要的，哪些数据是我们不需要的。 在理想的情况下，我们希望每一个hash bucket里面最多只有一个hash key的数据，因此往往hash bucket的个数是要比hash key的个数要多的。
onepass hash join模式
我们知道optimal hash join发生在我们可以把整个hash table全部放在内存里的时候，从而所有的join操作都可以在内存里面完成，这是我们最理想的模式。但是，当我们的内存无法放下整个hash table，我们就不得不在onepass模式下进行hash join。
1. 首先，由于内存无法放下所有的hash table内容，那么就会导致有的hash bucket放在内存里，有的hash bucket放在磁盘上，但不管放在哪里，Oracle使用一个bitmap结构来反应这些bucket的状态，包括位置和是否有数据在里面。
2. 当我们的probe table对连接的列使用hash函数之后，先到bitmap上看看对应的bucket是不是为空，而过为空，这条数据就丢掉不管。如果不为空，还要看现 在这个bucket是在内存里还是在磁盘上。如果是在内存里，就直接访问这个bucket并检查是否有数据匹配，如果有匹配就返回这条查询结果。第二种情 况是如果要访问的这个bucket在磁盘上，这时候如果直接去磁盘*问显然cost很大，所以oracle的处理方法是先把这个probe的数据放到一 边不管。顺便一提的是这个probe的值首先是会放在内存里，如果以后积累了一定量的其他probe上的数据之后，oracle会把这些数据批量的写入到 磁盘，这就是图上的dump probe partitions to disk。
3. 当我们把probe完整的扫描了一边之后，我们可能已经返回了一部分匹配的数据，但是我们现在在磁盘还有两部分没有处理的数据：build table的hash table的一部分数据和probe table的一部分数据，现在oracle就把这两部分数据重新做一次hash join（这时候会重新比较谁的结果集比较小，因此可能会出现原来的build table变成probe table，原来的probe table变为build table），然后返回最终的查询结果。这就是onepass hash join的大致过程。
multipass hash join模式
最后，如果我们的内存特别小或者相对而言需要hash的数据特别大，hash join就会以最恶劣的方式执行：multipass hash join。如果说onepass是只需要多从磁盘做一次probe table的读取，那么multipass就需要做多次读取，这往往发生在可用内存和数据量相差很大的情况下。multipass hash join是我们需要尽量避免的东西
对HASH JOIN的一次优化：
http://www.cnblogs.com/killkill/archive/2010/07/22/1782889.html
--整理自网络