¿Como detectar la fragmentación de indices en base de datos oracle?

Es muy probable que no sepas que los indices se fragmentan pero si se fragmentan (sino para que escribo este post) y lo que podemos hacer es reconstruirlos, lo cual es un proceso lento (si el indice es grande) pero puede mejorar la performance de los indices. 

Por lo tanto una tarea recomendada es chequear los indices cada tanto y reconstruirlos (cuando no esten utilizando la base) 

Lo que hice es un script que inserta en la tabla INDEX_REBUILD los datos de los indices que deben ser reconstruidos. Y luego podemos reconstruirlos de forma automatica o manual.  

Lo hice para base de datos oracle pero es interesante hacerlo para otros motores : 

DROP TABLE INDEX_REBUILD CASCADE CONSTRAINTS;

CREATE TABLE INDEX_REBUILD AS (select * 

        from index_stats);

DECLARE

CURSOR INDEX_NAMES is

SELECT INDEX_NAME FROM all_indexes 

where TABLE_OWNER = 'nombreDelEsquema'

  -- AND TABLE_NAME = 'nombreDeLaTabla'; -- Si deseo correrlo para una tabla puedo descomentar esta linea.

BEGIN

     FOR N IN INDEX_NAMES LOOP

       EXECUTE IMMEDIATE 'analyze index ' || N.INDEX_NAME || ' validate structure';

       dbms_output.put_line(  N.INDEX_NAME );

     END LOOP;

     

     INSERT INTO INDEX_REBUILD select * 

        from index_stats

        where round((del_lf_rows/lf_rows)*100,2) >= 20

        OR height > 3

        OR lf_rows > lf_blks;

        

      COMMIT;

END; 

Dado que tenemos los indices a reconstruir en una tabla, podemos hacer esto de forma automática : 

CURSOR INDEX_NAMES is

SELECT NAME FROM INDEX_REBUILD;

BEGIN

FOR N IN INDEX_NAMES LOOP

       EXECUTE IMMEDIATE 'ALTER index ' || N.NAME || ' REBUILD';

     END LOOP;

END; 

Pero tenemos que tener cuidado porque este proceso puede llevar mucho tiempo. Si se hace para una o dos tablas puede servir, pero para una base de datos grande o mediana, nunca terminaría. En ese caso es mejor hacerlo manual con un equipo de mate y bizcochitos. 

Talvez tendría que hacer un post explicando un poco más la fragmentación y porque esos valores mágicos que pongo en la consulta, pero eso va llevar mucho tiempo...