A PL/SQL function in Oracle can be tagged with a DETERMINISTIC clause, to indicate that the function will always produce the same output for a given input and will have no side effects.
A little elaboration is in order here.
1. …will always produces the same output for a given input
Let’s see this with an example: a function get_primary_phone takes as input customer_id and returns the customer’s primary phone. Internally, the function executes SQL on a customer contact table, ranks and filters the result to get the customer’s primary phone number.
If the customer’s contact information changes in the table, the value returned by get_primary_phone(customer_id) will not produce the same output for the same input.
This implies that get_primary_phone(customer_id) cannot be deterministic.
In general, any function whose output depends on the contents of variables / conditions / tables / datasets not passed as IN parameters is non-deterministic.
So,
SYSDATE is non-deterministic – the current date and time will change depending on when SYSDATE is called, but
LAST_DAY(date) is deterministic – for a given input date, the last date of the month will be unchanged no matter when or how many times LAST_DAY(date) is called.
Another eligible deterministic function: Java arithmetic calculation.
2. …will have no side effects
This means that the function produces no other changes in local static variables, non-local variables, reference arguments, and performs no DML operations on other tables.
What’s the use of qualifying a function as DETERMINISTIC?
You may well ask. PL/SQL functions work fine without any of this DETERMINISTIC business: why, then, should we add this clause?
Here are a couple of reasons.
-
The DETERMINISTIC clause is a shorthand to document a function’s special properties – that its output depends wholly on its inputs and that it produces no side effects
-
Making a function DETERMINISTIC has a performance benefit – if the function is called multiple times within the scope of a single server call (e.g. execution of a PL/SQL block) with the same parameter values, the optimizer can choose to use the previously calculated result instead of invoking the function repeatedly.
More of the performance benefit in a demo below.
DETERMINISTIC functions: Syntax
Just plop in the word “DETERMINISTIC” after specifying the return type of the function.
Example: deterministic function is_number(p_value) – this checks if a string input is a valid number or not.
-- Function that tests if the input is a number
-- Returns 'Y' if number, else 'N'
create or replace function is_number
(p_value in varchar2)
return char
deterministic
is
p_num number;
begin
p_num := to_number(p_value);
return 'Y';
exception
when value_error then
return 'N';
end;
/
DETERMINISTIC functions: Optimization in action
We’ll see how Oracle leverages a function’s deterministic property to avoid executing it multiple times for the same input.
For our tests, we will query a table EMPLOYEE (EMP_ID, EMP_NAME, DEPT) with 20 rows.
[Tests run on Oracle 11.2.0.2]
SQL> select * from employee; EMP_ID EMP_NAME DEPT ------ -------------------- ---------- 100 Steven King 515 101 Neena Kochhar 515 102 Lex De Haan 515 103 Alexander Hunold 590 104 Bruce Ernst 590 105 David Austin A90 106 Valli Pataballa 590 107 Diana Lorentz 590 108 Nancy Greenberg 515 109 Daniel Faviet 515 110 John Chen 515 111 Ismael Sciarra 515 112 Jose Manuel Urman A1A 113 Luis Popp 515 114 Den Raphaely 515 115 Alexander Khoo 515 116 Shelli Baida 515 117 Sigal Tobias 515 118 Guy Himuro 515 119 Karen Colmenares A1A 20 rows selected.
Column DEPT in this table is a string which may or may not be a number: we will call the function is_number(dept) defined above to ascertain if DEPT is a number or not.
SQL> select emp_id 2 , emp_name 3 , dept 4 , is_number(dept) is_dept_numeric 5 from employee; EMP_ID EMP_NAME DEPT IS_DEPT_NUMERIC ------ -------------------- ---------- -------------------- 100 Steven King 515 Y 101 Neena Kochhar 515 Y 102 Lex De Haan 515 Y 103 Alexander Hunold 590 Y 104 Bruce Ernst 590 Y 105 David Austin A90 N 106 Valli Pataballa 590 Y 107 Diana Lorentz 590 Y 108 Nancy Greenberg 515 Y 109 Daniel Faviet 515 Y 110 John Chen 515 Y 111 Ismael Sciarra 515 Y 112 Jose Manuel Urman A1A N 113 Luis Popp 515 Y 114 Den Raphaely 515 Y 115 Alexander Khoo 515 Y 116 Shelli Baida 515 Y 117 Sigal Tobias 515 Y 118 Guy Himuro 515 Y 119 Karen Colmenares A1A N 20 rows selected.
Note that the same value of DEPT is repeated across several rows in the table: Oracle need not reevaluate the Y/N return for an input if it has done so already in the SQL for another row.
Without a DETERMINISTIC clause, Oracle will not be aware that it can avoid reevaluation, and proceed to execute is_number(dept) N times for N EMPLOYEE rows.
Let’s compare the SQL behavior before and after making the function DETERMINISTIC.
Before making the function DETERMINISTIC:
We will add a dbms_output.put_line to is_number(p_value), to check how many times the function gets invoked in our SQL.
Non-deterministic function:
-- Function that tests if the input is a number
-- Returns 'Y' if number, else 'N'
create or replace function is_number
(p_value in varchar2)
return char
is
p_num number;
begin
dbms_output.put_line('is_number ('||p_value||') called');
p_num := to_number(p_value);
return 'Y';
exception
when value_error then
return 'N';
end;
/
Compile the function:
SQL> -- Function that tests if the input is a number
SQL> -- Returns 'Y' if number, else 'N'
SQL> -- non-deterministic
SQL> create or replace function is_number
2 (p_value in varchar2)
3 return char
4 is
5 p_num number;
6 begin
7 dbms_output.put_line('is_number ('||p_value||') called');
8 p_num := to_number(p_value);
9 return 'Y';
10 exception
11 when value_error then
12 return 'N';
13 end;
14 /
Function created.
Now, run the SQL:
SQL> -- SQL result when the called function SQL> -- is non-deterministic SQL> select emp_id 2 , emp_name 3 , dept 4 , is_number(dept) is_dept_numeric 5 from employee; EMP_ID EMP_NAME DEPT IS_DEPT_NUMERIC ------ -------------------- ---------- -------------------- 100 Steven King 515 Y 101 Neena Kochhar 515 Y 102 Lex De Haan 515 Y 103 Alexander Hunold 590 Y 104 Bruce Ernst 590 Y 105 David Austin A90 N 106 Valli Pataballa 590 Y 107 Diana Lorentz 590 Y 108 Nancy Greenberg 515 Y 109 Daniel Faviet 515 Y 110 John Chen 515 Y 111 Ismael Sciarra 515 Y 112 Jose Manuel Urman A1A N 113 Luis Popp 515 Y 114 Den Raphaely 515 Y 115 Alexander Khoo 515 Y 116 Shelli Baida 515 Y 117 Sigal Tobias 515 Y 118 Guy Himuro 515 Y 119 Karen Colmenares A1A N 20 rows selected. is_number (515) called is_number (515) called is_number (515) called is_number (590) called is_number (590) called is_number (A90) called is_number (590) called is_number (590) called is_number (515) called is_number (515) called is_number (515) called is_number (515) called is_number (A1A) called is_number (515) called is_number (515) called is_number (515) called is_number (515) called is_number (515) called is_number (515) called is_number (A1A) called
You can see that the non-deterministic function was called once per row i.e. 20 times for 20 rows.
After making the function DETERMINISTIC:
We will make exactly one change to the is_number(p_value) code: add the clause DETERMINISTIC.
Deterministic function:
-- Function that tests if the input is a number
-- Returns 'Y' if number, else 'N'
-- deterministic
create or replace function is_number
(p_value in varchar2)
return char
deterministic
is
p_num number;
begin
dbms_output.put_line('is_number ('||p_value||') called');
p_num := to_number(p_value);
return 'Y';
exception
when value_error then
return 'N';
end;
/
Compile the function:
SQL> -- Function that tests if the input is a number
SQL> -- Returns 'Y' if number, else 'N'
SQL> -- determinitic
SQL> create or replace function is_number
2 (p_value in varchar2)
3 return char
4 deterministic
5 is
6 p_num number;
7 begin
8 dbms_output.put_line('is_number ('||p_value||') called');
9 p_num := to_number(p_value);
10 return 'Y';
11 exception
12 when value_error then
13 return 'N';
14 end;
15 /
Function created.
Now, run the SQL:
SQL> -- SQL result when the called function SQL> -- is deterministic SQL> select emp_id 2 , emp_name 3 , dept 4 , is_number(dept) is_dept_numeric 5 from employee; EMP_ID EMP_NAME DEPT IS_DEPT_NUMERIC ------ -------------------- ---------- -------------------- 100 Steven King 515 Y 101 Neena Kochhar 515 Y 102 Lex De Haan 515 Y 103 Alexander Hunold 590 Y 104 Bruce Ernst 590 Y 105 David Austin A90 N 106 Valli Pataballa 590 Y 107 Diana Lorentz 590 Y 108 Nancy Greenberg 515 Y 109 Daniel Faviet 515 Y 110 John Chen 515 Y 111 Ismael Sciarra 515 Y 112 Jose Manuel Urman A1A N 113 Luis Popp 515 Y 114 Den Raphaely 515 Y 115 Alexander Khoo 515 Y 116 Shelli Baida 515 Y 117 Sigal Tobias 515 Y 118 Guy Himuro 515 Y 119 Karen Colmenares A1A N 20 rows selected. is_number (515) called is_number (515) called is_number (590) called is_number (A90) called is_number (A1A) called is_number (515) called is_number (A1A) called
Lo! The non-deterministic function was called far fewer times this time round.
This difference in performance with the DETERMINISTIC clause can be significant when dealing with huge volumes of data.
Caution: Don’t mark non-deterministic functions as DETERMINISTIC!
As the saying goes, with great power comes great responsibility. Don’t let this performance benefit tempt you into marking non-deterministic functions as deterministic. Doing so might mislead Oracle to believe that the result of the function, once calculated, is good for reuse when in fact it isn’t — and you can end up with query results that are plain inaccurate.
Rule of thumb: If a function uses package variables or accesses the database in any way that might affect its output, do not make it deterministic.
Summary
This article describes when to use the DETERMINISTIC clause with Oracle functions and what optimization you might get when marking functions as deterministic.
For Further Reading
- Deterministic algorithm
- PL/SQL Language Reference 12c
- How to write a safe result-cached PL/SQL function
- Caching and worries about consistent data
{ 2 comments… read them below or add one }
Just a correction “Regular expressions are NOT deterministic when you take NLS settings into account and thus cannot be used in constraints and/or function-based indexes.”
https://connor-mcdonald.com/2018/08/22/take-care-with-regular-expressions/
@gary: Thanks for that. Something new learned today! Post updated to remove the regex reference.