【译】Oracle调优技巧22：Hash Outer Join
查找Oracle相关优化过程中，遇到这此系列文章，感觉解释的简单易懂，翻译过来分享给大家。顺序以个人查找优化相关的次序为主，可通过查看原文看作者原版文章。原文若无法直接打开，请自行通过梯子尝试。
原文：Hash Outer Join : Oracle Tuning Tip#21
作者：kali
话题
Hash Outer Join（散列外连接，音译哈希连接）
定义
根据Hash Outer Join 的定义，保留驱动表（父表）的行信息用于构建哈希表（ hash table），被驱动表（子表）用于探测哈希表。
为了执行Hash outer join ，Oracle 遵循如下步骤（假设子表必须与父表外连接）：
- Oracle选择父表作为（也称为驱动表，
driving table）。Oracle 在驱动表的连接列（joining column(s)）上应用哈希函数（hash function） 后，在 RAM （内存）中构建hash表。 - Oracle 选择子表作为探测表（也可以称为被驱动表，
probe table/driven table/probing table）。遍历探测表中的所有记录，对连接列【连接这两个表的列】应用相同的hash函数，并将命中hash表中相应的条目。 - 如果在驱动表中的一条记录已经存在于相同的
hash key中，Oracle 将返回该输出并在父hash表中将此列标记为“MATCHED”(匹配)，反之则不返回任何记录。【我使用"MATCHED" 标志术语来简单地解释这一步】。 - 在处理完驱动表中的所有记录后，Oracle 将再次遍历父级驱动表并返回没有标记为
“MATCHED”的行的值以及查询中提到的子级探测表的列的值为NULL。
如您所知，在 hash equi-join中, 决定哪个表是hash 表将是其决定因素。通常，两个表中最小的会被视为是驱动表，这样成本（cost ）将很低。【成本将很低，是因为两个表中最小的表将完全适合RAM，在RAM中占用更少的内存空间，并且子表将在一次传递中完全遍历】。然而在 Hash outer join 中，这个决策过程变得无关紧要，因为默认情况下父表会被选为hash表，而不管它是小表还是大表。原因是连接条件（joining condition）将决定哪个表将成为此 Hash outer join 中的 hash 表，而不是cost ， 后者是用于标识 hash equi-join中hash表的度量。
这种方式的逻辑活动图如下所示：
从父表中读取所有需要的数据，应用 hash 函数后在 RAM 中建立 hash 表。
Loop(子表中所有必须的记录)
- 对每条记录应用 hash 函数以获得在RAM中的地址[hashed value]
- 命中RAM中的相应地址
- 如果驱动表中的一条记录已经存在于相同的hash key，将父 hash 表中的这条记录标 为“MATCHED”，并返回查询中提到的两个表中的列，否则转到子表中的下一条记录。
Eed loop
Loop(父Hash表中所有未标记“MATCHED”的记录)
返回查询中提到的hash表中的列，同时查询中提到的子探测表中的列返回NULL值。
End loop
2
3
4
5
6
7
8
9
10
需要记住的鲜为人知的事实
/*+ USE_**HASH**(<<driving table>>) */是可以用来强制使用(impose)该Hash outer join的 hint。- 只有当两个表都由outer join“（+）”操作符连接时，Oracle才会选择这种方法。
- 在RAM中，
Hash outer join仅在分配的内存空间中执行，该内存空间只是PGA的**HASH**_AREA_SIZE部分。 - RAM中的hash表仅插入驱动表，被驱动表永远不会写入RAM中的hash表中。hash函数在被驱动表中使用，只是为了在hash表中找到相应的内存条目，以便从驱动表中寻找匹配的条目。
- 在一个表非常小而另一个非常大时，这是非常成功的。在这种情况下，它比
nested loop outer join的效果更好。 - 如果驱动表无法在RAM中一次性执行完hash（
in single pass，即at one shot or one go）,那么hash表中的一部分会溢出到磁盘上（实际上，这里将使用临时表空间（TEMP tablespace ）来保存驱动表中溢出的数据集）。当被驱动表探测hash表时，链接键（ join keys ）与内存中hash表的那部分相匹配的行立即连接；【驱动表】其余部分也被写入临时表空间，并在第二次传递时连接。驱动表越大，可以容纳在RAM中的hash表的比例越小，剩余数据将被溢出到临时表空间，并且必须经过后续的传递，直到处理从驱动表和被驱动表溢出到临时表空间的所有记录。这大大降低了散列连接（Hash Join）的速度，也使得该连接在此场景下不可伸缩（non-scalable）。
优点
- 如果驱动表足够小到可以放入RAM中，在查询中使用外连接“
(+)”操作符，并且被驱动表非常大，那么hash outer join比nested loop outer join效率更出色。 nested loop outer join仍然将是检索第一个匹配记录的最快方法【因为hash outer join应用Hash函数在RAM中为驱动表构建hash表需要花费一些时间】但是与nested loop outer join相比，hash outer join将是获取所有匹配记录的最快方法。一般来说，hash 函数（在hash outer join中）的性能更好，效率更高。
缺点
- 当选择这样连接2个大表时，临时表空间将被广泛使用，因为来自驱动表和被驱动表的溢出数据集都将写入临时表空间。
- 上面的场景使
hash outer join变得更不可伸缩（ non-scalable ），因为它必须通过后续的过程（subsequent passes ）连接这些从这两个表中溢出的数据集（临时表空间可以找到的）。
如何验证
如何验证Oracle在执行sql时是否遵循hash outer join 。如果一个查询遵循它，您将发现如下类似的执行计划：
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | HASH JOIN (OUTER) | | |
| 2 | TABLE ACCESS (FULL) | <<parent table’s name>> | |
| 3 | TABLE ACCESS (FULL) | <<child table’s name>> | |
2
3
4
5
6
7
在执行计划中，无论何时选择此方式，它都会在 Operation 列显示关键字“HASH JOIN (OUTER)” 。在这个单词后面显示的表名是驱动表（也被称为hash表）。并且这就是为什么你会发现在父表名后面有被驱动表的名字（也被称为探测表）。
示例
为了理解 hash outer join 的机制与 hash equi-join 的不同之处，我将使用我在那里(Tip#20)使用的相同的例子，并做了一些修改，并强制使用外连接。
创建2个表。
一个表存储所有员工的信息并插入6条记录。
另一个表存储员工的销售记录并插入3条记录。
这些表看起来如下：
对这些表执行查询，要求显示EMP表所有可用员工的name和sales_amt （如果他们已经有）。
Select a.empname, b.sales_amt
from emp a, sales b
where a.empid = b.empid(+);
2
3
由于使用外连接“(+)”操作符，将使用hash join 来连接这两个表。
根据这个查询，emp 是父表，bonus 是子表，因为后者sales.empid(+)] 与前连接外连接。因此，Oracle将emp作为驱动表，bonus 作为被驱动表，尽管后者比前者小。这就是cost 不是决定性因素，而连接条件( join condition)是hash outer join 的决定性因素的原因。
假设Oracle可以在RAM分配的内存空间（由全局参数HASH_AREA_SIZE定义 ）中一次保存10条记录。
为了便于理解，请假设内存地址从0到9用来保存hash表中的记录。
为了便于理解，请假设以10为基数的 MOD 函数将被作为hash 函数。在现实世界中，hash函数会更复杂，更难解码。
根据 hash outer join 的设计，在执行该查询时，将遵循以下步骤：
- 由于 SALES 与 EMP 是外连接的，因此EMP被视为驱动表，SALES被视为被驱动表。
- 对于 EMP 中的第一条记录，应用hash函数并返回5作为其散列值
hashed value（即，hash(3825)->mod(3825/10)->5）。 - 因此，此记录将作为hash表中的第6条记录插入。
- 对于 EMP 中的下一条记录，应用hash函数并返回7作为其散列值(即，
hash(9827)->mod(9827/10)->7)。 - 因此，此记录将作为hash表中的第8条记录插入。
- 对于 EMP 中的下一条记录，应用hash函数并返回9作为其散列值(即，
hash(2389)->mod(2389/10)->9)。 - 因此，此记录将作为hash表中的第10条记录插入。
- 对于 EMP 中的下一条记录，应用hash函数并返回4作为其散列值(即，
hash(1784)->mod(1784/10)->4)。 - 因此，此记录将作为hash表中的第5条记录插入。
- 对于 EMP 中的下一条记录，应用hash函数并返回6作为其散列值(即，
hash(4556)->mod(4556/10)->6)。 - 因此，此记录将作为hash表中的第7条记录插入。
- 对于 EMP 中的下一条记录，应用hash函数并返回6作为其散列值(即，
hash(8711)->mod(8711/10)->1)。 - 因此，此记录将作为hash表中的第2条记录插入。所以，在这一步的最后，hash表会是这样的：
- 对于 SALES 中的第一条记录，应用hash函数并返回7作为其散列值（即，
hash(9827)->mod(9827/10)->7）。 - 所以，它 hits 了hash表的第8条记录。
- 由于在哈希表的第8条记录中有一条 EMP 记录可用，该记录将被标记为“MATCHED”，并在输出中返回数据。
- 对于 SALES 中的第二条记录，，应用hash函数并返回9作为其散列值(即，
hash(2389)->mod(2389/10)->9)。 - 所以，它 hits 了hash表的第10条记录。
- 由于在哈希表的第10条记录中有一条 EMP 记录可用，该记录将被标记为“MATCHED”，并在输出中返回数据。
- 对于 SALES 中的第三条记录，，应用hash函数并返回2作为其散列值（即，
hash(5642)->mod(5642/10)->2）。 - 所以，它 hits 了hash表的第3条记录。
- 由于hash表的第3条记录中没有来自EMP的记录，因此输出中将不会返回任何数据。
- hash表中未被标记为“MATCHED”的所有记录将再次被处理，所有这些来自hash表的记录(empids:8711,1784,3825,4556)将与查询中提到的子表列【
sales_amt column in this case】的NULL值一起返回。 - 退出。
输出结果如下所示：
如果仔细查看输出，您会发现输出是以这样的方式显示的：首先显示匹配的记录，然后显示只有EMP表中才有的不匹配的记录。
现在，解释计划（ explain plan）是这样的：
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------------------------------------------------------
| 4 | SELECT STATEMENT | | 6 |
| 3 | HASH JOIN (OUTER) | | 6 |
| 1 | TABLE ACCESS (FULL) | EMP | 6 |
| 2 | TABLE ACCESS (FULL) | SALES | 3 |
2
3
4
5
6
7
从这个解释计划中，我们可以说这两个表都是通过 hash outer join 的方式连接的，EMP被视为驱动表【hash 表】，SALES 被视为被驱动表【探测表】。
译者附录
PGA包含单个服务器进程或者单个后台进程的数据和控制信息， 与几个进程共享的SGA正好相反，是一个只被一个进程使用的区域，PGA在创建进程时分配终止进程时回收。
- sort_area_size 用户排序所占的内存
- hash_area_size 用户散列联接， 位图索引
参考与扩展：Oracle 内存参数调优设置
除特别注明外，本站所有文章均为 竹风清语 原创，转载请注明出处来自： yi-oraclediaoyoujiqiao22-hash-outer-join
暂无数据