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

 

Mostrando los resultados ordenados por cualquier criterio

8 comentarios

Cuando en un SELECT usamos la cláusula ORDER BY le estamos indicando al Firebird en cual orden queremos que las filas sean mostradas. En general ese orden es fijo y ya lo conocemos antes de escribir el SELECT. Sin embargo, en ocasiones no ocurre así sino que el orden de las filas depende de alguna condición.

Ejemplo:

Tenemos una tabla llamada PRODUCTOS la cual tiene las siguientes filas:

ORDEN1

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

Y queremos mostrar a esas filas ordenadas según un criterio muy particular, y no tenemos un índice creado que podamos usar. En este caso, lo que queremos es mostrar primero a todas las filas en cuyo nombre esté “350”, luego las filas en cuyo nombre está la palabra “LITRO” y luego las filas en cuyo nombre está la palabra “LITROS”.

Resumiendo:

  • Primero las filas que tienen “350”
  • Después las filas que tienen “LITRO”
  • Después las filas que tienen “LITROS”

Con el siguiente SELECT … ORDER BY obtendemos lo que buscamos:

SELECT
   PRD_IDENTI,
   PRD_NOMBRE,
   CASE
      WHEN PRD_NOMBRE CONTAINING '350'    THEN 1
      WHEN PRD_NOMBRE CONTAINING 'LITROS' THEN 3
      WHEN PRD_NOMBRE CONTAINING 'LITRO'  THEN 2
   END
FROM
   PRODUCTOS
ORDER BY
   CASE
      WHEN PRD_NOMBRE CONTAINING '350'    THEN 1
      WHEN PRD_NOMBRE CONTAINING 'LITROS' THEN 3
      WHEN PRD_NOMBRE CONTAINING 'LITRO'  THEN 2
   END

El primer CASE … END es opcional, no necesitamos escribirlo pero si lo escribimos nos ayuda a entender lo que sucede. El resultado será el siguiente:

ORDEN2

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

Entonces ¿por qué funciona lo que escribimos en la cláusula ORDER BY? porque hemos creado una columna virtual, y las filas se muestran ordenadas según esa columna virtual. O sea, primero todas las que tiene el valor 1, luego las que tienen el valor 2 y finalmente las que tienen el valor 3.

Desde luego que podríamos tener más valores si los necesitamos: 4, 5, 6, 7, …., etc.

¿Y por qué los WHEN que escribimos no están ordenados de menor a mayor?

Porque los caracteres “LITRO” está incluidos dentro de los caracteres “LITROS”. Si hubiéramos escrito primero el WHEN que tiene “LITRO” y luego el WHEN que tiene “LITROS” entonces no habríamos obtenido el resultado deseado. ¿Por qué? porque en ese caso “LITROS” habría tenido el valor 2 y no el valor 3, que es el que necesitamos.

Conclusión:

Es importante recordar que podemos mostrar a las filas por cualquier orden que se nos ocurra, y que no es necesario tener un índice para ello, y que la condición puede ser cualquiera. La técnica es crear una columna virtual (la cual por supuesto no es necesario mostrársela a los usuarios) y así las filas serán mostradas según el orden en que las hayamos colocado en esa columna virtual.

En este ejemplo se mostró la columna virtual, pero eso es para que se entienda la técnica, a los usuarios no necesitamos mostrársela.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

Agrupar y ordenar por nombres o por posiciones de las columnas

Deja un comentario

Como sabes, en SQL para ordenar el resultado de una consulta puedes escribir:

SELECT
   PRD_CODIGO,
   PRD_NOMBRE
FROM
   PRODUCTOS
ORDER BY
   PRD_NOMBRE

o escribir algo como:

SELECT
   PRD_CODIGO,
   PRD_NOMBRE
FROM
   PRODUCTOS
ORDER BY
   2

Y el resultado que obtendrás será exactamente el mismo. De la misma manera puedes agrupar por nombres de las columnas o por la posición que esas columnas ocupan.

¿Qué es preferible, usar el nombre de la columna o la posición de la columna?

Antes de que existiera SQL la forma normal era utilizar la posición de la columna, el hecho de poder utilizar el nombre de la columna en SQL resultó un gran avance en esa época.

Si usas la posición, el problema que puedes tener es que agregas una columna a tu SELECT y si te olvidas de actualizar también las cláusulas ORDER BY o GROUP BY, entonces obtendrás un resultado incorrecto.

SELECT
   PRD_CODIGO,
   PRD_UNIMED,
   PRD_NOMBRE
FROM
   PRODUCTOS
ORDER BY
   2

Aquí, se agregó la columna PRD_UNIMED (unidad de medida) pero no se actualizó la cláusula ORDER BY y por lo tanto el orden de las filas no será el correcto. En cambio, en este caso:

SELECT
   PRD_CODIGO,
   PRD_UNIMED,
   PRD_NOMBRE
FROM
   PRODUCTOS
ORDER BY
   PRD_NOMBRE

También se agregó la columna PRD_UNIMED pero sin embargo se sigue mostrando a las filas en el orden correcto porque en la cláusula ORDER BY se utiliza el nombre de la columna, no su posición.

Conclusión:

Usar el nombre de la columna o la posición de la columna tiene ventajas y desventajas.

La ventaja de usar el nombre de la columna es que aunque agregues columnas el resultado siempre estará ordenado o agrupado correctamente.

La ventaja de usar la posición de la columna es que escribes menos.

Los buenos profesionales siempre recomiendan usar el nombre de la columna porque se escribe más, sí, pero se entiende mejor.

Artículo relacionado:

El índice del blog Firebird21