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