Indexes

There are several types of indexes available with Oracle databases:

• normal/B-Tree (balanced/ binary tree) indexes;
• bitmap indexes (which literally use a map of bits to represent the whole index 
• function-based indexes (which are based on the value of an expression instead of the value of a column);
• partitioned indexes (tables and indexes may be partitioned separately)
• domain indexes (application-specific indexes created on complex data types such as spatial data).

• B-Tree Indexes

  B-Tree Indexes are most appropriate when retrieving a small amount of data from a very large table. The bigger the table and the lower the number of rows that you want to retrieve then the more effective the index.
  
  Indexes may improve read access to data and update operations (including insert and delete) using sub-queries. However update performance will be impaired because every time a row is inserted into, deleted from or updated (if the indexed column(s) is/are updated) in the database, the indexes have to be adjusted accordingly, thereby increasing the amount of i/o that has to performed.
  
  Therefore having many indexes is ideal when the database is mostly read only, but if there are a high proportion of inserts and deletes and only a few read operations, then you would find that adding more indexes would degrade rather than improve performance. You also need to ensure that the column(s) you are proposing to index is/are used frequently as the limiting conditions for a query, if not you will waste a lot of space and degrade database performance.
  
  Another factor to consider with b-tree indexes in Oracle databases, is how specific they are - their cardinality. The ideal b-tree index is one that refers to only one row in the table, as a primary key does by definition. This reduces considerably the amount of i/o required to retrieve the data, but if the index value relates to many rows because it is not selective, then a lot more data will be retrieved resulting in unnecessary i/o.
  
  
  

  Concatenated Indexes vs. Multiple Single Column Indexes in Oracle databases 

  As mentioned before the ideal index will refer to just one row in the table and the same rule applies to concatenated indexes. With concatenated indexes, therefore, the column that is used most frequently in the limiting conditions of queries should be the most selective and should be the leading column in the concatenated index because this will have the most impact on the biggest number of queries.
  
  
  If the leading column of a concatenated index is not the most selective and not the one used the most often in queries, then the application should be redesigned to ensure that this is the case to avoid performance problems or the index should be replaced with a better one.
  If the leading column is the most selective and the most frequently used in the limiting conditions then the second and subsequent columns of the index will be of most benefit if they are the next most selective and the next most frequently queried. Assuming that these other columns are frequently referenced together with the leading column, there will be significant performance advantages by creating a concatenated index instead of two separate indexes, because Oracle will have to perform less i/o and won't need to merge the results from the two separate index reads, resulting in less filtering.
  

  Bitmap Indexes 

  If your Oracle application is designed to query on non-selective columns (i.e. those with low cardinality), then you should consider the use of bitmap indexes, but onlywhen the data is infrequently updated as bitmap indexes add a considerable overhead to update operations - each new value of the indexed column will require a whole new bitmap to be created and deletes and updates of the indexed column will also require the bitmaps to be adjusted, as there is one bitmap for each distinct value in the column.
  
  Another disadvantage of bit-map indexes in a transaction processing environment is that each update would lock a large number of rows, degrading throughput even further. Where bitmap indexes are most valuable is in a data warehouse where non selective columns in large tables are frequently queried and where little or no updating is done.
  

  Summary

  In summary, to obtain maximum performance from your Oracle databases you need to consider how much data will be retrieved from each of the tables; how selective the indexes are; and how frequently the indexed columns are queried compared to the frequency of update of the same columns before deciding whether or not to use an index. For b-tree indexes, the fewer rows being selected by the query, the bigger the performance increase would be.
  Generally if you're reading a small percentage (1-10%) of a very large table, your Oracle database will have much better performance if the data is accessed via an index scan of a b-tree index followed by table lookup, rather than a full table scan. If a query will access more than 1-10% of the table then a full table scan may produce better performance but you would need to test this under real-world conditions. One last thing to remember is that the more indexes there are then the longer updates and inserts will take.
  
  Original Source- http://www.smart-soft.co.uk/Oracle/oracle-performance-tuning-part2_1.htm