Recreando todos los índices de todas las tablas

3 comentarios

Como hemos visto en estos artículos:

Recreando los índices de las tablas

Usando índices en Firebird

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?

  1. 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
  2. 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.
  3. 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

Usando índices en Firebird

El índice del blog Firebird21

SET AUTODDL ON

3 comentarios

Las siglas DDL significan: Data Definition Language o en castellano: Lenguaje para la Definición de Datos.

Entonces, al escribir el comando SET AUTODDL ON le estamos indicando al Firebird que queremos que realice un COMMIT automático a continuación de cada instrucción DDL.

¿Cuáles son las instrucciones DDL?

Todas las que empiezan con las palabras: CREATE, ALTER, DROP, DECLARE, RECREATE, SET

Sirven para crear tablas, índices, stored procedure, triggers, etc. También para realizar modificaciones, borrados, declaraciones.

Ejemplos: CREATE TABLE, CREATE INDEX, ALTER TABLE, DROP INDEX

¿Cuál es la ventaja de escribir SET AUTODDL ON?

Que no necesitaremos escribir un COMMIT a continuación de las instrucciones DDL

¿Dónde se usa SET DDL ON?

En el programa ISQL.EXE, en los scripts, y en algunos otros programas que implementan esta funcionalidad.

Ejemplo:

Queremos agregarle la tabla BANCOS a nuestra Base de Datos mediante un script:

/* Al poner AUTODDL en ON no necesitamos escribir los COMMIT */

SET AUTODDL ON;

/* Primero, creamos la tabla BANCOS, sin un COMMIT al final del CREATE */

CREATE TABLE BANCOS (
   BAN_IDENTI D_IDENTIFICADOR DEFAULT 0 NOT NULL,
   BAN_NOMBRE D_NOMBRE40 NOT NULL);

/* Después, creamos las restricciones Primary Key y Unique Key, sin COMMIT */

ALTER TABLE BANCOS ADD CONSTRAINT PK_BANCOS PRIMARY KEY (BAN_IDENTI);

ALTER TABLE BANCOS ADD CONSTRAINT UQ_BANCOS UNIQUE (BAN_NOMBRE);

/* A continuación creamos un trigger para tener un identificador que sea autoincremental */

SET TERM ^ ;

CREATE TRIGGER BI_BANCOS_BAN_IDENTI FOR BANCOS
   ACTIVE BEFORE INSERT
   POSITION 0
AS
BEGIN
   IF (NEW.BAN_IDENTI IS NULL OR NEW.BAN_IDENTI = 0) THEN
      NEW.BAN_IDENTI = GEN_ID(BANCOS_BAN_IDENTI_GEN, 1);
END^

SET TERM ; ^

Como puedes ver, ningún COMMIT ha sido escrito sin embargo la tabla BANCOS ha sido creada exitosamente, como también las dos restricciones y el trigger.

¿Por qué? Porque al estar AUTODDL en ON es el propio Firebird quien se encarga de poner el COMMIT luego de cada instrucción DDL.

Artículo relacionado:

El índice del blog Firebird21