A common question with caching is whether the
DETERMINISTIC option or the
RESULT_CACHE is best.
As always, the answer is: ‘It depends.’
When you call a deterministic function many times from within the same SQL statement, the
RESULT_CACHE does not add much to what the
DETERMINISTIC option already covers.
Since a single SQL statement is executed from only one session, the function result cache cannot help with multi-session caching as there is nothing to share across sessions.
As we have said, marking a deterministic function as
DETERMINISTIC is a good idea in any case.
When you call a deterministic function many times from different SQL statements — in potentially different sessions or even instances of a RAC — and even PL/SQL blocks, the
RESULT_CACHE does have benefits.
Now, Oracle can access a single source of cached data across statements, subprograms, sessions, or even application cluster instances.
The ‘single source of cached data’ is of course only true for DR units.
For IR units, the function result cache is user-specific, which probably dampens your euphoria regarding the function result cache somewhat.
Nevertheless, both caching mechanisms are completely handled by Oracle Database.
All you have to do is add a simple
RESULT_CACHE to a function’s definition.