Automatic Index Statistics Gathering and Minimizing Over Indexing

In order to gather statistics on an index we may use the Oracle supplied package DBMS_STATS:


SQL> Execute DBMS_STATS.GATHER_INDEX_STATS (‘HR’,’LOC_COUNTRY_IX’);
    

or gather statistics at index creation time:


SQL> Create index hr.loc_country_ix … compute statistics;
    

or gathering statistics when rebuilding an index:


SQL> Alter index hr.loc_country_ix rebuild compute statistics;
    

But after 10g even you do not mention to collect, statistics are collected by default;

Code listing 66a : Automatic Index Statistics Gathering with 10g Example

Indexing strategies are very important for OLTP applications. But over indexing affects DML operations. Oracle, if possible uses an already created index for primary or unique keys to minimize over indexing;

Code listing 66b : Minimizing Over Indexing Example

Also you may identify unused indexes beginning with Oracle9i. Statistics about the usage of an index can be gathered and displayed in V$OBJECT_USAGE. If the information gathered indicates that an index is never used after some time, the index can be dropped. Each time the MONITORING USAGE clause is specified, V$OBJECT_USAGE is reset for the specified index. The previous information is cleared, and a new start time is recorded;

Code listing 66c : Minimizing Over Indexing with Index Monitoring and V$OBJECT_USAGE

Testing Information : the scripts mentioned are tested on Oracle Database 10g Express Edition Release 10.2.0.1.0

Refences Used :
Oracle® Database Performance Tuning Guide 10g Release 2 (10.2)-Chapter 2 Designing and Developing for Performance