amar on web

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.

Type of joins considered by optimizer:
Nest loop join
Sort merge join
Cluster join
Hash join

Bottom line, it becomes all the more important to use explain plan tools to find out how oracle is behaving in RBO. The behaviour is consistent (unlike CBO), unless you plan to introduce new indexes on existing tables.

Courtesy
www.assist.com
www.odtug.com
www.ixora.com
Oracle 8i concepts- Optimisation of Joins (chpt24)

Best viewed in medium text size. Please refresh this page (F5) to view the latest information.
This page was create on 15-dec-2000 and last updated on 15-dec-2000.
please forward all queries to amar_padhi@fastmail.fm