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.