散列联接和合并联接(Oracle RDBMS)有什么区别?

“排序合并”联接是根据联接键对要联接的两个数据集进行排序,然后将它们合并在一起。合并是非常便宜的,但排序可能会非常昂贵,特别是如果排序溢出到磁盘。如果可以通过索引按排序顺序访问其中一个数据集,则可以降低排序成本,尽管与全表扫描相比,通过索引扫描访问表块的高比例也非常昂贵。

散列联接是通过将一个数据集根据联接列散列到内存中,读取另一个数据集,并探测散列表中的匹配项来执行的。当散列表可以完全保存在内存中时,散列连接的成本非常低,其总成本几乎不超过读取数据集的成本。如果哈希表必须以一次通过排序的方式溢出到磁盘上,则成本会增加,而对于多路径排序,成本会大幅增加。

(在pre-10g中,从大表到小表的外部联接在性能方面存在问题,因为优化程序无法解决先访问较小表进行哈希联接的需要,而先访问较大表进行外部联接的需要。因此,在这种情况下哈希联接不可用)。

通过对连接键上的两个表进行分区,可以降低哈希连接的成本。这允许乐观者推断一个表中某个分区的行只能在另一个表的特定分区中找到匹配项,对于具有n个分区的表,哈希联接将作为n个独立的哈希联接执行。这有以下效果:

1. 每个哈希表的大小都会减小,从而减少所需的最大内存量,并可能消除操作需要临时磁盘空间的需要。
2. 对于并行查询操作,进程间消息传递的数量大大减少,从而减少了CPU的使用并提高了性能,因为每个哈希连接都可以由一对PQ进程执行。
3. 对于非并行查询操作,内存需求减少了一个系数n,并从前面的查询中投影第一行。

应该注意,哈希连接只能用于equi连接,但合并连接更灵活。

一般来说,如果您在equi连接中加入大量数据,那么散列连接将是更好的选择。

文档中很好地涵盖了这个主题。

http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/optimops.htm#i51523

12.1个文档: https://docs.oracle.com/database/121/TGSQL/tgsql_join.htm

我只想为子孙后代编辑这个,我回答这个问题时没有添加Oracle的标签。我的回答更适用于MS SQL。

合并联接是最好的方法,因为它利用了顺序,从而只传递一次表来进行联接。如果有两个表(或覆盖索引)的顺序相同,例如主键和该键上表的索引,那么如果执行该操作,将导致合并联接。

hash join是第二个最好的方法,因为它通常是在一个表中有少量(相对)项目时完成的,它有效地为每一行创建一个带有哈希的临时表,然后继续搜索以创建join。

最坏的情况是嵌套循环,即order(n*m),这意味着没有可利用的顺序或大小,而join只是在表x中的每一行中搜索表y以查找要执行的join。