HomeOracle 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?).
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 AS l_dept_data department_tp; BEGIN l_dept_data := read_from_cache (p_dept_id); IF (l_dept_data.NAME IS NULL) THEN l_dept_data := read_from_db (p_dept_id); IF (l_dept_data.NAME IS NOT NULL) THEN 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.
DECLARE l_dept_data dept_cache.department_tp; BEGIN l_dept_data := dept_cache.dept_data ('10'); END;
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.:
DECLARE l_dept_data dept_cache.department_tp; BEGIN FOR emp_rec IN (SELECT * FROM emp) LOOP 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) ); ENDLOOP; END;
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.