Example 1. Example of VARCHAR2-indexed collection.

 1  DECLARE
 2     idx VARCHAR2(64);
 3     TYPE population_type IS TABLE OF NUMBER INDEX BY idx%TYPE;
 4
 5     country_population population_type;
 6     continent_population population_type;
 7
 8     howmany PLS_INTEGER;
 9  BEGIN
10     country_population('Norway') := 4000000;
11     country_population('Greenland') := 100000;
12     country_population('Iceland') := 750000;
13
14     continent_population('Australia') := 30000000;
15
16     continent_population('Antarctica') := 1000;
17
18     continent_population('Antarctica') := 1001;
19
20     howmany := country_population.COUNT;
21     DBMS_OUTPUT.PUT_LINE ('COUNT = ' || howmany);
22
23     idx := continent_population.FIRST;
24     DBMS_OUTPUT.PUT_LINE ('FIRST row = ' || idx);
25     DBMS_OUTPUT.PUT_LINE (
26        'FIRST value = ' || continent_population(idx));
27
28     idx := continent_population.LAST;
29     DBMS_OUTPUT.PUT_LINE ('LAST row = ' || idx);
30     DBMS_OUTPUT.PUT_LINE (
31        'LAST value = ' || continent_population(idx));
32
33    idx := country_population.FIRST;
34    WHILE idx IS NOT NULL
35    LOOP
36      DBMS_OUTPUT.PUT_LINE ( idx || ' = ' || country_population(idx) );
37      idx := country_population.NEXT(idx);
38    END LOOP;
39  END;

Here's the output one would see in SQL*Plus when running this script with SERVEROUTPUT turned ON:

COUNT = 3
FIRST row = Antarctica
FIRST value = 1001
LAST row = Australia
LAST value = 30000000
Greenland = 100000
Iceland = 750000
Norway = 4000000

Using VARCHAR2-Indexed Collections

So why would a developer care about the fact that you can now index by VARCHAR2 in addition to PLS_INTEGER? First of all, in general, you'll want to take advantage of associative arrays when you need to maintain any sort of lists of data in your PL/SQL programs. Sure, you can use relational tables to manage lists, but they involve much more programming and CPU overhead. The code you write for associative arrays is lean and mean.

The following scenarios generally indicate a need for collections:

• Repeated access to the same, static database information. If, during execution of your program (or during a session, since your collection can be declared as package data and thereby persist with all its rows for the entire session), you need to read the same data more than once, load it into a collection. Multiple scannings of the collection will be much more efficient than multiple executions of a SQL query.

• Management of program-only lists. You may build and manipulate lists of data that exist only within your program, never touching a database table. In this case, collections-and, specifically, associative arrays-will be the way to go.

Let's now look at a specific scenario in which a VARCHAR2-indexed array would be ideal. The requirement to look up a value via a unique non-numeric key is a generic computational problem. Of course, the Oracle 9i Database provides a solution with SQL and a B*-tree index. But there's a set of scenarios where considerable performance improvement can be obtained by instead using an explicit PL/SQL implementation. This was true even before the new features discussed in this article were available. These scenarios are characterized by very frequent lookups in a relatively small set of values, usually in connection with flattening a relational representation for reporting or for UI presentation.

For this article, we'll work with an English-French vocabulary and translation mechanism. Suppose we have a set of English-French vocabulary pairs stored persistently in the most obvious way in a schema-level table:

-- translations.sql
CREATE TABLE translations (
   english varchar2(200),
   french varchar2(200));

and we have data in the table as follows (populated by the translations.sql file):

SELECT * FROM translations;

ENGLISH              FRENCH
-------------------- ----------
computer             ordinateur
tree                 arbre
book                 livre
cabbage              chou
country              pays
vehicle              voiture
garlic               ail
apple                pomme
desk                 ‚scritoire
furniture            meubles

Our task is to allow lookup from French to English, and to allow efficient addition of new vocabulary pairs. We'll immediately turn to the package construct to provide a clean interface to this functionality, as shown in Example 2.

Example 2. The vocab package interface to the French-English translation engine.

CREATE OR REPLACE PACKAGE vocab
IS 
   FUNCTION lookup (p_english IN VARCHAR2)
      RETURN VARCHAR2;

   PROCEDURE new_pair (
      p_english IN VARCHAR2, p_french IN VARCHAR2);
END vocab1;
/

The vocab.new_pair procedure performs a straightforward insert into the table:

PROCEDURE new_pair (
   p_english IN VARCHAR2, 
   p_french IN VARCHAR2)
IS
BEGIN
   INSERT INTO translations
      (english, french)
   VALUES (p_english, p_french);
END new_pair;

This vocabulary is, furthermore, static during the user's session (that is, during the time when the user application accesses the translation table, no changes are being made to this table). Our challenge then becomes: What's the most efficient way to implement the lookup procedure?

We certainly have a wide set of choices, including:

• Pure SQL approach: Simply query the English word for the French each time it's needed.

• Full collection scan, a.k.a. "linear search": Use the "traditional" INDEX BY BINARY_INTEGER collection to cache all the French-English pairs. Search the entire collection for a match each time a lookup is needed.

• Hash-based indexing: Build our own VARCHAR2-based index using Oracle's hashing algorithm.

• VARCHAR2-indexed associative array: Cache all French-English pairs using the French word as the key, allowing direct lookup of the English word, all within PL/SQL.

In the following sections, we'll examine each of these approaches, implemented in distinct packages (vocab1 through vocab4). Then we'll execute a test script that compares the performance of these implementations.