When choosing an index, keep in mind this basic rule: the indexing strategy should be based on the columns you use when querying the table. You can create more than one index on a table and have an index contain multiple columns. You will make better decisions if you first consider what types of queries you execute on a table. If you have identified a poorly performing SQL query, also consider creating indexes for the following columns:

1. Create indexes on columns used often as predicates in the WHERE clause; when multiple columns from a table are used in the WHERE clause, consider using a concatenated (multi-column) index.
2. Create a covering index on columns used in the SELECT clause.
3. Consider creating indexes on columns used in the ORDER BY, GROUP BY, UNION, or DISTINCT clauses.

Oracle allows you to create an index that contains more than one column. Multicolumn indexes are known as concatenated indexes (sometimes referred to as composite indexes). These indexes are especially effective when you often use multiple columns in the WHERE clause when accessing a table. Concatenated indexes are often times more efficient in this situation than creating separate single column indexes.
Columns included in the SELECT and WHERE clauses are also potential candidates for indexes. Recall that a covering index is one that includes all columns returned by the query. In this situation, Oracle can use the index structure itself (and not the table) to satisfy the results of the query. Also, if the column values are selective enough, Oracle can use an index on columns referenced in the WHERE clause to improve query performance. Also consider creating indexes on columns used in the ORDER BY, GROUP BY, UNION, or DISTINCT clauses. This may result in more efficient queries that frequently use these SQL constructs.