Índices simples y compuestos

En Firebird tenemos la posibilidad de usar índices simples e índices compuestos.

Un índice es simple cuando solamente una columna pertenece a ese índice y es compuesto cuando más de una columna pertenecen al índice.

Cada índice, sea simple o compuesto, debe estar en un orden: ascendente (por defecto) o descendente.

El optimizador de consultas del Firebird siempre verifica si hay un índice disponible. De haberlo verifica también la selectividad de ese índice. La selectividad es un número que indica que tan eficiente será usar ese índice.

En los índices compuestos el orden (ascendente o descendente) es el mismo para todas las columnas. No se puede tener una columna ordenada ascendentemente y otra columna ordenada descendentemente. Si necesitamos algo así entonces deberemos crear dos índices simples: uno ordenado en forma ascendente y el otro ordenado en forma descendente.

En los índices compuestos podemos mezclar tipos de datos: una columna puede ser numérica, otra columna puede ser carácter, otra columna puede ser fecha, etc.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

 

Indices ascendentes y descendentes

Los índices en Firebird pueden ser ascendentes (1, 2, 3, 4, 5, 6, …) o descendentes (999, 998, 997, 996, …)

Lo importante a recordar es que son siempre recorridos en el mismo orden en que fueron creados. O sea que un índice ascendente siempre se recorre de menor a mayor y un índice descendente siempre se recorre de mayor a menor.

¿Por qué es importante saber eso?

Porque tener el índice adecuado puede hacer que nuestras consultas sean rapidísimas.

Veamos un ejemplo:

Listado 1.

SELECT
   MAX (MiColumna)
FROM
   MiTabla

Aquí tenemos tres posibilidades:

1. No hay un índice sobre la columna MiColumna
2. Hay un índice ascendente sobre la columna MiColumna
3. Hay un índice descendente sobre la columna MiColumna

En el caso 1. y en el caso 2. para hallar el valor máximo de MiColumna el Firebird debe recorrer la tabla completa, desde la primera fila hasta la última fila, porque no sabe cual de esas filas tendrá al mayor valor. Un índice ascendente jamás se usaría en este caso.

En cambio, en el caso 3. el valor máximo estará sí o sí en la primera fila del índice.

La diferencia en tiempo puede ser muy grande. En tablas que tienen millones de filas la diferencia será muy notoria porque el caso 1. y el caso 2. deben recorrer todas esas millones de filas y en cambio el caso 3. siempre encontrará el valor buscado en la primera fila.

¿Y si en lugar de escribir la función MAX() escribimos la función MIN()?

Pues allí se usará un índice ascendente, si es que existe.

Recuerda que crear un índice tiene sus ventajas y sus desventajas:

• La ventaja es que si se lo utiliza en un SELECT entonces los resultados se obtendrán muy rápido
• La desventaja es que debe ser actualizado cada vez que se realiza un INSERT, un UPDATE o un DELETE en esa tabla

Hay por lo tanto que comprobar si vale la pena crear un índice.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

Entendiendo los índices compuestos

Como sabes, cuando creas un índice éste puede tener una columna o más de una columna. Si el índice tiene una sola columna se lo llama «simple» y si tiene más de una columna se lo llama «compuesto». ¿Y en qué casos el Firebird utiliza un índice compuesto?

Supongamos que tu índice está compuesto por tres columnas. Firebird podría no usar ese índice, o usar solamente la primera columna, o usar la primera columna y la segunda columna, o usar las tres columnas. Es decir que estas son las posibilidades:

1. No se usa el índice compuesto
2. Se usa solamente la primera columna
3. Se usan la primera columna y la segunda columna
4. Se usan la primera columna y la segunda columna y la tercera columna

¿De qué depende?

De lo que hayas escrito en la cláusula WHERE. Para que una columna de un índice compuesto se utilice las columnas anteriores deben ser comparadas por igualdad. No por menor, ni por mayor, ni por menor o igual, ni por mayor o igual, ni por distinto. Por igualdad.

 Ejemplo:

Supongamos que tenemos un índice compuesto por tres columnas, (MiColumna1, MiColumna2, MiColumna3). Si escribimos:

WHERE
   MiColumna1 >= 21 AND
   MiColumna2 >= 500 AND
   MiColumna3 <= 2000

se usará solamente la columna MiColumna1 del índice compuesto ¿Por qué? porque en MiColumna1 no se usó una igualdad. En cambio, si escribimos:

WHERE
   MiColumna1 = 21 AND
   MiColumna2 >= 500 AND
   MiColumna3 <= 2000

se usarán las columnas MiColumna1 y MiColumna2 del índice compuesto ¿por qué? porque MiColumna1 fue comparada por igualdad, entonces se usa esa columna y también la siguiente. En cambio, si escribimos:

WHERE
   MiColumna1 = 21 AND
   MiColumna2 = 500 AND
   MiColumna3 <= 2000

se usarán las columnas MiColumna1 y MiColumna2 y MiColumna3. ¿Por qué? porque MiColumna1 y MiColumna2 fueron comparadas por igualdad, entonces se usan esas dos columnas y también la siguiente.

¿Y si se requiere que en todos los casos se usen las tres columnas del índice?

En ese caso la solución es no crear un índice compuesto sino crear tres índices simples, uno por cada columna. De esta manera te asegurarás de que siempre las tres columnas utilicen un índice.

Conclusión:

Es muy importante entender en que circunstancias el Firebird usa los índices compuestos para no crearlos innecesariamente. No te olvides que cada índice ocupa espacio en el disco duro y además hace que todas las operaciones de inserción, actualización, y borrado se realicen más lentamente. Mejoran la velocidad de las consultas pero empeoran las demás operaciones.

Como todo, los índices compuestos tienen sus ventajas y sus desventajas. La ventaja es que es más fácil mantener un solo índice compuesto que varios índices independientes. La desventaja es que podrías tener un índice compuesto que usas muy poco porque en tus consultas usas muy pocas igualdades. Si ése es el caso entonces muy probablemente te convendrá tener varios índices simples y no un índice compuesto.

¿Tienes dudas sobre si te conviene o no crear un índice compuesto?

Haz pruebas. Créalo y verifica el rendimiento. Luego elimina el índice compuesto y crea índices simples y verifica el rendimiento. Compara esos rendimientos y así sabrás si te conviene o no tener un índice compuesto.

Artículo relacionado:

El índice del blog Firebird21

 

Recreando índices y calculando estadísticas

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:

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

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

Usando índices en Firebird

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

Claves primarias ¿simples o compuestas?

Las claves primarias o Primary Keys pueden ser simples (o sea, una sola columna actúa como Primary Key) o compuestas (o sea, dos o más columnas actúan como Primary Key)

¿Cuál es la mejor alternativa, cuál deberíamos emplear?

Como puedes leer en este artículo:

https://firebird21.wordpress.com/2013/03/15/entendiendo-a-las-primary-keys/

una Primary Key no puede tener valores repetidos ni valores nulos. Y eso, hay varias formas de conseguirlo.

Lo más conveniente siempre es que la columna que actúa como Primary Key (o una de las columnas, si es compuesta) sea autoincremental porque eso nos asegura que no tendrá valores repetidos ni nulos porque el mismo Firebird se encarga de asignarle valores a esa columna.

Pero ¿una columna o varias columnas?

Si planeas usar replicación entonces lo más conveniente es que la Primary Key esté compuesta por dos (o más) columnas. Si crees que nunca usarás replicación entonces puedes usar una sola columna autoincremental para ella.

La replicación es enviar los datos de una Base de Datos a otra Base de Datos. Por ejemplo tienes sucursales en Buenos Aires, Montevideo y Bogotá, cada una de ellas con su respectiva Base de Datos y quieres consolidar las ventas de esas tres sucursales para tenerlas en una sola Base de Datos. Entonces, debes poder distinguir en cual sucursal se realizó cada venta y los identificadores no te servirán porque pueden estar repetidos.

En Buenos Aires tenemos ventas con identificadores 1, 2, 3, 4, etc.

En Montevideo tenemos ventas con identificadores 1, 2, 3, 4, etc.

En Bogotá tenemos ventas con identificadores 1, 2, 3, 4, etc.

¿Cómo distinguimos entonces a cuál sucursal corresponde cada venta? Con otra columna, llamada por ejemplo CódigoSucursal.

Buenos Aires tiene el código 1

Montevideo tiene el código 2

Bogotá tiene el código 3

Entonces las ventas de Buenos Aires tendrán como Primary Key:

11, 12, 13, 14, etc.

Las ventas de Montevideo tendrán como Primary Key:

21, 22, 23, 24, etc.

Las ventas de Bogotá tendrán como Primary Key:

31, 32, 33, 34, etc.

Y nunca habrá confusión posible.

Conclusión:

• Si no usas ni planeas usar replicación entonces la Primary Key puede estar compuesta por una sola columna autoincremental
• Si usas o podrías llegar a usar replicación, entonces la Primary Key debería estar compuesta por dos columnas: una que identifique a la Sucursal y otra que sea autoincremental

Artículos relacionados:

Entendiendo a las Primary Keys

Uso de la Primary Key

El índice del blog Firebird21

 

https://firebird21.wordpress.com/2013/03/15/entendiendo-a-las-primary-keys/

Los índices de las restricciones

Cada vez que estableces una restricción (Primary Key, Foreign Key, Unique Key) el Firebird automáticamente crea un índice asociado para esa restricción.

Por lo tanto, no deberías crear un índice sobre esas mismas columnas ya que si haces eso tendrás dos índices idénticos y eso te acarreará dos problemas.

1. El Firebird podría confundirse sobre cual índice usar y por lo tanto tu consulta será más lenta de lo que debería
2. Cada vez que insertes una fila (o actualices una columna involucrada en el índice que creaste) el Firebird deberá actualizar dos índices y eso lo hará más lento.

Sin embargo…hay una excepción a esta regla.

Por defecto, el Firebird crea los índices de forma ascendente (es decir: 1, 2, 3, 4, 5, 6, 7, etc.) y a veces para tus consultas podrías necesitar un índice descendente. Y en esos casos sí deberías crear el índice.

Por ejemplo, si usas la función MAX() en una consulta, tener un índice descendente sobre esa columna te aumentará la velocidad de respuesta de forma impresionante.

Otro ejemplo, si deseas que la consulta te aparezca ordenada en forma descendente un índice descendente es lo adecuado.

Entonces, no crees un índice sobre una columna que tiene una restricción (Primary Key, Foreign Key, Unique Key) porque el Firebird ya crea ese índice por su propia cuenta. Las únicas excepciones son si necesitas ordenar una consulta en forma descendente o si empleas la función MAX().

Artículo relacionado:

El índice del blog Firebird21

Agregando una Primary Key

A veces, necesitamos importar en nuestras tablas de Firebird el contenido existente en otras tablas, quizás .DBF, Access, Paradox, etc., en las cuales no teníamos definida una Primary Key.

Recuerda que en Firebird es súper recomendable que todas las tablas tengan su correspondiente Primary Key (la única excepción son las tablas que tienen una sola fila).

Entonces, luego de crear una tabla en Firebird e importar los datos desde la otra tabla procedemos a agregarle la Primary Key (si es que en el programa de importación ya no lo habíamos hecho) con un stored procedure similar al siguiente:

CREATE PROCEDURE AGREGAR_PRIMARYKEY
AS
   DECLARE VARIABLE lnContador INTEGER;
BEGIN

   FOR SELECT TAR_IDENTI FROM TARJETAS INTO lnContador DO BEGIN
      UPDATE
         TARJETAS
      SET
         TAR_IDENTI = ROW_COUNT + 1;
   END

END;

La columna TAR_IDENTI de la tabla TARJETAS es numérica y su valor podría haber sido cero o NULL en todas las filas. Este stored procedure utiliza a la variable de contexto ROW_COUNT para conocer cual es el número de la fila que se está procesando en el FOR SELECT. Como el valor inicial de ROW_COUNT es cero se le tuvo que sumar uno para que la numeración empezara con 1.

ROW_COUNT es una variable de contexto que está disponible en los stored procedures, triggers y execute block y que nos informa la cantidad de filas que se obtuvieron con el último comando DML (INSERT, UPDATE, DELETE, o FETCH).

Cuando se usa a ROW_COUNT dentro de un FOR SELECT su valor es incrementado en cada iteración, empezando con cero.

Solamente después de ejecutar ese stored procedure con éxito podemos definir a la columna TAR_IDENTI como Primary Key, si lo hubiéramos intentado antes hubiéramos obtenido un error ya que la columna TAR_IDENTI tenía valores repetidos (un cero o un NULL en cada fila de la tabla) y en las Primary Key no pueden existir valores repetidos ni nulos.

Al finalizar el stored procedure y luego del COMMIT correspondiente nos aseguramos que la columna TAR_IDENTI no tiene valores repetidos por lo cual podemos proceder a definirla como Primary Key.

¿Cómo ejecutar este stored procedure?

Tienes 3 formas:

1. Mediante el programa ISQL.EXE que viene incluido con el Firebird:

(haciendo clic en la imagen la verás más grande)

2. Si usas el programa EMS SQL Manager, mediante la opción «Execute»

(haciendo clic en la imagen la verás más grande)

 3. Si usas el programa EMS SQL Manager, también puedes usar el editor SQL

(haciendo clic en la imagen la verás más grande)

 En estos dos últimos casos no te olvides de hacer clic sobre la opción COMMIT para que los cambios queden grabados permanentemente.

Usando un índice descendente

Cuando creamos un nuevo índice y no especificamos si lo queremos ascendente (es decir: 1, 2, 3, 4, 5, etc.) o descendente (es decir: 10, 9, 8, 7, 6, etc.) el Firebird por defecto lo crea ascendente.

¿Por qué?

Porque para la mayoría de los casos es la mejor opción, porque es la que más frecuentemente se usa.

Cuando definimos una restricción Primary Key, Foreign Key o Unique Key el Firebird automáticamente crea un índice para esas restricciones. Y ese índice es siempre ascendente.

¿Y qué sucede si se le envía como argumento a la función MAX() la columna de la Primary Key?

SELECT
   MAX(PRD_IDENTI)     /* PRD_IDENTI es la Primary Key de la tabla PRODUCTOS */
FROM
   PRODUCTOS

Si miramos el PLAN de esta consulta veremos que no usa un índice:

(haciendo clic en la imagen la verás más grande)

¿Por qué no se usó un PLAN?

Porque el índice de la Primary Key, el índice que el Firebird automáticamente crea para esa restricción es siempre ascendente. Si queremos que la función MAX() use un índice en esta consulta entonces tenemos que crear ese índice de forma descendente.

CREATE DESC INDEX IDX_PRODUCTOS ON PRODUCTOS(PRD_IDENTI);

Si después de crear un índice descendente para la Primary Key nos fijamos cual es el PLAN que se usará en la consulta de arriba esto será lo que veremos:

(haciendo clic en la imagen la verás más grande)

 O sea que ahora sí el Firebird está usando un índice.

Conclusión:

Verifica siempre cual es el PLAN de tus consultas, a veces agregándoles un índice se puede obtener una ganancia en velocidad impresionantemente grande.

Artículos relacionados:

Usando un PLAN

Algo más sobre PLAN