rbo下針對表與表之間的連線執行計劃的確定原則！

需要注意的一句話是：

Usually, the optimizer does not consider the order in which tables appear in the FROM clause when choosing an execution plan.

[@more@]

Choosing Execution Plans for Joins with the RBO

With the rule-based approach, the optimizer performs the following steps to choose an execution plan for a statement that joins R tables:

1. The optimizer generates a set of R join orders, each with a different table as the first table. The optimizer generates each potential join order using this algorithm:
   • To fill each position in the join order, the optimizer chooses the table with the most highly ranked available access path according to the ranks for access paths described in "Understanding Access Paths for the RBO". The optimizer repeats this step to fill each subsequent position in the join order.
   • For each table in the join order, the optimizer also chooses the operation with which to join the table to the previous table or row source in the order. The optimizer does this by ranking the sort-merge operation as access path 12 and applying these rules:
     • If the access path for the chosen table is ranked 11 or better, then the optimizer chooses a nested loops operation using the previous table or row source in the join order as the outer table.
     • If the access path for the table is ranked lower than 12, and if there is an equijoin condition between the chosen table and the previous table or row source in join order, then the optimizer chooses a sort-merge operation.
     • If the access path for the chosen table is ranked lower than 12, and if there is not an equijoin condition, then the optimizer chooses a nested loops operation with the previous table or row source in the join order as the outer table.
2. The optimizer then chooses among the resulting set of execution plans. The goal of the optimizer's choice is to maximize the number of nested loops join operations in which the inner table is accessed using an index scan. Because a nested loops join involves accessing the inner table many times, an index on the inner table can greatly improve the performance of a nested loops join.

   Usually, the optimizer does not consider the order in which tables appear in the FROM clause when choosing an execution plan. The optimizer makes this choice by applying the following rules in order:

   • The optimizer chooses the execution plan with the fewest nested-loops operations in which the inner table is accessed with a full table scan.
   • If there is a tie, then the optimizer chooses the execution plan with the fewest sort-merge operations.
   • If there is still a tie, then the optimizer chooses the execution plan for which the first table in the join order has the most highly ranked access path:
     • If there is a tie(平局，不分勝負) among multiple plans whose first tables are accessed by the single-column indexes access path, then the optimizer chooses the plan whose first table is accessed with the most merged indexes.
     • If there is a tie among multiple plans whose first tables are accessed by bounded range scans, then the optimizer chooses the plan whose first table is accessed with the greatest number of leading columns of the composite index.
   • If there is still a tie, then the optimizer chooses the execution plan for which the first table appears later in the query's FROM clause.