RBO secret #3

If multiple indexes within the specification of a WHERE clause, and all have the same number of columns, the index will be used as the last set.

For example:

SELECT col1, ...
FROM emp
WHERE emp_name = 'Gurry'
AND emp_no = 127
AND dept_no = 12
AND emp_category = 'CLERK';
Indexl (emp_name, emp_category) 6.00 Created 11 Feb 2002
Index2 (emp_no, dept_no) 7.00 Created 11 Feb 2002

In the example above, will be used only Index2 because it was created at 7.00, while the second index at 6.00.

Optimizer can cause some problems because after rebuilding indexes amended in relation to the initial order may suddenly find that to execute a query using a different index. To work around this problem in a number of centers introduced index naming standard, which requires that, to the extent of their creation give them names in alphabetical order. Then, if the table is rebuilt, the indexes can be rebuilt in alphabetical order to preserve the correct order of creation. Indexes can also be numbered. In this case, each newly added to the index table will be named the next number.