Poniendo los JOIN en el orden correcto

1 comentario

Muchas veces cuando escribimos un SELECT que no es muy sencillo necesitamos relacionar a una tabla con otra u otras tablas. Eso lo hacemos con las cláusulas JOIN.

Como sabemos, hay dos clases de JOIN:

  • INNER JOIN
  • OUTER JOIN

La palabra INNER puede omitirse si se desea, ya que es la que por defecto asumirá el Firebird si no se escribe algo antes de la palabra JOIN. Los OUTER a su vez pueden ser: LEFT, RIGHT, FULL

Entonces, la pregunta es: ¿importa si se escribe primero un INNER o un OUTER? ¿O es lo mismo?

Consulta 1. Poniendo primero el INNER JOIN

SELECT
   *
FROM
   MiTabla1
JOIN
   MiTabla2
      ON MiCondición1
LEFT JOIN
   MiTabla3
      ON MiCondición2

Aquí se escribió primero el INNER JOIN (la palabra INNER no es obligatorio escribir) y luego el OUTER JOIN (la palabra OUTER tampoco es obligatorio escribir, al escribir LEFT ya el Firebird sabe que se trata de un OUTER)

Consulta 2. Poniendo primero el OUTER JOIN

SELECT
   *
FROM
   MiTabla1
LEFT JOIN
   MiTabla3
      ON MiCondición2
JOIN
   MiTabla2
      ON MiCondición1

Aquí se escribió primero el OUTER JOIN (en este caso, fue un LEFT) y luego el INNER JOIN (la palabra INNER es opcional, no se requiere escribirla)

¿Cuál consulta es mejor?

¿La Consulta 1 es más eficiente que la Consulta 2? ¿La Consulta 2 es más eficiente que la Consulta 1? ¿O son igual de eficientes?

El optimizador del Firebird solamente reordena las tablas en el PLAN hasta que encuentra el primer OUTER JOIN (sea LEFT, RIGHT, o FULL).

Por lo tanto, la correcta es la Consulta 1.

Conclusión:

El orden de los JOIN sí importa en Firebird porque el optimizador reordena las tablas para obtener un mejor PLAN solamente hasta que encuentra el primer OUTER JOIN.

¿Consejo?

Revisa todos tus SELECTs y todas tus vistas para asegurarte de que siempre los INNER JOIN se encuentren antes de los OUTER JOIN, conseguirás consultas más eficientes de esa manera.

Artículos relacionados:

INNER JOIN y OUTER JOIN

Ejemplo Nº 001 – Usando INNER JOIN

Ejemplo Nº 002 – Usando INNER JOIN eficientemente

Ejemplo Nº 003 – Escribiendo varios INNER JOIN

Ejemplo Nº 004 – Usando un INNER JOIN para autoreferenciar una tabla

Ejemplo Nº 006 – Usando LEFT JOIN e INNER JOIN

JOIN implícito y JOIN explícito

NATURAL JOIN y CROSS JOIN

Relacionando dos tablas: la forma vieja y la forma nueva

Teoría de Conjuntos: Unión, Intersección, Diferencia

Entendiendo las tablas autoreferenciadas

Autoreferenciar una tabla. Algunos ejemplos

El índice del blog Firebird21

El foro del blog Firebird21

Anuncios

Optimizando un SELECT al usar una tabla derivada

1 comentario

Las tablas derivadas son una de las construcciones que podemos usar con Firebird pero que poca gente conoce o utiliza. Bien utilizadas pueden resultar muy útiles pues nos permitirán obtener los resultados con mucha mayor velocidad.

Una tabla derivada, como ya habíamos visto en artículos anteriores es un SELECT que se escribe a continuación de la cláusula FROM.

¿Cuál es la ventaja de usarlas?

Que los datos de nuestro SELECT principal ya se encuentran filtrados por el contenido de la tabla derivada. De esta manera, la cantidad de filas a procesar es mucho menor, y por ese motivo el consumo de memoria es pequeño, y la velocidad de respuesta es muy alta.

Veamos un ejemplo para explicar mejor el concepto.

Problema: devolver la primera fila de cada grupo

Tenemos una tabla llamada MOVIMCAB (cabecera de movimientos) donde guardamos los datos de cabecera de cada venta realizada. Y una tabla llamada MOVIMDET (detalles de movimientos) donde guardamos los datos de cada producto vendido, esta tabla es hija de MOVIMCAB y por lo tanto tiene una restricción Foreign Key con ella.

TABLAS_DERIVADAS_01

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

MVC_CODSUC = Código de la sucursal
MVC_IDENTI = Identificador de la fila
MVC_FECHAX = Fecha de la venta
MVC_IDECLI = Identificador del Cliente
MVC_TIPDOC = Tipo de documento
MVC_NRODOC = Número de documento

TABLAS_DERIVADAS_02

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

MOV_CODSUC = Código de la sucursal
MOV_IDENTI = Identificador de la fila
MOV_IDECAB = Identificador de la fila cabecera, o sea la fila de MOVIMCAB
MOV_IDEPRD = Identificador del producto o artículo vendido
MOV_CANTID = Cantidad vendida
MOV_PRECIO = Precio unitario de venta

Lo que deseamos obtener es el primer producto vendido en cada venta. O sea, lo que vemos en la Captura 3.

TABLAS_DERIVADAS_03

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

Solución 1. Sin usar tablas derivadas

SELECT
   T1.MOV_IDENTI,
   T1.MOV_IDECAB,
   T2.MVC_FECHAX,
   T2.MVC_NRODOC,
   T1.MOV_IDEPRD,
   T1.MOV_CANTID,
   T1.MOV_PRECIO
FROM
   MOVIMDET T1
JOIN
   MOVIMCAB T2
      ON MOV_IDECAB = MVC_IDENTI
WHERE
   T1.MOV_IDENTI = (SELECT
                       FIRST 1
                       T2.MOV_IDENTI
                    FROM
                       MOVIMDET T2
                    WHERE
                       T1.MOV_IDECAB = T2.MOV_IDECAB
                    )

TABLAS_DERIVADAS_04

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

Solución 2. Sin usar tablas derivadas

SELECT
   MOV_IDENTI,
   MOV_IDECAB,
   MVC_FECHAX,
   MVC_NRODOC,
   MOV_IDEPRD,
   MOV_CANTID,
   MOV_PRECIO
FROM
   MOVIMDET T1
JOIN
   MOVIMCAB T2
      ON MOV_IDECAB = MVC_IDENTI
WHERE
   NOT EXISTS(SELECT
                 *
              FROM
                 MOVIMDET T3
              WHERE
                 T1.MOV_IDECAB = T3.MOV_IDECAB AND
                 T1.MOV_IDENTI > T3.MOV_IDENTI)

TABLAS_DERIVADAS_05

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

Solución 3. Usando una tabla derivada

SELECT
   T1.MOV_IDENTI,
   T1.MOV_IDECAB,
   T1.MOV_FECHAX,
   T1.MOV_NRODOC,
   T2.MOV_IDEPRD,
   T2.MOV_CANTID,
   T2.MOV_PRECIO
FROM
   (SELECT
       MIN(MOV_IDENTI) AS MOV_IDENTI,
       MOV_IDECAB,
       MVC_FECHAX AS MOV_FECHAX,
       MVC_NRODOC AS MOV_NRODOC
    FROM
       MOVIMDET
    JOIN
       MOVIMCAB
          ON MOV_IDECAB = MVC_IDENTI
    GROUP BY
       2, 3, 4
    ) T1
JOIN
   MOVIMDET T2
      ON T1.MOV_IDENTI = T2.MOV_IDENTI

TABLAS_DERIVADAS_06

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

Análisis

Con las tres consultas se obtiene el resultado deseado pero como podemos observar en los gráficos de sus rendimientos no es lo mismo usar una que otra.

La primera consulta es claramente ineficiente porque lee 2 veces la tabla MOVIMDET, una vez usando índice y la otra vez sin usarlo, así que en tablas grandes, que tienen millones y millones de filas el tiempo que se demorará será muy grande. Podría usarse en tablas pequeñas porque no se notará mucho que es lenta, pero en tablas grandes será inaceptable.

La segunda consulta también lee todas las filas de la tabla MOVIMDET y sin usar un índice. Eso no es un error ni está mal, ya que para obtener el resultado deseado sí o sí hay que leer todas las filas de esa tabla. En este caso, como hay que recorrer toda la tabla, el que no se haya usado un índice es algo positivo, con un índice se demoraría más.

La tercera consulta, usando una tabla derivada, también es muy eficiente. Que no se haya usado un índice en la tabla MOVIMCAB no es algo malo porque como está relacionada con MOVIMDET a través de una Foreign Key cuando se lee una fila de MOVIMDET ya se conoce cual es la fila que le corresponde en MOVIMCAB, así que no es necesario un índice ahí, más bien al contrario: si se usa un índice en MOVIMCAB se demoraría más. ¿Por qué en MOVIMDET se leyeron 13 filas? Porque se leyeron todas las filas de esa tabla (que son 8) y además se leyeron las que cumplían la condición de ser la primera de cada grupo (que son 5). La suma da 13. En MOVIMCAB se leyeron todas las filas de esa tabla, porque sí o sí se necesita leer esa tabla completa.

Conclusión:

Si usamos Firebird normalmente hay varias consultas que podemos escribir para obtener el resultado deseado. Pero eso no significa que el rendimiento es igual en todas esas consultas, no, no es así. En general siempre habrá una consulta que sea mejor que las demás y si somos buenos profesionales deberíamos preocuparnos por encontrar a la mejor consulta posible.

Una de las técnicas que disponemos es a través del uso de tablas derivadas. Con ellas lo que hacemos es filtrar los datos previamente a extraer las filas que nos interesan. En muchos casos (aunque no en todos, por supuesto) el uso de tablas derivadas puede ayudarnos a aumentar la velocidad con la cual se obtienen los resultados deseados.

Entonces, si descubrimos que una consulta está lenta o que lee demasiadas filas de las tablas, tendríamos que pensar en el uso de una tabla derivada, podría ser la mejor solución.

Artículos relacionados:

Tablas derivadas

Entendiendo subconsultas y tablas derivadas

Optimizando los JOIN

Optimizando las subconsultas

El índice del blog Firebird21

El foro del blog Firebird21

Protegiendo las bases de datos contra accesos no autorizados

Deja un comentario

Una de las mayores preocupaciones de quienes guardan datos sensibles en bases de datos es acerca de la seguridad con que se cuenta para evitar el acceso no autorizado. Entonces ¿qué podemos decir sobre este tema?

Primero, y fundamental, que la seguridad implica tomar muchas medidas de precaución, no solamente una.

La seguridad de una Base de Datos no debe estar basada en nombres de usuarios y en contraseñas. Realmente en Firebird el intruso solamente debe conocer la contraseña del usuario SYSDBA y ya podrá hacer lo que se le antoje.

Creer que la contraseña lo es todo en cuanto a seguridad, es un gran error.

¿Por qué?

Porque el elemento más débil en la seguridad, no es la contraseña sino que es … el usuario humano.

Si el usuario permite que le vean el teclado mientras escribe la contraseña … murió la seguridad

Si el usuario escribe la contraseña en un papel que otros pueden ver … murió la seguridad

Si el usuario le dice a otra persona cual es su contraseña … murió la seguridad

Si el usuario elige una contraseña fácil de descubrir … murió la seguridad

Si el intruso puede copiar la Base de Datos en un pen-drive … murió la seguridad

Explicación:

Si alguien puede ver tu teclado mientras estás escribiendo la contraseña del usuario SYSDBA entonces te puede estar filmando (quizás a una distancia prudencial), con cualquier teléfono celular (móvil) o con esas minicámaras que se disimulan en bolígrafos, relojes, u otros aparatos. Luego, simplemente reproduce la filmación y podrá descubrir cuales teclas presionaste, y en que orden lo hiciste. Y listo, ya conoce la contraseña.

Si escribes la contraseña en un papel, archivo de texto, planilla Excel, o cualquier otro lugar donde alguien más puede leerlo, entonces ya la consiguió. Hay gente que se inventa una contraseña muy complicada, y para no olvidarla, la anota en un papel que deja en el primer cajón de su escritorio. Algo como: “contraseña de SYSDBA = euldlm67”

Mujeres sexies que actuaron de espías hay varias en la Historia, quizás la más conocida haya sido Mata Hari, quien se acostaba con los jefes del ejército enemigo para sacarles información. En una noche de juerga, sexo y borrachera, lo que menos le importará a un fulano es contarle cual es la contraseña del usuario SYSDBA a su amante. Estos casos, aunque no involucren sexo, se conocen como “Ingeniería Social” donde a la víctima se le quita información usando la psicología. Diciéndolo lo importante que es, o algunas otras frases se lo dispone a compartir esa contraseña que debería mantener en secreto.

Hay contraseñas que son muy fáciles de descubrir, entre ellas tenemos a: “123456”, “1234567”, “12345678”, “admin”, “supervisor”, “root” y unas 50 más, que son ampliamente utilizadas por una gran cantidad de personas. Cualquier aprendiz de hacker sabe que debe empezar a intentar acceder con ellas, pues muchas personas las utilizan. Si se conocen algunos datos del usuario eso también puede facilitar la obtención de la contraseña. Por ejemplo, en las mujeres es muy frecuente que usen el nombre del novio, del perro, o del hijo. En los hombres, el club de fútbol, un jugador famoso, un personaje de historieta o de ficción. Entonces, usando “Ingeniería Social” se averiguan esos nombres y listo.

Cambiando la contraseña del usuario

Sabiendo que el eslabón más débil es el usuario porque muchos no entienden la importancia de mantener la contraseña en absoluto secreto, hay que tratar de disminuir la probabilidad de que si la comunican a otra persona, por accidente o por intención, el intruso pueda conectarse a la Base de Datos.

Un método, que no es infalible pero ayuda a aumentar la seguridad es el siguiente:

  • En las aplicaciones (Contabilidad, Ventas, Producción, Sueldos, etc.) jamás se debe permitir que ingrese el usuario SYSDBA
  • En las aplicaciones (Contabilidad, Ventas, Producción, Sueldos, etc.) jamás debe permitirse que ingrese un usuario con el rol RDB$ADMIN, pues en esa Base de Datos tendrá los mismos privilegios que el usuario SYSDBA
  • La aplicación debe verificar que el usuario no sea SYSDBA y que el rol utilizado no sea RDB$ADMIN
  • Las tareas administrativas de la Base de Datos que requieren del usuario SYSDBA o de un usuario con el rol RDB$ADMIN (backup, sweep, etc.) deben realizarse a través de una aplicación diseñada para ese efecto. Nada de hacerlas en la línea de comando.
  • La contraseña que el usuario introduce en esa aplicación jamás debe ser la contraseña verdadera del usuario SYSDBA o del usuario que tiene el rol RDB$ADMIN. La aplicación debe transformar la contraseña introducida por el usuario en otra contraseña, en la verdadera contraseña. Esto es muy fácil de hacer, por ejemplo si se reemplaza cada carácter de la contraseña por el siguiente, se convertirá a “12345678” en “23456789”. La contraseña que introduce el usuario es “12345678”, la verdadera contraseña es “23456789”. Si el idiota del usuario le comunica su contraseña a otra persona, y esta otra persona quiere conectarse desde la línea de comandos como SYSDBA con la contraseña “12345678” (que es la que el usuario conoce y utiliza) no lo conseguirá, porque esa no es la verdadera contraseña. Esa es la contraseña que introduce en la aplicación, pero no es la contraseña requerida para realizar la conexión. Desde luego que el algoritmo utilizado para cambiar la contraseña no debe ser tan simple como el de este ejemplo. Un buen algoritmo puede convertir a “12345678” en algo como “X&9_7sxÇ”

Protegiendo una Base de Datos:

En entornos donde la seguridad es importante, las medidas de protección mínimas son las siguientes:

  1. La computadora donde se encuentra el Servidor no debe tener puertos USB habilitados ni grabador de CD/DVD
  2. La computadora donde se encuentra el Servidor no debe tener acceso directo a Internet
  3. La computadora donde se encuentra el Servidor debe encontrarse en una habitación aparte, la cual normalmente se encuentra cerrada con llave, y esa llave se guarda en un lugar seguro
  4. La conexión a la Base de Datos se hace siempre con alias
  5. Se registra en un archivo de log, que se guarda afuera de la Base de Datos, los datos de cada conexión: usuario, IP de su computadora, fecha de entrada, hora de entrada, fecha de salida, hora de salida
  6. Se restringe el acceso a la Base de Datos fuera de los días y horarios establecidos
  7. La contraseña que escribe el usuario en la aplicación, nunca debe ser la usada para la conexión sino que debe sometérsela a algún algoritmo que la convierta en la contraseña correcta. Un ejemplo muy sencillo: si en la aplicación el usuario escribe la contraseña “12345678” una función debe convertirla en “23456789”, que es la contraseña reconocida por la Base de Datos. De esa manera, si alguien conoce la contraseña del usuario y quiere acceder a la Base de Datos desde afuera de la aplicación no lo conseguirá porque estará intentando la conexión con una contraseña que no es la correcta.

Conclusión:

Si queremos tener bases de datos seguras lo más importante es siempre concientizar a los usuarios sobre la gran importancia de mantener el acceso restringido y que nunca deben comunicar sus contraseñas a otras personas, por ningún motivo.

Pero tipos que no entenderán aunque se les repita mil veces siempre existirán, por lo tanto no debemos confiar en la gente sino que debemos preocuparnos nosotros mismos de ese aspecto.

La seguridad jamás debe estar basada solamente en contraseñas, porque no es el punto más débil. El punto más débil siempre son los usuarios descuidados, negligentes, ignorantes, o indolentes. Ante gente así, ni el mejor algoritmo de encriptación del mundo servirá de algo.

Un método para mejorar la seguridad que proporcionan las contraseñas es convertir la contraseña introducida por el usuario en otra contraseña, que es la correcta para realizar la conexión.

Artículos relacionados:

Una técnica para dificultar el acceso no autorizado

Inferencia de datos

Impidiendo la conexión a una Base de Datos

Atacando a una Base de Datos: SQL injection

Evitando que los mirones averigüen nuestro password en ISQL

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

Herramientas para Firebird de Devart

Deja un comentario

Devart es una empresa que desarrolla software para motores de bases de datos, entre ellos Firebird.

Tiene oficinas en la República Checa y en Ucrania.

Lo que tiene para ofrecernos es:

Firebird ODBC driver. Que podemos encontrar en: https://www.devart.com/odbc/firebird/

Interbase and Firebird Data Access Components. Es una biblioteca de componentes que provee conexión nativa desde Delphi, C++ Builder, Lazarus y FreePascal. Podemos encontrar en: https://www.devart.com/ibdac/

dbExpress Driver for Interbase and Firebird. Es una interface para rápido acceso a las bases de datos de Firebird, que puede ser utilizado con Delphi y C++ Builder. Podemos encontrar en: https://www.devart.com/dbx/interbase/

– ORM for Delphi with LINQ support works with Firebird. Es un ORM (Object Relational Mapping) que tiene soporte para LINQ (Language Integrated Query), lo cual unifica el código y hace las consultas mucho más rápidas. Podemos encontrar en: https://www.devart.com/entitydac/

– Universal (all databases) Data access components for Delphi, C++ Builder, Lazarus. Es una biblioteca de componentes que provee acceso directo a múltiples bases de datos de diversos motores, entre ellos: Oracle, SQL Server, MySQL, InterBase, Firebird, PostgreSQL, SQLite, DB2, Access, Sybase Advantage Database Server, Sybase Adaptive Server Enterprise, y otras bases de datos si se usa ODBC. Podemos encontrarlo en: https://www.devart.com/unidac/

Conclusión:

Siempre es útil contar con herramientas que nos faciliten el trabajo, así que descargar y evaluar lo que tiene para ofrecernos Devart puede ser una buena idea.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

El tipo de datos BOOLEAN en Firebird 3

6 comentarios

Un tipo de datos que siempre le faltó a Firebird fue el BOOLEAN… hasta ahora.

Desde siempre contábamos con los tipos de datos: SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE PRECISION, NUMERIC, DECIMAL, DATE, TIME, TIMESTAMP, CHAR, VARCHAR, BLOB, pero no contábamos con BOOLEAN, entonces si lo necesitábamos (algo muy frecuente al crear una tabla) lo simulábamos creando un dominio como el siguiente:

CREATE DOMAIN D_BOOLEAN AS
   CHAR(1)
      CHECK (VALUE = 'F' OR VALUE = 'T');

Funcionaba bien, claro que sí, pero no es realmente un tipo de datos BOOLEAN.

¿Por qué no?

Porque le faltan los predicados lógicos. Es decir las comparaciones por verdadero o falso.

Un ejemplo de lo que ahora podemos hacer:

UPDATE
   MiTabla
SET
   MiColumnaBoolean = (MiValor1 IS DISTINCT FROM MiValor2)

Una comparación puede darnos uno de estos tres resultados posibles:

  • Verdadero
  • Falso
  • Nulo o desconocido

(Recuerda que en SQL un valor nulo significa: “desconocido”)

En el ejemplo anterior, si MiValor1 es distinto de MiValor2 en MiColumnaBoolean se guardará Verdadero, ya que la condición se cumplió. Si alguno de esos valores era nulo entonces se guardará Desconocido (porque el resultado de comparar un valor desconocido con cualquier otro valor siempre es desconocido).

Valores posibles de una columna de tipo BOOLEAN

Si declaramos que una columna será de tipo BOOLEAN, en ella podremos guardar cualquiera de los siguientes valores:

  • TRUE
  • FALSE
  • UNKNOWN

TRUE significa “verdadero”, FALSE significa “falso” y UNKNOWN significa “desconocido”.

IMPORTANTE: podemos usar NULL o UNKNOWN, como nos guste más, ambas palabras son sinónimos y pueden usarse intercambiablemente, así que usar una u otra depende del gusto de cada quien.

El operador IS

Para hacer las comparaciones podemos usar el operador IS [NOT], escribiendo algo como:

MiColumna1 IS TRUE
MiColumna2 IS FALSE
MiColumna3 IS NOT TRUE
MiColumna4 IS NOT FALSE
MiColumna5 IS UNKNOWN
MiColumna6 IS NULL
MiColumna7 IS DISTINCT FROM MiColumna1

Los operadores de comparación

Además del operador IS que vimos en el apartado anterior, también podemos comparar con: “=”, “<“, “<=”, “>”, “>=”, “!=”, “<>”

Comparación abreviada

Cuando comparamos por “verdadero”, podemos escribir algo como:

WHERE
   MiColumnaBoolean1

Fíjate que no escribimos MiColumnaBoolean1 IS TRUE, ya que el IS TRUE está implícito. Podemos escribirlo, si queremos, pero no es necesario. Similarmente, para comparar con “falso” podríamos escribir:

WHERE
   NOT MiColumnaBoolean1

En este caso, la condición se cumplirá cuando el valor guardado en la columna MiColumnaBoolean1 sea “falso”. También podríamos haber escrito: MiColumnaBoolean1 IS FALSE, al igual que antes, es cuestión de gustos usar una forma u otra.

Valores devueltos por el comando SELECT

Cuando en la lista de columnas que muestra nuestro SELECT existe alguna de tipo BOOLEAN, los valores que podemos ver son los siguientes:

<true>

<false>

<null>

Convirtiendo un tipo de datos BOOLEAN a CHAR o VARCHAR

Solamente podemos convertir el valor de una columna de tipo BOOLEAN a CHAR o a VARCHAR, no se puede convertir a alguno de los demás tipos de datos.

Para ello, usamos la función CAST()

Ejemplo 1. Creando una tabla que tendrá una columna de tipo BOOLEAN

CREATE TABLE
   MiTabla (
      MiColumnaEntera   INTEGER,
      MiColumnaBoolean1 BOOLEAN
) ;

COMMIT;

Ejemplo 2. Insertando valores en una columna de tipo BOOLEAN

INSERT INTO
   MiTabla
      VALUES (1, TRUE);

INSERT INTO
   MiTabla
      VALUES (2, 5 IS DISTINCT FROM 4);

INSERT INTO
   MiTabla
      VALUES (3, NULL);

Ejemplo 3. Asignando valores a una columna de tipo BOOLEAN

UPDATE
   MiTabla
SET
   MiColumnaBoolean1=TRUE,
   MiColumnaBoolean2=FALSE,
   MiColumnaBoolean3=2=4,
   MiColumnaBoolean4=NULL,
   MiColumnaBoolean5=UNKNOWN,
   MiColumnaBoolean6=5 > 1
WHERE
   MiCondición

Ejemplo 4. Consultando los valores de una columna de tipo BOOLEAN

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumnaBoolean1

Esta consulta nos mostrará todas las filas que tengan TRUE en la columna MiColumnaBoolean1.

Ejemplo 5. Consultando por FALSE

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumnaBoolean1 IS FALSE

Ejemplo 6. Consultando por UNKNOWN

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumnaBoolean1 IS UNKNOWN

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumnaBoolean1 IS NULL

Conclusión:

Algo que siempre le había faltado a Firebird era tener un verdadero tipo de datos BOOLEAN, podíamos suplir esa carencia creando un dominio pero hacerlo así no era del todo completo. Ahora, con Firebird 3 sí ya tenemos un verdadero tipo de datos BOOLEAN.

Los resultados de una comparación pueden ser: “verdadero”, “falso”, “desconocido”. Y esos son justamente los valores que podemos guardar en una columna definida como de tipo BOOLEAN, aunque desde luego que usaremos las palabras reservadas: TRUE, FALSE, UNKNOWN.

Para las comparaciones podemos usar el operador IS [NOT] o los operadores de comparación matemáticos.

El resultado de un SELECT que contenga columnas de tipo BOOLEAN puede ser: <true>, <false>, <null>

Recuerda que NULL en SQL significa “desconocido”, y la palabra UNKNOWN también significa “desconocido”, por eso pueden usarse como sinónimos.

Artículos relacionados:

¿Por qué Firebird 3?

Los archivos de configuración del Firebird 3

Entendiendo a los plug-in del Firebird 3

Parametrizando el archivo DATABASES.CONF

Agregando el usuario SYSDBA en Firebird 3

El índice del blog Firebird21

El foro del blog Firebird21

Older Entries