Optimización de Índices: De Reverse a B-Tree en Entornos Particionados

En administración de bases de datos Oracle, no todos los índices se comportan igual ante un cambio de estructura. Recientemente me enfrenté a la necesidad de transformar un índice Reverse Key a un índice Normal (B-Tree).

El matiz: ¿Global o Local?

Si estuviéramos ante un índice Global, la solución sería inmediata y elegante: ALTER INDEX IDX_GLOBAL REBUILD ONLINE NOREVERSE;

Pero, ¿qué pasa si el índice es LOCAL y la tabla está particionada? Aquí la sintaxis de Oracle nos limita, y no podemos usar la cláusula NOREVERSE en un rebuild de este tipo. Por ello, debemos recurrir a una estrategia de reconstrucción completa.

La Estrategia: Creación online e Invisible

Para evitar el error ORA-01408 (columna ya indexada) y no degradar rendimiento, el flujo de trabajo ideal es el siguiente:

Preparación del terreno (TEMP y Monitorización)

Dado que moveremos grandes volúmenes de datos (en mi caso, más de 100 GB), es vital asegurar que el tablespace temporal aguante el "sort". SQL

-- Monitorización del consumo de TEMP durante la creación en entorno de prueba
SELECT ROUND(SUM(blocks)*8/1024, 2) MB_REAL FROM v$sort_usage;

Creación del nuevo índice "en la sombra"

Utilizamos las cláusulas ONLINE para no bloquear DML e INVISIBLE ya que si no nos encontraríamos el error: ORA-01408: such column list already indexed.

Oracle permite tener dos índices sobre las mismas columnas siempre y cuando solo uno de ellos sea visible para el optimizador y una estructura física distinta,

CREATE INDEX XXX.IDX_LOG_ENV_PERF 
ON XXX.LOG_ENTREGAS (ID_DISPOSITIVO, FECHA_LOG) 
LOCAL 
ONLINE 
INITRANS 50 
PCTFREE 20 
TABLESPACE TBS_INDEXES 
PARALLEL 8 -- Aprovechamos la potencia del server 
INVISIBLE;

ALTER INDEX XXX.IDX_LOG_ENV_PERF NOPARALLEL;

Compruebo que ambos conviven:

Nota importante sobre el rendimiento: Ten en cuenta que mientras ambos índices convivan (el antiguo Reverse y el nuevo Normal), cada operación de INSERT, UPDATE o DELETE tendrá que actualizar ambos. Esto supone una penalización temporal en las operaciones DML, por lo que se recomienda realizar el cambio en horas de menor carga.

El Switch de Visibilidad

Una vez creado, el cambio es instantáneo a ojos de la aplicación. Pasamos el índice antiguo a modo invisible y activamos el nuevo. Esto nos da un plan de rollback inmediato: si algo falla, solo hay que revertir la visibilidad.

ALTER INDEX XXX.IDX_ORIGINAL_REVERSE INVISIBLE; 
ALTER INDEX XXX.IDX_LOG_ENV_PERF VISIBLE;

Va todo bien? Liberamos espacio una vez validado el cambio .

DROP INDEX XXX.IDX_ORIGINAL_REVERSE;

Cuando trabajamos con índices locales, perdemos la facilidad del REBUILD NOREVERSE.

Utilizar índices invisibles y creación online es la forma más transparente de realizar este cambio en entornos críticos de 24/7.