Indexes are one of the most powerful  tool and technique in database querying and operations. Here are some tips on the same picked up from expereince and as suggested by Mr Keshav Tripathy.

Some Unstructured Tips and Tricks Useful Statement: Most books and product documentation are set up like reference materials. Training courses focus on the fundamental aspects of products in a limited time Hands-on experience is one of the best ways to learn tricks to maximize your use of Oracle. Good Things About Indexes is that these are the easiest way to Improve Performance. Indexes improves all types of queries: Not only Select Queries But Also Updates & Deletes. Indexes also help in enforcing referential integrity : Primary/Unique Foreign Keys Also Get Benefits of Indexes… How…? When a child row is inserted (or updated), the parent key must be verified. By placing and index on the column of the key in the parent table, the database can use the index to validate the child. Otherwise, a full table scan of the parent table is required to validate the child.

Bad Things About Indexes is that Finding Most Efficient Index is Hard Task :As Most of the Indexes will be of B-Tree Type : (Default) If our query uses a function in the where clause, the Database will not use a B-Tree index. For that we need a Function Based Index (FBI). A FBI creates the index by applying the function to each value. This allows the database to make direct comparisons with the function in the WHERE clause. Problems of Function Based Indexes FBIs pay the penalty of having to execute the function for each change in the index, and the RULE based optimizer cannot use FBIs .Bit Map Indexes : Computer CPUs are incredibly fast at comparing bits. By having a bit for each value in the index, Oracle can create a mask of the value wanted and OR down the bit mapped index to find the values that match. Bit mapped indexes are very fast. Problems in Bit Map Index First, to work efficiently, the index values are reduced to bits. The width of the bitmap index is basically the number of values in the index. Thus the indexed column needs to be of low cardinality (few distinct values). Good to be used in Read Only Kind of Environment and Data warehouse implementations. IS More Index is Good or Bad ??

Every index added to a database will have a performance impact on INSERTs, UPDATEs and DELETEs to the underlying table. This is because the database must update the indexes when ever the underlying tables data changes. If a table has 6 indexes and you insert a row, the table and all six indexes will have to be updated. Thus adding indexes is always a trade off between SELECT performance and DML performance.

Indexes are Ugly

Too many Indexes will also slow down performance of updates and inserts. Too few and all types of queries may run slower (even updates and deletes). If it were just a trade off, performance of updates and inserts verses performance of selects, optimizing Indexes in a database would be fairly easy. However, it is not that simple.

First is the question of is the index being used? If it is used, is it the correct index? Is there a better index? How can a DBA with a database choking on indexes, focus his efforts to optimize index usage?

Experience :DBA can use to first determine the indexes that are not being used and second to optimize the indexes on the system. This methodology will move the system closer to being index optimized. The question is not just “are my indexes being used?” but “do I have the right indexes on the right data to optimize my database ?”Locating Excess Indexes - The Plan Finding and removing indexes that are not used will improve database performance by removing the maintenance overhead. However, finding and removing duplicate indexes can have just as great an effect if not more. Step # 1. Locate All Unused Indexes & Remove them. Step # 2. Locate possible duplicate indexes and Remove/Modify to meet the existing SQL.Step # 3. Identifying tables/indexes that are candidates for Function Based Indexes (FBI). How to Find Unused Indexes Till Oracle 8i is difficult as dba_indexes view does not have a Flag to Check the Index Usage. Look into the Caches(Buffer Cache or Library Cache) The basic steps are to retrieve all the sql currently in the cache, explain each statement into the PLAN_TABLE, and then query the PLAN_TABLE for index names. Monitor index usage daily, weekly and Monthly and act accordingly.(Dropping or Recreate) Oracle9i and 10g : Easy to know the unused index : The db will monitor for index use and update a view called v$object_usage. When you turn monitoring on with: alter index monitoring usage; The database begins monitoring the index and updates the USED column when the index is first used. When you want to stop monitoring simply execute:alter index nomonitoring usage;

Oracle 10g :AWR It captures index use and can get used for time series analysis. Thank you very much. I would love to recollect my old association with a fantastic product like Oracle and would like to share something which I used to do at work but may be much more improved functionalities have come which must be making life more easy and cool.