warehouse 客栈 : rbo下针对表与表之间的连接执行计划的确定原则！

===========================================================

rbo下针对表与表之间的连接执行计划的确定原则！

===========================================================

作者: warehouse(http://warehouse.itpub.net)
发表于: 2008.05.04 10:10
分类: 性能优化
出处: http://warehouse.itpub.net/post/777/461073
---------------------------------------------------------------

需要注意的一句话是：

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

Choosing Execution Plans for Joins with the RBO

Note:

The following considerations apply to both the cost-based and rule-based approaches:

The optimizer first determines whether joining two or more of the tables definitely results in a row source containing at most one row. The optimizer recognizes such situations based on UNIQUE and PRIMARY KEY constraints on the tables. If such a situation exists, then the optimizer places these tables first in the join order. The optimizer then optimizes the join of the remaining set of tables.
For join statements with outer join conditions, the table with the outer join operator must come after the other table in the condition in the join order. The optimizer does not consider join orders that violate this rule.

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

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.
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.