Deterministic vs. Non-Deterministic Functions in Oracle

In a previous post (PL/SQL, Deterministic Functions, and Function-Based Indexes) we discussed the creation of a function-based index using a deterministic, user-defined function to improve performance, as well as a means of creating reusable code. This is a continuation to that post investigating the meaning of DETERMINISTIC.

A deterministic function is one that will always return the same value given the same set of parameter values. As an example, SUBSTR is a deterministic function (as is IS_DATE, the user-defined function we created in the post mentioned above) because each time you call SUBSTR with the same parameter values the same result will be returned [e.g. SUBSTR('Hello World', 1, 5) will always return 'Hello']. As we described in our previous post, in order to create a function-based index using a user-defined function, we must specify the function is DETERMINISTIC. But what will happen if we specify a function is deterministic when in reality, it is not? We will use another simple example to find out.

The following function returns the number of years from today given a specified date parameter value. This function is not deterministic because the function returns a different value for a given parameter value depending upon the day it is executed; however, we have created it using the DETERMINISTIC keyword to see the effects.

CREATE OR REPLACE FUNCTION years_from_today
 ( p_date   IN DATE )
RETURN NUMBER DETERMINISTIC IS
BEGIN
  RETURN ABS(MONTHS_BETWEEN(SYSDATE, p_date) / 12);
END years_from_today;
/

SHO ERR

To test the function, we will create a test table with a date column then query the table and compare the current date to the date in the DATE_VALUE column.

CREATE TABLE det_test AS
SELECT TO_DATE('01-JUL-2009', 'DD-MON-YYYY') AS date_value 
FROM   dual;

SELECT date_value, SYSDATE, years_from_today(date_value)
FROM   det_test
WHERE  years_from_today(date_value) < 2;

DATE_VALU SYSDATE   YEARS_FROM_TODAY(DATE_VALUE)
--------- --------- ----------------------------
01-JUL-09 20-SEP-10                   1.21861774

Create a function-based index on the new table.

CREATE INDEX det_test_fbi ON det_test (years_from_today(date_value));

Examine the execution plan to verify the use of the index when YEARS_FROM_TODAY is placed in the WHERE clause.

EXPLAIN PLAN FOR
SELECT date_value, SYSDATE, years_from_today(date_value)
FROM   det_test
WHERE  years_from_today(date_value) < 2;

SELECT * FROM TABLE(dbms_xplan.display());

----------------------------------------------------
| Id  | Operation                   | Name         |
----------------------------------------------------
|   0 | SELECT STATEMENT            |              |
|   1 |  TABLE ACCESS BY INDEX ROWID| DET_TEST     |
|*  2 |   INDEX RANGE SCAN          | DET_TEST_FBI |
---------------------------------------------------- 

Now, to see the implications of our DETERMINISTIC choice, change the date on the server ... in a test environment of course ... to move ahead a full year. Even though the date has changed, running the query again will still return the same value as before from YEARS_FROM_TODAY, along with the same row, because the index is used instead of executing the function.

SELECT date_value, SYSDATE, years_from_today(date_value)
FROM   det_test
WHERE  years_from_today(date_value) < 2;

DATE_VALU SYSDATE   YEARS_FROM_TODAY(DATE_VALUE)
--------- --------- ----------------------------
01-JUL-09 20-SEP-11                    1.2186201 

Without the WHERE clause, the query should return the following:

DATE_VALU SYSDATE   YEARS_FROM_TODAY(DATE_VALUE)
--------- --------- ----------------------------
01-JUL-09 20-SEP-11                   2.21867063

As is evident from the erroneous output, a function should never be created as deterministic unless it will ALWAYS return the same value given the same parameters.