Oracle makes it possible to create an index with more than one index column. These are known as concatenated or composite indexes, because multiple columns are linked together in a series in a single index B-tree. Concatenated indexes are very useful in cases where multiple criteria are used to access a table. To illustrate, consider the following index definition:
Create index
Student_idx
On
student
(
student_level,
major,
last_name
);
Multi-key Indexes
In these multi-key indexes, Oracle will create each index node to contain all of the keys. When we issue a query that uses the high-order index column in the WHERE clause, Oracle uses this index to speed access to the table rows.
Let us examine the examples below:
Select
Student_name
From
Student
Where
Student_level = 'Freshman'
And
Birthdate > to_date('1980', 'YYYY');
This query can use the concatenated index because the high-order key of student_level is referenced in the query.
Select
Student_name
From
Student
Where
Student_level = 'Freshman'
And
Major = 'Computer Science'
;
The index is also used because both of the high-order keys are present in the index.
In this query the index is also used because both of the high-order keys are present in the index.
Select
Student_name
From
Student
Where
Major = 'Computer Science'
And
Birthdate > to_date('1990', 'YYYY')
;
The index is NOT used because the student_level is not referenced in the WHERE clause
In this query, the index is NOT used because the student_level is not referenced in the WHERE clause.
To summarize, the concatenated index is very useful when your tables normally have multiple column specifications in the SQL queries.
When defining a concatenated index, you should place the column with the most distinct values first. For example, if you were creating a concatenated index on state_name (50 distinct values) and customer_status (10 distinct values), you should define state_name first in the index. The next lesson examines the function-based indexes of Oracle8.
Appropriate Oracle Indexes
Before you move on to the next lesson, click the link below to check your knowledge of Oracle indexing options. Appropriate Oracle Indexes