不等式合并合并算法 [英] Algorithm for merge join with inequality condition

问题描述

我读到Oracle支持不等式联接谓词的合并联接. 是否在线参考了用于实现此类联接的算法? 如果有人知道该怎么做，您能回答吗?

I read that Oracle supports merge join with inequality join predicates. Is there online reference to algorithm used in implementation of such join ? If anyone knows how to do that, Can you put it in answer?

推荐答案

这是您要找的.

7.4排序合并联接

7.4 Sort Merge Joins

排序合并联接可以联接来自两个独立来源的行.在 通常，哈希联接的性能要优于排序合并联接.然而， 如果两个排序合并连接都比散列连接执行得更好， 存在以下条件:

Sort merge joins can join rows from two independent sources. In general, hash joins perform better than sort merge joins. However, sort merge joins can perform better than hash joins if both of the following conditions exist:

行源已排序.不需要排序操作.然而， 如果排序合并联接涉及选择较慢的访问方法( 索引扫描(而不是全表扫描)，那么使用的好处 排序合并可能会丢失.

The row sources are sorted. A sort operation is not required. However, if a sort merge join involves choosing a slower access method (an index scan as opposed to a full table scan), then the benefit of using a sort merge might be lost.

当两个表之间的联接条件时，排序合并联接非常有用 是不等式，例如<，< =，>或> =.排序合并联接 对于大型数据集，其性能优于嵌套循环联接.哈希加入 需要一个相等条件.

Sort merge joins are useful when the join condition between two tables is an inequality condition such as <, <=, >, or >=. Sort merge joins perform better than nested loops joins for large data sets. Hash joins require an equality condition.

在合并联接中，没有驱动表的概念.加盟 包括两个步骤:

In a merge join, there is no concept of a driving table. The join consists of two steps:

排序加入操作

这两个输入都按连接键排序.

Both the inputs are sorted on the join key.

合并联接操作

已排序的列表已合并.

如果输入按连接列排序，则进行排序连接操作 不会对该行源执行.但是，排序合并联接 总是在右侧创建可定位的排序缓冲区 加入，以便在以下情况下可以返回到最后一个匹配项: 重复的联接键值出现在联接的左侧.

If the input is sorted by the join column, then a sort join operation is not performed for that row source. However, a sort merge join always creates a positionable sort buffer for the right side of the join so that it can seek back to the last match in the case where duplicate join key values come out of the left side of the join.

这篇关于不等式合并合并算法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！