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

 

Anuncios

Indices ascendentes y descendentes

2 comentarios

Los índices en Firebird pueden ser ascendentes (1, 2, 3, 4, 5, 6, …) o descendentes (999, 998, 997, 996, …)

Lo importante a recordar es que son siempre recorridos en el mismo orden en que fueron creados. O sea que un índice ascendente siempre se recorre de menor a mayor y un índice descendente siempre se recorre de mayor a menor.

¿Por qué es importante saber eso?

Porque tener el índice adecuado puede hacer que nuestras consultas sean rapidísimas.

Veamos un ejemplo:

Listado 1.

SELECT
   MAX (MiColumna)
FROM
   MiTabla

Aquí tenemos tres posibilidades:

  1. No hay un índice sobre la columna MiColumna
  2. Hay un índice ascendente sobre la columna MiColumna
  3. Hay un índice descendente sobre la columna MiColumna

En el caso 1. y en el caso 2. para hallar el valor máximo de MiColumna el Firebird debe recorrer la tabla completa, desde la primera fila hasta la última fila, porque no sabe cual de esas filas tendrá al mayor valor. Un índice ascendente jamás se usaría en este caso.

En cambio, en el caso 3. el valor máximo estará sí o sí en la primera fila del índice.

La diferencia en tiempo puede ser muy grande. En tablas que tienen millones de filas la diferencia será muy notoria porque el caso 1. y el caso 2. deben recorrer todas esas millones de filas y en cambio el caso 3. siempre encontrará el valor buscado en la primera fila.

¿Y si en lugar de escribir la función MAX() escribimos la función MIN()?

Pues allí se usará un índice ascendente, si es que existe.

Recuerda que crear un índice tiene sus ventajas y sus desventajas:

  • La ventaja es que si se lo utiliza en un SELECT entonces los resultados se obtendrán muy rápido
  • La desventaja es que debe ser actualizado cada vez que se realiza un INSERT, un UPDATE o un DELETE en esa tabla

Hay por lo tanto que comprobar si vale la pena crear un índice.

Artículos relacionados:

El índice del blog Firebird21

El foro del blog Firebird21

 

Indexando una columna computada

Deja un comentario

Como sabes, las columnas computadas son muy útiles para ver su contenido en los SELECTs y también cuando las usamos en la cláusula WHERE (de un UPDATE, de un DELETE, o de un SELECT) o en la cláusula ORDER BY.

Ya las habíamos visto en varios artículos anteriormente, ahora veremos una de sus características más interesantes: pueden ser indexadas.

Las primeras versiones del Firebird no lo permitían, pero a partir de la versión 2.0 ya tenemos esa posibilidad.

Por ejemplo, la tabla de SUCURSALES tiene estos datos:

INDICE1

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

Como podemos ver, algunos nombres están en mayúsculas y algunos nombres están en minúsculas. Podríamos crear el siguiente índice:


CREATE INDEX IDX_SUCURSALES1 ON SUCURSALES COMPUTED BY (UPPER(SUC_NOMBRE));

COMMIT;

Y luego usarlo en comandos como los siguientes:


SELECT * FROM SUCURSALES ORDER BY UPPER(SUC_NOMBRE)

INDICE2

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

Aquí, los nombres de las sucursales aparecen ordenados alfabéticamente y al mirar el PLAN vemos que efectivamente se usó el índice recientemente creado.


SELECT * FROM SUCURSALES WHERE UPPER(SUC_NOMBRE) STARTING WITH 'BOG'

INDICE3

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

Aquí le pedimos que nos muestre los nombres de todas las sucursales que empiezan con las letras ‘BOG’ y vemos que también usó el índice computado que habíamos creado anteriormente.


DELETE FROM SUCURSALES WHERE UPPER(SUC_NOMBRE) = 'MIAMI - ESTADOS UNIDOS'

INDICE4

También en este caso se usó el índice de la columna computada, como podemos comprobarlo mirando el PLAN.

Otros ejemplos:


CREATE INDEX IDX_MOVIMCAB1 ON MOVIMCAB COMPUTED BY (EXTRACT(YEAR FROM MVC_FECHAX))

CREATE INDEX IDX_MOVIMCAB2 ON MOVIMCAB COMPUTED BY (EXTRACT(YEAR FROM MVC_FECHAX) || MVC_NRODOC)

CREATE DESCENDING INDEX IDX_MOVIMCAB3 ON MOVIMCAB COMPUTED BY (EXTRACT(YEAR FROM MVC_FECHAX) || MVC_NRODOC)

El índice IDX_MOVIMCAB1 de la tabla MOVIMCAB (cabecera de movimientos) la indexa según el año del movimiento.

El índice IDX_MOVIMCAB2 de la tabla MOVIMCAB (cabecera de movimientos) la indexa según el año del movimiento y el número del documento.

El índice IDX_MOVIMCAB3 de la tabla MOVIMCAB (cabecera de movimientos) es muy parecido al índice IDX_MOVIMCAB2 pero ahora el índice es descendente (es decir, de mayor a menor).

Conclusión:

Tener columnas computadas es algo muy bueno porque nos facilita la vida. Poder indexar a esas columnas computadas es algo más bueno aún.

Como hemos visto en los ejemplos anteriores, el Firebird usará a los índices de las columnas computadas cada vez que los necesita.

Esos índices pueden involucrar a una sola columna o a varias columnas (como en los dos últimos ejemplos donde IDX_MOVIMCAB2 y IDX_MOVIMCAB3 involucran a las columnas MVC_FECHAX y MVC_NRODOC).

Artículos relacionados:

Columnas computadas

Un truco para encontrar valores que pueden estar en varias columnas

Usando un SELECT en una columna computada

Utilizando columnas computadas

Algunos ejemplos de uso de las columnas computadas

Usando un PLAN

Algo más sobre PLAN

Entendiendo el contenido de un PLAN

El índice del blog Firebird21

El foro del blog Firebird21

Entendiendo el contenido de un PLAN

6 comentarios

En Firebird todos los SELECTs que ejecutemos (manualmente o dentro de una vista) sí o sí usarán un PLAN. Ese PLAN puede ser elegido por el Firebird o por nosotros mismos. En general el Firebird hace un excelente trabajo en hallar el PLAN más adecuado pero en ocasiones no es así y allí nosotros podemos especificar uno mejor.

NOTA: Estos conocimientos no son necesarios para quienes son principiantes en Firebird, tales personas lo mejor que pueden hacer es dejarle al Firebird que especifique el PLAN a ser usado. Pero para quienes tienen un nivel más avanzado sí es importante, porque en algunos casos podrán mejorar drásticamente la velocidad de sus SELECTs.

Para entender mejor lo que es un PLAN puedes leer estos artículos:

Usando un PLAN

Algo más sobre PLAN

 En un PLAN lo que se especifica son los índices que serán usados. Las posibilidades son:

  • No hay un índice y no se usa un índice. Verás la palabra NATURAL.
  • No hay un índice y se necesita usar un índice. Verás la palabra SORT.
  • Hay un índice y no se usa ese índice. Verás la palabra NATURAL.
  • Hay un índice y se usa ese índice. Verás la palabra INDEX.

Si el SELECT tiene un JOIN (o más de uno, claro) entonces las posibilidades son:

  • No se necesita ordenar los conjuntos de resultados usados. Verás la palabra JOIN
  • Se necesita ordenar los conjuntos de resultados usados. Verás la palabra MERGE o más comúnmente las palabras SORT MERGE

Si se quiere mostrar las filas ordenadas:

  • Si hay un índice verás la palabra ORDER
  • Si no hay un índice verás la palabra SORT

El significado de NATURAL

Si ves esta palabra en el PLAN significa que no existe un índice o que sí existe pero no se lo utiliza. Si no se lo utiliza en general es por la pobre selectividad del mismo. Para saber más sobre la selectividad puedes leer estos artículos:

Selectividad de los índices

Recreando índices y calculando estadísticas

NATURAL significa que las filas son extraídas con el mismo orden en el cual fueron guardadas. Esto implica que todas las páginas deben ser leídas antes de que pueda ejecutarse el filtro (la condición que hayas puesto en la cláusula WHERE). Eso puede ser muy lento en tablas grandes.

Sin embargo, NATURAL no siempre es malo, si no te interesa mostrar a las filas con algún orden en particular entonces NATURAL es lo correcto porque es más rápido que usar un índice. Si se usa un índice primero se busca la clave en el índice y luego se extrae la fila correspondiente, si se usa NATURAL se ahorra el primer paso y por lo tanto es más rápido; la diferencia es muy notoria en tablas muy grandes.

El significado de SORT

La palabra SORT significa “ordenar” en inglés y eso es lo que hace el Firebird. Ordena el conjunto de resultados creando archivos temporales. Este proceso puede ser muy lento en tablas grandes porque crear esos archivos temporales puede demorar mucho tiempo. Si encuentras la palabra SORT eso es muy malo para el SELECT. Pero cuidado con esto, que sea malo para el SELECT no implica que necesariamente será malo para la Base de Datos. ¿Por qué? porque para evitar el SORT tendríamos que crear un índice, pero el mantenimiento de los índices es costoso, ya que cada vez que se hace un INSERT, un UPDATE, o un DELETE el índice debe ser modificado, eso toma tiempo y en tablas que constantemente están cambiando le provocarán un sobrecosto sustancial. Por lo tanto, la regla es crear un índice solamente si se lo usará frecuentemente. Por ejemplo, si tu SELECT se ejecutará una sola vez al año (o muy pocas veces en el año) y las filas involucradas se cuentan por millones entonces probablemente lo mejor sea no tener un índice y que el Firebird haga el SORT cuando lo necesite. Es cierto que el SELECT será lento, pero los INSERT, UPDATE, y DELETE serán más rápidos y como las filas se cuentan por millones entonces valdrá la pena no crear un índice.

El significado de INDEX

Esta palabra significa que existe un índice y que se lo utiliza.

El significado de JOIN

La palabra JOIN significa “juntar” en inglés. Si la ves en un PLAN significa que no se requiere ordenar ambos conjuntos de resultados porque ya se encuentran en el orden correcto.

El significado de MERGE o SORT MERGE

La palabra MERGE significa “combinar” o “mezclar” en inglés. MERGE y SORT MERGE hacen exactamente lo mismo, pero se suele escribir SORT MERGE para que quede más claro lo que hacen.

Lo que hacen es lo siguiente: un SORT al primer conjunto de resultados (como vimos más arriba), un SORT al segundo conjunto de resultados (como vimos más arriba) y luego combinar ambos conjuntos de resultados, ya ordenados.

Fíjate que hay dos SORT y una combinación de resultados. Cuidado con eso, algo así puede ser lentísimo en tablas grandes.

Un SORT MERGE es lo peor que puedes tener en un PLAN.

El significado de ORDER

La palabra ORDER significa “ordenar” en inglés, aunque en este caso la podríamos traducir “ordenar por el índice” y a continuación el nombre del índice que usa.

Analizando el PLAN

Para que todo esto quede más claro a continuación veremos varios ejemplos de SELECT con sus respectivos planes de ejecución; y explicaremos cada uno de ellos.

Ejemplo 1:

SELECT
   *
FROM
   BANCOS

PLAN (BANCOS NATURAL)

¿Qué significa este PLAN? que como no le dijimos en cual orden queremos ver a los Bancos entonces los mostró en el mismo orden conque los guardó en la tabla. Eso está muy bien, es lo correcto, porque es lo más rápido.

Ejemplo 2:

SELECT
   *
FROM
   BANCOS
ORDER BY
   BAN_CODSUC,
   BAN_IDENTI

PLAN (BANCOS ORDER PK_BANCOS)

¿Qué significa este PLAN? que como le dijimos que queremos ver a las filas ordenadas por Código de la Sucursal y por Identificador y tenemos un índice con esas columnas entonces usó a ese índice, cuyo nombre es PK_BANCOS.

Esto es más lento que el Ejemplo 1., pero si es lo que necesitamos mostrar entonces está ok.

Ejemplo 3:

SELECT
   *
FROM
   BANCOS
ORDER BY
   BAN_NOMBRE

PLAN SORT ((BANCOS NATURAL))

¿Qué significa este PLAN? que la tabla de BANCOS no estaba ordenada por la columna BAN_NOMBRE (por eso la palabra NATURAL) y que el Firebird tuvo que ordenarla (por eso la palabra SORT) antes de mostrar las filas. Ese ordenamiento crea archivos temporarios (en la memoria caché o en el disco duro) y puede ser muy lento en tablas muy grandes.

Ejemplo 4:

SELECT
   *
FROM
   BANCOS
WHERE
   BAN_CODSUC = 0 AND
   BAN_IDENTI > 10

PLAN (BANCOS INDEX (PK_BANCOS))

¿Qué significa este PLAN? que la tabla BANCOS tiene un índice según las columnas BAN_CODSUC y BAN_IDENTI (que son las usadas para filtrar con la cláusula WHERE) y que el Firebird usó ese índice.

En los WHERE hay siempre que tratar de usar índices porque eso aumenta muchísimo la velocidad.

Ejemplo 5:

SELECT
   *
FROM
   BANCOS
WHERE
   BAN_NOMBRE LIKE 'BAN%'

PLAN (BANCOS NATURAL)

¿Qué significa este PLAN? que la tabla BANCOS no tiene un índice según la columna BAN_NOMBRE y que como no le pedimos que muestre las filas con algún orden en particular las mostró como fueron guardadas.

Ejemplo 6:

SELECT
   *
FROM
   BANCOS
WHERE
   BAN_NOMBRE LIKE 'BAN%'
ORDER BY
   BAN_NOMBRE

PLAN SORT ((BANCOS NATURAL))

¿Qué significa este PLAN? que la tabla BANCOS no tiene un orden según la columna BAN_NOMBRE pero como se necesita mostrar a las filas ordenadas según esa columna entonces se hizo un SORT (es decir, se creó un archivo temporal para poder mostrar a las filas ordenadas).

No te olvides que hacer un SORT es más lento que usar un índice, a veces muchísimo más lento, porque las filas deben ser ordenadas y guardadas en un archivo temporal antes de ser mostradas y eso toma su tiempo.

Ejemplo 7:

SELECT
   B.BAN_CODSUC,
   S.SUC_NOMBRE,
   B.BAN_NOMBRE
FROM
   BANCOS     B
JOIN
   SUCURSALES S
      ON B.BAN_CODSUC = S.SUC_CODIGO

PLAN JOIN (B NATURAL, S INDEX (UQ_SUCURSALES))

¿Qué significa este PLAN? que la tabla S tiene un índice llamado UQ_SUCURSALES según su columna SUC_CODIGO, la tabla BANCOS no tiene un índice sobre su columna BAN_CODSUC y que se hará el JOIN sin ordenar las filas de la tabla B, así como están serán mostradas.

Ejemplo 8:

SELECT
   B.BAN_CODSUC,
   S.SUC_NOMBRE,
   B.BAN_NOMBRE
FROM
   BANCOS     B
JOIN
   SUCURSALES S
      ON B.BAN_CODSUC = S.SUC_CODIGO
ORDER BY
   B.BAN_NOMBRE

PLAN SORT (JOIN (B NATURAL, S INDEX (UQ_SUCURSALES)))

¿Qué significa este PLAN? que la tabla S tiene un índice llamado UQ_SUCURSALES según su columna SUC_CODIGO, la tabla BANCOS no tiene un índice sobre su columna BAN_CODSUC, que se hará el JOIN de esas filas (como vimos en el Ejemplo 7.) y posteriormente se hará un SORT para mostrar a las filas ordenadas según la columna BAN_NOMBRE.

Conclusión:

Entender el contenido del PLAN es muy útil por dos cosas: a) porque sabremos lo que está haciendo el Firebird para mostrar las filas del SELECT, y b) porque podremos analizarlo y quizás descubrir una mejor alternativa. Claro que esto último lleva su buen tiempo, no es algo que conseguiremos hacer enseguida pero vale la pena el esfuerzo porque un PLAN adecuado muestra a las filas muy rápidamente.

Si en el PLAN vemos la palabra SORT eso es algo malo y si vemos las palabras SORT MERGE eso es mucho más malo. Pero son malos para el SELECT, quizás no lo sean para la Base de Datos. Eso porque para evitar los SORT hay que crear índices, pero los índices deben ser mantenidos cada vez que se realiza un INSERT, un UPDATE o un DELETE, y ese mantenimiento no es instantáneo, toma su tiempo, que en tablas muy grandes y con muchas operaciones de inserción, actualización y borrado puede ser muy grande.

Por lo tanto debemos estudiar bien el caso y decidir si creamos el índice o si dejamos que se haga el SORT. En general, si el SELECT será ejecutado muchas veces, frecuentemente, deberíamos crear el índice. En cambio, si se lo ejecutará raramente lo mejor puede ser no crear el índice.

Otro aspecto muy importante a tener en cuenta es que las tablas son dinámicas, constantemente están cambiando, entonces un PLAN que hoy puede ser perfecto podría no serlo dentro de algunos meses o años. Si vamos a escribir el PLAN manualmente debemos tener bien presente que eso implicará más trabajo para nosotros porque de vez en cuando tendremos que verificar que siga funcionando muy bien, y si no es así entonces buscar y escribir un mejor PLAN. Pero encontrar un mejor PLAN no es algo que haremos en dos segundos, puede hacernos perder muchos minutos o horas inclusive.

Artículos relacionados:

Usando un PLAN

Algo más sobre PLAN

Selectividad de los índices

Recreando índices y calculando estadísticas

El índice del blog Firebird21

El foro del blog Firebird21

Entendiendo los índices compuestos

Deja un comentario

Como sabes, cuando creas un índice éste puede tener una columna o más de una columna. Si el índice tiene una sola columna se lo llama “simple” y si tiene más de una columna se lo llama “compuesto”. ¿Y en qué casos el Firebird utiliza un índice compuesto?

Supongamos que tu índice está compuesto por tres columnas. Firebird podría no usar ese índice, o usar solamente la primera columna, o usar la primera columna y la segunda columna, o usar las tres columnas. Es decir que estas son las posibilidades:

  1. No se usa el índice compuesto
  2. Se usa solamente la primera columna
  3. Se usan la primera columna y la segunda columna
  4. Se usan la primera columna y la segunda columna y la tercera columna

¿De qué depende?

De lo que hayas escrito en la cláusula WHERE. Para que una columna de un índice compuesto se utilice las columnas anteriores deben ser comparadas por igualdad. No por menor, ni por mayor, ni por menor o igual, ni por mayor o igual, ni por distinto. Por igualdad.

 Ejemplo:

Supongamos que tenemos un índice compuesto por tres columnas, (MiColumna1, MiColumna2, MiColumna3). Si escribimos:

WHERE
   MiColumna1 >= 21 AND
   MiColumna2 >= 500 AND
   MiColumna3 <= 2000

se usará solamente la columna MiColumna1 del índice compuesto ¿Por qué? porque en MiColumna1 no se usó una igualdad. En cambio, si escribimos:

WHERE
   MiColumna1 = 21 AND
   MiColumna2 >= 500 AND
   MiColumna3 <= 2000

se usarán las columnas MiColumna1 y MiColumna2 del índice compuesto ¿por qué? porque MiColumna1 fue comparada por igualdad, entonces se usa esa columna y también la siguiente. En cambio, si escribimos:

WHERE
   MiColumna1 = 21 AND
   MiColumna2 = 500 AND
   MiColumna3 <= 2000

se usarán las columnas MiColumna1 y MiColumna2 y MiColumna3. ¿Por qué? porque MiColumna1 y MiColumna2 fueron comparadas por igualdad, entonces se usan esas dos columnas y también la siguiente.

¿Y si se requiere que en todos los casos se usen las tres columnas del índice?

En ese caso la solución es no crear un índice compuesto sino crear tres índices simples, uno por cada columna. De esta manera te asegurarás de que siempre las tres columnas utilicen un índice.

Conclusión:

Es muy importante entender en que circunstancias el Firebird usa los índices compuestos para no crearlos innecesariamente. No te olvides que cada índice ocupa espacio en el disco duro y además hace que todas las operaciones de inserción, actualización, y borrado se realicen más lentamente. Mejoran la velocidad de las consultas pero empeoran las demás operaciones.

Como todo, los índices compuestos tienen sus ventajas y sus desventajas. La ventaja es que es más fácil mantener un solo índice compuesto que varios índices independientes. La desventaja es que podrías tener un índice compuesto que usas muy poco porque en tus consultas usas muy pocas igualdades. Si ése es el caso entonces muy probablemente te convendrá tener varios índices simples y no un índice compuesto.

¿Tienes dudas sobre si te conviene o no crear un índice compuesto?

Haz pruebas. Créalo y verifica el rendimiento. Luego elimina el índice compuesto y crea índices simples y verifica el rendimiento. Compara esos rendimientos y así sabrás si te conviene o no tener un índice compuesto.

Artículo relacionado:

El índice del blog Firebird21

 

Evitando que se use un índice en una consulta

Deja un comentario

El optimizador de consultas de Firebird es muy bueno y en casi la totalidad de los casos elegirá la mejor alternativa: cual de los índices usar o no usar un índice.

Sin embargo no es perfecto y alguna vez podrías descubrir que está usando un índice cuando no debería hacerlo. Esto en general ocurre cuando el índice tiene poca selectivdad (es decir: muchos valores repetidos).

Puedes saber cual índice se usa en una consulta mirando el PLAN de la misma.

Si descubres que usando un índice obtienes los resultados a una cierta velocidad (por ejemplo: 1500 ms) y sin usarlo obtienes los resultados a una velocidad mayor (por ejemplo: 100 ms) lo que debes hacer es pedirle al Firebird que no use ese índice. Eso lo consigues agregándole a tu SELECT la cláusula PLAN.

Ejemplo:

Queremos obtener los nombres de los proveedores que empiezan con “H”. La tabla de PROVEEDORES tiene un índice según las columnas (PRO_CODSUC y PRO_NOMBRE).

En PRO_CODSUC se guarda el código de la Sucursal.

En PRO_NOMBRE se guarda el nombre del Proveedor.

SELECT
   PRO_NOMBRE
FROM
   PROVEEDORES
WHERE
   PRO_CODSUC = 0 AND
   PRO_NOMBRE LIKE 'H%'
ORDER BY
   PRO_CODSUC,
   PRO_NOMBRE

Verificamos el tiempo que tardó el SELECT en extraer los datos de la tabla de PROVEEDORES y encontramos que es de 1500 ms y que está usando el índice, así que ahora le pedimos que no use el índice:

SELECT
   PRO_NOMBRE
FROM
   PROVEEDORES
WHERE
   PRO_CODSUC = 0 AND
   PRO_NOMBRE LIKE 'H%'
PLAN
   (PROVEEDORES NATURAL)
ORDER BY
   PRO_CODSUC,
   PRO_NOMBRE

y encontramos que el tiempo disminuyó a solamente 100 ms.

Conclusión:

Aunque casi siempre el Firebird hace un muy buen trabajo eligiendo el índice más adecuado para una consulta no debes asumir que siempre será así, a veces puede equivocarse. Para descubrirlo siempre tienes que observar el tiempo de extracción de los datos y si es mayor que 500 ms entonces es casi seguro que el PLAN puede mejorarse. Un buen profesional siempre verifica los tiempos de extracción de los datos y los optimiza al máximo. Esto es de suma importancia cuando las consultas son ejecutadas frecuentemente y las tablas involucradas tienen muchas filas.

Artículos relacionados:

Usando un PLAN

Algo más sobre PLAN

El índice del blog Firebird21

Recreando todos los índices de todas las tablas

3 comentarios

Como hemos visto en estos artículos:

Recreando los índices de las tablas

Usando índices en Firebird

es muy importante que todos los índices de todas nuestras tablas estén bien balanceados para que cuando realicemos las operaciones de INSERT, UPDATE, DELETE no se pierda más tiempo del debido en mantenerlos actualizados. Un índice desbalanceado tarda más en actualizarse que uno correctamente balanceado; por ese motivo debemos tratar de tener siempre a todos los índices de todas las tablas bien balanceados.

Los siguientes comandos tienen por objetivo recrear un índice, o reindexarlo como también se dice:

ALTER INDEX MiIndice INACTIVE

ALTER INDEX MiIndice ACTIVE

y funcionan muy bien pero … ¿y si queremos reindexar no solamente un índice sino todos los índices de todas las tablas? Evidentemente escribir esos comandos para cada índice de cada tabla tomará mucho tiempo, será muy tedioso y además correremos el riesgo de olvidarnos de alguno. Por ello, escribí el siguiente stored procedure que se encarga de dicha tarea:

SET TERM ^ ;

CREATE PROCEDURE SP_ACTUALIZAR_INDICES
AS
   DECLARE VARIABLE lcNombreIndice VARCHAR(31);
BEGIN

   FOR SELECT
      RDB$INDEX_NAME
   FROM
      RDB$INDICES
   WHERE
      LEFT(RDB$INDEX_NAME, 4) <> 'RDB$'
   INTO
      :lcNombreIndice
   DO BEGIN
      IF (NOT EXISTS(SELECT RDB$INDEX_NAME FROM RDB$RELATION_CONSTRAINTS WHERE RDB$INDEX_NAME = :lcNombreIndice)) THEN BEGIN
         EXECUTE STATEMENT 'ALTER INDEX ' || :lcNombreIndice || ' INACTIVE ;' ;
         EXECUTE STATEMENT 'ALTER INDEX ' || :lcNombreIndice || ' ACTIVE ;' ;
      END
   END

END^

SET TERM ; ^

¿Qué hace este stored procedure?

  1. Obtiene los nombres de todos los índices de todas las tablas cuyas primeras 4 letras sean distintas que “RDB$”. ¿Por qué eso? porque los nombres de las tablas y de los índices de los metadatos siempre empiezan con “RDB$” y los nombres de nuestras tablas y de nuestros índices no deberían empezar con esas letras. En otras palabras, lo que se obtiene son los nombres de nuestros índices, no los nombres de los índices que usa internamente el Firebird
  2. Los índices de las restricciones (Primary Key, Foreign Key, Unique Key) no pueden ser desactivados, el Firebird no lo permite. Y es muy lógico, si se desactivara el índice de una restricción entonces el Firebird no podría verificar esa restricción, por lo tanto hace la fácil: no te permite desactivar el índice de una restricción. Entonces en el stored procedure se verifica que el índice no pertenezca a una restricción. Eso se consigue buscando su nombre en la tabla RDB$RELATION_CONSTRAINTS pues en esa tabla se guardan los datos de todas las restricciones.
  3. Si el nombre del índice no empieza con RDB$ (o sea, si es un índice nuestro) y no es el índice asociado a una restricción entonces se lo inactiva y se lo activa. Este ciclo desactivar/activar tiene por efecto recrear al índice y nos asegura que el nuevo índice esté correctamente balanceado.

Otra versión del stored procedure:

Aquí hay otra versión del stored procedure de arriba, en esta es seleccionable para que puedas ver los nombres de los índices que se están recreando.

SET TERM ^ ;

CREATE PROCEDURE SP_ACTUALIZAR_INDICES
RETURNS(
   tcNombreIndice VARCHAR(31))
AS
BEGIN

   FOR SELECT
      RDB$INDEX_NAME
   FROM
      RDB$INDICES
   WHERE
      LEFT(RDB$INDEX_NAME, 4) <> 'RDB$'
   INTO
      :tcNombreIndice
   DO BEGIN
      IF (NOT EXISTS(SELECT RDB$INDEX_NAME FROM RDB$RELATION_CONSTRAINTS WHERE RDB$INDEX_NAME = :tcNombreIndice)) THEN BEGIN
         EXECUTE STATEMENT 'ALTER INDEX ' || :tcNombreIndice || ' INACTIVE ;' ;
         EXECUTE STATEMENT 'ALTER INDEX ' || :tcNombreIndice || ' ACTIVE ;' ;
         SUSPEND ;
      END
   END

END^

SET TERM ; ^

Y llamarías a este stored procedure así:

SELECT * FROM SP_ACTUALIZAR_INDICES

El problema con los índices de las restricciones

¿Y qué pasa con los índices de las restricciones? ¿No pueden quedar desabalanceados? Por supuesto que sí pueden estar desabalanceados, son índices comunes, no son mágicos. Pero como nosotros no podemos recrearlos tenemos una sola alternativa: realizar un ciclo backup/restore porque al restaurar un backup se crean nuevamente todos los índices, los de las restricciones incluidos.

Artículos relacionados:

Recreando los índices de las tablas

Usando índices en Firebird

El índice del blog Firebird21

Older Entries