以前对hash join时应该用大记录集做驱动表还是用小记录集做驱动表一直存在疑虑, 直到最近做了一个实验, 才真正体会到其间的差别.实验过程如下.
用大记录集做驱动的执行计划如下:

———————————————————————————————————————————————————
| Id  | Operation              |  Name                | Rows  | Bytes |TempSpc| Cost  |
———————————————————————————————————————————————————
|   0 | SELECT STATEMENT       |                      |     1 |    12 |       |  1784 |
|   1 |  SORT AGGREGATE        |                      |     1 |    12 |       |       |
|*  2 |   HASH JOIN            |                      |  1820K|    20M|    31M|  1784 |
|   3 |    INDEX FAST FULL SCAN| SAMPLES_MEMINFO_IND  |  1820K|    10M|       |  1077 |
|   4 |    INDEX FAST FULL SCAN| MEMBER_PK            |   373K|  2188K|       |   106 |
———————————————————————————————————————————————————

用小记录集做驱动的执行计划如下:

———————————————————————————————————————————————————
| Id  | Operation              |  Name                | Rows  | Bytes |TempSpc| Cost  |
———————————————————————————————————————————————————
|   0 | SELECT STATEMENT       |                      |     1 |    12 |       |  1784 |
|   1 |  SORT AGGREGATE        |                      |     1 |    12 |       |       |
|*  2 |   HASH JOIN            |                      |  1820K|    20M|  6568K|  1784 |
|   3 |    INDEX FAST FULL SCAN| MEMBER_PK            |   373K|  2188K|       |   106 |
|   4 |    INDEX FAST FULL SCAN| SAMPLES_MEMINFO_IND  |  1820K|    10M|       |  1077 |
———————————————————————————————————————————————————

看起来cost都是一样的, 只是对TempSpc(temp表空间)的估算不太一样, 大记录集的是31M, 小记录集的是6568K.
 
 
Set autot trace, 各自执行看看.
大记录集作驱动:
12499  consistent gets
 2849  physical reads
小记录集做驱动:
12499  consistent gets
    0  physical reads
以上结果是反复执行多次之后的结果.
可以看到:
1. 逻辑读都是一样的.
2. 大记录集做驱动时多了很多物理读, 而且无论我怎么反复执行这个SQL, 这些物理读总是一个也不少.
那么这些物理读是哪儿冒出来的呢
 
 
看看10104 trace吧, 差别一下子就出来了.
首先来看hash桶的一些统计,
大记录集作驱动:
### Hash table overall statistics ###
Total buckets: 4194304 Empty buckets: 4173603 Non-empty buckets: 20701
Total number of rows: 1820941
Maximum number of rows in a bucket: 26932
Average number of rows in non-empty buckets: 87.963915
小记录集作驱动:
### Hash table overall statistics ###
Total buckets: 524288 Empty buckets: 257306 Non-empty buckets: 266982
Total number of rows: 373588
Maximum number of rows in a bucket: 7
Average number of rows in non-empty buckets: 1.399300
可以看到, hash桶的总数/构建hash的总记录数/平均每个hash桶里的记录数 都是前者大. 而hash size是有限的, 那么内存中能承载的hash桶的数目也是有限的, 过多的hash桶会导致频繁的访问io, 读取hash桶. 而平均每个hash桶里的记录数太大, 又导致每个hash到的值在一个桶内匹配计算的次数增加(这些是CPU的负担, 在trace里甚至都看不到).
 
 
但是真正导致物理读的原因还不在于hash匹配, 而是创建hash桶. 我们再来看看10104 trace中对hash桶构建时的信息记录.
在大记录集做驱动时10104 trace文件中的构建hash桶这段, 多了很多这种东西:
kxhfRead(): Reading dba=156615 into slot=80
kxhfIsDone: waiting slot=80 lbs=0xb442fd40
我grep了一下, 执行一次这个SQL, Reading/waiting 总共有80次.而小记录集做驱动时一个这个操作都没有.
 
 
结论:
1. hash的时候一定要用小记录集做驱动.
2. 大/小记录集作驱动时, 读取数据文件的cost两者是一样的.但是前者的记录集在大到一定程度的时候, 在构建hash桶会产生很多物理读, 而且这些物理读根本无法消除, 每次执行都会产生.
  
结合工作:
1. 我们平常接触的OLTP系统可能用hash的时候比较少, 即使用到hash, 也不会是join太大的记录集, 除了搜索引擎的dump等特别SQL.
2. 但是对于OLAP系统来说, 几乎所有的SQL都是这样的大记录集的hash join, 这个时候就要尤其注意用小记录集来驱动大记录集.昨天帮数据仓库部门优化了一个SQL, 四个表做join, 其中三个表的有效记录集都是1千万左右, 另一表的有效记录集则只有几十笔. 原来的执行计划里用大表做驱动, 跑了几个小时, 最后把temp表空间撑爆了也没有执行出结果. 后来我就用这个小表做驱动, 结果两分钟就跑出了结果.