三、Onepass hash join

1、内存hash表总体信息

kxhfWrite: hash-join is spilling to disk
-- 发生了dump disk的动作

*** RowSrcId: 1 HASH JOIN BUILD HASH TABLE (PHASE 1) ***

Total number of partitions: 8
--hash表分为8个分区

Number of partitions which could fit in memory: 1
--适合存放在内存中分区数量为1

Number of partitions left in memory: 1
--留在内存分区数量为1

Total number of slots in in-memory partitions: 4
--在内存中分区的slot数量为4

Total number of rows in in-memory partitions: 222
--内存分区中存放小表的行数为222.

Estimated max # of build rows that can fit in avail memory: 1872
--评估所分配内存，最大能存放小表的行数位1872，测试中，小表的行数为2000行，所以可用预估，需要onepass hash join

2、分区表具体存放行数的信息

### Partition Distribution ###
Partition:0    rows:249        clusters:4      slots:1      kept=0
Partition:1    rows:272        clusters:4      slots:1      kept=0
Partition:2    rows:243        clusters:4      slots:1      kept=0
Partition:3    rows:269        clusters:4      slots:1      kept=0
Partition:4    rows:251        clusters:4      slots:1      kept=0
Partition:5    rows:249        clusters:4      slots:1      kept=0
Partition:6    rows:222        clusters:4      slots:4      kept=1
Partition:7    rows:245        clusters:4      slots:2      kept=0
--存放2000行数据小表分布在8个分区，目前partition 6保存在内存中(kept=1)，其他的存放在磁盘中。

Number of buckets: 512
--hash 表有512个hash bucket管理

3、hash表的具体存放信息：

352+114+36+5+4+1=512
### 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:        352
Number of buckets with   1 rows:        114
Number of buckets with   2 rows:         36
Number of buckets with   3 rows:          5
Number of buckets with   4 rows:          4
Number of buckets with   5 rows:          1
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

对应的行数量=114 + 26*2 + 5*3 +4*4 + 1*5=202，我们发现计算的行数，正如所注释一样，少于实际行数，但不清楚为什么存在此现象？

4、partition 6的hash 表统计信息
Total buckets: 512
Empty buckets: 352
Non-empty buckets: 160
Total number of rows: 222
Maximum number of rows in a bucket: 5
Average number of rows in non-empty buckets: 1.387500

5、大表对应小表partition 6进行过滤
Used bitmap filtering: filtered rows=23 minimum required=50 out f=1000
Used bitmap filtering: filtered rows=237 minimum required=50 out f=1000
Used bitmap filtering: filtered rows=448 minimum required=50 out f=1000
..............................

以上第一阶段与optimal hash join机制基本相同。以下是onepass处理的第二阶段

1、从磁盘中提取分区，进行hash join信息：

*** RowSrcId: 1 HASH JOIN GET FLUSHED PARTITIONS (PHASE 2) ***
Getting a pair of flushed partions.
 BUILD PARTION: nrows:249 size=(4 slots, 288K)
 PROBE PARTION: nrows:210 size=(3 slots, 216K)
   ROLE REVERSAL OCCURRED

--从磁盘提取的另一个分区表，行数为249，我们可以从上面分区分配情况得知其为partition 0，对应进行处理的大表行数为210行。我们从早先的跟踪文件信息知道multiblock IO=9，这就意味着4 slots对应着36个blocks。

2、hash join处理重要信息

Number of blocks that may be used to build the hash hable 72
Number of rows left to be iterated over (start of function): 210
Number of rows iterated over this function call: 210
Number of rows left to be iterated over (end of function): 0

-- 最后一行为0，表示此次处理只需要一次onepass就能完成，如果非0，意味着需要multi-pass处理。

3、partition 0 hash表信息
Number of buckets with   0 rows:        346
Number of buckets with   1 rows:        132
Number of buckets with   2 rows:         24
Number of buckets with   3 rows:         10
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: 346 Non-empty buckets: 166
Total number of rows: 210
Maximum number of rows in a bucket: 3
Average number of rows in non-empty buckets: 1.265060

其他分区处理与以上类似。

OnePass工作机制：
1、hash 内存区域中分配了8个分区，每个分区对应4K bitmap位图，小表行存放到hash表中，因为内存限制，在内存中只保留一个分区，其余分区dump到磁盘中。其处理机制与optimal相同。

2、大表按照小表分区表进行整理分类，当从磁盘中提取其他分区时候，对应提取整理过大表分区。获取所需要的行

3、从跟踪文件中，小表处理过程先读取数据到磁盘，然后将部分数据dump到磁盘，然后再从磁盘读取到内存中，而大表处理过程，也是先从磁盘读到内存，然后dump到磁盘，再从磁盘读取。对磁盘的写和读为1次。