RBO secret #7

If the WHERE clause appears in the column, which is leading a column index, a rule-based optimizer uses just this index. The exception is when the WHERE clause in the leading index column impose a specific function.

For example:

SELECT col1. . .
FROM emp
WHERE emp_name = 'Gurry';
Indexl (emp_name, emp_class, emp_category)
Index2 (emp_class, emp_name, emp_category)

Indexl is used as emp_name (occurring in the WHERE clause) is the leading column. Index2 will not be used, because it is not emp_name leading his column.

The following example shows a situation where the indexed column to use the function:

SELECT col1, ...
FROM emp
WHERE LTRIM (emp_name) = 'Gurry';

In this case - because of the use of functions LTRIM - do not use one index.