JOIN ON与WHERE中的Oracle SQL查询过滤器

问题描述

对于内部连接，在JOIN ON子句或WHERE子句中应用过滤器的性能有何不同?哪个会更有效，或者优化程序会使其相等?

For inner joins, is there any difference in performance to apply a filter in the JOIN ON clause or the WHERE clause? Which is going to be more efficient, or will the optimizer render them equal?

加入

SELECT u.name
FROM users u
JOIN departments d
ON u.department_id = d.id
AND d.name         = 'IT'

VS

位置

SELECT u.name
FROM users u
JOIN departments d
ON u.department_id = d.id
WHERE d.name       = 'IT'

Oracle 11gR2

Oracle 11gR2

推荐答案

应该没有区别.在这两种情况下，优化器都应生成相同的计划，并且应该能够基于特定查询的最有效方法，在这两种情况下的连接之前，之后或之中应用谓词.

There should be no difference. The optimizer should generate the same plan in both cases and should be able to apply the predicate before, after, or during the join in either case based on what is the most efficient approach for that particular query.

当然，优化器可以做某事通常不能保证优化器将在特定查询中实际做某事.随着查询变得越来越复杂，不可能详尽地考虑每个可能的查询计划，这意味着即使有了完美的信息和完美的代码，优化器也可能没有时间去做您想做的所有事情.您需要查看为这两个查询生成的实际计划，以查看它们是否实际上相同.

Of course, the fact that the optimizer can do something, in general, is no guarantee that the optimizer will actually do something in a particular query. As queries get more complicated, it becomes impossible to exhaustively consider every possible query plan which means that even with perfect information and perfect code, the optimizer may not have time to do everything that you'd like it to do. You'd need to take a look at the actual plans generated for the two queries to see if they are actually identical.

这篇关于JOIN ON与WHERE中的Oracle SQL查询过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！