四、Multipass hash join

1、内存hash表信息

Hash-join fanout: 8
Number of partitions: 8
Number of slots: 14
Multiblock IO: 1
Block size(KB): 8
Cluster (slot) size(KB): 8
Minimum number of bytes per block: 8160
Bit vector memory allocation(KB): 0
Per partition bit vector length(KB): 0
Maximum possible row length: 1077
Estimated build size (KB): 2046
Estimated Build Row Length (includes overhead): 1048
.....................
kxhfWrite: hash-join is spilling to disk

*** RowSrcId: 1 HASH JOIN BUILD HASH TABLE (PHASE 1) ***
Total number of partitions: 8
Number of partitions which could fit in memory: 0
--8个分区中没有一个分区能全部放到内存中

Number of partitions left in memory: 0
--没有一个分区能整个留在内存中

Total number of slots in in-memory partitions: 0
Total number of rows in in-memory partitions: 0
   (used as preliminary number of buckets in hash table)
Estimated max # of build rows that can fit in avail memory: 224

--从以上的初始化信息，我们就很容易判断hash join机制是optimal、onepass还是multipass。

2、分区表信息
### Partition Distribution ###
Partition:0    rows:262        clusters:34     slots:1      kept=0
Partition:1    rows:269        clusters:35     slots:1      kept=0
Partition:2    rows:245        clusters:32     slots:1      kept=0
Partition:3    rows:231        clusters:30     slots:1      kept=0
Partition:4    rows:244        clusters:32     slots:1      kept=0
Partition:5    rows:254        clusters:33     slots:1      kept=0
Partition:6    rows:254        clusters:33     slots:1      kept=0
Partition:7    rows:241        clusters:31     slots:6      kept=0
--在multpass 中所有kept=0,说明每个分区都没有数据完全能够存放在hash 内存中。

3、第二阶段处理过程

因为分区表无法单独存放在内存中，所以需要将该分区再次分割成几个子分区，以下就是各个子分区的处理跟踪信息：

分区1处理过程：

*** RowSrcId: 1 HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) ***
Getting a pair of flushed partions.
 BUILD PARTION: nrows:231 size=(30 slots, 240K)
 PROBE PARTION: nrows:246 size=(32 slots, 256K)
*** RowSrcId: 1 HASH JOIN BUILD HASH TABLE (PHASE 2) ***

Number of blocks that may be used to build the hash table 9
--从信息中可以看出，内存hash表中9个blocks来处理30blocks ( 30slots = 30 blocks)  数据，所以该内存需要分四次才能处理完成(round(30/9)=4)，从侧面上可以推断至少需要提高4倍的hash area size才能实现onepass功能。

Number of rows left to be iterated over (start of function): 231
--231行处理中，从第一行进行处理

Number of rows iterated over this function call: 70
Number of rows left to be iterated over (end of function): 161
--还有161行未处理完成，需要继续进行处理

### Hash table ###
# NOTE: The calculated number of rows in non-empty buckets may be smaller
#       than the true number.
Number of buckets with   0 rows:        448
Number of buckets with   1 rows:         58
Number of buckets with   2 rows:          6
Number of buckets with   3 rows:          0
Number of buckets with   4 rows:          0
Number of buckets with   5 rows:          0
Number of buckets with   6 rows:          0
Number of buckets with   7 rows:          0
Number of buckets with   8 rows:          0
Number of buckets with   9 rows:          0
Number of buckets with between  10 and  19 rows:          0
Number of buckets with between  20 and  29 rows:          0
Number of buckets with between  30 and  39 rows:          0
Number of buckets with between  40 and  49 rows:          0
Number of buckets with between  50 and  59 rows:          0
Number of buckets with between  60 and  69 rows:          0
Number of buckets with between  70 and  79 rows:          0
Number of buckets with between  80 and  89 rows:          0
Number of buckets with between  90 and  99 rows:         0
Number of buckets with 100 or more rows:          0
### Hash table overall statistics ###
Total buckets: 512 Empty buckets: 448 Non-empty buckets: 64
Total number of rows: 70
Maximum number of rows in a bucket: 2
Average number of rows in non-empty buckets: 1.093750
kxhfResetIter(1105ed460)
qerhjFetchPhase2(): building a hash table
*** RowSrcId: 1 HASH JOIN BUILD HASH TABLE (PHASE 2) ***
Number of blocks that may be used to build the hash hable 9
Number of rows left to be iterated over (start of function): 161
Number of rows iterated over this function call: 71
Number of rows left to be iterated over (end of function): 90

以下省略。

以上实验Multipass工作机制

1、hash 内存中分配8个分区存放小表数据，但所提供的内存不足以支撑任何一个分区进行hash join，所以一个分区再分成多个子分区，然后分别从磁盘提取到hash内存与大表对应的分区表进行匹配，匹配的原理与前面所述一致。

2、小表的分区表总的被扫描匹配一次，但大表的分区表，每次都要与小表进行扫描匹配一次，这就意味着，如果一个分区表被分成四个子分区表，则对应的大表分区表，将被扫描匹配4次。所以大表的multipass=4。