Problem
You want to create a PL/SQL function that will return the same result whenever a given input, or set of inputs, is presented to it. You want the database to optimize based upon that deterministic nature.
Solution
Specify the DETERMINISTIC
keyword when creating the function to indicate that the function will always return the same result for a given input. For instance, you want to return a specific manager name based upon a given manager ID. Furthermore, you want to optimize for the fact that any given input will always return the same result. The following example demonstrates a function that does so by specifying the DETERMINISTIC
keyword:
CREATE OR REPLACE FUNCTION manager_name(mgr_id IN NUMBER)
RETURN VARCHAR2
DETERMINISTIC IS
first_name employees.first_name%TYPE;
last_name employees.last_name%TYPE;
BEGIN
IF mgr_id IS NOT NULL THEN
SELECT first_name, last_name
INTO first_name, last_name
FROM EMPLOYEES
WHERE employee_id = mgr_id;
RETURN first_name || ' ' || last_name;
ELSE
RETURN 'N/A';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 'N/A';
END;
This function will return the manager name for a matching EMPLOYEE_ID. If there are no matches for the EMPLOYEE_ID found, then N/A will be returned.
How It Works
A deterministic function is one that always returns the same resulting value as long as the parameters that are passed in are the same. This type of function can be useful for improving performance. The function will be executed only once for any given set of parameters. This means that if the same parameters are passed to this function in subsequent calls, then the function will be bypassed and return the cached value from the last execution using those parameters. This can really help in cases where calculations are being performed and repeated calls to the function may take a toll on performance.
The DETERMINISTIC
clause is required in a couple of cases. In the event that you are calling a function in an expression of a function-based index, you need to write the function as DETERMINISTIC
, or you will receive errors. Similarly, a function must be made DETERMINISTIC
if it is being called in an expression of a materialized view query or if the view is marked as ENABLE QUERY REWRITE or REFRESH FAST
.