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

Without knowing how complex and wide ranging the values upon which your function cache values are, it is difficult to make a prescription, so pay attention to these IFs:

IF the number of objects and columns on which your function return value depends is modest and IF a given object.column value from this set of dependencies is unique (even though used in a complex and possibly time consuming calculation), THEN you could create a user_identifier, value pair table such that a trigger on any of the object.column values on which you depend either nullifies or causes the recalculation of the value. Then when your complex function runs, if the value is not null you just fetch it and if the value is null (or there is not entry for the user_identifier, I suppose) then you run your full complexity, both returning the value and depositing the pair in the lookup table.

Your mileage may vary and there may well be better solutions.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lothar Flatz Sent: Thursday, November 26, 2015 11:50 AM To: oracle-l_at_freelists.org
Subject: Re: Simple Idea to cache return values from procedures on Oracle Standard edition

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


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