Identificación de Índices inutilizados

Esto se logra con el comando ALTER INDEX MONITORING USAGE

Esto es de mucha ayuda ya que nos ayuda con los siguientes problemas.
- Consumen espacio que puede ser muy necesario.
- Degrada el rendimiento a causa de una innecesaria sobre carga durante operaciones DML.

La información que aquí se presenta es en base a notas de metalink 136642.

¿Cómo identificamos un Índice que no se está utilizando?.
1.- Hay que poner el índice bajo monitoreo.
SQL> alter index I_EMP monitoring usage;
Index altered.

SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP YES NO 03/14/2001 09:17:19

2.- Checar si el índice está usando la columna USED en V$OBJECT_USAGE
SQL> select sal from emp where ename='SMITH';
SAL
----------
800

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'I_EMP' (NON-UNIQUE)

The explain plan indicates the index is used.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP YES YES 03/14/2001 09:17:19

Si el índice no está en uso entonces podremos borrar el índice.

3.- Para detener el monitoreo en algún indice lo hacemos con el comando siguiente.
SQL> alter index i_emp nomonitoring usage; Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
--------------- --------------- --- ---- ------------------- -------------------
I_EMP EMP NO YES 03/14/2001 09:17:19 03/14/2001 09:55:24

Una vez que se active de nuevo el monitoreo la columna la columna de MONITORING y USAGE se vuelven a limpiar.