Re: Simple Idea to cache return values from procedures on Oracle Standard edition

From: Lothar Flatz <l.flatz_at_bluewin.ch>
Date: Thu, 26 Nov 2015 17:49:32 +0100
Message-ID: <5657381C.1020403_at_bluewin.ch>

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

-- 




--
http://www.freelists.org/webpage/oracle-l

Received on Thu Nov 26 2015 - 17:49:32 CET