oracle 连接查询中的表顺序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3976941/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Order of tables in join query
提问by Jerry
I found this paragraph in Oracle documentation
我在 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.
如果要选择每个部门的名称及其经理的名称，可以使用以下两种方式之一编写查询。在接下来的第一个示例中，提示 /++ordered++/ 表示按照表出现在 FROM 子句中的顺序进行连接，并尝试优化连接顺序。
SELECT /*+ordered*/ d.NAME, e.NAME FROM DEPT d, EMP e WHERE d.MGR = e.SS#或者：
SELECT /*+ordered*/ d.NAME, e.NAME FROM EMP e, DEPT d WHERE d.MGR = e.SS#假设有 10 个部门和 1000 名员工，并且每个查询中的内表在连接列上都有一个索引。在第一个查询中，第一个表生成 10 个符合条件的行（在本例中为整个表）。在第二个查询中，第一个表生成 1000 个符合条件的行。第一个查询将访问 EMP 表 10 次并扫描 DEPT 表一次。第二个查询将扫描 EMP 表一次，但将访问 DEPT 表 1000 次。因此，第一个查询的性能会好得多。根据经验，表格应该从最小有效行数到最大有效行数排列。查询中表的有效行大小是通过应用完全在该表上解析的逻辑条件获得的。
But I don't correctly understand this. If there are mrows in table t1 and nrows in table t2, wouldn't the sql engine go through m x nrows in both cases?
但我没有正确理解这一点。如果m表 t1 中有n行，表 t2 中有行，那么 sql 引擎不会m x n在这两种情况下都遍历行吗？
Update:Thanks for all the replies. I won't be overriding the optimizer, just wanted to confirm my thought.
更新：感谢所有回复。我不会覆盖优化器，只是想确认我的想法。
回答by Tony Andrews
Well, in the first case the number of logical reads is 10+10, in the second 1000+1000, with each department being read on average 100 times.
那么，在第一种情况下，逻辑读取的次数是 10+10 次，在第二种情况下是 1000+1000 次，每个部门平均被读取 100 次。
However, writing queries with the ORDERED hitn like this is not normal practice. It's best to leave optimisation to the optimiser most of the time.
但是，像这样使用 ORDERED 命中来编写查询是不正常的做法。大多数时候最好将优化留给优化器。
I'm not sure exactly which documentation you got that quote from, but where I have seen it it is preceded by this very important paragraph that you omitted. I quote it here for the benefit of others who may otherwise think this method of writing queries is standard:
我不确定您从哪个文档中获得了该引用，但是我在哪里看到它之前是您省略的这个非常重要的段落。我在这里引用它是为了其他人的利益，他们可能认为这种编写查询的方法是标准的：
Normally optimizer picks the best execution plan, an optimal order of tables to be joined. In case the optimizer is not producing a good execution plan you can control the order of execution using the HINTS feature SQL. For more information see the Oracle Database Lite SQL Reference.
通常优化器会选择最佳执行计划，即要连接的表的最佳顺序。如果优化器没有生成好的执行计划，您可以使用 HINTS 功能 SQL 控制执行顺序。有关更多信息，请参阅 Oracle Database Lite SQL 参考。
回答by thomaux
That depends on the WHERE statement.
这取决于 WHERE 语句。
SELECT /++ordered++/ d.NAME, e.NAME FROM DEPT d, EMP e WHERE d.MGR = e.SS#
Will select all managers for each department. As there are 10 departments, this results in 10 records being fetched.
将选择每个部门的所有经理。由于有 10 个部门，因此会提取 10 条记录。
SELECT /++ordered++/ d.NAME, e.NAME FROM EMP e, DEPT d
This will select all employees with the name of the department they're working in. As there are 1000 employees, your resultset will have 1000 rows.
这将选择具有他们所在部门名称的所有员工。由于有 1000 名员工，您的结果集将有 1000 行。
A JOIN will never cause your engine to loop over m x nrows, you're resultset of an inner join will always be mif m < n
JOIN 永远不会导致您的引擎m x n在行上循环，您的内部连接的结果集将始终是m如果m < n
回答by Markus Winand
You really found that in oracle docs?
你真的在 oracle 文档中找到了？
You should not use the ORDERED hint and let oracle do the decision for you--that is most of the time working very well nowadays.
您不应该使用 ORDERED 提示而让 oracle 为您做决定——这在当今大多数情况下工作得很好。
However, the join order makes an difference performance wise.
但是，连接顺序会在性能方面产生差异。
The example seems to discuss the NESTED LOOPS join:
该示例似乎讨论了 NESTED LOOPS 连接：
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
