de Martin LE TARNEC.

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).

Atención

PostgreSQL y la base de datos Oracle confían en la declaración de DETERMINISTIC o IMMUTABLE. Eso significa que confían en el desarrollador.

Se puede declarar la función GET_AGE para ser determinista y usarla dentro de un índice. Sin tomar en cuenta la declaración, eso no funcionará como se espera porque la edad almacenada dentro del índice no se incrementará a medida que los años pasen; los empleados no serán mayores; por lo menos no en el índice.

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.

Nota

DB2 no puede usar funciones definidas por usuarios dentro de un índice (aunque sean deterministas)

Piénsalo

¿Cómo puede existir un índice para optimizar una sentencia para todos los empleados que tienen 42 años de edad?

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

Acerca del autor

Foto de Markus Winand

Markus Winand enseña eficientemente SQL, en casa y online. Minimiza el tiempo de desarrollo utilizando moderno SQL y optimiza el tiempo de ejecución con indexación inteligente. Para ello también ha publicado el libro SQL Performance Explained.

“Use The Index, Luke” de Markus Winand se halla bajo licencia Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Aspectos legales | Contacto | SIN GARANTÍA | Marcas | Privacy | CC-BY-NC-ND 3.0 licencia