11.6.5 Sequential and Nonsequential Associative Arrays

Associative arrays can be sparse; there is no requirement that you fill and use row 1, then row 2, and so on. This means that you generally have a choice when populating and using an associative array:

Fill it sequentially

Starting from row 0 or 1 (or whatever is appropriate for your code), you add new values to the collection by appending them to the end of the collection. This technique is valuable when the order in which items are added to the list is important.

Fill it non-sequentially

In this case, you use the row number as some kind of "intelligent key," usually the primary key values from a database table. The ability torandomly (i.e., nonsequentially) place values in a table can come in very handy when the primary key value for the table's row is not sequentially derived, but is instead based on data in your application.

Let's look at an example of each of these approaches.

11.6.5.1 Sequential usage

I want to replace Oracle's TO_DATE function with my own better function. What would make it better? I would like to be able to convert virtually any date represented as a string into a real date, regardless of the format of that string—and without having to make the user provide or hardcode the format.

Here's my idea: I will populate a collection with a variety of date format masks. Then my_to_date will try to convert the string using each of the masks in turn until one of them works. You can see the straightforward code for this function in the following example. In this case, the order in which I place the format masks in my collection affects how the strings are converted. In other words, a string like 04-02-01 can be interpreted as MM-DD-RR or DD-MM-RR. The order of masks in the array decides the matter.

/* File on web: mytodate.sf */

CREATE OR REPLACE FUNCTION my_to_date (value_in IN VARCHAR2)

RETURN DATE

IS

TYPE mask_t IS TABLE OF VARCHAR2 (30) INDEX BY BINARY_INTEGER;

fmts mask_t;

retval DATE := NULL;

mask_index INTEGER := 1;

date_converted BOOLEAN := FALSE;

PROCEDURE init_fmts

IS

BEGIN

fmts (1) := 'DD-MON-RR';

fmts (2) := 'DD-MON-YYYY';

fmts (3) := 'DD-MON';

fmts (4) := 'MM/DD';

...

END;

BEGIN

init_fmts;

WHILE mask_index IS NOT NULL AND NOT date_converted

LOOP

BEGIN

retval := TO_DATE (value_in, fmts (mask_index));

date_converted := TRUE;

EXCEPTION

WHEN OTHERS THEN

mask_index := fmts.NEXT (mask_index);

IF mask_index IS NULL THEN RAISE; END IF;

END;

END LOOP;

RETURN retval;

END my_to_date;

11.6.5.2 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 IS

10 return_value hairstyles.description%TYPE;

11

12 FUNCTION desc_from_database RETURN hairstyles.description%TYPE

13 IS

14 CURSOR desc_cur IS

15 SELECT description FROM hairstyles WHERE code = code_in;

16 desc_rec desc_cur%ROWTYPE;

17 BEGIN

18 OPEN desc_cur;

19 FETCH desc_cur INTO desc_rec;

20 RETURN desc_rec.description;

21 END;

22 BEGIN

23 RETURN descriptions (code_in);

24 EXCEPTION

25 WHEN NO_DATA_FOUND THEN

26 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.
23	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.