Diccionario de Oracle (II) - USER_INDEXES y Indices basados en función

SELECT * FROM DICT WHERE TABLE_NAME LIKE '%INDEX%'



Entre otros, vemos:

• USER_INDEXES Description of the user's own indexes
• ALL_INDEXES Descriptions of indexes on tables accessible to the user

SELECT * FROM USER_INDEXES

Podemos ver información sobre los índices, entre otros:
INDEX_NAME: nombre del indice
TABLE_NAME: Tabla indexada
STATUS: Si es valido o no
LAST_ANALYZED: Fecha en que se analizo por última vez.
FUNCIDX_STATUS: ENABLED o DISABLED. Indica si la funcion en que se basa un índice esta correctamente compilada o no.


Si tenemos un índice basado en función, eso es,
un índice que en su definición contenga la llamada a una función, por ej:

CREATE INDEX indx_mes ON mi_tabla(
TO_CHAR(fecha,'MM'));

CREATE INDEX indx_nif ON mi_tabla (
PQT_UTILIDADES.FNT_NORMALIZA_NIF(nif));

En el primer caso no vamos a tener ningún problema ya que es una función de Oracle (To_Char), pero en el segundo caso, es una función definida por nosotros en un paquete PL/SQL que podría quedar descompilado.

Eso lo podríamos ver con esta Query:

SELECT * FROM USER_INDEXES
WHERE INDEX_TYPE ='FUNCTION-BASED NORMAL'
AND FUNCIDX_STATUS ='DISABLED'

Si esto pasa, el índice se tiene que volver a crear, o bien con el código CREATE INDEX o bien con la sentencia REBUILD:

ALTER INDEX indx_nif REBUILD;

Tuvimos un problema con un índice basado en función.

Era una función que estaba dentro de un paquete con muchas otras funciones,
Siempre que modificábamos cualquier función (aunque no fuera la que utilizaba el índice), este quedaba descompilado.

Por eso es recomendable tener la función a parte.

Si modificas la función, obviamente tendrás que reconstruir el índice,
pero no te pasará que modificando cualquier función del paquete dónde se encuentra la utilizada por el índice, este deje de funcionar.