Generic Architecture for Caching Table Data: Supercharge Your PL/SQL Applications - Conclusion
(Page 5 of 5 )
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
