Es realmente necesario reconstruir los índices periodicamente?

Hoy me gustaria comentar algo respecto al mantenimiento de índices, en particular los de tipo mas común (B*TREE). Existe un gran debate en los foros sobre si es necesario o no realizar reconstrucciones (rebuild) de indice cada cierto tiempo. Principalmente existen dos corrientes: a) la pragmatica, encabezada por Burleson (ver: Burleson: Index Rebuild Approach)que sin probar nada y exclamando tener basta experiencia práctica sobre sistemas reales promueve la necesidad de detección y reconstrucción periodica de indices desbalanceados o con alocación redundante y b) la cientifica con sus dos grandes exponentes: Tom Kyte y sobre todo Jonathan Lewis (ver: Lewis: Index Rebuild Approach)quienes siempre prueban y demuestran con una formalidad cuasi-matematica cada situación y que dicen que por definicón los indices son balanceados y solo en raras ocasiones se justifica un rebuild. Cualquiera que haya visto alguno de mis otras notas se dará cuenta que mi intención es probar tal cual lo hacen los dos gurues del caso b, obviamente ellos estan en un nivel superlativo y yo humildemente trato de imitar sus metodos de prueba.
El enfoque pragmatico (llamese Burleson y sus amigos) dicta que un índice con blevel (la cantidad de bloques del arbol b*tree hasta llegar a la hoja) de 4 o mayor (valga la aclaración que un índice con un blevel mayor a 3 es muy raro) o un indice cuya tabla tenga un ratio de cambios alto (sobre todo inserts y deletes) provoca que la alocación en las hojas sea muy ineficiente por lo cual una reconstrucción haría que los datos se compacten, ocupen menos hojas y por lo tanto aloquen menos espacio. Esto ultimo no es tan infrecuente como el caso del blevel mayor a 4 pero si pensamos un segundo, al reconstruir compactamos pero cto tardará en llenarse nuevamente?, si la operatoria es insert y luego delete tendría que pensar en un proceso diario o al menos semanal que llevará su tiempo y espacio adicional ya que cuando se reconstruye internamente (ya sea online o no) se va creando una copia, luego se renombra y se borra el viejo. Si no tenemos ventana de mantemiento tenemos que tener en cuenta que habrá que hacerlo online (desde 9i esto es posible).
El enfoque científico prueba que reconstruir en forma periodica los indices solo servirá para tener una posible ganancia (o tal vez perdida) en performance y para ganar espacio temporalmente dado que el clustering_factor (el dato mas importante para un indice, para que el optimizador sepa cuando es realmente necesario usarlo para armar el plan de acceso) no cambia con la reconstrucción.
Al margen de esta discusión, suena lógico que luego de 30 años desde la primera version de Oracle (Oracle 2) sea necesario reconstruir en forma sistematica y periodica los indices "ineficientes"?, algunos habrán notado que desde 10g existen tareas de mantenimiento automatizadas de segmentos por default que dan recomendaciones para liberar de ciertas tareas a los dba's (SEGMENT ADVISOR) y que hay casos donde una de las recomendaciones es reconstruir indices con el fin de recuperar espacio (shrink). Si bien esta sugerencia es licita, es realmente necesario?. Tal vez podría servirnos si una cierta tabla tuvo alguna carga y luego un borrado masivo y que no se prevean nuevas operaciones masivas. En ese caso se liberaría espacio.
Podría pensarse que al reconstruir se compactarán los hojas ,y en ciertos casos bajará el blevel, y por lo tanto un "range scan" recorrerá menos bloques hoja, pero si pensamos en que la tabla asociada se llenará masivamente en un corto plazo tendremos la contra de que el indice se tendrá que ir balanceando nuevamente y que esa tarea demandará split de bloques y contención dml. La ganancia es tan importante para tener que reconstruir todo el tiempo los indices sobre tablas con alto ratio de inserts/deletes?.
En conclusión, yo creo que la reconstrucción es necesaria en los siguientes casos: 1. cuando una tarea admistrativa deje en estado unusable a los indices (ej: operaciones sobre particiones sin actualizar los indices globales, move de tablas, sqlldr directo, etc), 2. cuando por un tema de espacio sea necesario realocar el indice en otro tablespace ó 3. cdo sea realmente justificado (y no por la dudas) como explique mas arriba.

A continuación voy a mostrar los efectos de la reconstrucción y que datos estadisticos cambian:

Primero voy a llenar una tabla con 1,5M de filas, uso la vista all_objects de base

rop@TEST10G> create table t as select * from all_objects;

Tabla creada.

rop@TEST10G> insert into t select * from t;

94773 filas creadas.

rop@TEST10G> /

189546 filas creadas.

rop@TEST10G> /

379092 filas creadas.

rop@TEST10G> /

758184 filas creadas.

rop@TEST10G> commit;

Confirmación terminada.

rop@TEST10G> select count(1) from t;

  COUNT(1)
----------
   1516368

Ahora voy a crear un indice por las columnas object_id y object_name

rop@TEST10G> create index t_idx on t(object_id,object_name);

Índice creado.

Voy a analizar la tabla y su indice con el viejo ANALYZE, pero para esta prueba sirve:

rop@TEST10G> analyze table t compute statistics for all indexes;

Tabla analizada.

Vamos a ver solo las estadisticas que me interesan mostrar en esta prueba:

rop@TEST10G> select blevel,leaf_blocks,clustering_factor from user_ind_statistics
  2  where index_name = 'T_IDX';

    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ----------- -----------------
         2        8620           1516368

El blevel o cantidad de bloques hasta llegar a las hojas es 2, la cantidad de hojas es 8620 y el clustering_factor es 1516368, es decir igual a la cantidad de filas (ver clustering factor)

En este punto voy a eliminar masivamente casi todas las filas de la tabla, analizarla nuevamente y ver como cambiaron las estadisticas que me interesan del índice

rop@TEST10G> delete from t where rownum <= 1500000;

1500000 filas suprimidas.

rop@TEST10G> commit;

Confirmación terminada.

rop@TEST10G> analyze table t compute statistics for all indexes;

Tabla analizada.

rop@TEST10G> 
rop@TEST10G>   select blevel,leaf_blocks,clustering_factor from user_ind_statistics
  2   where index_name = 'T_IDX';

    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ----------- -----------------
         2        8620              2142

Observamos qeu la cantidad de hojas y el blevel quedaron iguales aunque ahora la tabla tiene muy pocas filas. El clustering factor (cf) cambió ya que se adecúo a los nuevos datos (recordemos que el cf es una relación entre datos de la tabla y el indice)
Haciendo un rebuild del indice t_idx veremos como cambiaron las estadísticas

rop@TEST10G> alter index t_idx rebuild;

Índice modificado.

rop@TEST10G>  select blevel,leaf_blocks,clustering_factor from user_ind_statistics
  2  where index_name = 'T_IDX';

    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------- ----------- -----------------
         1          99              2142

rop@TEST10G> 

Como vemos se redujeron notablemente la cantidad de hojas y tambien bajó el blevel, pero el cf quedó exactamente igual luego del rebuild.