SQL Guide : Tuning performance with SQL : Using indexes to improve query performance : Concatenated indexes
  
Concatenated indexes
An index can be made up of more than one column. Such an index is called a concatenated index. We recommend using concatenated indexes when possible.
Whether or not an SQL statement uses a concatenated index is determined by the columns contained in the WHERE clause of the SQL statement. A query can use a concatenated index if it references a leading portion of the index in the WHERE clause. A leading portion of an index refers to the first column or columns specified in the CREATE INDEX statement.
Example:
CREATE INDEX job_sal_deptno ON emp(job, sal, deptno);
This index can be used by these queries:
SELECT * FROM emp WHERE job = 'clerk' and sal =
800 and deptno = 20;
SELECT * FROM emp WHERE sal = 1250 and job = salesman;
SELECT job, sal FROM emp WHERE job = 'manager';
The following query does not contain the first column of the index in its WHERE clause and therefore cannot use the index:
SELECT * FROM emp WHERE sal = 6000;
Choosing columns to index
The following list gives guidelines in choosing columns to index:
You should create indexes on columns that are used frequently in WHERE clauses.
You should create indexes on columns that are used frequently to join tables.
You should create indexes on columns that are used frequently in ORDER BY clauses.
You should create indexes on columns that have few of the same values or unique values in the table.
You should not create indexes on small tables (tables that use only a few blocks) because a full table scan may be faster than an indexed query.
If possible, choose a primary key that orders the rows in the most appropriate order.
If only one column of the concatenated index is used frequently in WHERE clauses, place that column first in the CREATE INDEX statement.
If more than one column in a concatenated index is used frequently in WHERE clauses, place the most selective column first in the CREATE INDEX statement.
See also
Using indexes to improve query performance