Index Techniques   «Prev  Next»
Lesson 7Using concatenated indexes
ObjectiveCreate aConcatenated Index

Concatenated indexes in Oracle

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