How to choose Composite Indexes ?

How to choose Composite Indexes ?

A composite index contains more than one key column. Composite indexes can provide additional advantages over single column indexes.

Better Selectivity	Sometimes two or more columns, each with poor selectivity, can be combined to form a composite index with good selectivity.
Adding Data Storage	If all the columns selected by the query are in the composite index, Oracle can return these values from the index without accessing the table. However in this case, it's better to use an IOT (Index Only Table).

An SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index. 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 idx_composite ON mytab (x, y, z);

These combinations of columns are leading portions of the index: X, XY, and XYZ. These combinations of columns are not leading portions of the index: YZ and Z.

• Guidelines for choosing columns for composite indexes

Consider creating a composite index on columns that are frequently used together in WHERE clause conditions combined with AND operators, especially if their combined selectivity is better than the selectivity of either column individually. Consider indexing columns that are used frequently to join tables in SQL statements.