Function Results Cached with DETERMINISTIC Keyword

View All Scripts Login to Run Script

Script Name Function Results Cached with DETERMINISTIC Keyword
Description Whenever your function is pure of side effects, its return value is determined completely by its IN argument values. In this case, you should declare the function to be DETERMINISTIC. You can then create a function-based index with this function and it is possible that the SQL engine will avoid unnecessary executions of the function body. Specifically, if within a SQL statement execution the function is called repeatedly with the same input values, the SQL engine may cache the return values and simply return them without an execution of the function body. This script demonstrates this optimization.
Area PL/SQL General / PL/SQL Procedures, Functions, Packages
Contributor Steven Feuerstein
Created Tuesday March 08, 2016

Statement 1

We can't use DBMS_LOCK in LiveSQL, so we'll go with this one instead.

Homegrown Sleep Procedure

CREATE OR REPLACE PROCEDURE pause_program (secs_in IN PLS_INTEGER) AUTHID DEFINER
IS
   c_secs_in_day   CONSTANT PLS_INTEGER := 24 * 60 * 60;
   l_date                   DATE := SYSDATE;
   l_diff                   NUMBER;
BEGIN
   LOOP
      l_diff := SYSDATE - l_date;
      EXIT WHEN l_diff > (secs_in / c_secs_in_day);
   END LOOP;
END;

Procedure created.

Statement 2

Ignoring the call to pause_program, this function is pure of side effects: if you pass in the same three values for the IN parameters, you will always be given the same value back. But I do not yet add the deterministic keyword.

A Pure Function

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

Function created.

Statement 3

I call the function five times within a SELECT statement, with the same argument values each time. This will be very slow, because it executes the function body each time.

Run It Five Times

DECLARE
   l_start   PLS_INTEGER;
BEGIN
   l_start := DBMS_UTILITY.get_time;

   FOR rec IN (SELECT betwnstr ('FEUERSTEIN', 1, 5)
                 FROM hr.employees
                WHERE ROWNUM <= 5)
   LOOP
      NULL;
   END LOOP;

   DBMS_OUTPUT.put_line (   'Non-deterministic in query elapsed: '
                         || TO_CHAR (DBMS_UTILITY.get_time - l_start)
                        );
END;

Non-deterministic in query elapsed: 979

Statement 4

That's the only change I make.

Add the DETERMINISTIC Keyword

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

Function created.

Statement 5

You will see this block complete much more quickly, because the function body is only executed once, so it does not pause 5 times.

Now Much Faster

DECLARE
   l_start   PLS_INTEGER;
BEGIN
   l_start := DBMS_UTILITY.get_time;

   FOR rec IN (SELECT betwnstr ('FEUERSTEIN', 1, 5)
                 FROM hr.employees
                WHERE ROWNUM <= 5)
   LOOP
      NULL;
   END LOOP;

   DBMS_OUTPUT.put_line (   'Deterministic in query lapsed: '
                         || TO_CHAR (DBMS_UTILITY.get_time - l_start)
                        );
END;

Deterministic in query lapsed: 197

Statement 6

Yes, I do call the function inside a SELECT, but the caching triggered by the DETERMINISTIC keyword only applies to the scope of a single SQL statement. So each time the SELECT-FROM dual is executed, the caching starts anew - which means: no caching at all.

Back to the Slow Version

DECLARE
   l_string   VARCHAR2 (100);
   l_start    PLS_INTEGER;
BEGIN
   l_start := DBMS_UTILITY.get_time;

   FOR rec IN (SELECT *
                 FROM hr.employees
                WHERE ROWNUM <= 5)
   LOOP
      SELECT betwnstr ('FEUERSTEIN', 1, 5)
        INTO l_string
        FROM DUAL;
   END LOOP;

   DBMS_OUTPUT.put_line (   'Deterministic in SELECT from DUAL elapsed: '
                         || TO_CHAR (DBMS_UTILITY.get_time - l_start)
                        );
END;

Deterministic in SELECT from DUAL elapsed: 998

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database