Um problema grave nas empresas é que geralmente se criam muitos indices, para evitar isso monitore de tempos em tempos a utilizacao dos mesmos:

SQL
> drop table test purge;Table dropped.
SQL> create table test as select * from all_objects;Table created.
SQL> create index testidx on test(object_id);Index created.
SQL> –now enable monitoring on index
SQL> alter index TESTIDX MONITORING usage;Index altered.
SQL> —execute query where index is created.
SQL> select count(*) from test where object_id=77204;COUNT(*)
———-
1
SQL>—disable index mornitoring
SQL> alter index TESTIDX NOmonitoring usage;Index altered.
SQL> —now check the V$OBJECT_USAGE for index utilization. if we found nothing then drop index is better option
SQL> select * from v$object_usage;INDEX_NAME TABLE_NAME MON USED START_MONITORIN END_MONITORING
———- ———- — —– —————
—————
TESTIDX TEST NO YES 07/31/2008 19:51:20
07/31/2008 19:52:04