反联接比左联接更有效吗? [英] Is an anti-join more efficient than a left outer join?

问题描述

对此答案的评论指出，反连接可能已经过优化，比Oracle中的外部连接更有效.我很想知道哪些解释/证据可能支持或反驳这一主张.

A comment on this answer notes that anti-joins may have been optimized to be more efficient that outer joins in Oracle. I'd be interested to see what explanations/evidence might support or disprove this claim.

推荐答案

在SQL查询中使用不存在"或不在"时，您可以让Oracle选择合并反联接或哈希反联接访问路径

When you use "not exists" or "not in" in your SQL query, you let Oracle to choose merge anti-join or hash anti-join access paths.

快速说明

例如，给定连接表A和B的连接(从A上的连接B在Ax = Bx上)，Oracle将从表A中获取所有相关数据，并尝试将它们与表B中的对应行进行匹配，因此它严格依赖于表A谓词的选择性.

For example, given join betwen table A and B (from A join B on A.x = B.x) Oracle will fetch all relevant data from table A, and try to match them with corresponding rows in table B, so it's strictly dependent on selectivity of table A predicate.

使用反联接优化时，Oracle可以选择具有更高选择性的表并将其与另一个表进行匹配，这可能会导致代码更快.

When using anti-join optimization, Oracle can choose the table with higher selectivity and match it with the other one, which may result in much faster code.

使用常规联接或子查询无法做到这一点，因为它不能假设表A和B之间的匹配足以返回该行.

It can't do that with regular join or subquery, because it can't assume that one match between tables A and B is enough to return that row.

相关提示:HASH_AJ，MERGE_AJ.

Related hints: HASH_AJ, MERGE_AJ.

更多

这看起来像是一篇不错的详细文章主题.

This looks like a nice and detailed article on the subject.

这里是另一篇更简短的文章.

Here is another, more dencet article.

这篇关于反联接比左联接更有效吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！