RBO secret #1

Only single-column indexes are merged. Consider the following SQL statement and indexes:

SELECT col1, ...
FROM emp
WHERE emp_name = 'Gurry'
AND emp_no = 127
AND dept_no = 12;
Indexl (dept_no)
Index2 (emp_no, emp_name)

The SELECT statement through all three columns with an index. Many people think that in order to return the requested data, Oracle merges the two indexes, and thus indirectly all three columns. In reality, however, is only used two-column index, and a single-column index remains untapped. Oracle Single Column scaliłby two indexes, but not merge with another multi-column index.

Table 1. Conditions scheduling rule-based optimizer

Rank | Condition

1.  ROWID '= constant
2. Merge two clusters (cluster join) with a unique key or key = constant
3. Key hashed cluster (hash cluster) with a unique key or key = constant
4. whole unique concatenated index = constant
5. column with a unique index = constant
6. whole cluster key = corresponding cluster key in another table in the same cluster
7. Key cluster hashed non-unique = constant
8. whole cluster key = constant
9. All non-unique concatenated index = constant
10. Merge non-unique index
11. The whole concatenated index = lower limit
12. Most of the leading columns of a unique index = constant
13. Indexed column between the bottom and the top value or - indexed column LIKE "ABC%" (limited range) - Indexed
14. non-unique column between the bottom and the top value or indexed column LIKE "ABC%" (limited range)
15. unique indexed column or a fixed value (unlimited range)
16. non-unique indexed column or a fixed value (unlimited range)
17. Equality non-indexed columns = column or the value of the constant (join by sorting and merging)
18. MIN MAX or individual columns in indexes
19. ORDER BY the index
20. Inspection of the entire table

ROWID - row identifier, identifies each row by its location or address

In view of the above comments - be aware of one thing. If the index is a single-column index or a unique primary key, makes it. One column that the index takes precedence over multicolumn index. Just compare the rank of 4th with the rank of 9th of Table 1

NOTE

For Oracle8i introduced a new indication index_ JOIN, which allows you to merge multi-column indexes.