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

Older Entries