Oracle Database 11gR2 Performance Tuning Cookbook

Taking advantage of function result cache

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.

How to do it...

The following steps will demonstrate the use of the functions result cache:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create 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 ...