Oracle: différence entre déterministe et result_cache

Du traducteur: J'ai décidé de commencer mon voyage à Habr non pas avec une tentative d'écrire un texte unique à partir de zéro, mais avec la traduction d'un article relativement récent (du 17/08/2020) par le développement PL / SQL classique de Stephen Feuerstein , dans lequel il discute de la différence suffisamment détaillée entre les deux principales variantes du cache de résultats de la fonction PL / SQL. J'espère que cette traduction sera utile pour de nombreux développeurs qui se lancent dans les technologies Oracle.

introduction

Tôt ou tard, tout développeur Oracle chevronné se voit poser une question telle que:

Je ne comprends pas exactement quelle est la différence entre déterministe et result_cache. Ont-ils des cas d'utilisation différents? J'utilise déterministe dans de nombreuses fonctions qui obtiennent des données à partir de tables de consultation. Dois-je utiliser le mot clé result_cache au lieu de déterministe?

J'ai pensé qu'il valait la peine d'écrire sur les différences entre ces deux possibilités. Tout d'abord, assurons-nous que nous avons tous la même compréhension du moment où une fonction est déterministe.

Le Wikipedia donne la définition suivante d'un algorithme déterministe:

Un algorithme déterministe est un algorithme qui renvoie le même ensemble de sorties pour le même ensemble d'entrées, tout en exécutant la même séquence d'actions.

En d'autres termes, un sous-programme déterministe (procédure ou fonction) n'a pas d'effets secondaires. En passant un ensemble spécifique de valeurs comme paramètres d'entrée, vous obtiendrez toujours le même résultat en sortie, quels que soient le moment, l'endroit ou la fréquence d'appels de ce sous-programme.

Une question raisonnable est, quel est un effet secondaire d'une fonction PL / SQL? Au minimum (la liste n'est pas exhaustive):

• tout (c'est-à-dire tout) opérateur DML

  
  
  
  
  

• Utilisation d'une variable déclarée en dehors de cette fonction (c'est-à-dire globale, hors de portée aka "global")

  
  
  
  
  

• appeler n'importe quel sous-programme non déterministe

  
  
  
  
  

, 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



, , , .