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

From: Juan Carlos Reyes Pacheco <jcdrpllist_at_gmail.com>
Date: Thu, 26 Nov 2015 17:15:28 -0400
Message-ID: <CAGYrQyugANb0k-LnVb5g6XKcXHtYv5EBiLh3A_W1wHxz1bx5wg_at_mail.gmail.com>

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_at_gmail.com>:

> 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 - 22:15:28 CET