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 :)
