Is it possible for Oracle to reuse the result of a function when it is called in the same query (transaction?) without the use of the function result cache?
The application I am working with is heavily reliant on Oracle functions. Many queries end up executing the exact same functions multiple times.
A typical example would be:
SELECT my_package.my_function(my_id), my_package.my_function(my_id) / 24, my_package.function_also_calling_my_function(my_id) FROM my_table WHERE my_table.id = my_id;
I have noticed that Oracle always executes each of these functions, not realizing that the same function was called just a second ago in the same query. It is possible that some elements in the function get cached, resulting in a slightly faster return. This is not relevant to my question as I want to avoid the entire second or third execution.
Assume that the functions are fairly resource-consuming and that these functions may call more functions, basing their result on tables that are reasonably large and with frequent updates (a million records, updates with say 1000 updates per hour). For this reason it is not possible to use Oracle’s Function Result Cache.
Even though the data is changing frequently, I expect the result of these functions to be the same when they are called from the same query.
Is it possible for Oracle to reuse the result of these functions and how? I am using Oracle11g and Oracle12c.
Below is an example (just a random non-sense function to illustrate the problem):
-- Takes 200 ms SELECT test_package.testSpeed('STANDARD', 'REGEXP_COUNT') FROM dual; -- Takes 400ms SELECT test_package.testSpeed('STANDARD', 'REGEXP_COUNT') , test_package.testSpeed('STANDARD', 'REGEXP_COUNT') FROM dual;
CREATE OR REPLACE PACKAGE test_package IS FUNCTION testSpeed (p_package_name VARCHAR2, p_object_name VARCHAR2) RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY test_package IS FUNCTION testSpeed (p_package_name VARCHAR2, p_object_name VARCHAR2) RETURN NUMBER IS ln_total NUMBER; BEGIN SELECT SUM(position) INTO ln_total FROM all_arguments WHERE package_name = 'STANDARD' AND object_name = 'REGEXP_COUNT'; RETURN ln_total; END testSpeed; END; /