Cache Rows In Memory
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
General PL/SQL Utilities Library
There are times when, after bouncing a database, you want certain rows brought into memory with a specific statement. This sample code demonstrates one way it can be done. If you were to want to automate this code ideally you would run it by putting the contents of the anonymous block into an AFTER STARTUP DATABASE level trigger. That said the package below demonstrates four separate ways to accomplish the goal: Only the first procedure being required for the doing so based on the use of an AFTER STARTUP trigger.

Supporting Table
CREATE TABLE cached_lookup_tab (
rid   NUMBER(10) NOT NULL,
descr VARCHAR2(50) NOT NULL);

ALTER TABLE cached_lookup_tab
ADD CONSTRAINT pk_cached_lookup_tab
PRIMARY KEY (rid)
USING INDEX;
Insert table's demo data
DECLARE
 type t_tab IS TABLE OF NUMBER;
 l_tab t_tab := t_tab();
BEGIN
  FOR i IN 1 .. 1000 LOOP
    l_tab.extend;
    l_tab(l_tab.last) := i;
  END LOOP;

  FORALL i IN l_tab.first .. l_tab.last
  INSERT INTO cached_lookup_tab
  (rid, description)
  VALUES(l_tab(i), 'Description for ' || l_tab(i));

  COMMIT;
END;
/

exec dbms_stats.gather_table_stats(USER, 'CACHED_LOOKUP_TAB', CASCADE=>TRUE);

SELECT * FROM cached_lookup_tab;
Use Defined Package
CREATE OR REPLACE PACKAGE cached_lookup_api AUTHID DEFINER IS
  PROCEDURE populate_tab;
  PROCEDURE get_cached_info(p_id cached_lookup_tab.id%TYPE, p_info OUT NOCOPY cached_lookup_tab%ROWTYPE);
  PROCEDURE get_db_info(p_id cached_lookup_tab.id%TYPE, p_info OUT NOCOPY cached_lookup_tab%ROWTYPE);
END cached_lookup_api;
/

CREATE OR REPLACE PACKAGE BODY cached_lookup_api IS
 TYPE t_tab IS TABLE OF cached_lookup_tab%ROWTYPE
 INDEX BY BINARY_INTEGER;

 g_tab t_tab;
PROCEDURE populate_tab IS
BEGIN
  FOR i IN (SELECT * FROM cached_lookup_tab) LOOP
    g_tab(i.id) := i;
  END LOOP;
END populate_tab;

PROCEDURE get_cached_info(p_id cached_lookup_tab.id%TYPE, p_info OUT NOCOPY cached_lookup_tab%ROWTYPE) IS
BEGIN
  p_info := g_tab(p_id);
END get_cached_info;

PROCEDURE get_db_info(p_id cached_lookup_tab.id%TYPE, p_info OUT NOCOPY cached_lookup_tab%ROWTYPE) IS
BEGIN
  SELECT *
  INTO p_info
  FROM cached_lookup_tab
  WHERE id = p_id;
END get_db_info;

BEGIN
  populate_tab;
END cached_lookup_api;
/
Anonymous block for demo purposes
DECLARE
 l_loops CONSTANT NUMBER := 10000;
 l_seed           BINARY_INTEGER;
 l_start          NUMBER;
 l_id             cached_lookup_tab.id%TYPE;
 l_row            cached_lookup_tab%ROWTYPE;
BEGIN
  l_seed := TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYDDMMSS'));
  dbms_random.initialize(val => l_seed);

  l_start := dbms_utility.get_time;
  FOR i IN 1 .. l_loops LOOP
    l_id := TRUNC(dbms_random.value(low => 1, high => 1000));
    cached_lookup_api.get_cached_info(p_id=>l_id, p_info=>l_row);
  END LOOP;

  dbms_output.put_line('Cached Lookup(' || l_loops || ' rows): ' || (dbms_utility.get_time - l_start));

  l_start := dbms_utility.get_time;

  FOR i IN 1 .. l_loops LOOP
    l_id := TRUNC(dbms_random.value(low=>1, high=>1000));
    cached_lookup_api.get_db_info(p_id=>l_id, p_info=>l_row);
  END LOOP;

  dbms_output.put_line('DB Lookup(' || l_loops || ' rows) : ' || (dbms_utility.get_time -l_start));

  dbms_random.terminate;
END;
/
...
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved