An index can be made up of more than one column. Such an index is called a concatenated index. It is recommended to use 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.
For example, if you create the following index:
CREATE INDEX job_sal_deptno ON emp(job, sal, deptno);
the index can be used by the following 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
Use the following guidelines to choose the columns to index:
▪ Create indexes on columns that are used frequently in WHERE clauses.
▪ Create indexes on columns that are used frequently to join tables.
▪ Create indexes on columns that are used frequently in ORDER BY clauses.
▪ Create indexes on columns that have unique (or mostly unique) values in the table.
▪ Do not create indexes on small tables (tables that use only a few blocks) because a full table scan might 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.