Remote DBA Plans
Remote DBA Service

Tuning with Rule-Based Optimization

Oracle Tips by Burleson Consulting

The rule-based optimizer is the oldest and most stable of the optimizers. The rule-based optimizer is very simple and uses information in the data dictionary to make decisions about using an index. Also, unlike the cost-based optimizer, the order of tables in the FROM clause and the order of Booleans in the WHERE clause affect the execution plan for the query.

Changing the Rule-Based Driving Table

In Oracle's rule-based optimizer, the ordering of the table names in the FROM clause determines the driving table. The driving table is important because it is retrieved first, and the rows from the second table are then merged into the result set from the first table. Therefore, it is essential that the second table return the least amount of rows based on the WHERE clause.

NOTE: The driving table is not always the table with the least amount of rows. The Boolean conditions in the WHERE clause must be evaluated, and the driving table should be the table that returns the smallest number of rows.

With the rule-based optimizer, the table names are read from right to left. Hence, the last table in the FROM clause should be the table that returns the smallest number of rows. For setting the driving table for the rule-based optimizer, consider the following query where the order table has 100,000 rows and the customer table has 50,000 rows.

L 11-5

SELECT
   customer_name,
   customer_phone
FROM
   Customer,
   Order
WHERE
   customer_region = 'WEST'
and
   order_status = 'BACKORDER';

In this query, we see that the last table in the FROM clause is the order table, and order will be the driving table. This might make sense since we know that this table has half the rows of the customer table. However, we must first evaluate the WHERE clause to see what table returns the smallest number of rows.

Let's assume that there are 10,000 customers in the WEST region and 30,000 backordered status columns. Given this information, we know that the customer table should be last in the FROM clause because it returns fewer rows.

When the Rule-Based Optimizer Fails to Use the Correct Index

The rule-based optimizer's greatest shortcoming is its failure to use the best index. There are cases where the rule-based optimizer fails to choose the best index to service a query because it is not aware of the number of distinct values in an index. This is especially a problem when values within an index are highly skewed.

For example, let's assume in this example that there are 100,000 retired employees, 20,000 employees in the personnel department, and 500 who are both retired and belong to the personnel department. Let's also assume that we have a nonunique index on both the status and the department columns of our employee table.

We would expect that the most efficient way to service this query would be to scan the most selective index—in this case the department index, scanning the 20,000 retired employees to get the 500 in the personnel department. It would be far less efficient to scan the status index, reading through 100,000 retired employees to find those who work in the personnel department.

L 11-6

SELECT
    COUNT(*)
FROM
    employee
WHERE
   department = 'PERSONNEL'
AND
   status = 'RETIRED';

With the rule-based optimizer, we see the following execution plan:

L 11-7

SELECT STATEMENT
    SORT AGGREGATE
        SELECT BY ROWID EMPLOYEE
            NON-UNIQUE INDEX NON-SELECTIVE RANGE SCAN status_ix(status)

Even reversing the order of the items in the WHERE clause does not change the fact that the rule-based optimizer is choosing to scan through all 100,000 retired employees looking for the 500 that belong to the personnel department. With a cost-based optimizer, we see that the selectivity of the indexes is known and that the most efficient index is used to service the request:

L 11-8

SELECT STATEMENT
    SORT AGGREGATE
        SELECT BY ROWID EMPLOYEE
            NON-UNIQUE INDEX NON-SELECTIVE RANGE SCAN dept_ix(department)

In sum, we need to pay careful attention to the indexes that are chosen by the rule-based optimizer, and either disable the indexes that we do not want used in the query or force the use of the index that we want. To review, indexes can be explicitly specified with the INDEX hint, or unwanted indexes can be disabled by mixing datatypes on the index (i.e., WHERE numeric_column_value = 123||' ‘), or by specifying a FULL hint in the query.

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.