Generic Architecture for Caching Table Data: Supercharge Your PL/SQL Applications (
Page 1 of 5 )
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?).Why Caching?
The PL/SQL language was specifically created by Oracle for interacting with data in a database, yet ironically that same interaction is what makes most PL/SQL code slow. You could write a PL/SQL program containing tens of thousands of lines of code performing extremely complex arithmetic, and it would probably run quicker than a 500-line procedure that needs to query 1000 invoices from a table containing 100 million rows. It is exactly because of this inefficiency that one of the most effective performance enhancing techniques available to PL/SQL developers is caching data in PL/SQL collections.
Accessing data in the database involves (most of the time) reading data from a disk. This is inherently slow and you should try to avoid it as much as possible. Caching is a technique whereby you store data you have read from the disk previously in memory so that the next time you need to access it, you retrieve it from memory instead of from the disk. Reading data from the memory is much faster than reading data from a hard drive, so caching gives your applications a serious boost.
As you might have guessed, caching is only useful if you need to access the same data over and over again. There is no point to caching your data if you are not going to need it anymore afterwards.
