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

Anuncios

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?

3 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

Older Entries