Oracle Hash Join的代价模型及优化

    从整体上看，Oracle的Hash Join的原理和其它主流数据库的Hash Join的原理大致是一样的，只是Oracle对Hash Join进行了更为细粒度的划分，其中最为主要的就是，Oracle根据其内部的hash_area_size、_hash_multiblock_io_count、db_block_size以及需要构建hash的表（也即我们所说的Hash Join的内表）的大小等值将Hash Join分为三种不同的执行方式：最优Hash Join（Optimal Hash Join），一遍Hash Join（Onepass Hash Join），多遍Hash Join（Multipass Hash Join），本文主要讲解Oracle的上述三种Hash Join的具体执行机制以及Oracle选择每种执行方式的条件。

1.     Parameters

   在讲解Oracle的Hash Join之前，我们首先得了解一下与Oracle Hash Join有关的参数，具体如下：

1)       hash_area_size：Oracle服务器进程在内存中为构建Hash表所分配的存储空间。

2)       _hash_multiblock_io_count：Oracle在构建Hash表的时候，一次能够从磁盘向内存读取数据的块数，在早期的版本中，该值默认为9，但是，现在可以通过Alter Session语句进行动态设置。

3)       db_block_size：Oracle磁盘文件的块大小，默认为8KB。

个人认为与Hash Join有关的核心参数就这三个，当然这三个参数均是在SGA、PGA、UGA等参数的控制之下进行设置的，因此，如果想要详细了解Hash Join的执行的每个细节，建议查阅Oracle的相关资料，熟悉Oracle的这些参数的配置情况，用以在不同的系统环境中进行最为合适的设置，使HashJoin的性能得到最大化的提升。

2.     Oracle Inner and Outer

   在Oracle的Nest Loop连接中，通常将距离Join较近的表称为Outer表，将距离Join较远的表称为Inner表，但是，在Hash Join的时候，并没有Outer和Inner表之分，只有Build和Probe的说法。

1)       Build：也即我们所说的Hash Join中需要构建Hash表的“构建”表，处于Oracle连接计划的外层，距离Join较近。

2)       Probe：也即我们所说的需要对Build中的每个值进行“探测”的表，处于Oracle连接计划的内层，距离Join较远。

   因此，和其他连接计划不同，在Oracle中，我们将Build表称为Inner表，将Probe表称为Outer表。

3.     Hash Join Mechanism

   在了解Oracle的具体是如何实现Hash Join之前，我们有必要大致了解一下Hash Join的实现机制。

   首先，获取一个数据集，利用一个作用于连接键上的内部Hash函数对数据集中的每一条记录计算Hash键值，让后通过该键值将记录定位存储到Hash表的某个桶中，当数据集遍历完成后，Hash表也就构建完成。

   然后，开始获取第二个数据集中的记录，用相同的内部Hash函数作用于该记录的连接键上计算Hash键值，查看在存储器中的Hash表中是否能够定位到一个匹配的记录，如果匹配，在抛出该记录，如果不匹配，这舍弃该记录，继续获取下一条记录，当第二个数据集遍历完成后，也就完成了Hash Join。

   因此，可以看出，由于作用于连接键上的散列函数会使得Hash表中的数据随机分布，因此，只有当连接条件为等值连接的时候，Hash Join方可正常执行。

   由于需要构造Hash表，因此Hash Join的工作方式与第一个数据集的大小以及Oracle的hash_area_size的值有关，Oracle会根据具体的情况，将Hash Join分为三种执行模式：Optimal Hash Join、Onepass Hash Join、Multipass hash Join，接下来，我们就分别了解一下这三种Join的执行前提条件以及具体的执行机制。

4.     Optimal Hash Join

   如果，Build表的数据集很小，可以完全存放到hash_area_size中，那么，此时Oracle会执行最优的Hash Join（Optimal Hash Join），如下如所示：

图表 4‑1 Oracle Optimal Hash Join

执行步骤如下：

1)       构建Hash表：Oracle读取第一个数据集（小表数据集），并在内存中建立一个Hash桶（Hash Bucket）数组，数组中的每一个初始元素作为构建表中行的链表的开始位置。Oracle从小表中读取一条记录，通过作于该条记录的连接键上的内部Hash函数计算出一个Hash键值，该键值即为Hash桶数组的下标，然后根据该下标，定位到相应的Hash桶，存放记录，然后读取下一条记录。Hash桶数组的长度一般是2的偶数次幂。Hash桶数组是一个固定数组和链表所组成的复合数据结构，我们可以简单的将其看成一个矩阵，通过内部的Hash函数，使得构建表的数据随机分布于该矩阵之中。

2)       扫描探测表：在内存中构建完Hash表后，Oracle开始读取第二个数据集（大表数据集），针对读取到的每一条记录，用相同的内部Hash函数作用于连接键上计算出一个Hash键值。

3)       探测：根据第二步得到的Hash键值，看能否定位到内存Hash桶数组中，如果该键值不在Hash桶数组所表示的范围之内，则丢弃扫描到的记录，然后读取下一行，如果该键值在Hash桶数组所表示的范围之内，由于作用于不同值的Hash函数可能会产生相同的Hash键值，因此还需要定位到该Hash桶中，在Hash桶的记录链表上进行精确匹配，如果能够匹配上，则抛出该条记录，如果不能够匹配，则丢弃该条记录，并读取下一条记录，直到读取完第二个数据集中的所有数据。

从上面的执行流程中可以看出，如果在所有的桶中最多都只有一条记录，或者所有桶中的记录数很少，那么在进行探测的时候，就会减少大量的精确匹配，连接的效率也就会高很多，因此，一旦Hash Join开始执行，Oracle就会从hash_area_size中申请大量的空间（默认情况下占用hash_area_size的80%）用于创建大量的Hash桶，尽量将每个桶的冲突链减小到最少。

5.     Onepass Hash Join

   在大多数情况下，Optimal Hash Join太过理想，因为它要求整个Build所建立的Hash桶数组能够被存放于内存中，但是，如果hash_area_size比较小，Build所建立的Hash桶数组只能有一部分存放于内存中，而另一部分必须要存放于磁盘中，这种情况Oracle会如何处理呢？这时候，如果hash_area_size不是特别的小（至于什么情况是特别的小，我们讲解了MultipassHashJoin你就知道了），那么Oracle会选择一种被称为Onepass Hash Join的方式进行Hash连接。Onepass Hash Join的工作机制如下图所示：

图表 5‑1 Oracle Onepass Hash Join

   从上图可以看出，在Onepass Hash Join的模式下，由于Hash桶数组比较大，Oracle不得不将Hash桶数组中的部分Bucket数组转储到磁盘（图中步骤2:Dump build partitionsto disk），然后再重新读入。与此同时，Probe表中某些未匹配上的记录还不能够丢弃，也必须转储到磁盘上（图中步骤4c:Dump probe partitions to disk），然后重新读入，和剩下的Bucket数组进行匹配。

   注意对比图表5-1和图表4-1，为了解释最为原始的Hash Join，我们可以看出图表4-1中省略了一个位图（Bitmap），位图的概念是在Oracle后来的版本中引入的，在Oracle中该位图被称为Bloom Filter，该位图中的每一个位表示Hash表中的每一桶。当某个构建行被Hash到某一个特定的桶时，就在位图中设置该桶所对应的位。

   所以，不难理解，图表4-1中也应该有个位图，该位图中的相应位是在为Build表构建Hash桶数组的时候被标记的，在执行探测的时候，首先查看该行记录在位图中是否有标记，如果有，再到内存中的Hash桶数组中进行精确匹配，否则直接抛弃该条记录。当然，等到此时才来解释Optimal Hash Join的位图从某种程度看来显得比较晚，但是为了使得展示最为原始的Hash Join的工作机制变得简单，这种做法也就没有什么不妥之处了。

   在Onepass Hash Join的过程中，除Bitmap位图占用的空间之外，hash_area_size其余的空间被分解为chunk块（又称为簇或者槽，clusters or slots），这些簇的大小是由db_block_size和参数_hash_multiblock_io_count指定的，其中_hash_multiblock_io_count在早期的Oracle版本中的默认值为9，但是，现在可以在每个查询优化的时候进行动态的设置。簇的大小通过以下公式进行计算：

   从上图可以看出，被分解的一部分簇用于创建Hash表，而这些簇所占用的内存又被分为4个独立的分区（partitions）（至于为什么被分成4个独立的分区，而不是8个，或者16个，对于分区数字的选择机制，Oracle没有明确的说明，但就我个人看来，Oracle极有可能会根据统计信息中Build表的大小，以及Build表的Distinct值的数目，以及hash_area_size中能够用于创建Hash表的空间大小来大致估算出一个分区数目，在原则上满足每个分区的数据均匀分布，每个分区所出现的Distinct值大致相同，而且单个分区能够完全存放进hash_area_size用于构建Hash表的空间中，因此，图中的4个分区，仅仅是一个假设值，为了便于理解，在实际应用中，它不一定就是4，有可能是其它值），分区的数目一般是2的幂次数，除了这些分区所占用的空间外，剩下的内存簇主要用于满足向磁盘转储Hash表时预期的I/O要求（比如，图中右下侧的Hash分区显示，该分区中的一个簇即将要转储到磁盘上）。

   这里值得一提的是，在上图中，我们将各个分区都划分为块数相同的整齐小方格，目的是为了直观的表示出Hash桶在Hash表中的“逻辑标识”。在任何实际，构成每个分区的内存块数目可能不同。

究竟什么是“分区”

有人可能对分区难以理解，在Oracle中，“分区”表示的概念后很多，我们可以这样去理解Oracle Onepass Hash Join中的分区：假设Oracle的内存足够大，因此Build表能够被完全存放于内存中，那么我们就可以为Build表构建一个完整的，没有分区概念（或者说只有一个分区）的Hash桶数组，我们假设这个Hash桶数组就如实的放在了内存里面了，因此，我们就应该知道这个Hash桶数组的长度，每一个桶里面所存储的记录数目以及每个桶中记录所占用的空间，但这只是我们假设的情况，是一个逻辑上的假想，实际上，我们只能够一次将这个Hash桶数组的一部分存放于hash_area_size中用于构建Hash表的空间中，因此，我们就只能够根据其每个桶中所存储的记录占用的空间大小将这个完整的Hash桶数组分成若干区域，区域数通常是2的幂次数，我们将这些区域就称为“分区”，因此，为了满足数据在量上面的均匀分区，每个区域所包含的Hash桶数目可能会不同（比如，整个Hash表有8个桶，每个桶的冲突连长度分别为3、3、3、2、1、0、3、3，而每次只能够向内存中最多存放9条记录，因此，Oracle就可以将整个Hash桶数组分成2个分区，第一个分区拥有Hash桶数组中的前面3个桶所包含的记录，而第二个分区拥有Hash桶数组中后面5个桶所包含的记录，因此第一个分区会占用3个Bucket入口，第二个分区会占用5个Bucked入口，虽然Hash桶数组中的第六个Bucket中并没有任何数据，但它也要占用一个Bucked入口空间），只不过，我们通过这种假象所得到的分区数目，Oracle是通过一定的上下文环境（就像我前面所猜想的，通过统计信息等数据）得到的，那么，现在就应该理解，为什么在实际情况下，构成每个分区的内存块数目可能不同了。

    Oracle的Onepass Hash Join的执行步骤如下：

1)       获取Build表中的数据集，并将其分布到散列表中，一旦某个桶被使用，则设置该桶在位图中的对应位。

2)       随着内存的使用，簇被转换到磁盘上。Oracle在转储簇时会采用一种谨慎的策略，试图将尽可能多得完整分区在内存中保留尽可能长的时间（通过后面的阅读，我们能够猜想得到，Oracle在转储的时候，不会以Hash表这种数据结构作为转储单位，而是转储Hash桶中的记录）。当Build表处理完毕后，有可能，一些分区仍然完整的保留在内存中，而其他分区只有一些（但只至少有一个）簇保留在内存中。也有可能是某个分区的一些簇在内存中，而其他每个分区只有一个簇在内存中。但无论结果如何，Oracle都会维护一个详细的映像，以便找到每个分区中的数据。并且，当使用某个Hash桶时，无论该Hash桶是在内存中还是被转储到了磁盘上，位图中对应的位都会被设置，位图总是完整的保留在内存中。当Oracle将Build表的数据处理完毕后，根据每个分区中数据大大小以及考虑到可能使用到的I/O代价，Oracle会做出一种决策，使用最小的I/O将尽可能多的完整分区装载到内存，将其余分区中超出的部分转储到磁盘上。作为重建的一部分，Oracle会为每个分区至少保留一个簇在内存中，用于处理探测表的记录。

3)       获取Probe表中的数据集，在获取到的每一条记录的连接键上应用相同的Hash函数（个人猜想，此处的Hash函数有可能是多批，第一批Hash函数确定记录的分区，第二批Hash函数定位分区中的桶），Hash函数的结果用于检查位图中的相关位。针对位图的不同结果，Oracle有下列几种可能的操作：

a)        如果对应位被清除，表明没有匹配行，直接丢弃，并获取下一条记录。

b)       如果对应位被设置，而且相关Hash桶所在的分区在内存中，则定位到该Hash桶中进行精确匹配，如果匹配成功，则抛出，否则，直接丢弃，并获取下一条记录。

c)        如果对应位被设置，但是相关Hash桶所在的分区在磁盘上，则将该探测记录暂时搁置，因为该记录有可能匹配了磁盘上某个Hash桶分区的一个Build记录，但此时，如果为了探测该行记录而重新加载相关的分区，这种方式的成本太大。

d)       暂时搁置的探测行被收集到与先前转储到磁盘的Hash桶分区匹配的集合中，比如，按照上图所示，我们的Hash桶数组被分成4个分区，我么假设编号分别为1、2、3、4，当前假设第3个分区在内存中，此时Oracle会从剩余簇中选择出三个簇，建立三个探测记录缓存区，他们的编号分别是1、2、4，编号为1的缓存区缓存可能和磁盘上编号为1的Hash桶数组分区相匹配的探测记录，而编号为2的缓存区缓存可能和磁盘上编号为2的Hash桶数组分区相匹配的探测记录，以此类推。由于编号为3的Hash桶数组分区已经在内存中，可以直接进行匹配，因此，对于可能和编号为3的Hash桶数组相匹配的探测记录就没有必要缓存了，匹配则直接抛出，不匹配则丢弃即可。当某个簇写满后，我们不会立即将该簇的内容转储到磁盘上，Oracle此时会查看是否还有剩余的簇可用，如果有，则用该簇继续转储探测到的记录，并标记该簇的分区号，使之和磁盘中的Hash桶数组的分区号相对应，如果没有，则将所有簇的探测记录转储至磁盘。   

4)       当处理完探测表的最后一条记录后，就完成了对探测表的一次物理扫描，此时，我们可以在磁盘上得到Build表和Probe表中记录可能匹配的分区对（如上图所示，磁盘上剩下3个分区对），同时，Oracle也就获取关于全部数据的位置以及磁盘上的每个分区的行数的完整信息，因此，Oracle会选取一对转储分区（一个Buid表分区，一个Probe表分区），并对他们执行Optimal Hash Join，值得注意的是，此时Oracle会有一个细微的优化：由于知道了每个分区的记录数目和每个分区中的记录所占用的空间大小，Oracle会会尽可能在分区对中选择一个数据小、冲突链少的分区作为新的Build分区，而将分区对中的另一个分区作为新的Probe分区，我们将这个过程成为角色互换。

5)       待磁盘上的各个分区对都完成了Optimal Hash Join后，Oracle也就完成了OnepassHash Join。

    Oracle的这种Hash Join仅仅会重新从磁盘读取一遍探测表的数据集，因此这类连接匹配就被称为一遍工作区执行（onepassworkarea execution）。

   通过上面的讲述，我们可以看出，对于大容量的Hash Join，Build表的数据可能会向磁盘转储，对应的Probe表中数据也会向磁盘进行转储，因此，我们在计算Onepass Hash Join的代价时，就不得不考虑这部分磁盘转储以及后续重新读取这部分转储的数据所消耗的I/O量、I/O所采取的形式以及优化器衡量每一次I/O所消耗的代价的机制。

   可能有些最坏的情况，此时，Oracle需要从Build和Probe表中读取数据，并将大部分数据转储至磁盘，然后重新读取，因此，其Join的成本可能会上升至OptimalHash Join的三倍（因为读、写、重新读取了几乎全部的数据量）。当然，在代价计算的过程中，我们还需要考虑一个事实：转储并重新读取分区时的I/O操作的效率应该比最初收集数据时的I/O操作的效率要高；与此同时，我们没有必要转储每一行数据，因为最初从探测表上读取数据时，某些记录已经被处理（就像上图所示，在将探测表扫描完毕后，就已经处理完了一个分区对，只剩下3个分区对了）。

6.     Multipass Hash Join

   在了解了Onepass Hash Join之后，接下来，我们看看Oracle的另一种Hash Join执行模式，Mutlipass Hash Join。顾名思义，我们可以提前猜想，Multipass Hash Join肯定是指探测表的记录需要被重新从磁盘中读取多次。

   在Onepass hash Join的情况下，Orale至少能够将Build的单个分区完全存放到内存中，但是在实际的应用环境中，有那么一种极致的情况，用于构建Hash表的内存确实很小，或者由于优化器的统计信息极其不准确，优化器为Build表所选择的分区数目很不合适，导致内存小到不足以存放Build表单个分区的记录，此时，Oracle就会执行Mutlipass Hash Join。

   为了便于解释，我们假设Build表的大小为4MB，Probe表的大小为20MB，它们中的数据都是均匀分布的，而hash_area_size的设置使得只有18个块（2个簇，18 * 8 = 144KB）大小的空间用于为Build表构建Hash表，我们又假设，Oracle优化器计划将Build表分成4个分区（取决于Build表中的数据分布情况，划分出的每个分区在磁盘上的物理大小大约在1MB左右，我们就假设如果将每个分区的数据转储到磁盘后，每个分区所占用的平均空间大小就为1MB）。

   此时，Oracle有可能会选取内存中的16个块用于为Build表构建Hash表，并将其分为4个分区，每个分区4个块（4 * 8 = 32KB），Oracle会保证，至少剩下一个块（本用例中其实还剩下2个块）用于Build表的I/O，如下图所示：

图表 6‑1 Oracle Multipass Hash Join

       由于Oracle的运行时引擎知道，在将Build表分成4个分区的时候，也必须将Probe表分成4个分区，并在探测的时候，将某些搁置的记录转储到磁盘上，因此，运行时引擎必须在内存中为每一个Probe分区提供至少一个块，用于处理Probe表的I/O读取。

Oracle Multipass Hash Join的执行步骤如下：

1)       获取Build表中的数据集，将其定位到内存中Build表的一个分区的一个Hash桶中，同时设置该桶在位图中的对应位。

2)       随着内存的使用，当内存中Build表的某个分区的某个块写满后，该块的数据被转储到磁盘上Build表对应的分区中。

3)       当Build表中的记录扫描完成后，磁盘上会有4个Build表分区，每个分区在1MB左右，此时Oracle会整理一下内存，使得只有Build表的第一个分区中的部分块的数据被保留在内存中。

4)       获取Probe表中的数据集，与Onepass Hash Join一样，如果位图表明无匹配行，则丢弃该行，如果有匹配，而且匹配行恰好在内存中，则将之与内存中的Build表记录进行匹配（如果有，则抛出，如果没有，则丢弃），如果匹配行没有在内存中，则将该Probe记录搁置于用于缓存Probe表中的对应分区缓存块中，当对应分区的缓存块被写满后，则将该缓存块转储至磁盘的对应Probe表分区。

5)       在第一次遍历完成Probe表后，对于能够与在内存中的Build表记录相匹配的记录或者位图表明不存在匹配项的记录就已经被处理过了，此时，磁盘上就会存在4个Probe表分区，同时还有4个Build表分区（不过第一个Build表分区中的某些块已经在第一次遍历Probe表时被处理了）。此时，Oracle就会对每一个分区对进行Hash Join，但是每个Build表的分区大小约为1MB，而可用的内存每次只能够重新读取少量的块用于构建Hash桶数组，因为我们有18个块（144KB）可用，而且我们已经知道将要进行连接的分区存在匹配项，因此Oracle会做有一些控制的余地：可以用1个块用于为Build表分区构建Hash桶数组，而用17个块存放Probe表的对应分区的部分块数据，或者2个块用于Build表分区，16个块用于Probe表分区，以此类推；用于Build表分区的块数越多，用于Probe表分区的块数就越少，用于Build表分区的块数越少，用于Probe表分区的块数就会越多，但是，这样一来，Probe表的重复读取次数就会越多，因此，Oracle会根据各个Build分区和对应的Probe分区中数据的大小来进行一个权衡，使得用较少的重复次数，较低的I/O量就能够完成对该分区对的Hash Join，我们假设Oracle针对第一个分区对选择用8个块为Build表分区构建Hash桶数组，而用10个块读取Probe表分区中的记录，由于在第一次遍历的时候，Build表第一个分区的部分数据已经被处理过，因此Oracle只需要从该分区中剩下的数据块中读取8个块到内存中构建Hash桶数组即可，等到第一个分区对Join完毕，Oracle再继续处理下一个分区对，每个分区对的Join过程如下，为了便于说明，我们假设Oracle每次都选择用8个块为Build表分区构建Hash桶数组，用10个块读取Probe表分区中的记录：

a)        Oracle读取分区对中Build表分区中的8个块到内存并构建Hash桶数组。

b)       扫描分区对中的Probe表分区的全部记录，针对每一条记录执行可能的连接匹配，如果能够与内存中现有的Hash桶数组中某个桶的一条匹配上，则抛出连接结果，如果不能够匹配，则处理Probe表分区的下一条记录，直到处理完Probe表分区中的所有记录。

c)        在Build表分区中再依次读取8个块，并为之构建Hash桶数组，然后重新读取Probe表分区的所有记录，并按照上述步骤进行连接匹配。

d)       当Build表分区的中的所有记录都被处理完成后，也就完成了对该Build/Probe分区对的连接匹配，此时就需要按照相似的步骤去处理磁盘上的下一个分区对了。

   通过上面的步骤描述，我们可以知道，Probe表的每一个分区的全部数据都需要多次重新读取和遍历，才能够完成一个分区对的连接匹配，也正因如此，这个连接匹配方式被称为多遍工作区执行（Multipassworkarea execution），也成为多遍散列连接（MultipassHash Join）。

   同时，我们还可以看出，这种连接的I/O开销往往很大，就我们的例子，我们可以算出，每一个Probe表分区平均会被重复读取 1024/(8 * 8) = 16次，加上第一次的Probe表扫描以及一次转储，针对Probe表的I/O就有18次之多，算上Build表的I/O操作，因此Multipass Hash Join的效率是非常低的，因此优化器在估算这类Hash Join的时候，I/O代价的估算就成为了必不可少的环节。

7.     我对Hash Join的优化

   以下这些优化仅仅代表个人的观点，如有任何不妥或者有任何不同的意见，欢迎读者指正，本人感激不尽！

7.1Multipass Hash Join的优化

   从上节的分析可以看出，Multipass Hash Join在执行连接匹配的时候，针对Probe表的任何一个分区都要进行多次扫描，在Probe表较大的时候，这种扫描方式就会大大加重I/O负担，但是，其中各个分区中的大部分记录是没有必要进行扫描的，那么，能否在Multipass Hash Join的基础上作进一步的优化，减少Probe表的I/O次数，从而提升Hash Join的效率呢。

   为了便于说明优化的原理，我们不防利用上节的数据环境，Build表4MB大小，Probe表20MB大小，hash_area_size仅仅只有18个块，2个簇，144KB大小，我们同样假设，为Build表构建Hash桶数组的时候，Oracle将利用hash_area_size中的16个块，128KB大小，并将这16个块均分4个分区，每个分区占用4个块，32KB大小，剩余的2个块中，Oracle至少使用1个块用于处理Build表的I/O读取。当然，为什么将内存块分成4个分区，是因为优化器计划将Build表和Probe表划分为4个分区对，通过每一个分区对之间的Hash Join来完成Build表和Probe表之间的Hash Join。

   其实，我们可以采用一种递归的分裂策略来对Multipass Hash Join进行适当的优化，具体的优化策略是：在对Probe表的第一遍扫描完成后，Oracle肯定搜集到了每个分区对中的数据信息，因此，在第一次重新读取Probe表的时候，我们就可以将我们正在处理的分区对再进行划分，将它们划分为更小的子分区，这样，该分区对中就会有若干个子分区对，接下来，我们依次让该分区对中的各个子分区对进行连接匹配，当该分区对中的每个子分区对全部完成了连接匹配后，该分区对也就完成了连接匹配，接下来，我们按照相同的方式处理下一个分区对即可，如下图所示：

图表 7‑1递归的Multipass Hash Join

       递归的Multipass Hash Join的执行步骤如下：

1)       首先按照Multipass Hash Join的前4步对Build表和Probe表进行分区，在第一次遍历完成Probe表后，磁盘上就会存在4个Probe表分区记录，由于本属于Probe表的第一个分区中的某些记录记录已经在第一个遍历Probe表的时候被处理过了，因此这些记录就不会被写到Probe表的第一个分区中了。同时还有4个Build表分区，与Probe表第一个分区不同，Build表第一个分区中的某些块虽然已经在第一次遍历Probe表时被处理了，但是它们仍然存在于磁盘上。因此，此时磁盘上的每个Build分区大约为1MB左右，而除了Probe表第一个分区可能较小以外，其它每个Probe表分区大约在5MB左右，为了方便解释，我们假设此时Probe表的每个分区都在5MB左右。

2)       现在分别处理每一个分区对。我们假设目前我们需要处理第一个分区对（后续分区对可以按照类似的步骤进行处理），第一个分区对中，Build表分区大约1MB的空间，Probe表分区大约5MB的空间，此时，我们可以将Build表分区和Probe表分区当成数据量为1MB和5MB的伪表，我们的工作就是在这两张伪表之间进行匹配连接，而我们能够使用的hash_area_size仅仅只有18个块，2个簇，144KB，因此，我们可以根据其数据的分布情况按照Multipass Hash Join的前4个步骤对伪Build表和伪Probe表进行分区，我们假设引擎将此伪表对分成了4个区（其他伪表对有可能分成2个或者8个等分区数，为了便于解释，我们假设将所有的伪表对都分成了4个分区），也就是说，我们先扫描伪Build表，将其分成4个区，那么分区后，伪Build表的每个分区的数据量约为256KB左右，此时，我们加载伪Build表的第一个分区中的部分块到内存中，并构建Hash桶数组，然后获取为Probe表中的数据，如果伪Build表的位图表明，能够在内存中匹配，则匹配该记录，不存在匹配的，则丢弃该记录，可能匹配伪Build表在磁盘中的分区数据，则将该行暂搁于伪Probe表对应分区的缓存块中，待某个缓存块写满后，将其转储至磁盘上伪Probe表的对应分区中。

3)       等到第一次遍历完伪Probe表后，我们就将该伪表对分成了如上图所示的4个分区对，此时，从物理上看，我们将原Build表和原Probe表分成了4个物理分区对，然后将第一个分区对又分成了4个子分区对，此时，我们大致可以猜测，Build表第一个分区的每个子分区的数据量大约为256KB，Probe表第一个分区的每个子分区的数据量大约为1.25MB，因此，我们仅仅完成这些子分区对的匹配连接即可完成子分区对所对应的父分区对的匹配连接，此时，针对子分区对的连接匹配，可以有如下两种处理方式：

a)        如果子分区对中的某一个可以装入hash_area_size中，那么就可以为该子分区对执行Optimal Hash Join，显然我们现在的数据环境是不能够用Optimal Hash Join来进行子分区对的匹配连接的。

b)       如果子分区对中的任何一个都不能够装入hash_area_size中，那么就读取子分区对中Build表子分区的部分块（至于究竟读取多少块到内存中，可以参见Multipass Hash Join第5步中的说明，此处，我们也假设8个块用于构建Hash桶数组，剩下的10个块用于处理Probe表的I/O读取）到内存中并为之构建Hash桶数组，然后读取子分区对中Probe子分区中的一条记录，如果子分区对中Build表子分区对应的父分区的位图（在步骤2中扫描Build表的某个分区时建立的，一直保存在内存中，直到该分区对处理完毕）表明，能够在内存中匹配，则匹配该条记录，如果不存在匹配，在抛弃该条记录，如果可能和子分区在磁盘上的记录相匹配，则跳过该条记录，直接获取下一条记录进行连接匹配。当扫描完子分区对中Probe表子分区的记录后，我们接着读取子分区对中Build表子分区剩下数据的部分块到内存中，并构建Hash桶数组，然后再按照上面的模式，重新扫描子分区对中Probe表子分区中的每一条记录，并与内存中的Hash桶数组进行匹配，如此往复，等到子分区对中Build表子分区的所有数据都已经处理完毕后，该子分区对也就完成了连接匹配，接下来，我们按照相同的模式，继续处理子分区对对应的父分区对的下一个子分区对的连接匹配。

4)       等到第一个分区对中的所有子分区对都已经处理完毕后，我们对剩下的分区对重复步骤2和3，对他们进行连接匹配。

5)       待所有分区对处理完毕后，我们也就完成了Build表和Probe表的连接匹配。

   通过上面的分析，我们可以看出，经过优化后的Multipass HashJoin确实减少了分区对中Probe表分区中的大部分数据的重复I/O，在我们的用例中，我们大致可以计算一下，经过优化后，Multipass Hash Join能够减少的I/O量，具体如下：

   优化Multipass Hash Join之前的I/O量计算流程：

1)       在各个分区之间进行连接匹配的时候，使用8个块为Build表分区构建Hash桶数组（剩下10个块作为读取Probe表对应分区数据的缓存），每一个Build表分区大约为1MB左右，因此需要为Build表的每个分区构建 次Hash桶数组，因此，针对每一个分区对的连接匹配，Probe表的每个分区需要重复完全读取16次，其I/O量为，Probe表需要一次完整的扫描和一次完整的转储，以用于分区，因此Probe表的I/O量为：。

2)       针对Build表，需要完整读取一次，完整转储一次，然后再完整读取一次，因此其I/O量为：。

3)       因此，未优化的Multipass Hash Join的I/O量大致为：。

   优化Multipass Hash Join之后的I/O量计算流程：

1)       对Probe进行分区，需要扫描Probe一次，转储Probe一次，此过程，Probe表的I/O量为:。

2)       分裂后，Probe表的每个分区的大小为5MB，总共有4个分区，每个分区中的5MB需要再分裂为4个子分区，在分裂为子分区的过程中，每个分区需要扫描一次，然后转储一次，同时，在分裂后，每个Build表分区的子分区大小为256KB，每个Probe表分区的子分区大小为1.25MB，子分区对之间的连接匹配使用8个块为Build子分区构建Hash桶数组，于是每个Build子分区需要构建 次Hash桶数组，因此，针对子分区对中的每一个Probe子分区，它需要完整的读取4次，其I/O量为，由于每个Probe分区被分裂为4个子分区，那么，每一个分区对中的Probe表分区的I/O量为：。

3)       因此，针对Probe表，它的每一个分区，扫描一次，转储一次，然后在子分区对进行连接匹配，最后的单个分区的I/O量为:，那么整个Probe表的I/O量为：。

4)       针对Build表，扫描一次，转储一次，完成第一次分区，然后针对每个子分区扫描一次，转储一下，完成第二次分区，最后针对每个子分区完成一次扫描，那么不难算出Build表的I/O量为：。

5)       因此，优化后的Multipass Hash Join的I/O量为：。

   通过两者之间的对比，我们不难看出，优化后的Mulitpass HashJoin比优化前的Multipass Hash Join的I/O量减少了：，因此，从理论上来讲，能够通过一次简单的优化，将I/O量减少一半多，说明，这个优化过程还是值得去考虑的。

   细心的读者可能会有以下三点疑问：

1)       上述步骤3中的第2个子步骤中，在处理各个分区对的每个子分区对的连接匹配的时候，如果子分区对中的任何一个都不能够放到内存中进行Optimal Hash Join，那么为什么不将子分区对再进行递归分分裂，直到子分区对的各个子分区对都能够进行Optimal Hash Join呢？

2)       既然优化后的Multipass Hash Join需要将分区再进行一次分区，那么我们为何不统一在第一次分区的时候将其划分为更多更细的分区呢，比如我们上面的例子，我们首先将Build表和Probe表划分成4个分区对，然后再将每个分区对划分为4个子分区对，那么我们为什么不一次性的将Build表和Probe表划分为个分区对呢？

3)       针对Multipass Hash Join优化的问题，既然我们能够想到，Oracle的研究员们也肯定能够想到，那Oracle最后为什么不这么做，甚至为什么Oracle不对看上去效率如此龌龊的Multipass Hash Join按照我上面所讲的步骤进行优化呢？

   针对读者的上述疑虑，本人在研究Oracle的Multipass Hash Join并提出对之进行适当的优化策略的时候，也有类似的疑虑，既然我们能够想到这些问题，那么Oracle的研究员们也肯定会注意到这些问题，我想，Oracle的研究员们可能是基于如下几点考虑，才决定不再对Multipass Hash Join作过多的优化：

1)       首先，如果对子分区对继续进行递归的分裂，我们会读取Build表，然后将Build表以分区为单位对磁盘进行一次转储，对其重新读取每个分区，并以子分区为单位对磁盘进行一次转储，然后重新读取每个子分区以完成连接匹配，与此同时，我们也读取了探查表，以分区为单位对它进行了转储，然后重新读取每个分区，并以子分区为单位进行转储，然后重新读取子分区以完成连接匹配，抛开这种多层次递归所带来的代码复杂度不说，这种递归的多层次分裂必须应用到每个子分区，子分区的子分区，以此类推，并且，每次递归进入时，这种分区对之间的映射和其它开销可能会消耗掉更大的内存，而Oracle只是在极度缺少内存的时候才执行Multipass Hash Join，因此递归的分裂在实际上有可能根本就行不通。

2)       其次，在我们的例子中，不论是在对Build表构建Hash桶数组还是在对Probe表进行分区，如果我们需要将某条记录转储到磁盘，为了最多程度的降低磁盘的I/O次数，我们是先将其缓存到预留的一个I/O块中，等到该块被写满后，我们再将其以块为单位转储到磁盘上的对应分区中，因此，如果我们一次性的将Build表和Probe表分裂成16个分区的话，那么，我们在分裂的时候，就必须为Probe表的每一个分区至少保留一个块用于处理其I/O读取，因此，我们如果一次分裂成16个分区，那么我们就必须预留 (16代表需要为Probe表的每个分区中的记录缓存预留一个块；第一个1代表在读取Build表中的每一条记录时候，先将其读取到缓存块，然后进行Hash计算，然后在将其定位并拷贝到对应的Hash桶数组中；另一个1代表在处理Probe表中的每一条记录的时，先将其读取到缓存块，然后再进行匹配，对于匹配的行，直接抛出，对于不匹配的行，直接丢弃，对于有可能匹配的行，再将其定位到Probe表对应的分区缓存块中）的内存容量来进行Build表和Probe表的I/O缓存，显然，对于内存本身就非常小（我们的hash_area_size仅仅只有144KB），而且我们通常仅仅使用hash_area_size的80%，这种一次性分裂由于会导致大量的缓存的使用就会变得非常的不合时宜。

3)       最后，既然不能够多次递归的进行分裂，又不能一次性的分裂为较多的分区，那为什么不将Multipass Hash Join优化成按照我们所说的，仅仅执行“一层的递归分裂”，以此来减少对分区对的Probe表分区大部分数据的重复扫描。我想，一方面是因为Multipass Hash Join的代价估算模型本身就比较复杂，如果再将其进行递归分裂一次，那么其代价估算模型就有可能会更加复杂，而且这一次的递归分裂的执行，必须要Oracle将Build表和Probe表进行分区后，根据各个分区对中的数据动态的决定是否需要将分区对再进行分裂，也就是说，只有等到具体执行的时候，才能够知道是否需要对分区对再进行分裂，因此，优化器很难估算出执行后的代价模型，为了保证优化器和执行器在某种程度上的统一以及尽可能用较低的代码复杂度来实现较多得功能需求，Oracle放弃了对该MultipassHash Join的进一步优化。而另一方面，由于Multipass Hash Join只有可能出现在一些特殊的大型数据的处理中，随着单字节容量硬件价格的不断下降，如果涉及到大型的数据处理，Oracle情愿将更多的成本投入到扩大内存中，而不是选择用代码来实现比较复杂的逻辑并为之承担可能出现的不可估量的后果，如果在内存增加到适当的程度后，Oracle还是MultipassHash Join的连接方式，那么Oracle就要求应用应该更多的去考虑改变连接机制（比如，建表的时候以连接键为分区键，将表在物理上就建立称为分区表，然后让Oracle在进行Hash Join的时候选择Full Partition-Wise Join）或者重新设计SQL语句了。

因此，基于上述几点，如果我是Oracle的研究员，我也会选择放弃对Multipass Hash Join的进一步优化。

7.2Bitmap优化

在提出Hash Join后，Oracle又在Hash Join的基础之上提出了Bloom Filter，也就是我们前面所看到的Bitmap，Oracle在为Build表构建Hash桶数组的同时，会填充这个Bitmap数组，但是，究竟是如何填充的，我们不得而知，因此，我仅仅根据自己的个人猜想，来提出自己对Bitmap的一种优化，当然，如果Oracle确实按照我所讲的来进行的，那就当我没有做这个Bitmap优化吧。

一般情况下，我们得到一条记录，通过一个Hash函数计算出一个Key，然后标记Bitmap数组中的第Key位，这样，在扫面完Build表后，我们就得到了一个被标记的Bitmap数组，其中某些位有可能未被标记，某些位有可能被重复的标记了，因为不同的值通过同一个Hash函数计算出的Key有可能相同。

接下来，我们从Probe表中取出一条记录，用同样的Hash函数计算出一个Key，然后查看Bitmap数组中的第Key为是否被标记了，如果未被标记，则说明该条记录不存在匹配的项，丢地即可，如果被标记了，则说明该条记录有可能存在匹配项，则需要进行相关的匹配逻辑。

因此，通过一个简单的Bitmap数组，在进行实际的HashJoin之前，就能够过滤掉一些数据，提升Hash Join的连接效率。

但是，我们也发现了一个问题，上述在构建Bitmap数组的时候，针对每一个记录，仅仅通过一个Hash函数计算出一个Key，这样虽然能够过滤掉部分的记录，但是记录的过滤率却有可能很低的，我们举个例子来加以说明，具体如下：

假设Build表中的某一条记录，里面的存储了一个整形数据，其值为1，我们通过Hash函数，作用于该值上，计算出的Key为20，于是我们标记Bitmap的第20位，而且，我们假设，Build表中的另一条记录，其存储的值为4，我们通过Hash函数，作用于该上，计算出的Key仍然为20，于是我们再一次的标记Bitmap的第20位，我们假设，Build表中，通过Hash函数得出的Key为20的记录就只有这两条，因此值1和值4位于Hash桶数组中的同一个桶中，该桶的冲突链长度为2。

假设，Probe表中的某些记录中存储了值2,4,6,8,10,12,14，而这些值，通过相同的Hash函数计算出的Key值都为20，那么，当我们遍历到这些记录的时候，我们发现，Bitmap中的第20位被标记了，于是，我们需要对这些记录中的每一条进行Hash匹配，但是，我们能够匹配上的数据只有存储值为2和4的两条记录，其余的记录均匹配不上，也就是说，我们进行了多余的7次匹配（6,8,10,12,14），那么，能否有一种办法，能够尽量降低这种匹配的次数，提升HashJoin的匹配效率呢，从另一个角度来看，不论实际执行结果如何，至少从数学概率上，它能够在Bitmap阶段尽可能多的过滤掉不存在匹配的项。

在这种需求的驱使下，我们可以按照如下步骤来进行Bitmap过滤：

1)       从Build表中取出一条记录，通过8个（也可以选择4个或者更多个，但是从实际效果来看，8个就已经足够了）内部实现均不是相同的Hash函数分别作用于该记录的连接键上，得到8个不同的Key，然后通过一个定位函数将这8个Key分别定位到Bitmap数组中，并标记对应的位，最后通过某个接受8个参数，并输出一个定位值的定位函数，将读取到的记录定位到Hash桶数组中的某个桶中，等到Build表中记录扫描完成后，我们就能够得到一个Bitmap数组和Hash桶数组。

2)       从Probe表中获取一条记录，通过同样的8个Hash函数分别作用于该记录的连接键上，得到8个不同的Key，然后通过一个定位函数将这8个Key分别定位到Bitmap数组中，查看数组中对应的位是否被标记，如果其中某个Key对应的位未标记，那么丢弃该条记录，并获取下一条记录，如果所有Key对应的位都被标记，那么再通过定位函数将读取到的记录定位到Hash桶数组中进行精确匹配即可。

从概率论的角度来讲，通过8个不同内部实现的Hash函数计算出的8个Key值都分别对应相等的两个值不同的概率是非常低的，因此，通过此种方式，在Bitmap阶段就能最大程度的过滤掉哪些不存在匹配的记录，提高过滤效率。

8.     Cost

   自从Oracle提出了代价优化模型后，Oracle就会对任何一个执行计划赋予代价的概念，在基于CBO的优化模型下，每一个执行计划都会有自身的代价，Oracle就是通过这些计划的代价竞争，尽可能从众多的计划中选择出代价最小，执行效率最高的计划。

    Oracle并没有说明每种计划的代价估算模型，不过，就个人观点而言，Oracle能够通过某种方式（比如，从统计信息中获取信息），提取每个计划的信息，估算它们在执行的时候可能消耗的I/O量以及可能消耗的CPU资源，因此，本文就从计划对I/O和CPU资源的消耗上来简单探讨一下Oracle的代价模型。

8.1Optimal Hash Join Cost

Optimal Hash Join是全内存操作，因此其代价模型比较简单，具体如下:

如果，忽略CPU资源的消耗，则Optimal Hash Join的代价计算模型如下所示：

8.2Onepass Hash Join Cost

在Onepass Hash Join中，Build表和Probe表都需要读取、转储然后再重新读，因此其代价模型稍微有些复杂，具体如下：

       如果忽略CPU资源的消耗，则Onepass Hash Join的代价计算模型如下所示：

   

8.3Multipass Hash Join Cost

Multipass HashJoin需要将Probe表重复读取多次，因此其代价模型就更为复杂。

8.3.1      未优化的Multipass Hash Join

未优化的Multipass Hash Join的代价模型如下所示：

如果忽略CPU资源的消耗，则未优化的Multipass Hash Join的代价计算模型如下所示：

假设，我们Read或者Write所消耗的I/O代价为Read或者Write的I/O量，那么，按照上面的公式，在本文的数据环境中，未优化的Multipass Hash Join在忽略CPU资源消耗后的代价为：

这和我们之前所计算出的I/O量372MB是一样的。

8.3.2      优化后的Multipass Hash Join

优化后的Multipass Hash Join的代价模型如下所示：

为了便于说明，我们将优化后的Multipass Hash Join的代价模型分为两个部分Cost(Build)和Cost(Probe),如下所示：

1) 首先，计算Cost(Build):

简化上面的公式，得到最终Cost（Build）的计算公式：

2) 其次，计算Cost(Probe):

简化上面的公式：

继续简化，得到最终的Cost（probe）计算公式：

3) 将上面Cost(Build)与Cost(Probe)简化后的公式相加，即可得到Cost(HJ)的代码估算模型，如下所示：

         如果忽略CPU资源的消耗，则优化后的Multipass Hash Join的代价计算模型如下所示：

同样，假设，我们Read或者Write所消耗的I/O代价为Read或者Write的I/O量，那么，按照上面的公式，在本文的数据环境中，优化后的Multipass Hash Join在忽略CPU资源消耗后的代价为：

这和我们之前所计算出的I/O量180MB是一样的。

9.     Build Versus Probe

   读到这里，读者可能会问，既然整篇文章都在讲Hash Join中的Build表和Probe表，那么，Oracle是如何决定哪一张表为Build表，哪一张表为Probe表呢？

   这个问题我也不好回答，但是，通过对实际的Hash Join的计划的观察来看，Oracle通常将返回的数据集较小的表作为Build表，将返回的数据集较大的表作为Probe表，除非在查询语句中使用Hint命令来强制更改Hash Join的表连接顺序。当然，由于Oracle对此没有明确的说明，这仅仅是我个人在阅读Oracle的相关技术文档后得出的猜测性结论，如果读者发现任何不妥之处，还望指正，本人不胜感激。

10.数据分布

   注意，本文假设Build表和Probe表中的数据是分布均匀的，本文是基于此前提条件来进行讲解说明，并进行代价推导，那要是Build表或者Probe表中的数据分布不均匀的时候，本文所讲解的内容还是否适应呢？

   针对这个问题，我个人觉得，任何问题都会有一种极限情况，针对这种极限情况，我们可以根据当前的上下文环境将其转换为一般情况，至于如何转换，方法多种多样，由于篇幅有限，本文不做过多的说明，有兴趣的读者可以下来自己去研究一下（比如，可以尝试去分析一下当Build表或者Probe表中的数据经过分区后，大部分都落在了同一个分区中，其他分区要么没有数据，要么就只有几条数据，这个时候，我们该如何去将这种比较变态的情况转换为一般的情况呢）。

11.小结

1)       在Oracle中，Hash Join没有内外表之分，只有Build表和Probe表之分。

2)     Oracle在执行Hash Join的时候，会根据Build表和Probe表的数据大小、hash_area_size的大小等其它服务器参数将Hash Join分为Optimal 、Onepass、Mulitpass三种执行模式。

3)       Oracle没有明确之处三种执行模式的代价估算方式，本文所得出的代价估算模型仅仅是个人的猜想，有待验证。

4)       可以针对Multipass Hash Join进行适当的优化，可能是出于种种原因，Oracle并没有这么做。

5)       个人猜测，Oracle会尽量将返回数据集较小的表作为Build表，而将返回数据集较大的表作为Probe表，但是就像上面所讲，这仅仅是个人猜想，正确性还有待验证，至于如何验证，对于感兴趣的读者，Oracle运行时诊断可以作为考虑的途径之一。