Los alias de las columnas

3 comentarios

En Firebird tenemos la posibilidad de darles nombres alternativos, normalmente conocidos como “alias”, a las columnas de un SELECT pero ¿dónde podemos usar esos alias?

SELECT
   MiColumna1 AS UnAlias,
   MiColumna2 AS OtroAlias
FROM
   MiTabla

Según el estándar SQL (que Firebird cumple) los alias se usan para darle títulos a las columnas. Entonces ¿en qué momento Firebird conoce cuál es el alias de una columna y puede usarlo?

Solamente después de que conoce el valor de todas las columnas, nunca antes. Por lo tanto un alias no puede ser usado en otra columna, ni en la cláusula WHERE ni en la cláusula JOIN pero sí en las cláusulas GROUP BY y ORDER BY.

Ejemplo 1. No se puede usar ALIAS aquí.

Listado 1.

SELECT
   MOV_CANTID * MOV_PRECIO AS TOTAL_PRODUCTO,
   TOTAL_PRODUCTO / 11 AS TOTAL_IMPUESTO
FROM
   MOVIMDET

En el Listado 1. tenemos un alias llamado TOTAL_PRODUCTO y luego quisimos usarlo en la siguiente columna. Eso no está permitido, fallará.

Ejemplo 2. No se puede usar ALIAS aquí

Listado 2.

SELECT
   MOV_CANTID * MOV_PRECIO AS TOTAL_PRODUCTO
FROM
   MOVIMDET
WHERE
   TOTAL_PRODUCTO >= 100000

En el Listado 2. tenemos un alias llamado TOTAL_PRODUCTO y luego quisimos usarlo en la cláusula WHERE. Tampoco está permitido y fallará.

Ejemplo 3. No se puede usar ALIAS aquí

Listado 3.

SELECT
   MOV_CANTID * MOV_PRECIO AS TOTAL_PRODUCTO
FROM
   MOVIMDET
JOIN
   MOVIMCAB
      ON MVC_TOTALX > TOTAL_PRODUCTO * 2

En el Listado 3. tenemos un alias llamado TOTAL_PRODUCTO y quisimos usarlo en un JOIN. Eso no está permitido y fallará.

Ejemplo 4. Sí se puede usar ALIAS aquí.

Listado 4.

SELECT
   MOV_CANTID * MOV_PRECIO AS TOTAL_PRODUCTO
FROM
   MOVIMDET
GROUP BY
   TOTAL_PRODUCTO

En el Listado 4. tenemos un alias llamado TOTAL_PRODUCTO y queremos usarlo en la cláusula GROUP BY. Eso sí está permitido porque los nombres de todas las columnas ya están definidos.

Ejemplo 5. Sí se puede usar ALIAS aquí

Listado 5.

SELECT
   MOV_CANTID * MOV_PRECIO AS TOTAL_PRODUCTO
FROM
   MOVIMDET
ORDER BY
   TOTAL_PRODUCTO

En el Listado 5. tenemos un alias llamado TOTAL_PRODUCTO y queremos usarlo en la cláusula ORDER BY. Eso sí está permitido porque los nombres de todas las columnas ya están definidos.

Conclusión:

Solamente se puede usar un alias previamente definido después que todas las columna del SELECT han sido evaluadas, nunca antes. Por ese motivo no se puede usar usar dentro de la lista de columnas ni en la cláusula WHERE ni en la cláusula JOIN pero sí puede usarse en la cláusula GROUP BY y en la cláusula ORDER BY.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

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

Máxima cantidad de columnas en una tabla

3 comentarios

Si te preguntan ¿cuántas columnas puede tener una tabla de Firebird? tu respuesta debe ser: “depende de los tipos de datos de las columnas”

¿Por qué eso?

Porque en Firebird no hay una cantidad máxima de columnas, como sí hay en Visual FoxPro, en Access y en otros lenguajes o motores de bases de datos. En los dos anteriormente nombrados el límite es de 255 columnas, pero en Firebird no existe tal cosa.

¿Por qué no?

Porque en Firebird una fila puede tener un máximo de 65.536 bytes y por lo tanto la cantidad máxima de columnas que puede tener una tabla varía entre 1 y 65.536

 Veamos esto más detalladamente:

Algunos tipos de datos ocupan una cierta cantidad predeterminada de bytes. Por ejemplo, SMALLINT ocupa siempre 2 bytes, INTEGER ocupa siempre 4 bytes, BLOB ocupa siempre 4 bytes, etc.

Pero tenemos dos casos especiales, que son CHAR y VARCHAR, la cantidad de bytes que ellos ocupan dependen del CHARACTER SET usado. Si no especificamos un CHARACTER SET o especificamos uno de los llamados “occidentales” entonces 1 carácter ocupará siempre 1 byte. Para nosotros que escribimos en castellano, portugués, francés, inglés, u otros idiomas occidentales eso es lo normal. Pero para quienes escriben en árabe, hebreo, chino, japonés, coreano, vietnamita, etc., eso ya no es cierto. Entonces, si el CHARACTER SET de la columna es por ejemplo ISO8859_1 (el recomendado para guardar texto escrito en castellano) una columna definida como CHAR(20) se cuenta como 20 bytes pero si el CHARACTER SET de la columna es UTF8 entonces una columna definida como CHAR(20) se cuenta como 40 bytes, ya que cada carácter ocupa 2 bytes cuando se usa UTF8.

¿Qué implica todo esto?

Que solamente tendremos problemas si la suma de los bytes ocupados por todas las columnas supera la cantidad de 65.536, si es igual o menos que eso estará todo bien.

Como vimos anteriormente, es muy fácil hacer esa suma.

Ejemplo 1:

Evidentemente, cada caso es distinto, depende de la estructura de la tabla para saber cuantas columnas como máximo puede tener pero supongamos lo siguiente para este ejemplo:

La cantidad de bytes que ocupa cada columna de una tabla es en promedio de 20. Por lo tanto, dividiendo 65.536 por 20 obtenemos como resultado 3.276 y para esa tabla en particular podríamos tener hasta 3.276 columnas.

Como vemos, mucho más que las 255 columnas del Visual FoxPro o del Access

De todas maneras, si necesitas una tabla que tenga tantas columnas lo más probable es que tu diseño esté muy mal y tendrías que ver la forma de mejorarlo.

Ejemplo 2:

En una tabla solamente necesitamos guardar números enteros y como cada columna de tipo INTEGER ocupa 4 bytes entonces podríamos tener un máximo de 65.536 / 4 columnas lo cual nos da como resultado 16.384 columnas.

Ejemplo 3:

En una tabla necesitamos guardar una columna de tipo SMALLINT, tres columnas de tipo INTEGER, y el resto serán columnas de tipo CHAR(60). Entonces tenemos: 65.536 – 2 – 3 * 4 = 65.522, y como 65.522 / 60 es igual a 1.092, podríamos tener hasta 1.096 columnas en esa tabla (1 columna SMALLINT, 3 columnas INTEGER, y 1.092 columnas CHAR(60)).

Conclusión:

En Firebird no hay una cantidad máxima de columnas que puede tener una fila de una tabla, lo que sí hay es una cantidad máxima de bytes que puede tener una fila de una tabla. Ese número es 65.536 bytes y es muchísimo más que suficiente para todas las necesidades que podamos tener. Si llegamos a necesitar más que 65.536 bytes entonces es seguro que nuestra Base de Datos está mal diseñada y deberíamos preocuparnos por mejorar eso.

Artículos relacionados:

Entendiendo a los conjuntos de caracteres

Algo más sobre los conjuntos de caracteres

Funciones útiles con los conjuntos de caracteres

Cantidad de columnas de una tabla

El índice del blog Firebird21

El foro del blog Firebird21

 

 

Algunos ejemplos de uso de las columnas computadas

4 comentarios

Poder tener columnas computadas es una característica muy buena que tiene el Firebird, ya que nos permite tener una columna “virtual”, una columna a la cual nunca se le insertan datos sino que su contenido es calculado (o computado) según el contenido de otra u otras columnas previamente definidas.

Como esta característica no existe en otros motores SQL entonces puede ser de interés mostrar algunos ejemplos de uso.

Ejemplo 1. Mostrando apellidos y nombres

Si has leído algo sobre normalización de tablas entonces recordarás que en una sola columna no deberían encontrarse dos o más datos que significan cosas distintas. Es decir, guardar en una sola columna los apellidos y también los nombres está mal, es incorrecto, deberían guardarse en columnas distintas. Ok, se guardan en columnas distintas, pero al mostrarlos queremos que aparezcan siempre con el mismo formato: los apellidos, una coma, y los nombres. Usando columnas computadas es muy fácil:

CREATE TABLE VENDEDORES (
   VEN_IDENTI D_IDENTIFICADOR NOT NULL,
   VEN_NOMBRE D_NOMBRE20,
   VEN_APELLD D_NOMBRE20,
   VEN_APENOM COMPUTED BY (TRIM(VEN_APELLD) || ', ' || VEN_NOMBRE));

En este caso nuestra tabla de VENDEDORES tiene una columna para guardar los nombres, otra columna para guardar los apellidos, y una columna computada para mostrarlos a ambos.

COMPUTADAS1

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

Los usuarios solamente pueden guardar datos en las columnas VEN_NOMBRE y VEN_APELLD, en la columna VEN_APENOM no pueden hacerlo. Su contenido es calculado (computado) automáticamente por el Firebird.

Por supuesto que en nuestros SELECTs podemos usar cualesquiera de esas columnas.

Ejemplo 2. Mostrando el total vendido de cada producto

CREATE TABLE MOVIMDET (
   MOV_IDENTI D_IDENTIFICADOR NOT NULL,
   MOV_IDECAB D_IDENTIFICADOR,
   MOV_IDEPRD D_IDENTIFICADOR,
   MOV_CANTID D_CANTIDAD,
   MOV_PRECIO D_PRECIO,
   MOV_TOTALX COMPUTED BY (MOV_CANTID * MOV_PRECIO));

En este caso guardamos en la tabla MOVIMDET el detalle de las ventas realizadas. Tenemos una columna para guardar la cantidad vendida, otra columna para guardar el precio unitario y una columna computada para mostrar el importe vendido de cada producto.

COMPUTADAS2

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

En la columna MOV_TOTALX tenemos el total vendido de cada producto, ese valor lo halla automáticamente el Firebird, nosotros no necesitamos calcularlo cada vez que lo necesitemos porque ya el Firebird se encargó de calcularlo para nosotros.

Ejemplo 3. Mostrando la bonificación familiar

En Paraguay (donde reside el autor de este blog) a los empleados se les paga un adicional del 5% por cada hijo menor de 18 años que tienen. Entonces nuestra tabla de EMPLEADOS podría ser similar a esta:

CREATE TABLE EMPLEADOS (
   EMP_CODSUC D_CODIGOSUCURSAL NOT NULL,
   EMP_IDENTI D_IDENTIFICADOR NOT NULL,
   EMP_NOMBRE D_NOMBRE20,
   EMP_APELLD D_NOMBRE20,
   EMP_SALBAS D_PRECIO2,
   EMP_APENOM COMPUTED BY (TRIM(EMP_APELLD) || ', ' || EMP_NOMBRE),
   EMP_CANHIJ COMPUTED BY ((
      SELECT
         COUNT(*)
      FROM
         EMPLEADOSFAM
      WHERE
         EMF_CODSUC = EMP_CODSUC AND
         EMF_IDECAB = EMP_IDENTI AND
         EXTRACT(YEAR FROM CURRENT_DATE) -
         EXTRACT(YEAR FROM EMF_FECNAC) < 18
   )),
   EMP_BONFAM COMPUTED BY (EMP_SALBAS * EMP_CANHIJ * 5 / 100));

Como se puede ver aquí, en una columna computada también se puede usar un SELECT, pero en ese caso hay que escribir dos paréntesis, no solamente uno. Es lo que se hizo en la columna computada EMP_CANHIJ, donde queremos tener la cantidad de hijos menores de 18 años que tiene cada empleado. Los datos de los hijos (nombres, fechas de nacimiento, etc.) se guardan en otra tabla, cuyo nombre es EMPLEADOSFAM (familiares de los empleados).

COMPUTADAS3

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

COMPUTADAS4

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

En nuestra tabla de EMPLEADOS tenemos a dos personas, y como podemos ver estamos usando varias columnas computadas:

  • EMP_APENOM para mostrar los apellidos y nombres del empleado
  • EMP_CANHIJ para mostrar la cantidad de hijos menores de 18 años
  • EMP_BONFAM para mostrar la bonificación familiar que le corresponde al empleado

Relacionamos a ambas tablas mediante las columnas EMP_IDENTI (identificador del empleado) y EMF_IDECAB (identificador de la cabecera).

ADVERTENCIA:

Como hemos visto en el Ejemplo 3. se puede escribir un SELECT dentro de una columna COMPUTED BY pero eso no es recomendable hacerlo en tablas que tienen muchas filas porque por cada empleado se realizará el SELECT correspondiente y eso puede demorar mucho tiempo. En general para estos casos es preferible crear una vista. Usaríamos un SELECT dentro de una columna COMPUTED BY solamente cuando la tabla del SELECT (no la tabla principal, sino la tabla del SELECT) tiene pocas filas y usa un índice.

Artículos relacionados:

Columnas computadas

Usando un SELECT en una columna computada

Un truco para encontrar valores que pueden estar en varias columnas

Utilizando columnas computadas

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

¿Cuándo se actualiza la estructura de una tabla?

4 comentarios

Si con un programa cambias la estructura de una tabla y con otro programa quieres usar esos cambios notarás que a veces no puedes hacerlo.

Veamos un ejemplo:

El usuario ejecuta su programa de Contabilidad y está trabajando con ese programa. Quiere introducir el nombre de un Proveedor y la columna le queda corta, digamos que solamente puede introducir 25 caracteres y él necesita introducir 31. Te llama y tú le dices: “ok, no hay problema, ya te actualizo la estructura de la tabla”.

Entonces vas, abres el EMS SQL Manager (o cual sea tu administrador gráfico), y cambias el ancho de la columna a 40, compilas la tabla, y está todo ok. Haces una prueba modificando esa columna en una fila cualquiera para que tenga 40 caracteres, le haces el COMMIT correspondiente y todo bien, funcionó perfecto. Desde luego, para terminar tu prueba borras los caracteres que le habías agregado a esa columna para probar, para que no queden registrados.

Ya has comprobado que aumentar el tamaño de la columna funcionó perfectamente entonces le llamas al usuario, y le dices: “ya está, ya podrás introducir hasta 40 caracteres en esa columna”.

El usuario feliz intenta nuevamente grabar los 31 caracteres que necesitaba grabar y … ¡¡¡NO FUNCIONA!!!

Te llama y te dice: “sigue sin funcionar, está igual que antes”

Entonces tú como eres muy educado a él no le dices una palabra pero dentro tuyo piensas: “¡¡¡la regranp…que lo recontraparió!!! ¡¡¡CÓMO QUE NO FUNCIONA!!! ¡¡¡Acabo de probar y funcionaba perfecto!!!”

Vuelves a probar y funciona bien. El usuario vuelve a probar y no le funciona.

¿Qué está pasando, cuál es el problema?

Que en tu programa los cambios sí funcionan, pero en el programa del usuario no. ¿Por qué?

Porque tú mantienes abierta a la Base de Datos en tu EMS SQL Manager (o cual sea tu administrador gráfico) y por lo tanto el usuario no se enterará de los cambios que hiciste hasta que no cierres la Base de Datos o salgas de ese programa.

Y en ocasiones, puede requerirse que el usuario también salga de su programa y vuelva a entrar.

Conclusión:

Que tú hagas un cambio a la estructura de una tabla no significa que al instante todos los usuarios se enteran de ese cambio, no es así. Para que se enteren debes salir del programa que usaste para realizar los cambios (el EMS SLQ Manager, el IBEXPERT, el FlameRobin, etc.) y en ocasiones el usuario también deberá salir del programa que él estaba usando. Solamente después de eso los cambios a la estructura de la tabla serán visibles.

Así que si haces cambios a una tabla y el usuario no ve esos cambios, no te desesperes. Simplemente sal de tu administrador gráfico y pídele al usuario que también salga del programa que estaba usando; cuando vuelva a entrar el asunto estará solucionado.

Artículo relacionado:

El índice del blog Firebird21