Choosing Composite Indexes
A composite index contains more than one key column. Composite indexes can provide additional advantages over single-column indexes:
Sometimes two or more columns or expressions, each with poor selectivity, can be combined to form a composite index with higher selectivity.
If all columns selected by a query are in a composite index, then Oracle can return these values from the index without accessing the table.
A SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index.
Note:This is no longer the case with index skip scans. See "Index Skip Scans".
A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index. Consider this CREATE INDEX statement:
CREATE INDEX comp_ind
ON table1(x, y, z);
*x, xy, and xyz combinations of columns are leading portions of the index
*yz, y, and z combinations of columns are not leading portions of the index
Choosing Keys for Composite Indexes
Follow these guidelines for choosing keys for composite indexes:
*Consider creating a composite index on keys that are used together frequently in WHERE clause conditions combined with AND operators, especially if their combined selectivity is better than the selectivity of either key individually.
*If several queries select the same set of keys based on one or more key values, then consider creating a composite index containing all of these keys.
Of course, consider the guidelines associated with the general performance advantages and trade-offs of indexes described in the previous sections.
Ordering Keys for Composite Indexes
Follow these guidelines for ordering keys in composite indexes:
*Create the index so the keys used in WHERE clauses make up a leading portion.
*If some keys are used in WHERE clauses more frequently, then be sure to create the index so that the more frequently selected keys make up a leading portion to allow the statements that use only these keys to use the index.
*If all keys are used in the WHERE clauses equally often but the data is physically ordered on one of the keys, then place that key first in the composite index.