《Pro Oracle SQL》Chapter 3 -- 3.3 Join Methods

Join Methods  连接方法   (page 110)
    If there are multiple tables in your query, after the optimizer determines the access methods most
appropriate for each of the tables, the next step is to determine the way the tables can best be joined
together and the proper order in which to join them.  Anytime you have multiple tables in the FROM
clause, you will have a join.  Tables relationships are defined with a condition in the WHERE  clause.  If no
condition is specified, the join will be implicitly defined such that each row in one table will be
matched with every row in the other table.  This is called a Cartesian join and I will discuss it in further
detail later in this section.
    如果在你的查询中使用了多张表，在优化器确定每张表最佳的访问方法之后，下一步就是确定用何种方式能最佳的把表连接在一起而且顺序恰当。任何时候，若你在 FORM子句中有多张表，你就要连接。表之间的关系由WHERE子句条件确定。如果指定了条件，连接就被隐式的定义了，如在一张表中的每一行将匹配另一张表中的每一行。这就是所谓的笛卡尔连接，我将在本节之后进一步详细讨论。
    Joins occur between pairs of tables or row sources.  When multiple tables exist in the FROM clause,
the optimizer will determine which join operation is most efficient for each pair.  The join methods
are: nested loops joins, hash joins, sort-merge joins, and Cartesian joins.  Each join method has
specific conditions to which it is best suited.  For each pair, the optimizer must also determine the order
in which the tables are joined.  Figure 3-6 shows a diagram of how a query with four tables might be
joined.
    连接发生在表对或者行源之间。当多张表存在于FORM子句中，优化器将确定哪种连接操作是每对表的最效率的操作。连接方法有：嵌套循环连接，哈希连接，排序-合并连接，和笛卡尔连接。每个连接方法都有其最适合的特定条件。而每对连接，优化器还必须确定表的连接顺序。图3-6展示了一个带有四张表的查询如何连接的示意图。
    Notice that after the first pair of tables is joined, the next table is joined to the resulting row source
from the first join.  After that join is made, the next table is joined to that row source.  This continues
until all tables have been joined.  
    注意在第一对表连接之后，下一张表连接的是来至第一次连接的结果行源。在这次连接之后，下一表连接到(本次的)行源上。如此继续下去直到所有的表都连接到为止。
    Each join method will have two children.  The first table accessed is typically called the driving
table and the second table is called the inner or driven-to table.  The optimizer determines the driving
table by using the statistics and the filter conditions in the WHERE  clause to calculate how many rows
will be returned from each table.   The table with the smallest estimated size (in terms of blocks, rows,
and bytes) will typically be the driving table.  This is true particularly if the optimizer can determine
that one of the tables will return at most one row based on a UNIQUE or  PRIMARY KEY  constraint.  These tables are placed first in the join.  Tables with outer join operators (which I’ll discuss later) must come after the table to which it is joined.  Other than these two specific cases, the join order of the other tables is evaluated based on their computed selectivities based on the optimizer’s calculations using available table, column, and index statistics.
    每个连接方法都有两个孩子。访问的第一张表通常称之为驱动表，而第二张表称之为内部或者被驱动表。优化器使用统计信息和WHERE子句中的过滤条件计算从每张表将返回多少行以确定驱动表。 具有最小评估大小值(根据块，行和字节)的表通常就是驱动表。 特别是若优化器能确定其中的一张表，基于UNIQUE或者 PRIMARY KEY约束，最多只返回一行记录时，这就会成真。这些(驱动)表将置于第一次连接中。带有外连接操作符的表(我将之后讨论)必须放在连接过的表之后(最后连接)。 除了这两种特别情况，其它表的连接顺序将根据它们计算的选择性值来评估，选择性值是基于优化器使用有效的表、列和索引统计信息的计算(得来的)。

 Figure 3-6. Join order example diagram  连接顺序示意图