Uso de deterministic

Cuando sabemos que dados unos parámetros una función devolverá siempre lo mismo deberíamos utilizar la expresión DETERMINISTC. Este hint le va a permitir al optimizador de Oracle evitar hacer llamadas redundantes a la función. Sólo se puede garantizar que una función devolverá lo mismo dados unos parámetros cuando no dependa de variables de sesión (como sysdate) y otros objetos de la base de datos (llamadas a funciones no deterministic o consultas). El caso más simple y más común es una función que devuelva el valor de una constante. También son comunes funciones de operaciones aritméticas o de concatenación.

En este ejemplo podemos ver dos funciones DETERMINISTC y otra que no lo puede ser:

-- Especificación
CREATE OR REPLACE PACKAGE PPRUEBA IS 
  FUNCTION vValorCte RETURN VARCHAR2 DETERMINISTIC;
    PRAGMA RESTRICT_REFERENCES( vValorCte, RNDS, RNPS, WNDS, WNPS );
    -- Función que devuelve el valor de la constante VCONSTANTE
  
  FUNCTION nSuma( nValorA NUMBER, nValorB NUMBER ) 
  RETURN NUMBER DETERMINISTIC;
    PRAGMA RESTRICT_REFERENCES( nSuma, RNDS, RNPS, WNDS, WNPS );
    -- Función que devuelve la suma de los dos valores que le pasan 
    -- como parámetro.

  FUNCTION nSumaNoDeterminante( nValorA NUMBER, nValorB NUMBER ) 
  RETURN NUMBER;
    PRAGMA RESTRICT_REFERENCES( nSumaNoDeterminante, 
                                RNPS, WNDS, WNPS );
    -- Función que devuelve la suma de los dos valores que le pasan 
    -- como parámetro. Como hace una select de dual no puede hacerse
    -- deterministic
END;
/
SHOW ERRORS

-- Cuerpo
CREATE OR REPLACE PACKAGE BODY PPRUEBA IS 
  -------------
  -- Constantes
  -------------
  VCONSTANTE CONSTANT VARCHAR2(6) := 'PRUEBA';

  FUNCTION vValorCte RETURN VARCHAR2 DETERMINISTIC IS 
    -- Función que devuelve el valor de la constante VCONSTANTE
  BEGIN 
    RETURN VCONSTANTE;
  END vValorCte;

  FUNCTION nSuma( nValorA NUMBER, nValorB NUMBER ) 
  RETURN NUMBER DETERMINISTIC IS 
    -- Función que devuelve la suma de los dos valores que le pasan 
    -- como parámetro.
  BEGIN 
    RETURN nValorA + nValorB;
  END nSuma;
  
  FUNCTION nSumaNoDeterminante( nValorA NUMBER, nValorB NUMBER ) 
  RETURN NUMBER IS 
    -- Función que devuelve la suma de los dos valores que le pasan 
    -- como parámetro. Como hace una select de dual no puede hacerse
    -- deterministic
    ------------
    -- Variables
    ------------
    nSumaTotal NUMBER;
  BEGIN 
    SELECT nValorA + nValorB
    INTO   nSumaTotal
    FROM   DUAL;

    RETURN nSumaTotal;
  END nSumaNoDeterminante;
END;
/
SHOW ERRORS

Esto hace que sea muy aconsejable utilizar el hint siempre que sea posible, sobre todo si pensamos utilizar la función en alguna select. También es requisito para poder hacer un índice sobre una función.
La definición con propiedad puede verse en la documentación oficial: PL/SQL Subprograms.

Anuncios