数据库 · · By/蜜汁炒酸奶

【译】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 遵循如下步骤(假设子表必须与父表外连接):

  1. Oracle选择父表作为(也称为驱动表,driving table )。Oracle 在驱动表的连接列(joining column(s) )上应用哈希函数( hash function ) 后,在 RAM (内存)中构建hash表。
  2. Oracle 选择子表作为探测表(也可以称为被驱动表,probe table / driven table/probing table)。遍历探测表中的所有记录,对连接列【连接这两个表的列】应用相同的hash函数,并将命中hash表中相应的条目。
  3. 如果在驱动表中的一条记录已经存在于相同的hash key 中,Oracle 将返回该输出并在父hash表中将此列标记为“MATCHED” (匹配),反之则不返回任何记录。【我使用"MATCHED" 标志术语来简单地解释这一步】。
  4. 在处理完驱动表中的所有记录后,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

需要记住的鲜为人知的事实

  • /*+ 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>> | |

在执行计划中,无论何时选择此方式,它都会在 Operation 列显示关键字“HASH JOIN (OUTER)” 。在这个单词后面显示的表名是驱动表(也被称为hash表)。并且这就是为什么你会发现在父表名后面有被驱动表的名字(也被称为探测表)。

示例

为了理解 hash outer join 的机制与 hash equi-join 的不同之处,我将使用我在那里(Tip#20)使用的相同的例子,并做了一些修改,并强制使用外连接。

创建2个表。

一个表存储所有员工的信息并插入6条记录。

另一个表存储员工的销售记录并插入3条记录。

这些表看起来如下:

oracle_tuning_hashouterjoin_01.png

对这些表执行查询,要求显示EMP表所有可用员工的namesales_amt (如果他们已经有)。

Select a.empname, b.sales_amt from emp a, sales b where a.empid = b.empid(+);

由于使用外连接“(+)”操作符,将使用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函数会更复杂,更难解码。

oracle_tuning_hashouterjoin_02.png

根据 hash outer join 的设计,在执行该查询时,将遵循以下步骤:

  1. 由于 SALES 与 EMP 是外连接的,因此EMP被视为驱动表,SALES被视为被驱动表。
  2. 对于 EMP 中的第一条记录,应用hash函数并返回5作为其散列值hashed value(即,hash(3825)->mod(3825/10)->5)。
  3. 因此,此记录将作为hash表中的第6条记录插入。
  4. 对于 EMP 中的下一条记录,应用hash函数并返回7作为其散列值(即,hash(9827)->mod(9827/10)->7)。
  5. 因此,此记录将作为hash表中的第8条记录插入。
  6. 对于 EMP 中的下一条记录,应用hash函数并返回9作为其散列值(即,hash(2389)->mod(2389/10)->9)。
  7. 因此,此记录将作为hash表中的第10条记录插入。
  8. 对于 EMP 中的下一条记录,应用hash函数并返回4作为其散列值(即,hash(1784)->mod(1784/10)->4 )。
  9. 因此,此记录将作为hash表中的第5条记录插入。
  10. 对于 EMP 中的下一条记录,应用hash函数并返回6作为其散列值(即,hash(4556)->mod(4556/10)->6 )。
  11. 因此,此记录将作为hash表中的第7条记录插入。
  12. 对于 EMP 中的下一条记录,应用hash函数并返回6作为其散列值(即,hash(8711)->mod(8711/10)->1 )。
  13. 因此,此记录将作为hash表中的第2条记录插入。所以,在这一步的最后,hash表会是这样的:
    oracle_tuning_hashouterjoin_03.png
  14. 对于 SALES 中的第一条记录,应用hash函数并返回7作为其散列值(即,hash(9827)->mod(9827/10)->7)。
  15. 所以,它 hits 了hash表的第8条记录。
  16. 由于在哈希表的第8条记录中有一条 EMP 记录可用,该记录将被标记为“MATCHED”,并在输出中返回数据。
  17. 对于 SALES 中的第二条记录,,应用hash函数并返回9作为其散列值(即,hash(2389)->mod(2389/10)->9)。
  18. 所以,它 hits 了hash表的第10条记录。
  19. 由于在哈希表的第10条记录中有一条 EMP 记录可用,该记录将被标记为“MATCHED”,并在输出中返回数据。
  20. 对于 SALES 中的第三条记录,,应用hash函数并返回2作为其散列值(即,hash(5642)->mod(5642/10)->2)。
  21. 所以,它 hits 了hash表的第3条记录。
  22. 由于hash表的第3条记录中没有来自EMP的记录,因此输出中将不会返回任何数据。
    oracle_tuning_hashouterjoin_04.png
  23. hash表中未被标记为“MATCHED”的所有记录将再次被处理,所有这些来自hash表的记录(empids:8711,1784,3825,4556)将与查询中提到的子表列【sales_amt column in this case】的NULL值一起返回。
  24. 退出。

输出结果如下所示:

oracle_tuning_hashouterjoin_05.png

如果仔细查看输出,您会发现输出是以这样的方式显示的:首先显示匹配的记录,然后显示只有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 |

从这个解释计划中,我们可以说这两个表都是通过 hash outer join 的方式连接的,EMP被视为驱动表【hash 表】,SALES 被视为被驱动表【探测表】。

译者附录

PGA包含单个服务器进程或者单个后台进程的数据和控制信息, 与几个进程共享的SGA正好相反,是一个只被一个进程使用的区域,PGA在创建进程时分配终止进程时回收。

  • sort_area_size 用户排序所占的内存
  • hash_area_size 用户散列联接, 位图索引

参考与扩展:Oracle 内存参数调优设置

评论已关闭

example
C
蜜汁炒酸奶

当前处于试运行期间,可能存在不稳定情况,敬请见谅。

欢迎点击此处反馈访问过程中出现的问题