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
行吗?
更新:感谢所有的回复。我不会覆盖优化器,只是想确认我的想法。