Entendiendo los índices de expresiones

2 comentarios

Los índices sirven para dos cosas:

  1. Mostrar el resultado de los SELECTs ordenados por el criterio que nos interesa
  2. Realizar búsquedas o filtros, para que solamente sean afectadas las filas que cumplan con la condición que establecimos

Lo más común es que los índices estén compuestos por una o más columnas en forma directa. Veamos un ejemplo:

indices01

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

indices02

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

Tenemos una tabla ALUMNOS y para ordenar a los alumnos por APELLIDOS y por NOMBRES podríamos crear un índice como el siguiente:

Listado 1.

   CREATE INDEX IDX_ALUMNOS ON ALUMNOS(ALU_APELLD, ALU_NOMBRE);

Y está muy bien, funcionará perfectamente.

Podríamos escribir entonces un SELECT como el siguiente:

Listado 2.

SELECT
   *
FROM
   ALUMNOS
ORDER BY
   ALU_APELLD,
   ALU_NOMBRE

Y así obtendríamos un resultado como este:

indices03

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

Donde como puedes observar, los resultados aparecen ordenados por ALU_APELLD. Pero si queremos saber la diferencia entre ALU_TOTANO y ALU_TOTCOB no es posible usar un índice normal.

¿Y entonces?

Entonces la solución es crear un índice de expresión.

¿Qué es un índice de expresión?

Un índice en el cual se utiliza una expresión aritmética o una expresión alfanumérica o funciones internas.

Ejemplos de índices de expresión:

Listado 3.

   CREATE INDEX IDX_ALUMNOS2 ON ALUMNOS COMPUTED BY (LEFT(ALU_APELLD, 1) || LEFT(ALU_NOMBRE, 1));

   CREATE INDEX IDX_ALUMNOS3 ON ALUMNOS COMPUTED BY (ALU_TOTANO - ALU_TOTCOB);

Como puedes ver, la diferencia entre el índice creado en el Listado 1. y los índices creados en el Listado 3., es que en estos últimos se escribieron las palabras COMPUTED BY y también se usó la función LEFT() en IDX_ALUMNOS2 y una operación aritmética de resta en IDX_ALUMNOS3.

En todos los índices de expresión se deben escribir las palabras COMPUTED BY, tal como vimos en el Listado 3.

Usando índices de expresión

Algo muy importante a recordar es que cuando usamos índices de expresión debemos usarlos exactamente igual a como los definimos.

Listado 4.

SELECT
   *
FROM
   ALUMNOS
WHERE
   LEFT(ALU_APELLD, 1) || LEFT(ALU_NOMBRE, 1) = 'KM'

En este caso el Firebird usará el índice IDX_ALUMNOS2 porque la expresión escrita en la cláusula WHERE es la misma expresión usada en la definición del índice IDX_ALUMNOS2.

Listado 5.

SELECT
   *
FROM
   ALUMNOS
WHERE
   LEFT(ALU_NOMBRE, 1) || LEFT(ALU_APELLD, 1) = 'MK'

En el SELECT del Listado 5. el Firebird no usará el índice IDX_ALUMNOS2 porque la condición escrita en la cláusula WHERE no es igual a la expresión usada en la definición del índice IDX_ALUMNOS2.

Listado 6.

SELECT
   *
FROM
   ALUMNOS
WHERE
   ALU_TOTANO - ALU_TOTCOB > 1000

En el SELECT del Listado 6. el Firebird usará el índice IDX_ALUMNOS3 porque la condición escrita en la cláusula WHERE es la misma expresión escrita en la definición del índice IDX_ALUMNOS3.

Listado 7.

SELECT
   *
FROM
   ALUMNOS
ORDER BY
   ALU_TOTANO - ALU_TOTCOB

En el SELECT del Listado 7. también se usará el índice IDX_ALUMNOS3 porque la expresión escrita en la cláusula ORDER BY es la misma expresión que se usó en la definición del índice IDX_ALUMNOS3.

Conclusión:

Los índices de expresión pueden ser muy útiles en algunos casos, es bueno saber que contamos con esta herramienta para poder usarla cuando nos haga falta.

Artículos relacionados:

Optimizando un SELECT que compara columnas de la misma tabla (2)

El índice del blog Firebird21

El foro del blog Firebird21

 

Usando CASE en la cláusula WHERE

8 comentarios

La cláusula WHERE del SELECT nos permite poner condiciones o sea filtrar los datos que obtendremos como resultado.

¿Cómo funciona la cláusula WHERE?

Mediante una comparación.

Cuando comparamos una cosa con otra cosa el resultado de esa comparación solamente puede ser verdadero o falso, no existe otra posibilidad.

Ejemplos:

CLI_CODIGO IS NULL     -- ¿El código del cliente es NULL?

CLI_CODIGO IS NOT NULL     --¿El código del cliente no es NULL?

EXTRACT(YEAR FROM ALU_FECING) = 2016     -- ¿El año en que ingresó el alumno es 2016?

PRD_PREVTA >10000     -- ¿El precio de venta del producto es mayor que 10000?

PRO_NOMBRE LIKE 'J%'     -- ¿El nombre del profesor empieza con la letra Jota?

Como ves, en todas las condiciones anteriores la respuesta puede ser afirmativa (verdadera) o negativa (falsa). No existe otra posibilidad.

Al usar un CASE en la cláusula WHERE de un SELECT debemos tener en cuenta ese punto. Siempre debemos comparar lo que devuelve el CASE con algo.

Listado 1.

SELECT
   *
FROM
   PERSONAS
WHERE
   CASE
      WHEN PER_SEXOXX = 1 THEN 'F'
      WHEN PER_SEXOXX = 2 THEN 'M'
   END = 'F'

Aquí el CASE solamente nos puede devolver una letra ‘F’ o una letra ‘M’, no hay otra posibilidad. Y comparamos lo que nos devolvió con la letra ‘F’ (eso es lo que hacemos después del END).

En otras palabras, la cláusula WHERE podría ser:

WHERE ‘F’ = ‘F’

o podría ser:

WHERE ‘M’ = ‘F’

¿Y qué haríamos si la columna PER_SEXOXX puede contener NULL?

Listado 2.

SELECT
   *
FROM
   PERSONAS
WHERE
   CASE
      WHEN PER_SEXOXX = 1 THEN 'F'
      WHEN PER_SEXOXX = 2 THEN 'M'
      ELSE 'X'
   END = 'F'

O sea que la cláusula WHERE devolverá una letra ‘X’ si la columna PER_SEXOXX es distinta de 1 y es distinta de 2. Por supuesto que devolver una ‘X’ no es obligatorio, puedes devolver cualquier otra letra que quieras.

Artículos relacionados:

Un error de concepto en la cláusula WHERE

Mostrando los resultados ordenados por cualquier criterio

El índice del blog Firebird21

El foro del blog Firebird21

 

Obteniendo las ventas realizadas la semana pasada

Deja un comentario

Supongamos que tienes una tabla de ventas y deseas conocer ¿cuáles fueron las ventas realizadas la semana pasada?

Puedes obtener la respuesta con un SELECT similar al siguiente:

Listado 1:

SELECT
   *
FROM
   MiTabla
WHERE
   MiColumnaFecha BETWEEN CURRENT_DATE - EXTRACT(WEEKDAY FROM CURRENT_DATE) - 6 AND
CURRENT_DATE - EXTRACT(WEEKDAY FROM CURRENT_DATE)
ORDER BY
   MiColumnaFecha

En el Listado 1. se supone que las semanas empiezan los días Lunes, si necesitas que empiecen los días Domingo entonces a la primera fecha del BETWEEN deberías restarle 7 (y no 6) y a la segunda fecha deberías restarle 1.
Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

Poniendo ceros a la izquierda

5 comentarios

A veces necesitamos completar un string para que tenga una longitud fija de caracteres, algo muy común es agregarle ceros a la izquierda.

Por ejemplo, tenemos el string ‘123’ y queremos convertirlo a ‘0000123’

¿Cómo podemos hacerlo?

Usando la función LPAD(), como vemos a continuación:

Listado 1.

SELECT
   LPAD('123', 7, '0')
FROM
   RDB$DATABASE

Donde ‘123’ es el string que queremos modificar, 7 es la cantidad total de caracteres que tendrá el nuevo string, y ‘0’ es el carácter que se le pondrá a la izquierda (por supuesto que aquí podríamos elegir cualquier otro carácter, ponerle ‘0’ es solamente un ejemplo).

Listado 2.

UPDATE
   PRODUCTOS
SET
   PRD_CODIGO = LPAD(PRD_CODIGO, 9, '0')

En el Listado 2. nos aseguramos que todos los códigos de productos tengan exactamente 9 dígitos, rellenando con ceros a la izquierda cuando sea necesario.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

 

 

Entendiendo gráficamente un SELECT simple

1 comentario

En las bases de datos tenemos tablas cuyos contenidos alguna vez es seguro que vamos a querer consultar. En Firebird para consultar a una tabla utilizamos el comando SELECT.

Si nuestra consulta es simple, entonces involucra a una sola tabla. Consultas más complicadas involucrarán a dos o más tablas.

El resultado obtenido de un SELECT es siempre una tabla virtual y temporaria a la cual se acostumbra llamar SQL_RESULT, aunque ese nombre no es obligatorio. Lo importante a recordar es que al ejecutar un SELECT obtenemos una tabla.

En nuestro SELECT podemos pedir:

  • Todas las filas y todas las columnas
  • Todas las filas y algunas columnas
  • Algunas filas y todas las columnas
  • Algunas filas y algunas columnas

Para graficar el concepto, supongamos que tenemos una tabla compuesta por 8 filas y 5 columnas. Veamos cada caso:

SELECT1

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

Conclusión:

Cuando hacemos una consulta a una tabla siempre obtenemos como resultado otra tabla, a la cual llamamos SQL_RESULT.

Esta tabla SQL_RESULT puede contener todas las filas y todas las columnas de la tabla original, o solamente algunas filas, o solamente algunas columnas, como vemos en el Gráfico 1.

Artículos relacionados:

El resultado de un SELECT es una tabla

El índice del blog Firebird21

El foro del blog Firebird21

El resultado de un SELECT es una tabla

3 comentarios

Un concepto que debes tener bien presente y entenderlo muy bien es que en Firebird siempre, sí o sí, en el 100% de los casos, lo que devuelve un SELECT es una tabla.

Cuando ejecutas un SELECT lo que obtienes se llama conjunto resultado.

Y como recordarás de cuando estudiaste Teoría de Conjuntos en Matemática Moderna, un conjunto puede estar vacío, tener un solo elemento, o tener más de un elemento.

Eso exactamente ocurre con lo que devuelve un SELECT.

En el caso del Firebird ese conjunto resultado siempre es una tabla. Esa tabla no tiene un nombre específico, tú puedes llamarla como quieras, pero en la literatura SQL normalmente se la conoce como SQL_RESULT y por lo tanto será el nombre que usaremos en este artículo.

Una tabla, puede tener cero filas, una fila o más de una fila. Y estar compuesta por cero columnas, una columna o más de una columna.

Por lo tanto, la tabla obtenida puede contener:

  • 0 filas y 0 columnas
  • 1 fila y 1 columna
  • 1 fila y varias columnas
  • varias filas y una columna
  • varias filas y varias columnas

Puedes notar que si no hay filas, no hay columnas. Y si hay filas, la cantidad de columnas puede variar, entre una y muchas.

Algo muy importante a tener en cuenta es que como lo que obtienes al ejecutar un SELECT es una tabla, entonces puedes usar esa tabla obtenida dentro de cualquier comando del Firebird: INSERT, UPDATE, DELETE, SELECT.

Veamos algunos ejemplos:

Ejemplo 1. Un SELECT sencillo

SELECT
   *
FROM
   MiTabla1

En este caso el conjunto resultado (el cual, como recordarás es una tabla de nombre SQL_RESULT) consistirá de todas las filas y de todas las columnas de la tabla MiTabla1. ¿Por qué? porque el asterisco sirve para pedir todas las columnas de una tabla. Y como en este caso no se filtró el resultado (eso se hace con las cláusulas WHERE, JOIN, GROUP BY, HAVING, DISTINCT, FIRST, ROWS), se obtienen también todas las filas.

Por lo tanto la tabla SQL_RESULT (es decir, la tabla obtenida al ejecutar el SELECT) es una copia exacta de la tabla original.

Ejemplo 2. Un SELECT con pocas columnas

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla1

Aquí la tabla SQL_RESULT consiste de 3 columnas de MiTabla1 y de todas sus filas. SQL_RESULT tiene todas las filas de MiTabla1 porque no se filtró el resultado.

Ejemplo 3. Un SELECT con una subconsulta

SELECT
   MiColumna1,
   MiColumna2,
   (SELECT MiColumna21 FROM MiTabla2 WHERE MiCondición)
FROM
   MiTabla1

En este caso la tercera columna de la tabla SQL_RESULT se obtuvo de otra tabla, por eso se trata de una subconsulta. Recuerda que MiTabla1 y MiTabla2 pueden tener muchas columnas, pero las columnas de la tabla temporaria SQL_RESULT son las que se piden en el SELECT principal.

Eso significa que la tabla SQL_RESULT tendrá 3 columnas, 2 de ellas provenientes de MiTabla1 y 1 proveniente de MiTabla2.

¿Qué condiciones debe cumplir la subconsulta?

Como puedes ver el Ejemplo 3 es muy similar al Ejemplo 2, la diferencia se da solamente en la tercera columna, que es diferente. Entonces, en la tercera columna del Ejemplo 3 debe colocarse un valor, un solo valor, nunca más de un valor. Ese valor puede ser NULL (si ninguna columna de MiTabla2 cumple con MiCondición) o distinto de NULL (si una y solamente una fila de MiTabla2 cumple con MiCondición).

Por lo tanto, las condiciones a cumplir son:

  1. que en la subconsulta haya solamente una columna (nunca más de una columna)
  2. que una y solamente una fila de MiTabla2 cumpla con MiCondición.

Ejemplo 4. Un SELECT con filtro

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla1
WHERE
   MiValor = (SELECT MiColumna21 FROM MiTabla2 WHERE MiCondición)

Aquí, la condición de filtro (es decir, la especificada en la cláusula WHERE) se obtuvo de una subconsulta.

Como puedes ver, se puede usar sin problemas un SELECT en la cláusula WHERE. Pero para ello es requisito ineludible que ese SELECT devuelva cero filas o una sola fila. Y una sola columna. Si el conjunto resultado de la subconsulta está compuesto por más de una fila o por más de una columna, eso causará un error, ya que en la variable MiValor solamente se puede guardar un valor, no muchos.

Ejemplo 5. Un SELECT con filtro múltiple

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla1
WHERE
   MiValor IN (SELECT MiColumna21 FROM MiTabla2 WHERE MiCondición)

En este caso como no se usó = sino que se usó IN, la subconsulta puede devolver varios valores. O sea que puede devolver varias filas, aunque todas esas filas tendrán una sola columna. Recuerda que IN es una forma simplificada de escribir varios OR

MiValor IN (4, 10, 15) es lo mismo que escribir: MiValor = 4 OR MiValor = 10 OR MiValor = 15

El ahorro se vuelve muy notorio cuando la cantidad de valores distintos aumenta.

Ejemplo 6. Un SELECT con agrupamiento

SELECT
   MiColumna1,
   MiColumna2,
   (SELECT MiColumna21 FROM MiTabla2 WHERE MiCondición)
FROM
   MiTabla1
GROUP BY
   MiColumna1,
   MiColumna2,
   (SELECT MiColumna21 FROM MiTabla2 WHERE MiCondición)

En este caso la subconsulta fue utilizada en la lista de columnas que tendrá la tabla SQL_RESULT y como queríamos obtener los datos agrupados, esa misma subconsulta tuvimos que usar en la clásula GROUP BY.

Lo importante a notar es que podemos, sin problema, agrupar por una subconsulta.

Ejemplo 7. Un SELECT ordenado

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla1
ORDER BY
   (SELECT MiColumna21 FROM MiTabla2 WHERE MiCondición)

Aquí ordenamos a la tabla SQL_RESULT según una columna perteneciente a otra tabla.

Ejemplo 8. Un JOIN usando una subconsulta

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   MiTabla1
JOIN
   MiTabla2
      ON MiColumna1 = (SELECT MiColumna21 FROM MiTabla2 WHERE MiCondición)

Aquí la condición del JOIN se obtuvo de una subconsulta.

Ejemplo 9. Una tabla derivada

SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3
FROM
   (SELECT MiColumna1, MiColumna2, MiColumna3 FROM MiTabla2 WHERE MiCondición)

Cuando a continuación de la cláusula FROM se pone un SELECT, ya no se le llama subconsulta sino tabla derivada.

El requisito es que si se usa alguna de las columnas de la tabla derivada en el SELECT principal, el nombre sea idéntico. Normalmente se usan todas las columnas de la tabla derivada, pero no es obligatorio.

Ejemplo 10. Un INSERT desde una subconsulta

INSERT INTO
   MiTabla1
SELECT
   MiColumna1,
   MiColumna2,
   MiColumna3,
   MiColumna4
FROM
   MiTabla2
WHERE
   MiCondición

Si MiTabla1 tiene 4 columnas, entonces podemos escribir un INSERT como el anterior para agregarle una o más filas.

En este caso el SELECT puede devolver más de una fila. Si el SELECT devuelve 892 filas entonces 892 filas podrán ser agregadas con este simple comando.

Este ejemplo es útil cuando la tabla SQL_RESULT (o sea, la tabla obtenida al ejecutar el SELECT) tiene la misma cantidad de columnas que la tabla donde se insertarán las filas.

Ejemplo 11. Otro INSERT desde una subconsulta

INSERT INTO
   MiTabla1 (
      MiColumna11,
      MiColumna12,
      MiColumna13
   )
SELECT
   MiColumna21,
   MiColumna22,
   MiColumna23
FROM
   MiTabla2
WHERE
   MiCondición

Este tipo de INSERT usamos cuando la cantidad de columnas que tiene la tabla SQL_RESULT no es la misma cantidad de columnas que tiene la tabla MiTabla1. Nuestra tabla MiTabla1 quizás tiene 8 columnas, pero solamente 3 de ellas estamos usando en este INSERT, por eso debemos especificar cuales son esas columnas, poniéndolas entre paréntesis.

Ejemplo 12. Un UPDATE desde una subconsulta

UPDATE
   MiTabla1
SET
   MiColumna1 = (SELECT MiColumna21 FROM MiTabla2 WHERE MiCondición)

Así como podemos usar una subconsulta con el comando INSERT, también podemos usar una subconsulta con los comandos UPDATE y DELETE.

El requisito es que la subconsulta devuelva un solo valor, o ninguno. Si en la subconsulta hay más de una columna o si SQL_RESULT tiene más de una fila, eso provocará un error.

Ejemplo 13. Otro UPDATE desde una subconsulta

UPDATE
   MiTabla1
SET
   MiColumna1 = AlgúnValor
WHERE
   MiValor = (SELECT MiColumna21 FROM MiTabla2 WHERE MiCondición)

Así como podemos poner una subconsulta en la columna que queremos actualizar (como vimos en el Ejemplo 12.), también podemos poner una subconsulta para filtrar con la cláusula WHERE, como vemos en este ejemplo.

Ejemplo 14. Obteniendo el valor de una columna en un stored procedure o en un trigger

Dentro de un stored procedure o dentro de un trigger, podemos escribir algo como:

MiValor = (SELECT MiColumna1 FROM MiTabla1 WHERE MiCondición1);

El contenido de la columna MiColumna1 se guardará en la variable MiValor.

La condición, como es habitual en estos casos, es que SQL_RESULT tenga una sola fila (o ninguna fila, en cuyo caso MiValor será NULL) y una sola columna.

Si SQL_RESULT tiene más de una fila o más de una columna, la asignación anterior no podrá realizarse y provocará un error.

Conclusión:

Lo que obtienes al ejecutar un SELECT es un conjunto de resultados. Ese conjunto de resultados puede tener 0 elementos, 1 elemento, o más de 1 elemento. En Firebird ese conjunto de resultados es tratado como una tabla temporaria. A esa tabla temporaria comúnmente se la llama SQL_RESULT.

O sea que siempre y cada vez que se ejecuta un SELECT se crea una tabla temporaria, cuyo nombre es SQL_RESULT. (Salvo que se ejecuten varios SELECT, en cuyo caso los nombres serán SQL_RESULT, SQL_RESULT2, SQL_RESULT3, etc.)

Como una tabla tiene filas y columnas, y SQL_RESULT es una tabla, entonces puede usarse en cualquier lugar donde un valor es necesitado.

Artículos relacionados:

Tablas derivadas

Entendiendo subconsultas y tablas derivadas

Optimizando un SELECT al usar una tabla derivada

Optimizando las subconsultas

El índice del blog Firebird21

El foro del blog Firebird21

Ejemplo Nº 052 – Comparando strings

Deja un comentario

Cuando comparamos strings el resultado puede no ser el que esperábamos si es que no lo hacemos bien.

Ejemplo:

SELECT
   1
FROM
   RDB$DATABASE
WHERE
   'A' = 'A      '

Este SELECT devolverá 1 aunque los strings son diferentes. ¿Es eso correcto o incorrecto? Pues es lo correcto según el estándar SQL el cual dice lo siguiente: “cuando se comparan strings de distinta longitud, la comparación debe ser hecha como si al string más corto se le agregaran espacios en blanco hasta la longitud del string más largo”.

¿Y si queremos que la condición no se cumpla cuándo las longitudes son diferentes?

En ese caso deberemos utilizar LIKE, como vemos a continuación:

SELECT
   1
FROM
   RDB$DATABASE
WHERE
   'A' LIKE 'A   '

Aquí el resultado de la consulta será un conjunto vacío porque la condición no se cumple.

Conclusión:

Si quieres que la comparación entre dos strings sea estricta (o sea que sean idénticos y que tengan la misma longitud) debes usar el operador LIKE, no el símbolo =

Artículo relacionado:

El índice del blog Firebird21

Entendiendo subconsultas y tablas derivadas

6 comentarios

El lenguaje SQL nos permite escribir un SELECT en muchos lugares, veamos algunos ejemplos:

Ejemplo 1. Un SELECT simple

SELECT
   MiColumna1,
   MiColumna2
FROM
   MiTabla

En este caso se escribió solamente un SELECT, es útil para consultas sencillas pero cuando las consultas se complican ya no será suficiente.

Ejemplo 2. Un SELECT con una subconsulta

SELECT
   MiColumna1,
   (SELECT MiColumna2 FROM MiTabla2 WHERE MiCondición)
FROM
   MiTabla

En este ejemplo hemos reemplazado a MiColumna2 por una subconsulta, o sea por un SELECT dentro de otro SELECT. El SELECT que está entre paréntesis es la subconsulta. Fíjate que además del nombre de la tabla se escribió la cláusula WHERE ¿por qué eso? porque la subconsulta debe devolver una sola columna y una sola fila.

Si la subconsulta devuelve más de una columna entonces verás el error: “Count of column list and variable list do not match”. O sea que se esperaba una sola columna pero la subconsulta está devolviendo más de una columna.

Si devuelve una sola columna pero más de una fila entonces el mensaje de error que verás será: “Multiple rows in singleton select”. ¿qué significa esa frase? que la subconsulta debería devolver una sola fila pero está devolviendo más de una fila.

La cláusula WHERE sirve justamente para poner una condición que estás seguro que se cumple para una sola fila. Además de la cláusula WHERE también podrías usar, dependiendo del caso, las cláusulas FIRST 1, ó ROWS 1 ó DISTINCT o las funciones agregadas MAX(), MIN(), etc. Lo que debes recordar es que la subconsulta debe devolver una fila y solamente una fila. Puede devolver cero filas, y en ese caso el valor de la columna será NULL (en SQL un valor de NULL significa “valor desconocido”) pero jamás más de una fila.

Ejemplo 3. Un SELECT a continuación del FROM

SELECT
   MiColumna1,
   MiColumna2
FROM
   (SELECT MiColumna1, MiColumna2 FROM MiTabla)

Aquí el SELECT interno se escribió después de la cláusula FROM. En estos casos no se le llama subconsulta sino que se le llama tabla derivada.

Para que funcione, la cantidad de columnas de la tabla derivada siempre debe ser igual o mayor a la cantidad de columnas del SELECT principal, nunca puede ser menor (y a la tabla principal si lo deseas puedes agregarle columnas que tengan valores constantes, como 17, 21, ‘Asunción’, etc.). Y los nombres de las columnas deben coincidir, no puedes usar una columna en el SELECT principal que no hayas usado en la tabla derivada.

Fíjate que la subconsulta debe devolver una sola columna pero la tabla derivada puede devolver varias columnas.

Ejemplo 4. Una subconsulta en la cláusula GROUP BY

SELECT
   MiColumna1,
   (SELECT MiColumna2 FROM MiTabla2 WHERE MiCondición)
FROM
   MiTabla
GROUP BY
   MiColumna1,
   (SELECT MiColumna2 FROM MiTabla2 WHERE MiCondición)

En este caso, la subconsulta se usó también luego de la cláusula GROUP BY, eso es necesario porque todas las columnas que se incluyen en el SELECT principal y que no son funciones agregadas también deben encontrarse a continuación de la cláusula GROUP BY.

Ejemplo 5. Una subconsulta en la cláusula ORDER BY

SELECT
   MiColumna1,
   MiColumna2
FROM
   MiTabla
ORDER BY
   (SELECT MiColumna3 FROM MiTabla2 WHERE MiCondición)

Aquí como puedes ver la subconsulta se escribió a continuación de la cláusula ORDER BY y por lo tanto el SELECT principal será ordenado por el contenido de la subconsulta.

Ejemplo 6. Un subconsulta en el JOIN

SELECT
   MiColumna1,
   MiColumna2
FROM
   MiTabla
JOIN
   MiTabla2
      ON MiColumna = (SELECT MiOtraColumna FROM MiOtraTabla)

También puede usarse una subconsulta en el JOIN, sea éste del tipo que sea (INNER, LEFT, RIGHT, FULL, CROSS, NATURAL)

Ejemplo 7. Insertando, modificando o borrando filas desde una subconsulta

Si necesitas insertarle datos a una tabla y los datos que necesitas se encuentran en otra u otras tablas la forma más rápida es hacerlo con una subconsulta:

INSERT INTO
MiTabla
   (MiColumna1, MiColumna2, MiColumna3)
   SELECT MiValor1, MiValor2, MiValor3 FROM MiOtraTabla

También puedes usar una subconsulta para establecer la condición que necesitas para modificar o borrar filas. En general se usan subconsultas cuando las filas que se quiere insertar, modificar o borrar son muchas.

Conclusión:

Como seguramente habrás deducido luego de ver los ejemplos anteriores, las subconsultas pueden usarse en muchos lugares, prácticamente puedes usarlas en cualquier lugar donde necesites el valor de una columna.

Hay muchos ejemplos más que podría escribir pero con los anteriores supongo que ya tienes bien clara la idea.

¿Es conveniente usar subconsultas?

A veces sí, a veces no. En ocasiones es lo mejor que puedes hacer y en ocasiones hay mejores alternativas así que debes tratarlas como una herramienta más, algo que te puede ayudar a conseguir los resultados que estás buscando. Pero debes recordar que en general usar subconsultas es más lento que no usarlas y por lo tanto debes siempre verificar que no exista una mejor opción.

Artículos relacionados:

Tablas derivadas

El índice del blog Firebird21

Entendiendo la Integridad Referencial

2 comentarios

La “integridad referencial” es un concepto utilizado en las bases de datos y significa que la relación entre dos tablas siempre es consistente.

¿Qué significa la palabra “integridad”?

Que no pueden sobrar ni faltar datos, deben estar todos, ninguno más y ninguno menos.

¿Qué significa la palabra “referencia”?

Que conociendo algunos datos de una tabla se pueden conocer todos los datos de la otra tabla.

¿Cómo se establece la relación entre las dos tablas?

A través de una restricción Foreign Key.

En una restricción Foreign Key una de las tablas actúa como “padre” y la otra tabla actúa como “hija”. La relación entre ambas tablas está dada por una columna (o más de una columna) cuyos tipos de datos son idénticos. Por ejemplo, se puede establecer entre una columna INTEGER de la tabla “padre” y una columna INTEGER de la tabla “hija”, pero no se puede entre una columna INTEGER de la tabla “padre” y una columna VARCHAR de la tabla “hija”.

  • SMALLINT con SMALLINT, ok
  • INTEGER con INTEGER, ok
  • BIGINT con BIGINT, ok
  • SMALLINT con VARCHAR, error
  • INTEGER con FLOAT, error
  • VARCHAR con DATE, error
  • etc.

¿Qué es una relación consistente?

Aquella en la cual cada fila de la tabla “hija” tiene una fila y solamente una fila correspondiente en la tabla “padre”. Si una fila de la tabla “hija” no tiene una fila (y solamente una fila) correspondiente en la tabla “padre” entonces ya no existe la integridad referencial entre ambas tablas.

  • 1 fila de la tabla “hija” se corresponde con 0 filas de la tabla “padre” —> no hay integridad referencial
  • 1 fila de la tabla “hija” se corresponde con 1 fila de la tabla “padre” —> sí hay integridad referencial
  • 1 fila de la tabla “hija” se corresponde con 2 ó más filas de la tabla “padre” —> no hay integridad referencial

¿Qué debemos tener en cuenta para establecer una integridad referencial?

Que la relación entre la tabla “hija” y la tabla “padre” se haga mediante la Primary Key de la tabla “padre” o de una Unique Key de la tabla “padre”.

¿Qué implica que entre dos tablas exista una integridad referencial?

  1. Que no puedes agregarle una fila a la tabla “hija” si no existe la fila correspondiente en la tabla “padre”
  2. Que no puedes cambiar la columna usada en la referencia en la tabla “padre” (a menos que hayas establecido “actualización en cascada”)
  3. Que no puedes borrar la fila usada en la referencia en la tabla “padre” (a menos que hayas establecido “borrado en cascada”)

¿Qué significan “actualización en cascada” y “borrado en cascada”?

  • “Actualización en cascada” significa que si cambias el valor de la columna en la tabla “padre” ese mismo cambio se efectuará automáticamente en todas las filas de la tabla “hija” correspondientes
  • “Borrado en cascada” significa que si borras una fila de la tabla “padre” todas las filas correspondientes de la tabla “hija” también serán borradas.

Ejemplo:

Tenemos una tabla llamada PAISES y otra tabla llamada PRODUCTOS. Queremos que la tabla PRODUCTOS referencie a la tabla PAISES, para poder así saber cual es el país del cual proceden nuestros productos.

INTEGRIDAD1

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

La Primary Key de esta tabla está compuesta por las columnas PAI_CODSUC y PAI_IDENTI

INTEGRIDAD2

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

Podemos establecer una restricción “Foreign Key” entre ambas tablas usando las dos columnas que tienen en común: Código de la Sucursal e Identificador del País:

ALTER TABLE PRODUCTOS ADD CONSTRAINT FK_PRODUCTOS3 FOREIGN KEY (PRD_CODSUC,PRD_IDEPAI) REFERENCES PAISES(PAI_CODSUC,PAI_IDENTI);

Como ambas tablas están relacionadas mediante una “Foreign Key”, entonces:

  1. No podemos colocar en la columna PRD_IDEPAI un número que no exista en la columna PAI_IDENTI. O sea que el número que coloquemos en la columna PRD_IDEPAI debe existir en la columna PAI_IDENTI
  2. No podemos colocar NULL en la columna PRD_IDEPAI
  3. No podemos cambiar el número que se encuentra en la columna PAI_IDENTI si ese número existe en alguna columna PRD_IDEPAI
  4. No podemos borrar una fila de la tabla PAISES si el número de su columna PAI_IDENTI existe en la columna PRD_IDEPAI de alguna fila de la tabla PRODUCTOS

El punto 3. podemos hacerlo si establecimos “actualización en cascada”, ejemplo:

ALTER TABLE PRODUCTOS ADD CONSTRAINT FK_PRODUCTOS3 FOREIGN KEY (PRD_CODSUC,PRD_IDEPAI) REFERENCES PAISES(PAI_CODSUC,PAI_IDENTI) ON UPDATE CASCADE;

El punto 4. podemos hacerlo si establecimos “borrado en cascada”, ejemplo:

ALTER TABLE PRODUCTOS ADD CONSTRAINT FK_PRODUCTOS3 FOREIGN KEY (PRD_CODSUC,PRD_IDEPAI) REFERENCES PAISES(PAI_CODSUC,PAI_IDENTI) ON DELETE CASCADE;

Conclusión:

Siempre que sea posible debemos tener tablas con “integridad referencial” porque eso nos asegura de tener datos consistentes en las tablas “hijas”.

Como no se puede guardar en una tabla “hija” un valor que no exista en su tabla “padre” eso nos da la seguridad de que todos los valores de la tabla “hija” existan en la tabla “padre” y evitamos así tener filas “huérfanas” (se llaman así a las que no tienen “padre”). Las filas huérfanas solamente pueden causar problemas y ningún beneficio.

Artículos relacionados:

Entendiendo a las Foreign Keys

El índice del blog Firebird21

Entendiendo a las transacciones

7 comentarios

Entender correctamente como funcionan las transacciones es importantísimo en Firebird porque todo lo haces dentro de una transacción, no existe alguna operación que pueda realizarse afuera de ellas. Eso significa que siempre todos tus INSERT, UPDATE, DELETE, FETCH, SELECT y EXECUTE PROCEDURE se ejecutan dentro de una transacción, sí o sí.

¿Por qué eso?

Porque de esta manera existe la seguridad 100% de que tu Base de Datos cumpla con ACID, que es un paradigma que todos los buenos SGBDR (y por lo tanto todas sus bases de datos) deben cumplir.

Las bases de datos que cumplen con ACID son totalmente confiables y todas las bases de datos de Firebird cumplen con ACID al 100%, sin excepción.

https://firebird21.wordpress.com/2013/05/10/entendiendo-acid/

Iniciando una transacción

Si no había una transacción abierta entonces una transacción se inicia automáticamente cuando escribes un comando (por ejemplo: INSERT, UPDATE, DELETE, SELECT) .

El comando SET TRANSACTION (que verás más abajo) no inicia la transacción, lo que hace es:

  1. Un COMMIT a la transacción actual (el cual, como todo COMMIT, puede finalizar exitosamente o fallar)
  2. Establecer los parámetros que serán usados por la siguiente transacción

Finalizando una transacción

Hay solamente dos formas (normales, porque también hay formas anormales) en que una transacción puede ser finalizada:

  • Con un COMMIT
  • Con un ROLLBACK

Si finaliza con un COMMIT entonces todo lo que se hizo dentro de la transacción queda guardado en las tablas de la Base de Datos.

Si finaliza con un ROLLBACK entonces todo lo que se hizo dentro de la transacción es desechado, eliminado, como si nunca se hubiera hecho.

Un COMMIT puede fallar, un ROLLBACK jamás falla.

En los ejemplos que hay más abajo puedes ver casos de COMMITs que fallan.

Sintaxis de SET TRANSACTION:

SET TRANSACTION
   [NAME NombreTransacción]
[READ WRITE | READ ONLY]
[ [ISOLATION LEVEL] { SNAPSHOT [TABLE STABILITY]
| READ COMMITTED [[NO] RECORD_VERSION] } ]
[WAIT | NO WAIT]
[LOCK TIMEOUT segundos]
[NO AUTO UNDO]
[IGNORE LIMBO]
[RESERVING | USING ]

NAME se usa cuando abres una transacción dentro de tu programa. Te sirve para asignarle un nombre a la transacción. De esa manera podrías tener varias transacciones abiertas al mismo tiempo y realizar operaciones independientes en cada una de ellas. Es opcional, si no quieres no lo usas pero si no lo usas entonces solamente puedes tener una transacción abierta.

Con READ WRITE le pides al Firebird que abra la transacción para lectura y para escritura. Es el parámetro por defecto. Y es el que debes utilizar si dentro de la transacción tendrás los comandos INSERT o UPDATE o DELETE. Con READ ONLY le pides al Firebird que abra la transacción para lectura solamente. Y es el que debes utilizar cuando dentro de tu transacción solamente tendrás el comando SELECT o el comando EXECUTE PROCEDURE y éste no realiza ningún INSERT ni UPDATE ni DELETE. Usar READ ONLY en una transacción que no modifica la Base de Datos hace que ésta finalice más rápido y eso es algo muy bueno.

ISOLATION LEVEL es el nivel de aislamiento de la transacción y puede tener uno de estos valores:

SNAPSHOT. Significa que la transacción solamente conoce los valores que fueron confirmados (se confirma con un COMMIT) antes de que ella empezara. En general se usa este nivel de aislamiento cuando la transacción solamente tendrá SELECTs.

SNAPSHOT TABLE STABILITY. Obtiene acceso exclusivo para escritura en todas las tablas involucradas y también en todas las tablas relacionadas con las anteriores mediante una Foreign Key. Ninguna otra transacción podrá modificar (insertar, actualizar, borrar) las tablas que una transacción SNAPSHOT TABLE STABILITY está usando. Y una transacción SNAPSHOT TABLE STABILITY no podrá iniciar si alguna de las tablas que necesita está siendo usada por otra transacción READ WRITE. En Firebird no es recomendable usar este aislamiento. Es muy peligroso porque impide que otras transacciones modifiquen las tablas. En el rarísimo caso de que debas usar este aislamiento trata de que la transacción termine muy rápido o de que se ejecute cuando nadie más usa la Base de Datos.

READ COMMITED. Permite que la transacción pueda “ver” todas las filas que fueron insertadas, actualizadas o borradas por otras transacciones y que fueron confirmadas (mediante un COMMIT) por esas otras transacciones. Es el aislamiento que normalmente se usa en los programas que insertan, modifican o borran filas de las tablas. Este aislamiento además puede ser RECORD_VERSION o NO RECORD_VERSION.

RECORD VERSION. En este caso la transacción puede leer todas las filas que fueron confirmadas por otras transacciones. Y si la transacción es READ WRITE entonces podrá modificar también esas filas pero siempre y cuando haya empezado después que las otras. Ejemplo:

      • En la tabla PRODUCTOS el precio de venta de “Televisor Toshiba de 20 pulgadas” es de 120 dólares
      • Empezó la transacción T1
      • Empezó la transacción T2
      • La transacción T1 cambió el precio de venta de “Televisor Toshiba de 20 pulgadas” a 100 dólares
      • La transacción T2 ve que el precio de venta de “Televisor Toshiba de 20 pulgadas” es de 120 dólares. Como la transacción T1 aún no finalizó con un COMMIT, la transacción T2 ve el precio que ese producto tenía cuando empezó la transacción T2. La transacción T2 no puede saber lo que hace la transacción T1 antes de que la transacción T1 finalice con un COMMIT.
      • La transacción T1 finalizó con un COMMIT
      • La transacción T2 cambió el precio de venta de “Televisor Toshiba de 20 pulgadas” a 110 dólares
      • La transacción T2 finalizó con un COMMIT
      • Ahora, el precio de venta es de 110 dólares porque es el que corresponde al último COMMIT

Como la transacción T2 había empezado después que la transacción T1 entonces el COMMIT de la transacción T2 fue exitoso. Si hubiera empezado antes, hubiera sido rechazado. Por ejemplo:

      • En la tabla PRODUCTOS el precio de venta de “Televisor Toshiba de 20 pulgadas” es de 120 dólares
      • Empezó la transacción T1
      • Empezó la transacción T2
      • La transacción T2 cambió el precio de venta de “Televisor Toshiba de 20 pulgadas” a 110 dólares
      • La transacción T1 ve que el precio de venta de “Televisor Toshiba de 20 pulgadas” es de 120 dólares. Como la transacción T2 aún no finalizó con un COMMIT, la transacción T1 ve el precio que ese producto tenía cuando empezó la transacción T1.  La transacción T1 no puede saber lo que hace la transacción T2 antes de que la transacción T2 finalice con un COMMIT.
      • La transacción T2 finalizó con un COMMIT
      • La transacción T1 cambió el precio de venta de “Televisor Toshiba de 20 pulgadas” a 100 dólares
      • La transacción T1 intenta finalizar con un COMMIT
      • Su intento de COMMIT es rechazado. Como la transacción T1 empezó antes que la transacción T2 entonces la transacción T1 no puede modificar una fila que fue modificada por la transacción T2, ya que la transacción T2 es más nueva
      • Ahora, el precio de venta es de 110 dólares, que corresponde al último COMMIT exitoso

NO RECORD_VERSION. Es el valor por defecto. Impide que la transacción pueda siquiera leer una fila que fue actualizada por otra transacción y que aún no fue confirmada. Por ejemplo:

      • En la tabla PRODUCTOS el precio de venta de “Televisor Toshiba de 20 pulgadas” es de 120 dólares
      • Empezó la transacción T1
      • Empezó la transacción T2
      • La transacción T1 cambió el precio de venta de “Televisor Toshiba de 20 pulgadas” a 100 dólares
      • La transacción T2 no puede ver cual es el precio de venta de “Televisor Toshiba de 20 pulgadas” porque la transacción T1 aún no finalizó con un COMMIT. Por lo tanto la transacción T2 no tiene acceso a esa fila, ni siquiera para lectura. Y ahora pueden ocurrir dos cosas:
        1. Si el modo de bloqueo de la transacción T2 es WAIT entonces la transacción T2 esperará hasta que la transacción T1 finalice con un COMMIT o con un ROLLBACK. En ambos casos, como la transacción T2 es más nueva que la transacción T1 entonces la transacción T2 podrá finalizar con un COMMIT
        2. Si el modo de bloqueo de la transacción T2 es NO WAIT entonces la transacción T2 recibirá una notificación de que la fila que quiso modificar está bloqueada
      • Si la transacción T1 finaliza con un COMMIT, entonces el precio de venta será de 100 dólares, si finaliza con un ROLLBACK el precio de venta continuará en 120 dólares

WAIT. Este es el modo de bloqueo por defecto. Sirve para lo siguiente: cuando una transacción no puede modificar o borrar una fila porque otra transacción está usando esa fila, espera hasta que la otra transacción termine. Si la transacción que estaba esperando es más nueva entonces podrá modificar a esa fila cuando sea desbloqueada. Si es más vieja, obtendrá inmediatamente una notificación de error. No hay que usar WAIT en programas que muchos usuarios utilizan para modificar o borrar las mismas filas porque puede causar que muchos usuarios estén esperando varios minutos antes de poder continuar con sus tareas. Tampoco tiene sentido usar WAIT en transacciones SNAPSHOT sin usar también LOCK TIMEOUT en ellas. De todas maneras hay excepciones y lo que puedes hacer es verificar si en tu caso es bueno usar o no usar WAIT. En general, si son pocos los usuarios que modifican las mismas filas y las transacciones son cortas, usar WAIT suele ser lo correcto. Cuando el modo de bloqueo es NOWAIT el Servidor del Firebird inmediatamente informa que ocurrió un conflicto porque una transacción intenta modificar una fila que otra transacción también está modificando. Si muchos usuarios están modificando las mismas filas NOWAIT les informará rápidamente que ocurrió un problema mientras que WAIT les hará esperar. En transacciones SNAPSHOT lo recomendable suele ser usar NOWAIT y cuando se encuentra un conflicto hacer un ROLLBACK o esperar unos segundos y reintentar la modificación.

LOCK TIMEOUT es muy útil para evitar que una transacción espere indefinidamente que la fila sea desbloqueada. Solamente se puede usar cuando el modo de bloqueo es WAIT. Por ejemplo: WAIT LOCK TIMEOUT 5 esperará un máximo de 5 segundos para que la fila sea desbloqueada. Si no fue desbloqueada entonces se recibirá una notificación, como si el modo de bloqueo hubiera sido NOWAIT. Lastimosamente no puede usarse en transacciones iniciadas dentro de un programa.

NO AUTO UNDO le indica al Servidor del Firebird que no guarde los datos que normalmente guarda para hacer el ROLLBACK de la transacción. Si tu transacción tendrá muchos INSERT y estás seguro que no finalizará con un ROLLBACK entonces con esta opción conseguirás que la transacción finalice más rápidamente.

IGNORE LIMBO. Con esta opción los registros creados por las transacciones limbo son ignorados. Una transacción está en limbo si la segunda etapa de un COMMIT de dos etapas falla. Los COMMIT de dos etapas se usan en transacciones que involucran a dos bases de datos. No se usa esta opción en transacciones que son iniciadas dentro de los programas.

RESERVING reserva todas las tablas cuyos nombres se encuentran a continuación. La reserva empieza cuando la transacción empieza y termina cuando la transacción finaliza. Eso le garantiza a la transacción tener acceso a todas esas tablas y ninguna otra transacción podrá impedirle el acceso a las tablas listadas. SNAPSHOT TABLE STABILITY bloquea a todas las tablas que están dentro de la transacción (si hay 7 tablas bloquea a las 7), en cambio RESERVING solamente reserva a las tablas cuyos nombres se especificaron (quizás de esas 7 solamente 2 necesitaban realmente ser reservadas), siendo por lo tanto menos restrictivo y el método preferible cuando se deben bloquear o reservar tablas (algo que en Firebird muy raramente debería hacerse). USING limita los nombres de las bases de datos a los cuales la transacción puede acceder a los que aquí se hayan especificado.

Opciones por defecto

Las opciones por defecto de las transacciones (o sea, las que usará el Servidor del Firebird si no le indicas otra cosa) son:

READ WRITE + WAIT + SNAPSHOT

Lecturas sucias

Firebird no permite las lecturas sucias. Se le llama lectura sucia a los cambios que hace una transacción y antes de que dicha transacción termine con un COMMIT desde otra transacción se pueden ver esos cambios. Por ejemplo: la transacción T1 está haciendo un INSERT y antes de que la transacción T1 finalice con un COMMIT desde la transacción T2 ya se está viendo ese INSERT que hizo la transacción T1. Otros SGBDR permiten esa situación, pero Firebird no. Y es lo correcto porque eso otorga mucha mayor seguridad.

NOTA:

Todas las transacciones siempre pueden ver a todos los registros que fueron confirmados (finalizaron con un COMMITantes de que ellas empezaran. Pero solamente las transacciones READ COMMITED pueden ver a los registros que fueron confirmados después de que ellas empezaran.

Artículos relacionados:

COMMIT y ROLLBACK en stored procedures y triggers

Detectando aplicaciones y usuarios que mantienen las transacciones abiertas durante mucho tiempo

Terminar las transacciones de los SELECTs

Modos de bloqueo de las transacciones

Bloqueos mortales

Entendiendo ACID

La arquitectura MGA

Transacciones optimistas y transacciones pesimistas

El índice del blog Firebird21

El foro del blog Firebird21

Older Entries