Indexando una columna computada

Deja un comentario

Como sabes, las columnas computadas son muy útiles para ver su contenido en los SELECTs y también cuando las usamos en la cláusula WHERE (de un UPDATE, de un DELETE, o de un SELECT) o en la cláusula ORDER BY.

Ya las habíamos visto en varios artículos anteriormente, ahora veremos una de sus características más interesantes: pueden ser indexadas.

Las primeras versiones del Firebird no lo permitían, pero a partir de la versión 2.0 ya tenemos esa posibilidad.

Por ejemplo, la tabla de SUCURSALES tiene estos datos:

INDICE1

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

Como podemos ver, algunos nombres están en mayúsculas y algunos nombres están en minúsculas. Podríamos crear el siguiente índice:


CREATE INDEX IDX_SUCURSALES1 ON SUCURSALES COMPUTED BY (UPPER(SUC_NOMBRE));

COMMIT;

Y luego usarlo en comandos como los siguientes:


SELECT * FROM SUCURSALES ORDER BY UPPER(SUC_NOMBRE)

INDICE2

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

Aquí, los nombres de las sucursales aparecen ordenados alfabéticamente y al mirar el PLAN vemos que efectivamente se usó el índice recientemente creado.


SELECT * FROM SUCURSALES WHERE UPPER(SUC_NOMBRE) STARTING WITH 'BOG'

INDICE3

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

Aquí le pedimos que nos muestre los nombres de todas las sucursales que empiezan con las letras ‘BOG’ y vemos que también usó el índice computado que habíamos creado anteriormente.


DELETE FROM SUCURSALES WHERE UPPER(SUC_NOMBRE) = 'MIAMI - ESTADOS UNIDOS'

INDICE4

También en este caso se usó el índice de la columna computada, como podemos comprobarlo mirando el PLAN.

Otros ejemplos:


CREATE INDEX IDX_MOVIMCAB1 ON MOVIMCAB COMPUTED BY (EXTRACT(YEAR FROM MVC_FECHAX))

CREATE INDEX IDX_MOVIMCAB2 ON MOVIMCAB COMPUTED BY (EXTRACT(YEAR FROM MVC_FECHAX) || MVC_NRODOC)

CREATE DESCENDING INDEX IDX_MOVIMCAB3 ON MOVIMCAB COMPUTED BY (EXTRACT(YEAR FROM MVC_FECHAX) || MVC_NRODOC)

El índice IDX_MOVIMCAB1 de la tabla MOVIMCAB (cabecera de movimientos) la indexa según el año del movimiento.

El índice IDX_MOVIMCAB2 de la tabla MOVIMCAB (cabecera de movimientos) la indexa según el año del movimiento y el número del documento.

El índice IDX_MOVIMCAB3 de la tabla MOVIMCAB (cabecera de movimientos) es muy parecido al índice IDX_MOVIMCAB2 pero ahora el índice es descendente (es decir, de mayor a menor).

Conclusión:

Tener columnas computadas es algo muy bueno porque nos facilita la vida. Poder indexar a esas columnas computadas es algo más bueno aún.

Como hemos visto en los ejemplos anteriores, el Firebird usará a los índices de las columnas computadas cada vez que los necesita.

Esos índices pueden involucrar a una sola columna o a varias columnas (como en los dos últimos ejemplos donde IDX_MOVIMCAB2 y IDX_MOVIMCAB3 involucran a las columnas MVC_FECHAX y MVC_NRODOC).

Artículos relacionados:

Columnas computadas

Un truco para encontrar valores que pueden estar en varias columnas

Usando un SELECT en una columna computada

Utilizando columnas computadas

Algunos ejemplos de uso de las columnas computadas

Usando un PLAN

Algo más sobre PLAN

Entendiendo el contenido de un PLAN

El índice del blog Firebird21

El foro del blog Firebird21

Utilizando columnas computadas

5 comentarios

En este artículo ya habíamos visto lo que son las columnas computadas y para que sirven.

https://firebird21.wordpress.com/2013/06/23/columnas-computadas/

Ahora la pregunta es: ¿vale la pena usarlas?

La respuesta es un rotundo sí y las razones son las siguientes:

  1. Las columnas computadas (casi) no ocupan espacio en la Base de Datos
  2. En tus aplicaciones escribes menos
  3. En tus aplicaciones siempre obtendrás el mismo valor
  4. Si necesitas cambiar la fórmula, lo haces en un solo lugar

1. Las columnas computadas (casi) no ocupan espacio en la Base de Datos

¿Por qué? porque solamente se guarda la definición de la columna computada, no sus valores. Por ejemplo, si tu columna computada realiza alguna operación matemática entonces podrá ser de tipo BIGINT y ocupará 8 bytes. Nada más. Si la tabla tiene 1.000.000 de filas no se usarán 8.000.000 de bytes sino solamente 8 bytes, los de la definición.

Si quieres tener una columna para guardar en ella el total de la venta de un producto (cantidad * precio) y la defines como una columna normal entonces estarás haciendo dos cosas mal: primero, tu tabla no estará normalizada porque estarás guardando un valor que puede ser calculado y segundo, estarás desperdiciando espacio en el disco duro. En cambio, si la defines como columna computada la tabla continuará normalizada y además no usarás espacio del disco duro.

 2. En tus aplicaciones escribes menos

Si a tu columna computada la llamas, por ejemplo, TOTAL_VENTAS, entonces en tus stored procedures, en tus triggers, y en tu lenguaje de programación solamente necesitarás escribir TOTAL_VENTAS, no tendrás que estar multiplicando CANTIDAD * PRECIO en cada uno de esos lugares, así que ahorrarás escritura. Cuanto más larga sea la fórmula mayor cantidad de caracteres serán los que te ahorrarás de escribir.

3. En tus aplicaciones siempre obtendrás el mismo valor

Como siempre te estarás refiriendo a una sola columna (por ejemplo, llamada TOTAL_VENTAS) entonces en todos tus stored procedures, tus triggers, y en tu lenguaje de programación siempre obtendrás el mismo valor. Si no usas una columna computada podrías equivocarte y en lugar de escribir CANTIDAD * PRECIO escribir CANTIDAD + PRECIO y por supuesto el resultado estará equivocado. Es cierto que no es probable que te equivoques con una fórmula tan sencilla pero te podría ocurrir si estás muy apurado. Y aún más si la fórmula es complicada. Pero si usas columnas computadas jamás tendrás ese problema porque hay una sola fórmula que se encuentra en un solo lugar, así que es 100% seguro de que siempre obtendrás el mismo valor. Si la fórmula es correcta en todos los lugares obtendrás el valor correcto, y si la fórmula es incorrecta en todos los lugares obtendrás un valor incorrecto, pero siempre será el mismo y eso te ayudará si necesitas cambiar la fórmula.

4. Si necesitas cambiar la fórmula, lo haces en un solo lugar

Supongamos que el resultado de CANTIDAD * VENTAS lo necesitas mostrar en 40 lugares distintos. Y después te dicen que hay que multiplicar a ese resultado por 1.1, para aumentarlo en un 10 %. Si no usas columnas computadas entonces tendrás que hacer 40 cambios (y quizás ni te acuerdes que son 40 y cambies solamente en 28, a pesar de que deberías haber cambiado en 40).

Usando columnas computadas no tendrás ese problema, ya que el cambio lo haces en un solo lugar (en la columna definida como COMPUTED BY) y automáticamente ya el nuevo valor estará disponible en los 40 lugares distintos.

Conclusión:

Usar columnas computadas es altamente recomendable porque ahorramos tiempo y ganamos en confianza y en seguridad. Escribimos menos y además si alguna vez necesitamos cambiar la fórmula lo hacemos en un solo lugar.

Artículos relacionados:

Columnas computadas

Algunos ejemplos de uso de las columnas computadas

Un truco para encontrar valores que pueden estar en varias columnas

Usando un SELECT en una columna computada

Indexando una columna computada

El índice del blog Firebird21

El foro del blog Firebird21

Usando un SELECT en una columna computada

Deja un comentario

Las columnas computadas son columnas donde no se introducen datos, sino que su contenido es obtenido después de realizar algunas operaciones en otras columnas.

https://firebird21.wordpress.com/2013/06/23/columnas-computadas/

Por ejemplo, tenemos una columna para guardar los APELLIDOS de una persona, otra columna para guardar los NOMBRES de esa persona y creamos una columna computada para tener en ella los APELLIDOS y los NOMBRES. Algo como esto:

ALU_APENOM COMPUTED BY (TRIM(ALU_APELLD) || ', ' || ALU_NOMBRE)

Los usuarios introducen datos en las columnas ALU_APELLD (apellidos del alumno) y ALU_NOMBRE (nombres del alumno) pero no en la columna ALU_APENOM (apellidos y nombres del alumno). ¿Por qué no en esa columna? porque esa es una columna computada como podemos ver en la definición, ya que el nombre de la columna está seguido por COMPUTED BY. En el ejemplo anterior las columnas ALU_APELLD y ALU_NOMBRE pertenecen a la misma tabla que la columna ALU_APENOM. ¿Pero y si queremos columnas de otra tabla, es posible conseguir eso?

Sí, es posible.

Para ello debemos:

  1. Crear un stored procedure seleccionable
  2. Usar doble paréntesis, rodeando al SELECT que nos traerá los datos que queremos.

Ejemplo:

Supongamos que tenemos una tabla llamada EMPLEADOS y otra tabla llamada SALARIOS y deseamos que la tabla EMPLEADOS tenga una columna computada que contendrá el total de los salarios pagados a cada empleado.

CREATE PROCEDURE TOTAL_PAGADO(
   tnIdeEmp INTEGER)
RETURNS(
   ftnPagado INTEGER)
AS
BEGIN

   ftnPagado = (SELECT SUM(SAL_MONTOX) FROM SALARIOS WHERE SAL_IDEEMP = :tnIdeEmp);

   SUSPEND;

END;

Este stored procedure seleccionable (sabemos que es seleccionable porque tiene el comando SUSPEND en él) halla y devuelve la suma de todos los salarios pagados a cada empleado. Recibe como parámetro de entrada el identificador del empleado que nos interesa y devuelve la suma de los salarios que se le pagaron. Recuerda que el stored procedure debe ser seleccionable para que esta técnica funcione.

CREATE TABLE EMPLEADOS (
   EMP_IDENTI BIGINT NOT NULL,
   EMP_NOMBRE VARCHAR(20),
   EMP_APELLD VARCHAR(20),
   EMP_PAGADO COMPUTED BY ((SELECT FTNPAGADO FROM TOTAL_PAGADO(EMP_IDENTI))));

En la columna computada EMP_PAGADO el SELECT debe estar rodeado por dos paréntesis. Si pones un solo paréntesis el Firebird se enojará contigo y te dirá que cometiste un error. Debes usar dobles paréntesis.

 Luego, escribiendo un comando tal como:

SELECT
   *
FROM
   EMPLEADOS

Esto será lo que obtendremos:

COMPUTED1

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

Que es exactamente lo que queríamos obtener. Los datos de cada empleado y el total de los salarios que le hemos pagado hasta este momento.

Desde luego que podríamos haber obtenido estos mismos datos sin necesidad de usar columnas computadas, el hecho de usar columnas computadas tiene la ventaja de que nuestros SELECTs son más sencillos de escribir. Fíjate en el SELECT que nos mostró la Captura 1., si no las hubiéramos usado ese SELECT hubiera sido más complicado de escribir. Aquí simplemente escribimos: SELECT * FROM EMPLEADOS y ya obtuvimos lo que queríamos.

NOTA IMPORTANTE:

Esta técnica debe solamente ser empleada con versiones de Firebird 2.1 ó posteriores, ya que las versiones anteriores tenían un problema que causaba que los backups no pudieran ser restaurados. Así que si estás usando Firebird 1.0 ó Firebird 1.5 ó Firebird 2.0, ni se te ocurra utilizar esta técnica.

Y aunque estés usando Firebird 2.1 ó posterior siempre es recomendable que hagas un backup y lo restaures inmediatamente, para verificar de que tu backup puede ser restaurado exitosamente.

Conclusión:

Usar el comando SELECT dentro de una columna computada es una técnica de programación que nos facilitará escribir futuros SELECTs. Para que esta técnica funcione debemos hacer dos cosas:

  1. Escribir un stored procedure seleccionable
  2. Rodear con doble paréntesis a la columna COMPUTED BY

Artículos relacionados:

Columnas computadas

El índice del blog Firebird21

El foro del blog Firebird21