Nonsequential usage

In many applications, we find ourselves writing and executing the same queries over and over again. In some cases, the queries are retrieving static data, such as codes and descriptions that rarely (if ever) change. Well, if the data isn't changing—especially during a user session—then why would I need to keep querying the information from the database? Even if the data is cached in the System Global Area (SGA), I still need a network roundtrip to find that information in the data buffers and return it to the session program area (the Program Global Area, or PGA).

Here's an idea: set as a rule that for a given static lookup table, a user will never query a row from the table more than once in a session. After the first time, it will be stored in the session's PGA and be instantly available for future requests. This is very easy to do with collections. Essentially, you use the collection's index as an intelligent key.

Let's take a look at an example. I have a hairstyles table that contains a numeric code (primary key) and a description of the hairstyle (e.g., "Pageboy", "Shag"). These styles are timeless and rarely change.

Here is the body of a package that utilizes a collection to cache code-hairstyle pairs and minimizes trips to the database.

/* File on web: justonce.sql */ 1 CREATE OR REPLACE PACKAGE BODY justonce 2 IS 3 TYPE desc_t IS TABLE OF hairstyles.description%TYPE 4 INDEX BY BINARY_INTEGER; 5 descriptions desc_t; 6 7 FUNCTION description (code_in IN hairstyles.code%TYPE) 8 RETURN hairstyles.description%TYPE 9 IS10 return_value hairstyles.description%TYPE;11 12 FUNCTION desc_from_database RETURN hairstyles.description%TYPE13 IS14 CURSOR desc_cur IS15 SELECT description FROM hairstyles WHERE code = code_in;16 desc_rec desc_cur%ROWTYPE;17 BEGIN18 OPEN desc_cur;19 FETCH desc_cur INTO desc_rec;20 RETURN desc_rec.description;21 END;22 BEGIN23 RETURN descriptions (code_in);24 EXCEPTION25 WHEN NO_DATA_FOUND THEN26 descriptions (code_in) := desc_from_database;27 RETURN descriptions (code_in);28 END;29 END justonce;

The table provides a description of the interesting aspects of this program:

Line(s)	Description
3-5	Declare a collection type and the collection to hold my cached descriptions.
7-8	Header of my retrieval function. The interesting thing about the header is that it is not interesting at all. There is no indication that this function is doing anything but the typical query against the database to retrieve the description for the code. The implementation is hidden, which is just the way you want it.
12-21	That very traditional query from the database. But in this case it is just a private function within my main function, which is fitting becasue it is not the main attraction.
	The entire execution section! Simply return the description that is stored in the row indicated by the code number. The first time I run this function for a given code, the row will not be defined. So PL/SQL raises NO_DATA_FOUND (see lines 25-27). For all subsequent requests for this code, however, the row is defined and the function returns the value immediately.
25-27	So the data hasn't yet been queried in this session. Fine. Trap the error, look up the description from the database, and deposit it in the collection. Then return that value. Now we are set to divert all subsequent lookup attempts.

So how much of a difference does this caching make? Using the local Oracle9i database on my laptop, I ran some tests and found that it took just under 2 seconds to execute 10,000 queries against the hairstyles table. That's pretty darned fast. Yet it took only .1 second to retrieve that same information 10,000 times using the above function. That's more than an order of magnitude improvement—and that's with a local database. The superiority of the collection caching technique would be even greater in a real-world situation.

Here are some final notes on the collection caching technique:

· This technique is a classic tradeoff between CPU and memory. Each session has its own copy of the collection (this is program data and is stored in the PGA). If you have 10,000 users, the total memory required for these 10,000 small caches could be considerable. You should use this approach only with small, static tables.

· Your static table should have an integer primary key to be used as the row in the collection. If the primary key is concatenated or a string, you could use the DBMS_UTILITY.GET_HASH_VALUE function to produce a hashed value usable as a collection index value. The resulting complexity and overhead would, however, make the technique less attractive.

You can learn more about data caching and its applicability to your requirements in Chapter 17 and Chapter 20.