Funciones definidas por usuario

Un índices sobre expresiones es un concepto muy genérico. Además de las funciones como UPPER, también se puede usar expresiones como A + B y funciones especificadas en la definición del índice por el usuario.

Existe una excepción importante: no es posible, por ejemplo, referirse a la fecha actual dentro de la definición del índice, ni directamente ni indirectamente, como en el siguiente ejemplo.

CREATE FUNCTION get_age(date_of_birth DATE) 
RETURN NUMBER
AS
BEGIN
  RETURN 
    TRUNC(MONTHS_BETWEEN(SYSDATE, date_of_birth)/12);
END

La función GET_AGE usa la fecha actual (SYSDATE) para calcular la edad basado en la fecha de cumpleaños proporcionada. Se puede emplear la función en todas las partes de la sentencia SQL, por ejemplo, en select y en el filtro where:

SELECT first_name, last_name, get_age(date_of_birth)
  FROM employees
 WHERE get_age(date_of_birth) = 42

La sentencia lista todos los empleados de 42 años de edad. Usar un índice basado en función es una idea obvia para optimizar la sentencia, pero no puedes usar la función GET_AGE en la definición del índice porque no es determinista. Eso significa que el resultado de la función llamada no está completamente determinado por sus parámetros. Solamente las funciones que devuelven siempre el mismo resultado con los mismos parámetros (esas funciones son deterministas) pueden ser indexadas.

La razón de esta restricción es sencilla. Cuando se inserta un nuevo registro, la base de datos llama la función y almacena el resultado dentro del índice y ahí se queda, sin cambiar. No existe un proceso periódico que actualice los índices. La base de datos actualiza el índice de las edades cuando la fecha del cumpleaños está actualizada por una sentencia update. Después del próximo cumpleaños, la edad almacenada dentro del índice será incorrecta.

Además, al ser determinista, PostgreSQL y la base de datos Oracle requieren funciones ya declaradas para ser deterministas. Cuando se usan dentro de un índice entonces se tiene que usar la palabra DETERMINISTIC (Oracle) o IMMUTABLE (PostgreSQL).

Otros ejemplos para las funciones que no pueden ser “indexadas” son los generadores de números aleatorios y funciones que dependen de variables de entorno.

Si te gusta mi manera de explicar, te encantará mi libro.