Choice to create composite index

Whenever you create index on multiple columns in a table then that index is called composite index.

Advantage of Using Composite Index:
----------------------------------------------------------------
Improved selectivity: As we all know index is created for the column that has higher selectivity means there are a few duplicate column values of the indexing columns. Sometimes two or more columns each with poor selectivity, can be combined to form a composite index with higher selectivity.

Reduced I/O: If composite index is created with all the columns selected by the query, then oracle can return the values from the index itself instead of accessing values from the table using ROWID scan unlike single key index.

To know more about ROWID scan and explain plan search within my blog.

Composite Key Usage in a Query
----------------------------------------------------------
It is important to maintain order of the index while creating composite key index. For example create index on column(x,y) is not same as of create index on column(y,x). So while creating composite index take special care of the order of the columns inside index. More specifically, 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. Order of columns inside index creation make the leading constructs.

An example will make you clear.
If I create index as

CREATE INDEX comp_indx ON tabl(a, b, c);
Then,
a, ab, and abc combinations of columns in where clause will use the index.
bc, b, and c combinations of columns in where clause will not use the composite index.

Th ordering of keys of composite index is given below.

Ordering Keys for Composite Indexes
-----------------------------------------------------------
•Create the composite index so the keys used in WHERE clauses make up a leading portion.

•If some keys are used in WHERE clauses more frequently, then while creating composite index make sure 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.


Choosing Keys for Composite Indexes
----------------------------------------------------------------------
•Consider to create of composite index on columns that is used frequently in WHERE clause condition combined with AND operation.

•If several queries select a combination of columns based on one or more columns then consider to create composite index based on these columns.