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...
