Home arrow Oracle arrow Page 4 - Generic Architecture for Caching Table Data: Supercharge Your PL/SQL Applications

Retrieving department data - Oracle

In this series I am going to introduce you to a generic PL/SQL caching architecture. The goal is to give you an understanding of how you can store Table data in PL/SQL collections (“the cache”) and how to retrieve data from those same PL/SQL collections. The proposed architecture will be generic enough for you to use throughout your applications everywhere you need to retrieve data from the database (and isn’t that what PL/SQL applications are all about?).

  1. Generic Architecture for Caching Table Data: Supercharge Your PL/SQL Applications
  2. Why a Caching Architecture?
  3. An Example
  4. Retrieving department data
  5. Conclusion
By: Mark Vilrokx
Rating: starstarstarstarstar / 12
October 11, 2005

print this article



With these three procedures in place, the logic for retrieving department data becomes very simple really:


  • Read the department data from the cache

§        If it was not found in the cache, query it from the database

§        If it was found in the database, write it to the cache.

   FUNCTION dept_data (p_dept_id IN dept.deptno%TYPE)
      RETURN department_tp
      l_dept_data   department_tp;
      l_dept_data := read_from_cache (p_dept_id);
      IF (l_dept_data.NAME IS NULL)
         l_dept_data := read_from_db (p_dept_id);
         IF (l_dept_data.NAME IS NOT NULL)
            write_to_cache (p_dept_id, l_dept_data);
         END IF;
      END IF;
      RETURN l_dept_data;
   END dept_data;

Now wrap all these functions and procedures in a package body, call it dept_cache and expose all the types and the dept_data function (by putting them in the package header).  There is no need to expose any of the other procedures.

Now, if you want to get department data, you just call the dept_data function.

      l_dept_data   dept_cache.department_tp;
      l_dept_data := dept_cache.dept_data ('10');

Notice how the whole caching logic is completely transparent for the users of your package.  Nobody knows that it is using a cache (and neither should they have to know).

When you get your collection of employee data you can now simply loop over the data and invoke dept_cache.dept_data passing in the DEPTNO of the record you are currently processing, e.g.:

      l_dept_data   dept_cache.department_tp;
      FOR emp_rec IN (SELECT *
                        FROM emp)
         l_dept_data := dept_cache.dept_data (emp_rec.deptno);
         DBMS_OUTPUT.put_line (   emp_rec.ename
                               || ' works in the '
                               || INITCAP (l_dept_data.NAME)
                               || ' department which is located
in '
                               || INITCAP (l_dept_data.LOCATION)

In order to find out how much of a performance gain this caching is giving us I conducted a little test.  We will query 200.000 records using the read_from_db function (note that you will have to make the read_from_db function temporarily public to be able to run the test).  Then we will do the same test using the dept_data function.  Here is what I get on my Test DB:

   Timings when reading 200000 records from the DB = 2132 ms
   Timings when reading 200000 records, using the cache = 238 ms

These results will obviously vary, depending on your DB (version, setup…) and the volume of data in your department table, but typically you will see a huge increase in speed when caching your data.

>>> More Oracle Articles          >>> More By Mark Vilrokx

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: