Reconstruir indices en oracle

Un índice en oracle normalmente se guarda en una estructura de árbol (B-tree), digo normalmente porque en algunos casos este se guarda en un Bitmap pero se lo tienes que indicar tu cuando lo creas. En esta entrada hablaremos de los índices por defecto, los que se guardan en árbol.

En cada nodo de este árbol se encuentra la información del campo que a su vez es índice y su rowid, no se guarda información de toda la fila solo que fila es (rowid).

Cuando actualizamos y borramos el nodo no se destruye por lo que aunque no crezca la tabla el árbol del índice seguirá creciendo, por eso la importancia de reconstruir el índice.

¿Cuando reconstruir el índice?

Las estadísticas de la BBDD nos lo dirá, lo primero es ver si están actualizadas

-- Donde nb_usuario es el nombre del esquema del usuario ('SYS')

SELECT index_name, last_analyzed
FROM dba_indexed 
WHERE table_owner='nb_usuario'

Para actualizar la tabla lanzamos el siguiente código

-- Esquema es el nombre de nuestro esquema esquema  ('SYS')

Execute DBMS_STATS.gather_schema_stats('Esquema');

Una vez actualizadas las estadísticas lanzamos la siguiente consulta que nos devolverá si tenemos que reconstruir nuestro índice

-- Sustituimos Porpietario por el esquema o propietario que queramos verificar ('SYS')

SELECT index_name, blevel, 

  decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL',2, 
  'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OK 
FROM dba_indexes where table_owner='Propietario';

Si para el indice en cuestión que estamos viendo si tenemos que reconstruir en la columna "OK" aparece BLEVEL HIGH deberemos reconstruir ese indice.

Este nivel indica el número de veces que ORACLE ha tenido que reducir la búsqueda en ese indice. Si el valor esta por encima de 4 debería ser reconstruido.

Reconstruir el índice:

ALTER INDEX REBUILD

Debes tener en cuenta que para realizar la reconstrucción del índice deberíamos tener una cuota suficiente sobre el tablespace.

Para reconstruir una partición de un índice

ALTER INDEX REBUILD PARTITION NOLOGGING;

Hay veces que los índices están corruptos y no nos deja reconstruirlos, entonces la solución seria borrarlo y volver a crearlo