Utilizacao dos Indices

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