Indices de función para mejorar un LIKE

Los índices de función son útiles para poder indizar condiciones aplicadas sobre columnas y así acceder rapidamente a los registros.
Un ejemplo típico es el uso de UPPER(columna) o el abuso de LIKE:

SELECT *
FROM my_big_table
WHERE  buffer LIKE 'FF-%';

En este caso, una barrida por toda la tabla (FULL SCAN TABLE) es inevitable.

El uso de LIKE compromete el uso de índices resultando en baja peformance.
Usando la función substr podemos evitar el LIKE en este caso, ya que tenemos siempre le mismo prefijo 'FF-'. Para mejorar la ejecución, podemos crear un índice de función como el que sigue:

CREATE INDEX ix_my_big_table_01 ON my_big_table(substr(buffer,1,4));

La consulta quedaría entonces de esta forma:

SELECT *
FROM my_big_table
WHERE substr(buffer,1,4) = 'FF-';

Para que el índice sea tenido en cuenta por Oracle, necesitamos contar con el privilegio de sistema QUERY REWRITE y dos parámetros de la base seteados: query_rewrite_enabled=TRUE y query_rewrite_integrity=TRUSTED

Ver también:
Mis índices no funcionan!
Indices condicionales
11g y sus índices invisibles