Rule Based Optimizer   «Prev  Next»

Rule-Based Optimizer[Oracle] - Exercise

Overview of the rule-based optimizer

Basic rule-based optimizer concepts

Objective: Correctly order the rule-based optimizer hierarchy and process steps.

Instructions

Below are two sets of items presented out of order. The first is the rule hierarchy used by the rule-based optimizer and the second is the typical process that the rule-based optimizer uses. For each list, drag the steps into the correct order, with the first step at the top of the list.
When you think you have the steps in the correct order, click Done.
You will be told whether or not the order is correct. If you didn't get the order right, keep trying.
If you get stuck, try clicking Hint. The steps you have in the correct place will have green lights next to them. The steps that are not in the correct place will have red lights next to them.
Be sure you have ordered both lists before you click the Submit button.

Exercise Scoring

You will receive five points for this exercise. The exercise is auto-scored; when you have completed the exercise, click the OK, I'm Done button to receive full credit.
  1. These are some of the rules in the rule hierarchy.
    Type them in the correct order with the fastest access method on the top and the slowest on the bottom.

    1. Single row by rowid
    2. Cluster join
    3. Hash cluster key
    4. Composite key
    5. Single column indexes
    6. Bounded range search on indexed columns
    7. ORDER BY on indexed columns
    8. Full-table scan

  2. Here are the steps that the rule-based optimizer follows when it chooses an access path. Put them in the correct order.

  1. Parses the SQL syntax
  2. Examines FROM clause
  3. Establishes the driving table
  4. Examines the WHERE clause
  5. Chooses the access path