I tend to agree with the cautions on the thread so far.
Without knowing how complex and wide ranging the values upon which your
function cache values are, it is difficult to make a prescription, so pay
attention to these IFs:
IF the number of objects and columns on which your function return value
depends is modest and IF a given object.column value from this set of
dependencies is unique (even though used in a complex and possibly time
consuming calculation),
THEN you could create a user_identifier, value pair table such that a trigger
on any of the object.column values on which you depend either nullifies or
causes the recalculation of the value. Then when your complex function runs, if
the value is not null you just fetch it and if the value is null (or there is
not entry for the user_identifier, I suppose) then you run your full
complexity, both returning the value and depositing the pair in the lookup
table.
Your mileage may vary and there may well be better solutions.
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Lothar Flatz
Sent: Thursday, November 26, 2015 11:50 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Simple Idea to cache return values from procedures on Oracle
Standard edition
Generally speaking your idea seems far to dangerous to me. Time gives only an
approximate security.
I think it is better to optimize the function or work on the underlying issue
respectively.
E.g. ask yourself if that should be a function or if you could do it in sql?
Are you using the usual optimization like bulk collect?
It seems your function is not deterministic. Otherwise by declaring it
deterministic you would have a session cache.
On 26.11.2015 16:40, Juan Carlos Reyes Pacheco wrote:
Hello I got a practical idea to optimize creating a cache functions
for intensive processes, that calls several times a function, in a
short period.
But without the complexity of having to validate and guarantee the
data used to g et the data, changed.
1. The problem is the control of the modifications of values in tables
and packages, specific user variables, etc. that could change the
value a procedure returns.
2. But if we take a short period of time, and we include critical
variables like user and specific login information like year, that
could affect the result; we can create a generic solution.
original function
We have function with a complex query
schema.function_name(value)
cmoplex query
return return_value
end;
Optimizing function
rename schema.function_name(value)
to schema.function_name_cached(value)
create global temporary table schema%function_name
username
value1
return1
timestamp
new:
schema.function_name(value)
begin
select from schema%function_name where username=user and
value1=value and timestamp>sysdate-(5*60/*period*/);
when no_data_found then
return_value = schema.function_name_cached(value);
delete from schema%function_name where value
insert into schema%function_name
end;
return return_value
end;
So the idea is for every specific user a value will be stored in a
temporary table, when they call the function and it will be reused
only if it was get the last 5 minutes.
So unless this is a very dinamic query that requires to validate
modifications before that time, it's ok :)
