Thank you for your advice :) ,
it's only an idea, for some specific situations when there is no other
solution, not generic.
A big problem is SQL Plan Managementis only avaible on enterprise.
Anyway I can't skip the error you can't execute inserts inside a query.
and I have to execute a job or a loop before the select.
2015-11-26 11:40 GMT-04:00 Juan Carlos Reyes Pacheco <jcdrpllist@xxxxxxxxx>:
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 :)
