Consultas a tablas que tienen columnas de tipo BLOB

4 comentarios

Si escribimos un SELECT a una tabla que tiene columnas de tipo BLOB ¿cómo afectan a la velocidad de respuesta esas columnas?

Las columnas de tipo BLOB son muy útiles para guardar textos largos, fotografías, canciones, vídeos, etc. pero recuperar esa cantidad masiva de bytes puede hacer demorar mucho a nuestras consultas, entonces ¿qué podemos hacer para tener una buena velocidad de respuesta?

Primero, tener a las columnas de tipo BLOB en una tabla separada, exclusiva para ella. Por ejemplo, tenemos una tabla llamada PRODUCTOS y otra tabla relacionada llamada PRODUCTOSFOT, y en esta última tabla guardaremos las fotografías de los productos.

Segundo, si en una columna de tipo BLOB no se guardarán (aún, por lo menos) datos. ¿Es preferible que su valor sea NULL o que su valor sea un espacio en blanco?

Para no tener que escribir COALESCE(MiColumnaBLOB, ”) alguna gente prefiere guardar un espacio en blanco en la columna BLOB, ¿es esa una buena práctica?

No. Y la razón es que si el Firebird encuentra un NULL en una columna de tipo BLOB entonces ya sabe que no hay datos para esa columna y continúa con su siguiente tarea. Pero si hay cualquier valor distinto de NULL entonces ese valor distinto de NULL es un puntero a una página que guarda BLOBs. Por lo tanto debe visitar esa página para recuperar el contenido que encuentre en ella y que corresponde a la fila actual. Y aunque ese contenido sea un simple espacio en blanco tuvo que visitar igualmente a esa página.

En tablas que tienen pocas filas ese constante ir y venir entre las páginas de datos y las páginas BLOB no afectará mucho a la velocidad de respuesta de las consultas, pero en tablas que tienen cientos de miles o millones de filas ya es otro tema pues allí sí que será notoria la diferencia.

Por lo tanto, para que las columnas de tipo BLOB no hagan demorar mucho a los SELECTs:

  1. Esas columnas deben encontrarse en una tabla separada
  2. Si aún no se guarda algo en ellas, su valor debe ser NULL

Artículos relacionados:

Usando columnas de tipo BLOB

Guardando las columnas BLOB en tablas separadas

Almacenamiento de las columnas de tipo BLOB

Un stored procedure para conocer el tamaño total de los BLOB

Entendiendo las páginas de la Base de Datos

Eligiendo el tamaño adecuado de las páginas de la Base de Datos

¿Cómo las bases de datos de Firebird aumentan de tamaño?

El índice del blog Firebird21

El foro del blog Firebird21

Lidiando con la basura

1 comentario

Lo peor que le puede ocurrir a una Base de Datos operativa es que tenga basura dentro de ella porque en ese caso todas las consultas y todos los procesos dejarán de ser confiables. Se llama “basura” a cualquier dato que está guardado en alguna tabla pero no debería estar allí. La basura puede ser de dos tipos:

  • Relacionada con filas
  • Relacionada con columnas

Basura relacionada con filas

Estas se producen cuando dos o más filas tienen los mismos datos, es decir se tienen filas duplicadas; o cuando alguna fila tiene NULL en todas sus columnas.

¿Cómo se evita la basura relacionada con las filas?

Con las restricciones Primary Key y Unique Key.

Todas las tablas deben tener una restricción Primary Key, porque de esta manera se podrá identificar a cada fila sin equivocación posible y no se tendrán filas duplicadas totalmente, ya que al menos la Primary Key será diferente.

Cuando se sabe que una fila no admite duplicados, por ejemplo la columna CODIGO_PRODUCTO debe ser única, entonces una restricción Unique Key será la solución.

Sin embargo, a veces no se puede usar una Unique Key aunque se sospecha que puede haber filas duplicadas. Por ejemplo dos clientes pueden tener el mismo nombre, e inclusive el mismo domicilio, el mismo teléfono y la misma localidad porque son padre e hijo y viven en la misma casa.

Entonces aquí lo importante es detectar que hay filas duplicadas. Para ello podríamos escribir una consulta simple que nos indique si tal cosa ocurre:

Listado 1.

 
SELECT 
   CLI_NOMBRE, 
   COUNT(*) 
FROM 
   CLIENTES 
GROUP BY 
   CLI_NOMBRE 
HAVING 
   COUNT(*) >= 2

La consulta del Listado 1. nos mostrará los nombres de todos los clientes que estén duplicados, o sea que se encuentren dos o más veces en la tabla de CLIENTES.

Sin embargo, escribir esa consulta cada vez que deseamos verificar nuestras tablas puede ser muy tedioso, así que la mejor alternativa es crear una vista que nos de esa información y que podamos ejecutar cada vez que lo deseemos.

Listado 2.

CREATE VIEW V_MOSTRAR_DUPLICADOS(
   NOMBRE_TABLA,
   NOMBRE,
   CANTIDAD_DUPLICADOS)
AS
   SELECT
      'CLIENTES' AS NOMBRE_TABLA,
      CLI_NOMBRE AS NOMBRE,
      COUNT(*) AS CANTIDAD_DUPLICADOS
   FROM
      CLIENTES
   GROUP BY
      CLI_NOMBRE
   HAVING
      COUNT(*) >= 2

   UNION

   SELECT
      'GARANTES' AS NOMBRE_TABLA,
      GAR_NOMBRE,
      COUNT(*) AS CANTIDAD_DUPLICADOS
   FROM
      GARANTES
   GROUP BY
      GAR_NOMBRE
   HAVING
      COUNT(*) >= 2;

La vista V_MOSTRAR_DUPLICADOS nos mostrará de una sola vez a todos los clientes duplicados y también a todos los garantes duplicados. Desde luego que nuestra vista real tendrá a muchas tablas más, en este ejemplo se muestran solamente dos tablas para hacer el Listado 2. corto pero la idea es esa: con una sola vista, y mediante UNION tendremos a todas las filas duplicadas de todas las tablas que nos interesa verificar. Para poder conocer a cual tabla pertenece cada fila, en la primera columna colocamos el nombre de la tabla.

BASURA1

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

Y listo, ahora solamente nos queda comprobar que esas filas son legítimas o que deben ser eliminadas porque realmente están duplicadas.

Basura relacionada con columnas

Esta es la clase de basura que más comúnmente se encuentra en las tablas mal diseñadas. Ocurre cuando en una columna hay un dato incorrecto.

Por ejemplo, el precio de venta del producto es negativo, o la fecha de la cobranza es anterior a la fecha de la venta, o la cantidad vendida es cero, o la venta no tiene fecha, etc.

¿Cómo se evita la basura relacionada con las columnas?

Tenemos varias herramientas que nos permitirán realizar esa tarea:

  • La estructura de la tabla
  • Los dominios
  • La Primary Key
  • Las Foreign Keys
  • Los check
  • Las Unique Keys
  • Los triggers

En la estructura de la tabla podemos determinar que una columna no acepte valores NULL o que tenga un valor por defecto.

BASURA2

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

BASURA7

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

En los dominios podemos determinar los valores, o el rango de valores permitidos.

BASURA3

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

BASURA4

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

Con la Primary Key evitamos que esa columna tenga valores NULL o valores duplicados.

BASURA5

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

Con las Foreign Key evitamos que en una columna se introduzca un valor que no se encuentre en otra tabla. Se usa en las relaciones padre-hija y nos asegura que ambas tablas pueden relacionarse y que en ninguna fila de la tabla hija tengamos un valor que no se encuentre también en una fila de la tabla padre.

BASURA6

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

Con las restricciones check podemos comparar a dos (o más) columnas de la misma fila e inclusive con columnas de otras tablas.

BASURA8

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

En la Captura 8. vemos a dos restricciones check. La primera nos asegura que el valor colocado en la columna PRD_CODSUC exista en la tabla de SUCURSALES. ¿Y por qué no usar una Foreign Key aquí? Se podría y se obtendría el mismo resultado pero no sería eficiente ¿Por qué no? Porque las sucursales en general son muy pocas y en Firebird indexar por una columna que tiene muy pocos valores distintos es muy mala idea. Y todas las restricciones Foreign Key sí o sí crean un índice, así que lo recomendable es usar una restricción check que verifique si la sucursal existe o no. La segunda restricción check nos asegura que el precio de venta de un producto siempre sea mayor que su precio de costo.

Las restricciones check se deben cumplir siempre que se quiera insertar (INSERT) o actualizar (UPDATE) una fila. Así que en el ejemplo anterior una fila solamente podrá ser grabada si ambas restricciones check se cumplen.

Con las Unique Key nos aseguramos que nunca dos o más filas puedan tener el mismo valor.

BASURA9

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

En la Captura 9. vemos que hay una restricción Unique Key sobre las columnas PRD_CODSUC y PRD_CODIGO de la tabla PRODUCTOS. ¿Qué significa eso? Que esa combinación nunca podrá repetirse. En la misma Sucursal no podrá haber dos productos que tengan el mismo código. El Firebird no permitirá que la fila sea grabada, si se quiere hacer algo así.

Los triggers son la herramienta más poderosa que tenemos para evitar que entre basura en una tabla. ¿Por qué? Porque se puede escribir mucho código dentro de ellos. Hasta ahora, las herramientas que habíamos visto eran muy simples, por supuesto que nos ayudaban pero ninguna se compara a la potencia de los triggers.

BASURA10

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

En la Captura 10. vemos un trigger encargado de validar que el e-mail introducido sea válido. Si el e-mail no es válido entonces se lanza una excepción y por lo tanto no se podrá insertar (INSERT) ni actualizar (UPDATE) esa fila. Eso significa que además de todas las otras restricciones que puedan existir en la tabla de CLIENTES, para que se pueda insertar o actualizar una fila el e-mail debe ser válido.

BASURA11

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

En la Captura 11. el trigger valida que el código del país sea “54” o “595”. Cualquier otro código de país será rechazado. Además también valida que en el caso de ser “595” el código del área sea “21” o “61”, cualquier otro código de área será rechazado.

Una tabla puede tener muchos triggers, y si aunque sea uno solo de ellos finaliza con una excepción entonces la fila no será grabada en la tabla.

Por consiguiente, para que una fila pueda ser grabada en la tabla todos los triggers, el 100% de ellos, deben finalizar correctamente.

Conclusión:

Lo peor que le puede ocurrir a una Base de Datos operativa es tener basura dentro de ella. ¿Por qué? porque en ese caso ninguna consulta y ningún proceso podrá ser confiable.

Nuestra principal tarea cuando diseñamos una Base de Datos es impedir que entre basura en ella. Si tiene basura, no sirve.

El Firebird nos provee de varias herramientas para ayudarnos con la tarea de evitar a la basura y debemos aprender a usar todas esas herramientas pues para eso están.

La más poderosa de esas herramientas es el trigger, solamente con ella podríamos reemplazar a todas las demás herramientas pero a costa de escribir mucho código. Por lo tanto lo más inteligente es usar a todas las herramientas que tenemos a nuestra disposición y no solamente a los triggers.

Artículos relacionados:

Validando un e-mail

Validando números de teléfono

Asegurando tener datos consistentes en la Base de Datos

La doble validación

Entendiendo a las Foreign Keys

Conceptos sobre las Foreign Keys

Entendiendo las excepciones

Escribiendo un trigger

El índice del blog Firebird21

El foro del blog Firebird21

Escribiendo un trigger

Usando un solo generador autoincremental como Primary Key para TODAS las tablas

11 comentarios

Como sabes, lo mejor y más correcto es que la Primary Key de una tabla se obtenga a través de un generador autoincremental.

Lo normal es que cada tabla tenga su propio generador autoincremental, sin embargo existe otra posibilidad muy interesante: usar un solo generador autoincremental para obtener la Primary Key de todas las tablas.

¿Por qué haríamos algo así?

  1. Las Primary Key no necesitan ser visibles a los usuarios porque son una herramienta para mantener la integridad de la Base de Datos, los usuarios ni siquiera necesitan saber que existen
  2. Los números de las Primary Key no necesitan ser consecutivos, solamente se requiere que sean únicos
  3. Si se usa un BIGINT entonces estaríamos seguros de que nunca jamás tendremos números repetidos
  4. La Base de Datos ya puede estar preparada para replicación. Si en el Servidor1 el valor inicial del generador es 1.000.000.000 y en el Servidor2 el valor inicial del generador es 2.000.000.000 entonces nunca tendremos conflictos porque en la gran mayoría de las bases de datos dichos números nunca se repetirán. Tener 1.000.000.000 de transacciones es exageradamente mucho. Desde luego que si pensamos que tal cosa podría ocurrir nuestro generador no empezaría en 1.000.000.000 sino en 10.000.000.000 o en otro número mayor. De esta manera podríamos copiar sin problemas el contenido de las tablas de una Base de Datos a la otra Base de Datos con la seguridad de que no tendremos conflictos porque siempre las Primary Key serán diferentes.

Ejemplo:

En cada una de nuestras tablas su Primary Key es el Identificador de la misma

  • Insertamos una fila en MiTabla1, su Identificador es 1
  • Insertamos una fila en MiTabla2, su Identificador es 2
  • Insertamos una fila en MiTabla2, su Identificador es 3
  • Insertamos una fila en MiTabla3, su Identificador es 4
  • Insertamos una fila en MiTabla1, su Identificador es 5
  • Insertamos una fila en MiTabla3, su Identificador es 6

El Identificador de cada tabla no tiene números secuenciales y consecutivos, pero no necesita tenerlos, lo único que necesita es que esos números no se repitan.

Artículos relacionados:

Entendiendo a las Primary Keys

Uso de la Primary Key

Agregando una Primary Key

Tablas sin una Primary Key

Nombre de la Primary Key de una tabla

Valor duplicado en una Primary Key o en una Unique Key

Entendiendo a las Foreign Keys

Entendiendo la Integridad Referencial

El generador autoincremental

El índice del blog Firebird21

El foro del blog Firebird21

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

Cambiando el CHARACTER SET de una columna

1 comentario

Como ya habíamos visto en artículos anteriores, el CHARACTER SET nos determina cuales caracteres se pueden guardar en una columna cuyo tipo de datos es CHAR o VARCHAR. Esto es importante para nosotros pues normalmente en una columna de alguno de esos tipos de datos necesitaremos guardar letras acentuadas, letras eñe y letras u con diéresis. O sea, letras de uso frecuente en el idioma castellano.

¿Qué es transliteración?

Se llama transliteración a convertir los caracteres que están en un CHARACTER SET a otro CHARACTER SET.

Cuando Firebird hace transliteración preserva la fidelidad de los caracteres. Es decir que nunca sustituye un carácter que está en el CHARACTER SET original pero que no está en el CHARACTER SET destino. El propósito de esta restricción es garantizar que se pueda convertir un texto de un CHARACTER SET al otro, en ambas direcciones, y sin perder ni un solo carácter en el proceso.

¿Cómo podemos realizar la transliteración?

Si tenemos una una columna cuyo CHARACTER SET es equivocado, ¿cómo lo cambiamos?

Por ejemplo, nuestra columna usa NONE y queremos cambiarlo por ISO8859_1

Bien, hay dos métodos que podemos usar:

  1. ALTER TABLE
  2. Columna adicional

Método 1. Usando ALTER TABLE

Si usamos ALTER TABLE escribiríamos algo como:

ALTER TABLE 
   MiTabla 
ALTER COLUMN 
   MiColumna 
TYPE 
   VARCHAR(MiNúmero) 
CHARACTER SET 
   ISO8859_1;

COMMIT;

Método 2. Usando una columna adicional

Si usamos una columna adicional entonces tendríamos que hacer lo siguiente:

  • Crear una columna temporaria con el CHARACTER SET correcto
  • Copiar en la columna temporaria el contenido de la columna original, usando OCTETS como un CHARACTER SET intermedio
  • Borrar la columna original
  • Crear una nueva columna con el mismo nombre de la columna original y el CHARACTER SET correcto
  • Copiar en la nueva columna el contenido de la columna temporaria
  • Borrar la columna temporaria

¿Por qué usamos OCTETS en la columna temporaria?

Porque OCTETS es un CHARACTER SET especial, que guarda lo mismo que se escribió, o sea, no realiza la transliteración.

Entonces, escribiríamos algo como:

ALTER TABLE
   MiTabla
ADD
   MiColumnaISO8859_1 VARCHAR(40) CHARACTER SET ISO8859_1;

COMMIT;

UPDATE
   MiTabla
SET
   MiColumnaISO8859_1 = CAST(MiColumnaOriginal AS VARCHAR(40) CHARACTER SET OCTETS);

COMMIT;

ALTER TABLE
   MiTabla
DROP
   MiColumnaOriginal;

COMMIT;

ALTER TABLE
   MiTabla
ADD
   MiColumnaOriginal VARCHAR(40) CHARACTER SET ISO8859_1;

COMMIT;

UPDATE
   MiTabla
SET
   MiColumnaOriginal = MiColumnaISO8859_1;

COMMIT;

ALTER TABLE
   MiTabla
DROP
   MiColumnaISO8859_1;

COMMIT;

Desde luego que VARCHAR(40) es solamente un ejemplo, tú usarás CHAR() o VARCHAR() con la cantidad de caracteres que sea la adecuada en tu caso.

¿Cuál de los dos métodos es preferible usar?

Habrás notado que el Método 1 es mucho más sencillo de realizar que el Método 2, entonces ¿por qué no usar siempre el Método 1?

Lo que ocurre es que si usas el Método 1 el Firebird no cambia el texto que se encuentra en MiColumna, ese texto continúa allí exactamente como estaba. Solamente se cambian los metadatos de la tabla, pero no los datos de esa tabla, éstos permanecen exactamente igual que antes de cambiar el CHARACTER SET.

El Firebird realmente cambiará los datos cuando hagas un UPDATE o un SELECT que involucren a esa columna. Mientras no hagas ni un UPDATE ni un SELECT los datos continuarán con el CHARACTER SET antiguo. ¿Por qué actúa así? Porque el Firebird no quiere perder el tiempo en actualizar todas las filas de la tabla, una tabla que quizás tiene millones y millones de filas.

Cuando ejecutas un comando UPDATE o un comando SELECT que involucren a esa columna, esto puede (potencialmente) producir un error ya que algunos bytes en ISO8859_1 pueden no ser válidos.

En cambio, si usas el Método 2 la validación es realizada en el momento en que copias los datos de una columna a la otra y si ocurre algún error podrás subsanarlo en ese momento.

En síntesis, la principal diferencia es que si hay algún error y usaste el Método 1 muy probablemente será el usuario de tu aplicación quien lo descubrirá y seguramente se quejará. Pero si usas el Método 2 serás tú quien lo descubra.

Conclusión:

Lo más conveniente para quienes creamos bases de datos cuyos textos se introducirán en idioma castellano (español) es que el CHARACTER SET sea ISO8859_1. Si alguna columna de alguna tabla no tiene ese CHARACTER SET y deseamos que lo tenga, hay dos métodos que podemos usar. Uno de ellos es ALTER TABLE y el otro es creando una columna temporaria.

El primer método es más rápido, pero no valida que todos los caracteres del CHARACTER SET original tengan correspondencia con los caracteres del nuevo CHARACTER SET. Aunque se puede escribir un SELECT para verificarlo.

El segundo método es más lento, pero sí realiza esa validación. Además, como al escribir el comando UPDATE o el comando SELECT no tendrá que estar cambiando el CHARACTER SET también esos comandos se ejecutarán más rápido.

Artículos relacionados:

Entendiendo a los conjuntos de caracteres

Algo más sobre los conjuntos de caracteres

Funciones útiles con los conjuntos de caracteres

Máxima cantidad de columnas en una tabla

El índice del blog Firebird21

El foro del blog Firebird21

Indices de múltiples columnas

1 comentario

Muchas veces necesitamos que un índice involucre a varias columnas, así que ¿cómo funcionan esos índices?

Si creamos un índice como el siguiente:

CREATE INDEX
   MiIndice1
ON MiTabla
   (MiColumna1, MiColumna2, MiColumna3);

El índice MiIndice1 utiliza los datos de 3 columnas. La primera columna (en este ejemplo MiColumna1) es la columna principal, la segunda columna (en este ejemplo MiColumna2) es dependiente de MiColumna1, o sea tiene un menor nivel, no es tan importante. Y la tercera columna (en este ejemplo MiColumna3) es dependiente tanto de MiColumna1 como de MiColumna2 y por lo tanto es la menos importante de las tres.

¿Qué significa todo esto?

Que para que en la cláusula WHERE un índice sea usado se debe especificar a MiColumna1, sí o sí.

Que si además especificamos a MiColumna2 entonces la cantidad de filas que cumplen la condición serán (generalmente) menos que en el caso anterior.

Que si además especificamos a MiColumna3 entonces la cantidad de filas que cumplen la condición serán (generalmente) menos que en el caso anterior.

Ejemplos:

En los ejemplos que vienen a continuación suponemos que MiColumna1, MiColumna2, y MiColumna3 son numéricas, pero lo mismo funcionará con columnas que tengan cualquier otro tipo de datos.

Ejemplo 1. Se utiliza el índice

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna1 = 123

Aquí se utilizará el índice MiIndice1 ¿por qué? Porque en la cláusula WHERE especificamos a MiColumna1.

Ejemplo 2. Se utiliza el índice

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna1 = 123 AND
   MiColumna2 = 456

Aquí se utilizará el índice MiIndice1 ¿por qué? Porque en la cláusula WHERE especificamos a MiColumna1 y a MiColumna2. Recuerda que si se especifica la columna MiColumna1 se usa el índice.

Ejemplo 3. Se utiliza el índice

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna1 = 123 AND
   MiColumna3 = 789

Aquí se utilizará el índice MiIndice1 ¿por qué? Porque en la cláusula WHERE especificamos a MiColumna1 y aunque no especificamos a MiColumna2 el índice igualmente fue utilizado. Recuerda que siempre que especificques a MiColumna1 se usará el índice.

Ejemplo 4. Se utiliza el índice

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna2 = 456 AND
   MiColumna1 = 123

Aquí se utilizará el índice MiIndice1 porque se especificó a la columna MiColumna1, no importa que no haya sido la primera columna escrita en la cláusula WHERE ya que el Firebird es lo suficientemente inteligente como para cambiar el orden. Para el Firebird el Ejemplo 4 y el Ejemplo 2 son idénticos, no hay diferencia entre ellos, no importa el orden en que se escriban las columnas, lo importante es que se las haya escrito.

Ejemplo 5. Se utiliza el índice

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna1 = 123 AND
   MiColumna2 = 456 AND
   MiColumna3 = 789

Aquí se utilizará el índice MiIndice1 porque se especificó a la columna MiColumna1. Como las tres columnas fueron especificadas entonces esta condición será la más rápida de todas. La cantidad de filas que cumplen la condición del WHERE es siempre la menor posible cuando todas las columnas del índice múltiple son especificadas. Por lo tanto, siempre que sea posible eso es lo que debemos hacer: especificar a todas las columnas del índice en la cláusula WHERE.

Ejemplo 6. No se utiliza el índice

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna2 = 456

Aquí no se utilizará el índice MiIndice1. ¿Por qué no? porque en la cláusula WHERE no se especificó a la columna MiColumna1 y si esa columna no se especifica el índice no se utiliza.

Ejemplo 7. No se utiliza el índice

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumna2 = 456 AND
   MiColumna3 = 789

Aquí no se utilizará el índice MiIndice1. ¿Por qué no? porque en la cláusula WHERE no se especificó a la columna MiColumna1 y si esa columna no se especifica el índice no se utiliza.

Conclusión:

Crear índices de múltiples columnas puede ser muy útil en muchas ocasiones pero debemos recordar que en las claúsulas WHERE debemos siempre especificar a la primera columna de nuestro índice para que ese índice sea utilizado. Si la primera columna no es especificada, ese índice no será utilizado.

No hace falta que la primera columna del índice sea también la primera columna del WHERE porque el Firebird es lo suficiente inteligente como para (internamente) ubicar a las columnas en el mismo orden del índice.

Lo mejor que podemos hacer es que todas las columnas utilizadas en el índice sean especificadas en la cláusula WHERE, ya que así la cantidad de filas que cumplen la condición será mínima y obtendremos más rápidamente el resultado.

Artículos relacionados:

Entendiendo los índices compuestos

El índice del blog Firebird21

El foro del blog Firebird21

¿Cómo Firebird trata a los cambios de dominio?

5 comentarios

Supongamos que tienes un dominio y decides cambiarlo. Ese dominio quizás está siendo usado en muchas tablas, algunas de las cuales pueden tener millones y millones de filas.

No puedes cambiar un dominio para que guarde menos datos que antes, pero sí puedes cambiarlo para que guarde más datos que antes. Cambiar de INTEGER a SMALLINT no está permitido. Cambiar de SMALLINT a INTEGER sí está permitido. Cambiar de CHAR(10) a CHAR(2) no está permitido. Cambiar de CHAR(2) a CHAR(10) sí está permitido.

Así que veamos lo que ocurrirá cuando quieres cambiar un dominio y está permitido hacerlo.

Ejemplos:

D_PRECIO1 DECIMAL(9, 2) quieres cambiar a D_PRECIO1 DECIMAL(18, 2)

D_APELLIDOS VARCHAR(20) quieres cambiar a D_APELLIDOS VARCHAR(25)

¿Cómo actuará el Firebird en una situación así?

Cambiar cada fila de cada tabla que usa el dominio no es práctico, es totalmente improductivo, porque algunas tablas pueden tener millones y millones de filas y estar siendo usadas por cientos o miles de usuarios. Hacer algo así demoraría mucho tiempo y además podría causar un sinfín de problemas.

Por lo tanto, lo que el Firebird hace es lo siguiente:

  1. Cuando se cambia un dominio, no altera a ninguna columna de ninguna tabla. Todo sigue igual en las tablas que creó el desarrollador.
  2. Los que sí cambian son los metadatos, las estructuras de las tablas que usan ese dominio son cambiadas.
  3. Cuando se hace un INSERT, usa el nuevo dominio en la fila insertada
  4. Cuando se hace un UPDATE, usa el nuevo dominio en la fila actualizada (recuerda que cada UPDATE crea una nueva fila. Y en esa nueva fila se usará el nuevo dominio). La fila original no se toca, queda como estaba.
  5. Cuando se hace un SELECT, las filas que tienen el dominio viejo serán cambiadas en memoria para que tengan el dominio nuevo, así que al ver las filas obtenidas se las verá con el dominio nuevo. Las filas no son cambiadas en la Base de Datos, el cambio se hace solamente en la memoria RAM de la computadora del Servidor.

¿Es posible conseguir que todas las columnas de todas las tablas utilicen el nuevo dominio?

Sí. Usando GBAK para hacer un ciclo backup/restore la Base de Datos restaurada tendrá todas las columnas con el nuevo dominio.

GBAK hará que todas las columnas de todas las filas de todas las tablas estén estructuradas con los últimos dominios definidos.

Recuerda que la Base de Datos original no se cambia, queda como estaba, la que se cambia es la Base de Datos restaurada.

Conclusión:

El Firebird permite cambiar la definición de un dominio, si el nuevo dominio permitirá guardar más datos que el viejo dominio.

Cuando se cambia un dominio, se cambia la estructura de las tablas que usan ese dominio (es decir, los metadatos) pero no se cambian las filas que insertaron los usuarios. Esas filas quedan igual, no se tocan.

Cambiar las filas que insertaron los usuarios demoraría mucho tiempo y podría causar muchos problemas, sería una idea muy mala, por lo tanto el Firebird hace lo más inteligente:

Cuando se hace un INSERT, se usa el nuevo dominio. Cuando se hace un UPDATE, en la fila actualizada se usa el nuevo dominio. Cuando se hace un SELECT, las filas que tenían el dominio viejo se cambian en memoria para que tengan el dominio nuevo y así los usuarios puedan ver esas columnas con el dominio nuevo.

Si se desea que todas las columnas usen físicamente el nuevo dominio, hay que hacer un ciclo backup/restore usando el programa GBAK. Así, la Base de Datos restaurada tendrá a todas sus columnas estructuradas con los últimos dominios definidos.

Artículos relacionados:

Entendiendo a los metadatos del programador

Entendiendo a los dominios

La forma más fácil de cambiar un dominio

El índice del blog Firebird21

El foro del blog Firebird21

Validando números de teléfono

2 comentarios

La operación más importante de todas en una Base de Datos es la introducción de datos válidos. Si los datos no son válidos entonces todo lo demás que hagamos (consultas, procesamientos) será incorrecto y no servirá, será inútil.

Un dato que a veces es importante validar y a veces no, es el número de teléfono.

Muchas veces el número de teléfono es simplemente informativo, está ahí pero prácticamente no se lo usa. Sin embargo en ocasiones es extremadamente importante que sea válido. Es para estos últimos casos que debemos asegurarnos de que pueda ser utilizado cuando se lo necesita.

¿Cómo validamos un número de teléfono?

Los números de teléfono completos siempre están compuestos de la siguiente forma:

  • Código del país o región
  • Código del área
  • Número local

Cada país o región grande tiene un número que lo identifica. Por ejemplo el código de Argentina es 54, el de Bolivia 591, el de Brasil 55, el de Estados Unidos 1, el de Paraguay 595, el de Puerto Rico 1787, etc. Para saber que se trata del código del país o región y no de un número local, se le antecede con el signo + o con doble cero. Es decir que escribir 00 ó escribir + es lo mismo, la misma cosa.

El código del área (el área puede ser una ciudad grande, un estado o provincia o departamento) siempre viene a continuación del código del país.

Y finalmente viene el número local.

Ejemplos:

+49 30 1234567 corresponde a Alemania (porque empieza con 49), Berlín (porque continúa con 30), y el 1234567 es el número de esa ciudad y país. También se lo podría haber escrito como. 00 49 30 1234567.

+54 351 1234567 corresponde a Argentina (porque empieza con 54), provincia de Córdoba (porque sigue con 351) y el 1234567 es el número de esa ciudad y de ese país al cual se desea llamar. También se lo podría escribir como: 00 54 351 1234567.

+595 21 123456 corresponde a Paraguay (porque empieza con 595), ciudad de Asunción (porque sigue con 21),  el 123456 es el número de esa ciudad y ese país al cual se desea llamar. También se lo podría escribir como: 00 595 21 123456.

¿Cómo diseñamos la tabla?

Es muy común que para guardar números de teléfono se defina una columna como CHAR o VARCHAR y luego una longitud suficientemente grande, como por ejemplo:

CREATE TABLE CLIENTES(
   CLI_IDENTI INTEGER,
   CLI_NOMBRE VARCHAR(80),
   CLI_TELEFO CHAR(20)
);

En este caso la columna CLI_TELEFO está definida como CHAR(20) y la mayoría de los diseñadores pueden creer que está bien. Sin embargo, no es lo correcto. ¿Por qué no es correcto? Porque los usuarios en la columna CLI_TELEFO podrían insertar datos así:

1234567

+595-21-1234567

021-1234567

21.1234567

00-595-21-1234567

Y si nuestra aplicación debe usar esos números para hacerles llamadas a los clientes será muy problemático conseguirlo. Lo correcto por lo tanto es:

CREATE TABLE CLIENTES(
   CLI_IDENTI INTEGER,         -- Identificador del Cliente
   CLI_NOMBRE VARCHAR(80),     -- Nombre del Cliente
   CLI_CODPAI CHAR(4),         -- Código del país al cual corresponde el teléfono
   CLI_CODARE CHAR(4),         -- Código del área dentro del país
   CLI_TELEFO CHAR(8)          -- Número de teléfono local
);

De esa manera nunca habrá confusión posible. Inclusive podríamos tener un trigger BEFORE INSERT OR UPDATE que verifique el código del país, algo como:

CREATE EXCEPTION 
   E_CODIGO_PAIS_INCORRECTO 'El código del país es incorrecto';
CREATE TRIGGER CLIENTES_BIU FOR CLIENTES
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 1
   AS
   BEGIN
 
      IF (NEW.CLI_CODPAI <> '54' AND NEW.CLI_CODPAI <> '595') THEN
         EXCEPTION E_CODIGO_PAIS_INCORRECTO;
 
   END;

Este trigger verifica que el país sea Argentina (código 54) o que sea Paraguay (código 595). Si los países fueran más de 2 entonces lo más conveniente sería tener una tabla, llamada por ejemplo PAISES en la cual se encontraran los códigos y los nombres de los países.

De todas maneras lo importante del trigger es que valida que el país ingresado sea solamente uno de los países válidos, evitando así que el usuario introduzca datos que no tienen sentido.

Análogamente a como se validó el código del país se podría validar el código del área. Algo como:

CREATE TRIGGER CLIENTES_BIU FOR CLIENTES
   ACTIVE BEFORE INSERT OR UPDATE
   POSITION 1
   AS
   BEGIN
 
      IF (NEW.CLI_CODPAI <> '54' AND NEW.CLI_CODPAI <> '595') THEN
         EXCEPTION E_CODIGO_PAIS_INCORRECTO;
 
      IF (NEW.CLI_CODPAI = '595') THEN BEGIN
         IF (NEW.CLI_CODARE <> '21' AND NEW.CLI_CODARE <> '61') THEN
            EXCEPTION E_CODIGO_AREA_INCORRECTO;
      END
 
   END;

Aquí, si el código del país es 595 el código del área debe ser 21 ó 61, ningún otro código de área será aceptado.

Conclusión:

Validar los números de teléfono a veces no es importante porque es un dato meramente informativo, pero hay ocasiones en que debemos asegurarnos de que sea un número válido. Para esos casos lo más conveniente es agruparlo según el código del país, del área, y número local. Esas tres columnas deben ser validadas para asegurarnos de que contienen números correctos.

Artículos relacionados:

Diseño de bases de datos. 1FN

El índice del blog Firebird21

El foro del blog Firebird21

Cambiando el tipo de una columna: de caracter a numérico y viceversa

3 comentarios

Lo ideal es siempre que una vez que has definido la cantidad de columnas de cada tabla y el tipo de cada una de esas columnas ya no necesites modificarlas. Pero lo ideal … no siempre es lo que ocurre en la vida real.

Entonces, ahora te encuentras con una situación en la cual consideras conveniente o imprescindible cambiar el tipo de una columna. Por ejemplo era VARCHAR y la quieres convertir a INTEGER. O era SMALLINT y la quieres convertir a CHAR, cosas así.

El comando deseado

Si tienes una columna que es de tipo CHAR o VARCHAR y la quieres cambiar a INTEGER muchos desearían que existiera algo similar a:

ALTER TABLE
   MiTabla
ALTER COLUMN
   MiColumna
TYPE
   INTEGER

Pero tal comando no existe en Firebird, si lo intentas obtendrás el mensaje: “Cannot change datatype for column MiColumna from a character type to a non-character type.

¿Por qué, cuál es el problema? El problema es que Firebird no hace la conversión en el momento en que escribes un comando como el anterior sino en el momento en que consultas su valor (con un SELECT) o cambias su valor (con un UPDATE). Por lo tanto cuando escribes el comando ALTER TABLE él no puede saber si todos los valores pueden convertirse a INTEGER o no. Y entonces hace lo más seguro: rechaza el cambio.

Claro, tú puedes decir: “pero si solamente hay números enteros en esa columna, debería aceptar el cambio del tipo de datos”. Eso tú lo sabes, pero el Firebird no lo sabe porque no verifica que efectivamente así sea. ¿Y por qué no lo verifica? Porque tu tabla podría tener millones y millones de filas y verificarla podría demorar mucho tiempo, por lo tanto hace la sencilla: impide que modifiques el tipo de datos.

La solución

Ya hemos visto cual es el problema, ahora veremos que hay una solución. No es tan sencilla como escribir un comando ALTER TABLE pero funciona.

Paso 1. Verificar que todos los valores puedan ser convertidos a INTEGER

Evidentemente que el 100% de los valores deben poder convertirse a INTEGER, si hay al menos uno que no puede convertirse entonces primero hay que solucionar eso y luego continuar con los siguientes pasos.

SELECT
   CAST(MiColumna AS INTEGER) AS TodosSonNumerosEnteros
FROM
   MiTabla;

Si el SELECT anterior terminó con errores, y quieres ver cuales son las filas que tienen esos errores (o sea, las filas que no tienen números enteros) el siguiente artículo te muestra lo que puedes hacer para ver a esas filas problemáticas:

Validando que el contenido de un CHAR o VARCHAR sea numérico

Si el SELECT anterior terminó sin errores, entonces seguimos con el:

Paso 2. Renombrar la vieja columna

COMMIT;

ALTER TABLE
   MiTabla
ALTER COLUMN
   MiColumna
TO
   ColumnaVieja;

Si esta columna tiene dependencias (es decir, si ha sido usada en alguna vista, stored procedure o trigger) entonces obtendrás un mensaje de error. Debes solucionar eso antes de poder continuar.

Este paso es muy importante porque te permite descubrir si la columna tiene dependencias.

Paso 3. Agregarle una columna a la tabla con el tipo de datos deseado

COMMIT;

ALTER TABLE
   MiTabla
ADD
   MiNuevaColumna INTEGER;

Paso 4. Agregarle los valores a la nueva columna

COMMIT;

UPDATE
   MiTabla
SET
   MiNuevaColumna = CAST(ColumnaVieja AS INTEGER)
WHERE
   ColumnaVieja IS NOT NULL;

Paso 5. Verificar que la nueva columna tenga los valores correctos

COMMIT;

SELECT
   (MiNuevaColumna + 1)
FROM
   MiTabla;

Si el Firebird te muestra algún mensaje de error (supuestamente todo debería estar ok, pero siempre lo mejor es asegurarse de que así sea) entonces tienes que buscar el motivo y solucionarlo, antes de continuar.

Paso 6. Borrar la columna vieja

Si el SELECT anterior finalizó sin errores, entonces ya podemos borrar con toda seguridad a la columna vieja, porque ya no la necesitaremos más.

COMMIT;

ALTER TABLE
   MiTabla
DROP COLUMN
   ColumnaVieja;

COMMIT;

Este paso es muy conveniente hacerlo para no confundirnos. Si no borramos a la columna vieja más adelante podríamos estar asignándole valores, algo que muy probablemente ya no deberíamos hacer.

Paso 7 (opcional). Renombrar a la nueva columna

Si deseas que la nueva columna tenga el mismo nombre que tenía la columna vieja, entonces:

ALTER TABLE
   MiTabla
ALTER COLUMN
   MiNuevaColumna
TO
   MiColumna

COMMIT;

Paso 8 (opcional). Poner a la nueva columna en la misma posición que tenía la vieja columna

Si quieres que la nueva columna que acabas de crear se encuentre en la misma posición que tenía la columna vieja que acabas de borrar, entonces:

ALTER TABLE
   MiTabla
ALTER
   MiColumna
POSITION
   6;

COMMIT;

Si la vieja columna se encontraba en la posición 1, entonces después de POSITION escribirías 1. Si se encontraba en la posición 2, escribirías 2, y así sucesivamente. El 6 que se puso arriba es solamente un ejemplo.

Conclusión:

En Firebird no hay un comando que nos permita cambiar el tipo de datos de una columna. Y no lo hay por nuestra propia seguridad, de esa manera se evita que cometamos errores graves como convertir texto a INTEGER.

Sin embargo, si necesitamos hacerlo hay formas de conseguirlo. En este artículo se mostró la forma más segura de todas (no la única, pero sí la más segura y conveniente de utilizar).

Con el Paso 1. verificamos que todos los números sean enteros. Con el Paso 2. verificamos que no hay dependencias. Con el Paso 3. agregamos una columna que tiene el tipo de datos que deseamos tener. Con el Paso 4. le agregamos a la nueva columna los valores que debe tener. Con el Paso 5. verificamos que la nueva columna tenga los valores correctos. Con el Paso 6. borramos a la vieja columna, esto nos evitará confusiones más adelante. Con el Paso 7. renombramos a la nueva columna, para que se llame igual a la columna original. Con el Paso 8. ponemos a la nueva columna en la misma posición que tenía la columna original.

Siguiendo estos pasos te asegurarás que todo se ha realizado correctamente. Todo quedará como debe quedar.

Por lo tanto, si alguna vez necesitas cambiar el tipo de datos de una columna, ya sabes como lograrlo.

Artículos relacionados:

Validando que el contenido de un CHAR o VARCHAR sea numérico

El índice del blog Firebird21

El foro del blog Firebird21

Almacenamiento de las columnas de tipo BLOB

Deja un comentario

En Firebird tenemos la posibilidad de almacenar (guardar) archivos dentro de las bases de datos. Podemos usar esta característica para guardar documentos de texto, hojas de cálculo, gráficos, fotografías, canciones, vídeos, etc.

Para ello, declaramos a la columna como de tipo BLOB (Binary Large OBjets), o sea: “objetos binarios de gran tamaño”.

¿Dónde se guardan esos objetos binarios de gran tamaño?

Si declaramos a una columna como siendo de tipo CHAR, VARCHAR, SMALLINT, INTEGER, etc., es en esa misma columna donde se guardan sus valores. Pero con las columnas de tipo BLOB no sucede así. En este caso en la columna se guarda un puntero (un número que indica una dirección) a la ubicación física del archivo BLOB.

(Algo similar ocurre en los xBase: dBase, Visual FoxPro, etc., allí se les llama “campos memo” y se guardan en archivos distintos a las tablas .DBF)

Firebird hace lo siguiente:

  • Si el archivo BLOB cabe en la misma página que su fila (registro), se guarda en esa página. Recuerda que los tamaños de página pueden ser de 4.096, 8.192, 16.384 bytes. Como en general los archivos BLOB tienen un tamaño mayor a 16.384 bytes entonces es muy raro que sean guardados en la misma página que su fila respectiva.
  • Si el archivo BLOB no cabe en la misma página que su fila (registro) entonces se guarda en otra o en otras páginas. Estas páginas son del tipo “overflow”. Recordarás que todo en Firebird se guarda dentro de páginas. Los archivos BLOB no son la excepción. Esas “páginas de overflow” siempre están relacionadas con una tabla y por lo tanto son localizadas a través de las filas (registros) de esa tabla. El Firebird sabe a cual tabla pertenece cada “página de overflow” porque en la cabecera de la “página de overflow” coloca ese dato, así también como la fila (registro) que le corresponde y la secuencia en que se encuentra (si un archivo BLOB ocupa varias páginas el Firebird debe poder saber cual es la primera página, cual la segunda, cual la tercera, etc.)

Conclusión:

Las columnas de tipo BLOB normalmente se utilizan para guardar archivos dentro de ellas. Esos archivos como todo en Firebird se guardan dentro de páginas de la Base de Datos. Si el archivo BLOB es pequeño entonces podría guardarse en la misma página que su fila respectiva, pero lo normal es que el archivo BLOB sea más grande que el tamaño de una página y en ese caso se guarda en páginas llamadas de “overflow”. En la cabecera de las páginas de overflow se encuentran los datos que permiten saber a cual  tabla pertenecen, a cual fila de esa tabla, y la posición de esa página con relación a las demás páginas de ese archivo BLOB.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Older Entries