You can use materialized views on Standard Edition as well - including fast
refresh on commit.
If your data modification is not time-critical and you can handle the
overhead introduced by the fast refresh (and assuming the "complex" query
can be written or split up in a way that is fast-refreshable) this may be a
solution that doesn't compromise data consistency. The one thing you can't
do is query rewrite, as that's an EE feature. So you'd have to write your
application queries specifically to use the MVIEE instead.
Stefan
On Thu, Nov 26, 2015 at 10:40 PM, Juan Carlos Reyes Pacheco <
jcdrpllist@xxxxxxxxx> 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 :)
