Como hemos visto en estos artículos:
Recreando los índices de las tablas
es muy importante que todos los índices de todas nuestras tablas estén bien balanceados para que cuando realicemos las operaciones de INSERT, UPDATE, DELETE no se pierda más tiempo del debido en mantenerlos actualizados. Un índice desbalanceado tarda más en actualizarse que uno correctamente balanceado; por ese motivo debemos tratar de tener siempre a todos los índices de todas las tablas bien balanceados.
Los siguientes comandos tienen por objetivo recrear un índice, o reindexarlo como también se dice:
ALTER INDEX MiIndice INACTIVE ALTER INDEX MiIndice ACTIVE
y funcionan muy bien pero … ¿y si queremos reindexar no solamente un índice sino todos los índices de todas las tablas? Evidentemente escribir esos comandos para cada índice de cada tabla tomará mucho tiempo, será muy tedioso y además correremos el riesgo de olvidarnos de alguno. Por ello, escribí el siguiente stored procedure que se encarga de dicha tarea:
SET TERM ^ ; CREATE PROCEDURE SP_ACTUALIZAR_INDICES AS DECLARE VARIABLE lcNombreIndice VARCHAR(31); BEGIN FOR SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE LEFT(RDB$INDEX_NAME, 4) <> 'RDB$' INTO :lcNombreIndice DO BEGIN IF (NOT EXISTS(SELECT RDB$INDEX_NAME FROM RDB$RELATION_CONSTRAINTS WHERE RDB$INDEX_NAME = :lcNombreIndice)) THEN BEGIN EXECUTE STATEMENT 'ALTER INDEX ' || :lcNombreIndice || ' INACTIVE ;' ; EXECUTE STATEMENT 'ALTER INDEX ' || :lcNombreIndice || ' ACTIVE ;' ; END END END^ SET TERM ; ^
¿Qué hace este stored procedure?
- Obtiene los nombres de todos los índices de todas las tablas cuyas primeras 4 letras sean distintas que “RDB$”. ¿Por qué eso? porque los nombres de las tablas y de los índices de los metadatos siempre empiezan con “RDB$” y los nombres de nuestras tablas y de nuestros índices no deberían empezar con esas letras. En otras palabras, lo que se obtiene son los nombres de nuestros índices, no los nombres de los índices que usa internamente el Firebird
- Los índices de las restricciones (Primary Key, Foreign Key, Unique Key) no pueden ser desactivados, el Firebird no lo permite. Y es muy lógico, si se desactivara el índice de una restricción entonces el Firebird no podría verificar esa restricción, por lo tanto hace la fácil: no te permite desactivar el índice de una restricción. Entonces en el stored procedure se verifica que el índice no pertenezca a una restricción. Eso se consigue buscando su nombre en la tabla RDB$RELATION_CONSTRAINTS pues en esa tabla se guardan los datos de todas las restricciones.
- Si el nombre del índice no empieza con RDB$ (o sea, si es un índice nuestro) y no es el índice asociado a una restricción entonces se lo inactiva y se lo activa. Este ciclo desactivar/activar tiene por efecto recrear al índice y nos asegura que el nuevo índice esté correctamente balanceado.
Otra versión del stored procedure:
Aquí hay otra versión del stored procedure de arriba, en esta es seleccionable para que puedas ver los nombres de los índices que se están recreando.
SET TERM ^ ; CREATE PROCEDURE SP_ACTUALIZAR_INDICES RETURNS( tcNombreIndice VARCHAR(31)) AS BEGIN FOR SELECT RDB$INDEX_NAME FROM RDB$INDICES WHERE LEFT(RDB$INDEX_NAME, 4) <> 'RDB$' INTO :tcNombreIndice DO BEGIN IF (NOT EXISTS(SELECT RDB$INDEX_NAME FROM RDB$RELATION_CONSTRAINTS WHERE RDB$INDEX_NAME = :tcNombreIndice)) THEN BEGIN EXECUTE STATEMENT 'ALTER INDEX ' || :tcNombreIndice || ' INACTIVE ;' ; EXECUTE STATEMENT 'ALTER INDEX ' || :tcNombreIndice || ' ACTIVE ;' ; SUSPEND ; END END END^ SET TERM ; ^
Y llamarías a este stored procedure así:
SELECT * FROM SP_ACTUALIZAR_INDICES
El problema con los índices de las restricciones
¿Y qué pasa con los índices de las restricciones? ¿No pueden quedar desabalanceados? Por supuesto que sí pueden estar desabalanceados, son índices comunes, no son mágicos. Pero como nosotros no podemos recrearlos tenemos una sola alternativa: realizar un ciclo backup/restore porque al restaurar un backup se crean nuevamente todos los índices, los de las restricciones incluidos.
Artículos relacionados:
Recreando los índices de las tablas
Recreando índices y calculando estadísticas | Firebird SQL
Ago 24, 2013 @ 19:00:55
Oct 09, 2015 @ 05:53:10
Sólo un apunte sobre los índices de las restricciones(Primary Key, Foreign Key, Unique Key) para completar el artículo:
hay un truco(que parece ser que usa gbak) para reconstruir los índices sin desactivar la restricción. Consiste en los siguientes pasos:
– En la tabla RDB$INDICES el campo RDB$INDEX_INACTIVE puede tomar los valores:
– ‘0’ ó ‘null’ si está activo.
– ‘1’ inactivo.
– ‘3’ si el índice es una restricción indica índice inactivo.
Así podemos, en las restricciones:
1. Poner el campo RDB$INDEX_INACTIVE=3 para desactivar el índice manteniendo activa la restricción.
2. commit
3. Poner el campo RDB$INDEX_INACTIVE=0 para activar el índice de nuevo.
4. commit => ¡¡reconstruye el índice!!
Las escasas pruebas que he hecho funciona perfectamente, pero me quedan dudas, ya que no es una manera muy ortodoxa de hacerlo y prácticamente no existe documentación al respecto.
Un saludo,
Jose
Oct 09, 2015 @ 15:38:13
Gracias por la información, puede ser un tema a investigar. Pero GBAK construye los índices desde cero porque los índices no se copian en el backup.
Saludos.
Walter.