Cada vez que se ejecuta un SELECT actúa el optimizador de consultas del Firebird. Este es un módulo que se encarga de mejorar todo lo posible ese SELECT para que muestre sus resultados a la mayor velocidad posible.

El optimizador de consultas del Firebird realmente es muy, muy bueno, y en casi todos los casos hace un trabajo excelente optimizando las consultas. Así que en general nuestra mejor política es dejarlo hacer lo que sabe hacer muy bien.

Pero en raras ocasiones puede ocurrir que deseemos que no optimice las consultas porque nosotros vemos que hay otro PLAN con el cual obtendremos un mejor resultado.

¿Cómo funciona el optimizador de consultas del Firebird?

A grosso modo, de la siguiente manera: para cada una de las tablas involucradas en el SELECT lee las estadísticas de los índices que puede usar y elige el índice más adecuado, si puede y necesita cambiar el orden de las tablas, lo hace. Obtiene así (quizás) un nuevo SELECT que mostrará los mismos resultados que el SELECT original pero a mayor velocidad.

¿Cómo le decimos que no lo queremos utilizar?

Tenemos dos maneras de decirle que no actúe:

  1. Eligiendo nosotros el PLAN
  2. Impidiendo que use un índice

Caso 1. Eligiendo nosotros el PLAN

Si queremos usar un determinado PLAN entonces lo escribimos en nuestro SELECT, algo como:

Listado 1:

SELECT
   *
FROM
   MiTabla1
WHERE
   MiColumna1 = 1234
PLAN
   (MiTabla1 NATURAL)

Si hay un índice sobre MiColumna1 el optimizador de consultas va a querer usarlo, pero si no queremos que lo haga escribimos nuestro propio PLAN, como en el Listado 1. Si hay más de un índice y queremos usar uno distinto al elegido por el optimizador de consultas, entonces lo especificamos en el PLAN.

Listado 2:

SELECT
   *
FROM
   MiTabla1
WHERE
   MiColumna1 = 1234
PLAN
   (MiTabla1 INDEX (UQ_MITABLA1))

Haríamos algo así cuando por ejemplo el optimizador de consultas eligió al índice PK_MITABLA1 pero nosotros queremos que use el índice UQ_MITABLA1.

Caso 2. Impidiendo que use un índice

La técnica para impedir que use un índice en una columna es sumarle el número 0 (si la columna es numérica) o un espacio vacío (si la columna es de tipo carácter) o poner una condición que siempre evalúe a falso.

Listado 3:

SELECT
   *
FROM
   MiTabla1
WHERE
   MiColumna1 = 1234

Aquí el optimizador de consultas tratará de usar un índice que involucre a MiColumna1, si es que existe tal índice. Pero ¿si existe ese índice y no queremos que lo utilice?

Listado 4:

SELECT
   *
FROM
   MiTabla1
WHERE
   MiColumna1 + 0 = 1234

Al agregarle ese + 0 le estamos diciendo que en esa condición no use un índice (o sea que usará NATURAL en el PLAN). Si nuestra columna es de tipo carácter, entonces escribiríamos algo como:

Listado 5:

SELECT
   *
FROM
   MiTabla1
WHERE
   MiColumna1 || '' = 'ALICIA'

Ahora, es el || ” el que le dice que no use un índice sino que use NATURAL en el PLAN. Y si queremos usar una técnica que funcionará siempre, sin importar que la columna sea de tipo numérico o de tipo carácter o de tipo fecha, entonces podríamos escribir algo como:

Listado 6:

SELECT
   *
FROM
   MiTabla1
WHERE
   MiColumna1 = 1234 OR 1 = 0

Evidentemente que 1 = 0 siempre será falso y por lo tanto en esa condición no se usará un índice, siempre se usará NATURAL.

Conclusión:

El optimizador de consultas del Firebird es realmente muy, muy bueno, y en la grandísima mayoría de los casos nuestra mejor política es dejarlo hacer lo que sabe hacer muy bien. Para las muy raras ocasiones en que no queremos que optimice todo el SELECT o una parte del SELECT, tenemos dos técnicas que podemos usar: una es eligiendo nosotros mismos el PLAN y la otra es evitando que en una columna se use un índice.

Artículos relacionados:

Usando un PLAN

Algo más sobre PLAN

Entendiendo el contenido de un PLAN

Poniendo los JOIN en el orden correcto

¿Por qué deben escribirse primero los INNER JOIN?

Optimizando las consultas

Optimizando los JOIN

Selectividad de los índices

Recreando índices y calculando estadísticas

Recreando todos los índices de todas las tablas

Recreando los índices de las tablas

Usando índices en Firebird

El índice del blog Firebird21

El foro del blog Firebird21