Oracle: perbedaan antara deterministik dan result_cache

Dari penerjemah: Saya memutuskan untuk memulai perjalanan saya ke Habr bukan dengan mencoba menulis beberapa teks unik dari awal, tetapi dengan terjemahan artikel yang relatif baru (dari 17/08/2020) oleh pengembangan PL / SQL klasik Stephen Feuerstein , di mana ia membahas perbedaan yang cukup mendetail antara dua varian utama cache hasil fungsi PL / SQL. Saya harap terjemahan ini akan bermanfaat bagi banyak pengembang yang memulai dengan teknologi Oracle.

pengantar

Cepat atau lambat, setiap pengembang Oracle berpengalaman ditanyai pertanyaan seperti:

Saya tidak mengerti apa sebenarnya perbedaan antara deterministik dan result_cache. Apakah mereka memiliki kasus penggunaan yang berbeda? Saya menggunakan deterministik di banyak fungsi yang mendapatkan data dari tabel pencarian. Apakah saya perlu menggunakan kata kunci result_cache daripada deterministik?

Saya pikir akan bermanfaat untuk menulis tentang perbedaan antara kedua kemungkinan ini. Pertama, pastikan kita semua memiliki pemahaman yang sama tentang kapan suatu fungsi bersifat deterministik.

The Wikipedia memberikan definisi berikut algoritma deterministik:

Algoritme deterministik adalah algoritme yang mengembalikan kumpulan keluaran yang sama untuk kumpulan masukan yang sama, sambil melakukan urutan tindakan yang sama.

Dengan kata lain, subrutin deterministik (prosedur atau fungsi) tidak memiliki efek samping. Dengan meneruskan sekumpulan nilai tertentu sebagai parameter masukan, Anda akan selalu mendapatkan hasil yang sama pada keluaran, terlepas dari kapan, di mana, atau seberapa sering Anda memanggil subrutin ini.

Pertanyaan yang masuk akal adalah, apa efek samping dari fungsi PL / SQL? Minimal (daftarnya tidak lengkap):

• operator DML (yaitu, apa saja)

  
  
  
  
  

• Menggunakan variabel yang dideklarasikan di luar fungsi ini (yaitu global, out-of-scope alias "global")

  
  
  
  
  

• memanggil subrutin non-deterministik

  
  
  
  
  

, 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



, , , .