Recreando índices y calculando estadísticas

7 comentarios

En este artículo habíamos visto como recalcular las estadísticas de todos los índices de todas las tablas:

Selectividad de los índices

Y en este otro artículo habíamos visto como podemos reindexar todos los índices de todas las tablas:

Recreando todos los índices de todas las tablas

en el presente artículo combinaremos ambos stored procedures en uno solo porque de esa manera nos resultará más fácil el mantenimiento de los índices.

SET TERM ^ ;

CREATE PROCEDURE SP_MANTENIMIENTO_INDICES
RETURNS(
   tcNombreTabla  VARCHAR(31),
   tcNombreIndice VARCHAR(31),
   tcRestriccion  VARCHAR(11),
   tcReindex      VARCHAR( 2),
   tcStatistics   VARCHAR( 2))
AS
BEGIN

   FOR SELECT
      RDB$RELATION_NAME,
      RDB$INDEX_NAME
   FROM
      RDB$INDICES
   ORDER BY
      RDB$RELATION_NAME
   INTO
      :tcNombreTabla,
      :tcNombreIndice
   DO BEGIN
      tcReindex     = '';
      tcStatistics  = '';
      tcRestriccion = (SELECT RDB$CONSTRAINT_TYPE FROM RDB$RELATION_CONSTRAINTS WHERE RDB$INDEX_NAME = :tcNombreIndice);
      tcRestriccion = IIF(tcRestriccion IS NULL, '', tcRestriccion);
      IF (NOT EXISTS(SELECT RDB$INDEX_NAME FROM RDB$RELATION_CONSTRAINTS WHERE RDB$INDEX_NAME = :tcNombreIndice) AND LEFT(tcNombreIndice, 4) <> 'RDB$') THEN BEGIN
         EXECUTE STATEMENT 'ALTER INDEX ' || :tcNombreIndice || ' INACTIVE ;' ;
         EXECUTE STATEMENT 'ALTER INDEX ' || :tcNombreIndice || ' ACTIVE ;' ;
         tcReindex = 'SI';
      END
      EXECUTE STATEMENT 'SET STATISTICS INDEX ' || :tcNombreIndice || ';' ;
      tcStatistics = 'SI';
      IF (LEFT(tcNombreIndice, 4) <> 'RDB$') THEN
         SUSPEND;
   END

END^

En este stored procedure solamente recreamos los índices nuestros y que además no estén relacionados con una restricción pero recalculamos las estadísticas de todos los índices (los internos del Firebird y los de las restricciones también). Como normalmente no nos interesa mostrar los índices internos del Firebird sino solamente los nuestros entonces al SUSPEND lo colocamos dentro de un IF.

A este stored procedure lo llamaríamos así:

SELECT * FROM SP_MANTENIMIENTO_INDICES

Y aquí está una captura de lo que obtendremos al ejecutar este SELECT:

MANTENIMIENTO1

Captura 1. Si haces clic en la imagen la verás más grande

Como puedes ver, allí están el nombre de la tabla, el nombre del índice, la restricción, si fue reindexado o no, y si se recalcularon sus estadísticas. Este SELECT puede resultarte muy útil para usarlo en un programa que le muestre al usuario el mantenimiento de los índices.

Artículos relacionados:

Usando índices en Firebird

Selectividad de los índices

Recreando todos los índices de todas las tablas

El índice del blog Firebird21

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

Replicación 2. Una captura de pantalla

4 comentarios

Esta es una de las ideas que tengo para el programa de replicación, aunque esta captura de pantalla no está relacionada con el tema sirve para ilustrar los conceptos.

REPLICA2_1

Captura 1. Si haces clic en la imagen la verás más grande

En el panel de la izquierda se muestran todas las opciones del programa, con la información que resulte pertinente (1).

Cuando una de esas opciones se está ejecutando mostrarla resaltada para que el usuario pueda saber fácilmente cual opción se está ejecutando (2).

En el panel de la derecha pedirle que ingrese los datos que el programa necesita conocer antes de empezar la replicación o mostrarle el progreso de la replicación, por supuesto con los correspondientes mensajes explicativos (3).

Creo que este tipo de pantallas le facilita al usuario entender lo que está haciendo el programa y son útiles al menos para los usuarios principiantes. Para los usuarios avanzados podríamos tener otro tipo de pantallas.

¿Qué te parece la idea? ¿te gusta o no? ¿Algunos puntos negativos que puedas comentar?

Artículo relacionado:

El índice del blog Firebird21

Usando índices en Firebird

9 comentarios

Como seguramente ya sabes, los índices sirven para dos cosas:

  1. Buscar datos
  2. Mostrar las consultas ordenadas

Para poder realizar efectivamente ambas tareas los índices deben encontrarse en buen estado y es tu responsabilidad asegurarte de que eso sea así. El mantenimiento de los índices de Firebird debe contemplar dos puntos:

  1. Que el índice esté bien balanceado
  2. Que las estadísticas de los índices sean las correctas

Balanceando los índices:

Si un índice no está bien balanceado entonces las operaciones INSERT, UPDATE, DELETE, FECTCH y SELECT de esa tabla tomarán más tiempo del debido, haciendo lentas operaciones que podrían ejecutarse más rápidamente. Para balancear un índice debes escribir:

ALTER INDEX MiIndice INACTIVE

ALTER INDEX MiIndice ACTIVE

Este ciclo de inactivar/activar un índice tiene como efecto volver a crearlo. Es el equivalente al comando REINDEX de los lenguajes xBase (dBase, Clipper, FoxPro, etc.). Es conveniente ejecutar esos comandos después de haber realizado muchas operaciones en la tabla (por ejemplo: 20.000 ó más) para tener la seguridad de que el índice se encuentra bien balanceado.

En este artículo encontrarás más información:

https://firebird21.wordpress.com/2013/03/03/recreando-los-indices-de-las-tablas/

Recalculando las estadísticas:

El optimizador de consultas del Firebird revisa la estadística de un índice para decidir si lo utilizará en el PLAN de una consulta o no. Si la estadística de un índice no está actualizada entonces podría dejar de utilizar un índice que sí debería haber utilizado y el resultado será que tendrás una consulta mucho más lenta de lo que debería haber sido si el optimizador usaba el índice.

Es por ese motivo que siempre debemos asegurarnos de tener las estadísticas actualizadas, para que la decisión del optimizador sea la mejor posible. Como el Firebird calcula la estadística solamente cuando crea el índice y después de un ciclo backup/restore, es tu responsabilidad recalcularla periódicamente porque todas las operaciones de INSERT, UPDATE, DELETE que realices en esa tabla afectarán a esa selectividad.

Para recalcular la estadística de un índice debes escribir:

SET STATISTICS INDEX MiIndice

Puedes encontrar más información en este artículo:

https://firebird21.wordpress.com/2013/03/09/selectividad-de-los-indices/

¿Cuáles columnas indexar?

Puede ser tentador pensar que si se indexan todas las columnas de una tabla se conseguirá mucha velocidad en todas las consultas a esa tabla. Eso generalmente es falso. Indexar una columna que tiene pocos valores distintos es un error porque lo más probable es que el optimizador de consultas no utilice ese índice, y todas las operaciones de inserción, actualización, borrado que involucren a esa columna modificarán al índice y eso lleva tiempo entonces ¿para qué indexar esa columna si su índice nunca será usado y actualizarlo hará las operaciones más lentas?

La decisión de indexar o no una columna debe estar basada en si se obtiene una ganancia de velocidad considerable utilizándolo. Si no se obtiene una ganancia de velocidad o si la ganancia de velocidad obtenida es muy pequeña, lo correcto es no indexar esa columna para no gastar recursos en ella.

No tiene sentido indexar una columna si no se realizan búsquedas utilizando esa columna ni se ordenan las consultas según esa columna ni se obtiene una buena ganancia de tiempo.

Tener los índices correctos en cada tabla, y solamente los índices correctos, es una decisión de diseño que debe estar bien estudiada para evitar indexar una columna que no debería estar indexada o dejar de indexar una columna que sí debería estarlo.

Para complicar las cosas, un índice que ahora es bueno dentro de unos meses podría dejar de serlo. Y viceversa. Porque al aumentar grandemente la cantidad de datos en la tabla la selectividad varía y por lo tanto la bondad de indexar o no esa columna. Es por lo tanto necesario que al menos en las tablas más grandes de nuestra Base de Datos periódicamente verifiquemos los índices, su selectividad, y si son o no usados en los PLANES de los SELECTS. Un índice que nunca es utilizado en una búsqueda o en el ordenamiento de una consulta no tiene razón de ser, está de más, está sobrando, es inútil, solamente gasta recursos.

Tampoco es correcto indexar una columna y que su índice se use poquísimo, quizás una o dos veces al mes en una consulta para ganar 2 ó 3 segundos.

Conclusión:

Tener siempre los índices bien balanceados y con sus estadísticas actualizadas es lo que debes preocuparte en conseguir para que todas las operaciones en tus tablas (INSERT, UPDATE, DELETE, FETCH, SELECT) sean lo más rápidas posibles. Un índice en mal estado de conservación perjudica más de lo que ayuda. Por ello es conveniente que tengas un stored procedure que se encargue de esas tareas, el cual podrá ser llamado desde tu aplicación (el programa que hiciste en Visual FoxPro, Delphi, C, C++, etc.)

En muchos lenguajes de programación es el propio programador quien decide si utilizará un índice o no y en caso afirmativo cual índice utilizará, pero en el caso de Firebird es el optimizador de consultas quien toma esa decisión (si no se le ha dicho lo contrario) y basa esa decisión en la selectividad de los índices. ¿Quiéres que tome la decisión más correcta? entonces la selectividad de tus índices debe estar actualizada.

Artículos relacionados:

Recreando los índices de las tablas

Selectividad de los índices

Usando un PLAN

Usando índices correctos para aumentar la velocidad de las consultas

Optimizando las consultas

El índice del blog Firebird21