« in response to: Need … | Home |

Hoops to jump through if using Oracle 10g (instead of Postgres 6+)

I've been using PostgreSQL for years, and I've often made use of cacheing of functions.

Last year I discovered - working on an Oracle 10g environment, that any sort of caching of functinon results does not exist.  (It does for 11, though)  This totally blew me away, and lead to the following soluction.  PostgreSQL people may enjoy reading what they can happily avoid... 

The solution utilises oracle Packages.  These are assembles of functions and procedures that stay in memory for a time after first being called.  The packages can contain member private variables - and these are what we use to cache the results.

The result of doing this calculation in a cached function was a halving of run-time.

CREATE OR REPLACE PACKAGE         SCHEMA.etl_utils IS
       FUNCTION cache_clear RETURN INTEGER;
       FUNCTION keyword_text_clean(i_rawtext IN VARCHAR2 ) RETURN VARCHAR2;
END etl_utils;

create or replace PACKAGE BODY         SCHEMA.etl_utils IS
    TYPE assoc_array_str_type IS TABLE OF VARCHAR2(200) INDEX BY VARCHAR2(200);
    TYPE array_num_type IS TABLE OF number INDEX BY pls_integer;   
    TYPE assoc_array_nums_type IS TABLE OF array_num_type INDEX BY VARCHAR2(200);
   
    assoc_array_str assoc_array_str_type;
   
    assoc_array_nums assoc_array_nums_type;

    FUNCTION cache_clear RETURN INTEGER
    AS
    BEGIN
         assoc_array_str.DELETE;
         assoc_array_nums.DELETE;
         RETURN 1;
    END;

    FUNCTION keyword_text_clean(i_rawtext IN VARCHAR2 ) RETURN VARCHAR2
    AS
      l_out VARCHAR2(200);
      l_out_upper VARCHAR2(200);
      l_key VARCHAR2(200);
      l_found BOOLEAN := TRUE; -- set to TRUE to enable caching
    BEGIN
        l_key := trim(i_rawtext);
        BEGIN
              l_out := assoc_array_str( l_key );
        EXCEPTION
                WHEN OTHERS THEN
                     l_found := FALSE;
         END;

         --sometimes short text comes back NULL erroneously
         --this means we can't rely on looking up the keys that should resovle to null
         --pain but makes only a small difference
         IF l_out IS NULL THEN
                  l_found := FALSE;
         END IF;

--do calculation for value and store in array-cache.
         IF NOT l_found THEN

              l_out := .....

              assoc_array_str( l_key ) := l_out;
         END IF;
         RETURN l_out;
    END;

http://frakkle.com

one comment:

Please show me an example of of Postgres cached function. I know that the function plan can be cached, but please help me with cached results for a function.
Thanks
BobMN - 25 09 08 - 15:12

one trackback:

Log Buffer #116: A Carnival of the Vanities for DBAs
Welcome to the 115th edition of Log Buffer, the weekly review of database blogs.
This was the week of Oracle Open World (OOW), Oracle’s gigantic annual get-together in San Francisco — always the heaviest week in Oracle blogs, so let’s…
Sent on 26 09 08 - 12:45 , via Pythian Group Blog

Trackback link: