Guidelines for creating efficient indexes for your Oracle tables:
Here is some guidelines to help developers/DBA when creating indexes

• Index only if you need to access no more than 10 or 15 percent of the data in a table. The alternative to using an index to access row data in a table is to read the entire table sequentially from top to bottom, which is called a full table scan. Full table scans are better for queries that require a high percentage of the data in a table. Remember that using indexes to retrieve rows requires two reads: an index read followed by a table read.

• Avoid indexes on relatively small tables. Full table scans are just fine for small tables. There’s no need to store both table and index data for small tables.

• Create primary keys for all tables. When you designate a column as a primary key, Oracle automatically creates an index on the column.

• Index the columns that are involved in multi-table join operations.

• Index columns that are used frequently in WHERE clauses.

• Index the columns that are involved in ORDER BY and GROUP BY operations, or other operations, such as UNION and DISTINCT, that involve sorting. Because indexes are already sorted, the sorting necessary to perform the previously mentioned operations will be considerably reduced.

• Columns that consist of long character strings are usually poor candidates for indexing.

• Columns that are frequently updated should ideally not be indexed because of the overhead involved.

• Index tables with high selectivity only. That is, choose to index tables where few rows have similar values.

• Keep the number of indexes small.

• Composite indexes may need to be used where single-column values may not be unique by themselves. In composite indexes, the driving or the first column should be the most selective column.