A m Z o n e

Table positioning in from clause, for rule base optimization...Myth breaking

The widely believed fact is that table position in from clause decides what should be the driving table for a query. Is this always true?

I am providing some insight into Rule based optimization of query. Most of us go by the belief that the table positioning in the from clause decides the driving table, when we use multiple tables. It is true in most of the cases as the way we frame the query also satisfies the way the optimizer carries out ranking in RBO. But in some cases, rare enough, the query baffles you by not deciding on the driving table as per your specification! Well, I found it difficult to digest the fact that, yes oracle does have a say in RBO also. In CBO, Oracle relies on statistics, but in RBO oracle carries out a ranking process, choosing the top ranked options feasible for the query.

Usually, the optimizer does not consider the order in which tables appear in the FROM clause when choosing an execution plan!!! The following 3 rules are followed for an execution plan. The table positioning option is considered last in the process!!!

Choosing Execution Plans for Joins with the Rule-Based Approach (Extracts from the manual/courtesy sites)

With the rule-based approach, the optimizer follows these 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:
   1. 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. The optimizer repeats this step to fill each subsequent position in the join order.
   2. 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, 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 there is an equijoin condition between the chosen table and the previous table or row source in join order, the optimizer chooses a sort-merge operation.
      If the access path for the chosen table is ranked lower than 12, and there is not an equijoin condition, 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. Since 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:
   1. The optimizer chooses the execution plan with the fewest nested-loops operations in which the inner table is accessed with a full table scan.
   2. If there is a tie, the optimizer chooses the execution plan with the fewest sort-merge operations.
   3. If there is still a tie, 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, 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, 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, the optimizer chooses the execution plan for which the first table appears later in the query's FROM clause.
   
   

Press the Back button of you Browser to go to previous page
Home