sql - 连接查询中的表顺序

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.

但我没有正确理解这一点。如果有 m表 t1 和 n 中的行表 t2 中的行，sql 引擎不会通过 m x n两种情况下的行？

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

Answer 1

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

但是，像这样使用 ORDERED hitn 编写查询是不正常的做法。大多数时候最好将优化留给优化器。

我不确定您从哪个文档中获得了该引用，但是我在哪里看到它之前是您省略的这个非常重要的段落。我在这里引用它是为了其他人的利益，他们可能认为这种编写查询的方法是标准的:

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.

-- Oracle® Database Lite Developer's Guide