Oracle: diferencia entre determinista y result_cache

Del traductor: decidí comenzar mi viaje a Habr no con un intento de escribir un texto único desde cero, sino con la traducción de un artículo relativamente nuevo (del 17/08/2020) del clásico desarrollo de PL / SQL Stephen Feuerstein , en el que analiza la diferencia con suficiente detalle entre las dos variantes principales de la caché de resultados de la función PL / SQL. Espero que esta traducción sea útil para muchos desarrolladores que se inician en las tecnologías de Oracle.

Introducción

Tarde o temprano, a cualquier desarrollador experimentado de Oracle se le hace una pregunta como:

No entiendo cuál es exactamente la diferencia entre determinista y result_cache. ¿Tienen diferentes casos de uso? Utilizo determinista en muchas funciones que obtienen datos de tablas de búsqueda. ¿Necesito usar la palabra clave result_cache en lugar de determinista?

Pensé que valdría la pena escribir sobre las diferencias entre estas dos posibilidades. Primero, asegurémonos de que todos comprendamos lo mismo cuando una función es determinista.

La Wikipedia ofrece la siguiente definición de un algoritmo determinista:

Un algoritmo determinista es un algoritmo que devuelve el mismo conjunto de salidas para el mismo conjunto de entradas, mientras realiza la misma secuencia de acciones.

En otras palabras, una subrutina determinista (procedimiento o función) no tiene efectos secundarios. Al pasar un conjunto específico de valores como parámetros de entrada, siempre obtendrá el mismo resultado en la salida, independientemente de cuándo, dónde o con qué frecuencia llame a esta subrutina.

Una pregunta razonable es, ¿cuál es un efecto secundario de una función PL / SQL? Como mínimo (la lista no es exhaustiva):

• cualquier (es decir, cualquier) operador DML

  
  
  
  
  

• Usar una variable declarada fuera de esta función (es decir, global, fuera de alcance, también conocida como "global")

  
  
  
  
  

• llamar a cualquier subrutina no determinista

  
  
  
  
  

, deterministic result_cache , . . ( result_cache), , , .

FUNCTION betwnstr (
   string_in   IN   VARCHAR2
 , start_in    IN   INTEGER
 , end_in      IN   INTEGER
)
   RETURN VARCHAR2 DETERMINISTIC 
IS
BEGIN
   RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

- substr



, , . , , .

, Oracle Database , DETERMINISTIC



( ).

?

• ,

  
  
  
  
  

• ( )

  
  
  
  
  

, :

CREATE OR REPLACE FUNCTION pass_number (i NUMBER)
   RETURN NUMBER
   DETERMINISTIC
IS
BEGIN
   DBMS_OUTPUT.put_line ('pass_number executed');
   RETURN 0;
END;
/

DECLARE
   n   NUMBER := 0;
BEGIN
   FOR rec IN (SELECT pass_number (1)
                 FROM all_objects
                WHERE ROWNUM < 6)
   LOOP
      n := n + 1;
   END LOOP;

   DBMS_OUTPUT.put_line (n + 1);
END;
/

pass_number executed
6
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, , 5 , . Oracle Database , ( PL/SQL SQL-, ).

.

, result_cache

betwnstr



, result_cache:

FUNCTION betwnstr (
   string_in   IN   VARCHAR2
 , start_in    IN   INTEGER
 , end_in      IN   INTEGER
)
   RETURN VARCHAR2 
   RESULT_CACHE
IS
BEGIN
   RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

- RESULT_CACHE



. , DETERMINISTIC



, . , result_cache.

result_cache? :

• Oracle Database, SGA (Shared Global Area)

  
  
  
  
  

• , ,

  
  
  
  
  

• ( "" )

  
  
  
  
  

• ( Oracle - , references) , commit

  
  
  
  
  

, . RESULT_CACHE



- "" DETERMINISTIC



( , ) . RESULT_CACHE



, . RESULT_CACHE



Oracle Live SQL.

, , RESULT_CACHE



:

CREATE OR REPLACE FUNCTION pass_number (i NUMBER)
   RETURN NUMBER
   RESULT_CACHE
IS
BEGIN
   DBMS_OUTPUT.put_line ('pass_number executed for ' || i);
   RETURN 0;
END;
/

DECLARE
   n   NUMBER := 0;
BEGIN
   FOR rec IN (SELECT pass_number (100)
                 FROM all_objects
                WHERE ROWNUM < 6)
   LOOP
      n := n + 1;
   END LOOP;

   DBMS_OUTPUT.put_line ('All done ' || TO_CHAR (n + 1));
END;
/

pass_number executed for 100
All done 6


BEGIN
   DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (100));
   DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (200));
   DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (300));
   DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (100));
   DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (200));
   DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (300));
END;
/

Returned 0
pass_number executed for 200
Returned 0
pass_number executed for 300
Returned 0
Returned 0
Returned 0
Returned 0
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

100 ( ), , , .

200 300 - , .

! ( ) PL/SQL , :

All done 6
Returned 0
Returned 0
Returned 0
Returned 0
Returned 0
Returned 0
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, RESULT_CACHE



, , . , - - .

: , result_cache, , .

deterministic result_cache?

DETERMINISTIC



RESULT_CACHE



.

?

, , DETERMINISTIC



, ( ) ( SQL-, ). , , .

, , , RESULT_CACHE



, (instance) , ( , ) . , .

?

: DETERMINISTIC



, .

PL/SQL SQL, , (, ).

, . , , .

DETERMINISTIC



, . Oracle , , .

, result_cache?

. RESULT_CACHE



. DBA, , . , SGA , (latch contention).

, result_cache. :

• ?

  
  
  
  
  

• , ? ,

  
  
  
  
  

• , , , ? , ,

  
  
  
  
  

• - , NLS? , , , , TO_CHAR
  
  
  
    .

  
  
  
  
  

: RESULT_CACHE



, , , .