连接查询中表的顺序

Question

我在Oracle文档中发现了这段文字。

if you want to select the name of each department along with the name of its manager, you can write the query in one of two ways. In the first example which follows, the hint /++ordered++/ says to do the join in the order the tables appear in the FROM clause with attempting to optimize the join order.

SELECT /*+ordered*/ d.NAME, e.NAME
FROM DEPT d, EMP e WHERE d.MGR = e.SS#

or:

SELECT /*+ordered*/ d.NAME, e.NAME 
FROM EMP e, DEPT d WHERE d.MGR = e.SS# 

Suppose that there are 10 departments and 1000 employees, and that the inner table in each query has an index on the join column. In the first query, the first table produces 10 qualifying rows (in this case, the whole table). In the second query, the first table produces 1000 qualifying rows. The first query will access the EMP table 10 times and scan the DEPT table once. The second query will scan the EMP table once but will access the DEPT table 1000 times. Therefore the first query will perform much better. As a rule of thumb, tables should be arranged from smallest effective number rows to largest effective number of rows. The effective row size of a table in a query is obtained by applying the logical conditions that are resolved entirely on that table.

但我并没有完全理解这个问题。如果表t1有m行，表t2有n行，那么在这两种情况下，SQL引擎不都需要遍历m x n行吗？

更新：感谢所有的回复。我不会覆盖优化器，只是想确认我的想法。

- Jerry

---

请问您能否提供一下您所读到的文档链接？ - Tony Andrews

你的第二个查询缺少在Oracle文档中提到的“WHERE”子句。 - Peter Lang

@Peter - 添加了where子句 :) - Jerry

Answer 1

在第一种情况下，逻辑读取的数量是10+10，在第二种情况下是1000+1000，每个部门平均被读取了100次。

然而，像这样使用ORDERED提示符编写查询不是正常的做法。大多数情况下最好将优化留给优化器来处理。

我不确定您从哪里获取了该引用，但在我看到它之前，它的前面有一个非常重要的段落，您忽略了它。我在此引用它，以使其他人受益，否则他们可能会认为这种编写查询的方法是标准的：

通常，优化器选择最佳的执行计划，即表连接的最佳顺序。如果优化器没有生成良好的执行计划，则可以使用SQL的HINTS功能控制执行顺序。有关更多信息，请参见Oracle Database Lite SQL参考手册。

-- Oracle® Database Lite开发人员指南

- Tony Andrews

---

+1，尽管我认为它假定了一个嵌套循环连接（如果没有过滤条件，则可能会选择哈希连接）。文档假定了嵌套循环连接（RULE优化器？）并倡导使用提示，这使我认为这个建议来自一个非常古老的文档（Oracle 7？）。无论如何，让优化器自己完成工作是一个好建议。 - Vincent Malgrat

Answer 2

这取决于WHERE语句。

SELECT /++ordered++/ d.NAME, e.NAME FROM DEPT d, EMP e WHERE d.MGR = e.SS#

将为每个部门选择所有经理。由于有10个部门，因此会获取10条记录。

SELECT /++ordered++/ d.NAME, e.NAME FROM EMP e, DEPT d

这将选择所有以部门名称命名的员工。由于有1000名员工，因此您的结果集将有1000行。

一个JOIN永远不会导致引擎循环遍历m x n行，如果m < n，则内部JOIN的结果集始终为m。

- thomaux

---

@Tony，这取决于你的WHERE/ON子句。在第一种情况下，结果集将包含10行，在第二种情况下为1000行。 @Jerry，你所说的连接键索引是什么意思？ - thomaux

@Tony - 既然这是内部连接，那不应该是10而不是100吗？ - Jerry

@Anzeo - 我的意思是，如果e.SS＃和d.MGR上有索引。如果这两列都没有索引，引擎不会为每个记录进行全扫描吗？ - Jerry

@Jerry，是的，我在考虑“显示所有员工及其部门”的查询，而不是你在示例中实际执行的“显示所有部门及其经理”的查询。但重点是它是m或n，而不是Anzeo所说的m x m。 - Tony Andrews

@Tony 没错，我已经编辑了我的答案。@Jerry 如果我理解你的意思正确，在没有定义索引的情况下，搜索与 d.MGR = e.SS# 匹配的行将大大增加，但它不一定会在每次运行时搜索整个表。 - thomaux

Answer 3

你真的在Oracle文档中找到了这个吗？

你不应该使用ORDERED提示，让Oracle为你做决策--现在大多数情况下都能很好地工作。

然而，连接顺序在性能方面确实有所不同。

这个例子似乎讨论了嵌套循环连接：

Case 1:
 -> 1 lookup to find 10 rows in table A
 -> 10 index lookups in table B

Case 2:
 -> 1 lookup to find 1000 rows in table B 
 -> 1000 index lookups in table A

- Markus Winand