Detectando índices sin utilizar

Tener índices sin usar en la base de datos puede quitarnos espacio necesario y reducir el performance para los update o deletes, por eso es importante eliminar aquellos que no son utilizados.

ALTER INDEX...MONITORING USAGE permite habilitar un monitoreo de índices.

El reporte de los índices puede verse en la vista V$OBJECT_USAGE.

select * from v$object_usage;


INDEX_NAME  TABLE_NAME  MONITORING USED START_MONITORING    END_MONITORING  
----------- ----------- ---------- ---- ------------------- -------------------
INDICE1     TABLA1      YES        NO   5/06/2014 10:59:41                   

Para saber si es utilizado, solo es necesario ver que la columna USED contenga "YES". Les recomiendo revisarlo por lo menos una semana después de ser habilitado para no eliminar algun índice que se utilice una ves a la semana, incluso un mes después sería mas recomendable.

Para habilitar el monitore de todos los índices, pueden ejecutar el siguiente SQL que generará el script.

select 'alter index ' || index_name || ' monitoring usage;'
from user_indexes;

Es importante saber que la vista V$OBJECT_USAGE solo muestra información del usuario que esta ejecutandolo, por lo que si se requiere consultarlo para todos puede ejecutarse el siguiente query con permisos de DBA.

select io.name INDEX_NAME, t.name TABLE_NAME,
       decode(bitand(i.flags, 65536), 0, 'NO', 'YES') MONITORING,
       decode(bitand(ou.flags, 1), 0, 'NO', 'YES') USED,
       ou.start_monitoring,
       ou.end_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#;