In this recipe, we will see how to use the function result cache feature, available from Oracle 11g upwards, to enhance our function's performance.
The following steps will demonstrate the use of the functions result cache:
CONNECT sh@TESTDB/shCreate the function
C_N_K, which calculates the number of k-combinations in a set of n elements:CREATE OR REPLACE FUNCTION C_N_K (N IN NUMBER, K IN NUMBER) RETURN NUMBER IS N_FAT NUMBER := 1; K_FAT NUMBER := 1; N_K_FAT NUMBER := 1; BEGIN FOR J IN 1..N LOOP N_FAT := N_FAT * J; END LOOP; FOR J IN 1..K LOOP K_FAT := K_FAT * J; END LOOP; FOR J IN 1..(N - K) LOOP N_K_FAT := N_K_FAT * J; END LOOP; RETURN (N_FAT / (N_K_FAT * K_FAT)); END; /
Create a procedure with the name
STRESSto test the function in a loop:CREATE OR REPLACE PROCEDURE STRESS(ANUM NUMBER) IS AVAL NUMBER; BEGIN FOR J IN 1..ANUM LOOP AVAL := C_N_K (50,10); END LOOP...