Rule Based Optimizer   «Prev  Next»

Lesson 4Deficiencies of rule-based SQL optimization
ObjectiveList the disadvantages of the rule-based optimizer.

Disadvantages of rule-based SQL optimization

As we discussed, there are cases where the rule-based optimizer fails. This is most common in cases where a table has many indexes and the rule-based optimizer fails to choose the best index to service a query.
This generally happens because the rule-based optimizer is not aware of the number of distinct values in an index and the distribution of values within the index. Let us illustrate this point with an example shown in the following Slide Show.
1) Optimization Fail Example 1 2) Optimization Fail Example 2 3) Optimization Fail Example 3 4) Optimization Fail Example 4 5) Optimization Fail Example 5 6) Optimization Fail Example 6
So what can we do about the problem shown in the FlipBook?

Possible solutions

There are several remedies:
  1. Add an index hint: 
    SELECT /*+ INDEX dept_ix */
  2. Add a cost-based hint: 
     
SELECT /*+ ALL_ROWS */
  3. Invalidate the STATUS index.

Invalidating an index is a common trick that is used when the rule-based optimizer makes a bad choice of indexes.

Disabling unwanted indexes

Unwanted indexes can be disabled by mixing data type on the index. For example, we know that the status field is a numeric index, so we could concatenate a null character value to the predicate to invalidate it. Oracle will see the data type mismatch, and bypass that index. In the example below, we have concatenated a null character “” to the status predicate:

SELECT
Emp_name
FROM
employee
WHERE
department = ‘FINANCE’
AND
status = 3||0;

Now let us look at tricks for repositioning items in the FROM clause to improve the speed of rule-based SQL queries.