EVERY once in a while, a developer gets in touch with a question like this:
I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?
So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be deterministic.
In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.
Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get the same results - no matter when, where or how often you run it.
What, you may be wondering, would be a side effect in a PL/SQL function? Any of these (this list is not exhaustive):
- Any and every SQL statement
- Referencing an out-of-scope variable (aka, "global")
- Invoking a non-deterministic subprogram
Next we'll take a look at how you use these two features in your code. Then I will delve into the differences. We will, by the way, focus on functions in this post. It is possible to declare a procedure as deterministic, but this has no known impact that I am aware of and is not commonly used. And you cannot declare a procedure as "result cached."
1. Defining a function as deterministic
FUNCTION betwnstr ( string_in IN VARCHAR2 , start_in IN INTEGER , end_in IN INTEGER ) RETURN VARCHAR2 DETERMINISTIC IS BEGIN RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1)); END;
This simple encapsulation of SUBSTR allows me to specify the start and end positions, rather than the start position and number of characters. I hope you will agree that this is deterministic.
So that's all you have to do: add the DETERMINISTIC keyword to the header of the function or procedure.
What it does for you:
- Makes it possible to use the function in a function-based index
- Might improve performance by caching and reusing function return values
CREATE OR REPLACE FUNCTION pass_number (i NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN DBMS_OUTPUT.put_line ('pass_number executed'); RETURN 0; END; / DECLARE n NUMBER := 0; BEGIN FOR rec IN (SELECT pass_number (1) FROM all_objects WHERE ROWNUM < 6) LOOP n := n + 1; END LOOP; DBMS_OUTPUT.put_line (n + 1); END; / pass_number executed 6
Notice that even though the function was invoked 5 times, the body of the function was executed just once. Oracle Database created a tiny, little, short-lived cache, just for this function and just for the server call (PL/SQL block or SQL statement) in which it was invoked.
Rob van Wijk offers lots more details on the behavior and performance of deterministic functions here.
2. Defining a function as "result cached"
Let's now change make that betwnstr function a result-cached function:
FUNCTION betwnstr ( string_in IN VARCHAR2 , start_in IN INTEGER , end_in IN INTEGER ) RETURN VARCHAR2 RESULT_CACHE IS BEGIN RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1)); END;
Gee, that was (deceptively) easy. I just add the RESULT_CACHE keyword. Notice that I removed the DETERMINISTIC keyword, but I did that only for clarity's sake. A function can have both of these keywords in its header. It can, in other words, be deterministic and result-cached.
What it does for you:
- Tells Oracle Database that you want to use some memory in the SGA or Shared Global Area to cache argument values and returned values.
- From that point on, whenever the function is invoked by any session in the database instance, the body of the function will only be executed if it has not already been called with those same input values.
- If there is a "hit" in the cache for that combination of arguments, the return value(s) will simply be grabbed from the cache and returned to the calling block.
- If the function relies on (references) any database tables, when any user commits changes to that table, the cache for the function will be automatically wiped out.
CREATE OR REPLACE FUNCTION pass_number (i NUMBER) RETURN NUMBER RESULT_CACHE IS BEGIN DBMS_OUTPUT.put_line ('pass_number executed for ' || i); RETURN 0; END; / DECLARE n NUMBER := 0; BEGIN FOR rec IN (SELECT pass_number (100) FROM all_objects WHERE ROWNUM < 6) LOOP n := n + 1; END LOOP; DBMS_OUTPUT.put_line ('All done ' || TO_CHAR (n + 1)); END; / BEGIN DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (100)); DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (200)); DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (300)); DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (100)); DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (200)); DBMS_OUTPUT.PUT_LINE ('Returned ' || pass_number (300)); END; / pass_number executed for 100 All done 6 Returned 0 pass_number executed for 200 Returned 0 pass_number executed for 300 Returned 0 Returned 0 Returned 0 Returned 0
I call pass_number a total of three times with an argument value of 100. Notice that the function body is executed just once, out of those three times - even though the calls came from two different blocks.
And I call pass_number twice each with argument values of 200 and 300, but the function body is executed just once for each of those.
If I disconnect from my session and reconnect, then run those same two blocks (without recompiling the function), I will see only:
All done 6 Returned 0 Returned 0 Returned 0 Returned 0 Returned 0 Returned 0
The cache for a result-cached function persists across blocks, across sessions, across users. It is a complex feature that can have a ripple effect, both good and bad, across your application.
Which is to say: if you are not careful about how you use RESULT_CACHE, you could cause unintended negative consequences, some of which are explored in this article: How to write a safe result-cached function.
How these two features are similar
How they are different
When to use deterministic
When to use result cache
- It is invoked with the same argument values repeatedly?
- If the function relies on a table, is the data in the table or view static (example: materialized view)? That's a good candidate.
- If the function relies on a table, is the data queried much more frequently than it is updated? Also a good possibility then. Remember: the cache will be invalidated when changes to a dependent table are committed.
- Does the function have any session-specific dependencies, such as reliance on NLS parameter settings? If so, then the cached values might not match the session-dependent values. A good example is using TO_CHAR in your function without a format mask. VPD and reliance on system context values is another. Generally the solution for this is to move all such dependencies to the parameter list.