HomeOracle Page 5 - Generic Architecture for Caching Table Data: Supercharge Your PL/SQL Applications
Conclusion - 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?).
So far we have implemented only the very basic parts of the generic Caching Architecture, yet you should have an understanding of how caching can help you in building more performant applications.
Here are the steps we have implemented thus far:
First of all, you have to design your cache. It basically needs to be able to hold the data that you need to query from the database, which in turn is the data that the user is requesting. In our case, the user is requesting the department name and location, so we have to write a query that retrieves the department name and location, and we create our cache based on that. Whichever attribute the user is using to identify the data he needs, i.e. the where clause of your query, that attribute needs to become the index of your cache.
Note that from Oracle 9iR2 onwards, this index does not have to be a numeric value anymore, it can just as well be a VARCHAR2 -- which is just as well, because the department identifier is actually a VARCHAR2 field in the dept table, so you might want to use a VARCHAR2 instead as an index. The only reason I got away with using a binary_integer is because all the identifiers in my DEPT table are numbers, so they get implicitly converted for me without a problem.
Once you have your cache, you need to create three private procedures, one to write to the cache, one to read from the cache, and one that queries the DB. Finally you create a public function using all of the above procedures to manage the data flow as defined above and serve up the data to your users:
1. Create a cache
2. Create a read, write and query procedure
3. Create a data retrieval function
In the next article, we will expand the functionality of our cache.