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

Generic Architecture for Caching Table Data: Supercharge Your PL/SQL Applications

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



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.

>>> 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: