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