associative arrays in oracle 9i release 2
Arrays have been available in PL/SQL since its very early versions, when Oracle called them "PL/SQL Tables". Of course, they behave nothing like a table because they are essentially an array structure, certainly in terms of how we interact with them. Somewhere around Oracle 8, PL/SQL Tables were re-badged as "index-by tables", which referred to the syntax we used to declare the array types with (TYPE array IS TABLE OF datatype INDEX BY BINARY INTEGER). This rename was probably because Oracle added "collections", which referred to the then-new nested table and varray SQL types.
With the release of 9.2 (9i Release 2), Oracle has re-badged index-by tables as "associative arrays" and it is these that this article will focus on. They are still PL/SQL-only arrays as we know them, with the difference being that we can now index them in three ways instead of one.
indexing associative arrays
Associative arrays in Oracle 9.2 can be indexed by any of the following types:
- BINARY_INTEGER;
- PLS_INTEGER; and
- VARCHAR2.
PLS_INTEGER is a supposedly faster or equivalent implementation of BINARY_INTEGER but the last of these indexing methods is more likely to draw our attention. For the first time, we can index arrays by strings in PL/SQL. This simple fact makes arrays in PL/SQL far more flexible than in previous versions with more practical use for our program data.
index by integer
Indexing PL/SQL arrays by integer is as old as the technology itself, so we shall not spend much time on this. The following example performs a simple timing comparison of loading and accessing two arrays: one indexed by BINARY_INTEGER and the other by PLS_INTEGER.
SQL> DECLARE 2 3 TYPE aat_binary IS TABLE OF VARCHAR2(10) 4 INDEX BY BINARY_INTEGER; 5 aa_binary aat_binary; 6 7 TYPE aat_pls IS TABLE OF VARCHAR2(10) 8 INDEX BY PLS_INTEGER; 9 aa_pls aat_pls; 10 11 v_dummy VARCHAR2(10); 12 13 BEGIN 14 15 timer.snap; 16 FOR i IN 1 .. 1000000 LOOP 17 aa_binary(i) := RPAD('X',10); 18 END LOOP; 19 timer.show('Load BINARY'); 20 21 timer.snap; 22 FOR i IN 1 .. 1000000 LOOP 23 aa_pls(i) := RPAD('X',10); 24 END LOOP; 25 timer.show('Load PLS'); 26 27 timer.snap; 28 FOR i IN 1 .. 1000000 LOOP 29 v_dummy := aa_binary(i); 30 END LOOP; 31 timer.show('Access BINARY'); 32 33 timer.snap; 34 FOR i IN 1 .. 1000000 LOOP 35 v_dummy := aa_pls(i); 36 END LOOP; 37 timer.show('Access PLS'); 38 39 END; 40 /
[Load BINARY] 1.56 seconds [Load PLS] 1.81 seconds [Access BINARY] 0.70 seconds [Access PLS] 0.71 seconds PL/SQL procedure successfully completed.
The timings show that there is very little difference between the two indexing types so we should not be too concerned about converting all of our BINARY_INTEGER arrays to PLS_INTEGER!
index by string
Prior to the introduction of bulk fetching and binding in Oracle 8i (that requires arrays or collections to be used), PL/SQL developers generally made little use of arrays. Since then, we have become far more comfortable with using these structures, both for array processing and for small sets of program or reference data. String-indexed associative arrays provide us with more possibilities for array-usage in PL/SQL. Before we see some examples of their flexibility, we will see how to assign and use these structures.
In the following example, we will assign a small associative array with some hard-coded literal index values. We will also see how we define the type in our declaration. The index can be VARCHAR2 or a subtype of VARCHAR2, up to the usual PL/SQL limit of 32767 bytes. The CHAR type cannot be used.
SQL> DECLARE 2 3 TYPE aat_days_in_month IS TABLE OF NUMBER 4 INDEX BY VARCHAR2(30); 5 6 aa_days aat_days_in_month; 7 8 BEGIN 9 10 /* Literal assignments... */ 11 aa_days('JANUARY') := 31; 12 aa_days('FEBRUARY') := EXTRACT(DAY FROM LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),1))); 13 -- and so on -- 14 aa_days('DECEMBER') := 31; 15 16 /* Literal access... */ 17 DBMS_OUTPUT.PUT_LINE( 18 'There are ' || aa_days('FEBRUARY') || ' days in February this year.' 19 ); 20 21 END; 22 /
There are 28 days in February this year. PL/SQL procedure successfully completed.
Note that the array will be organised according to the alphabetical order of the index values, although the array is not contiguous as such. This means we cannot use FOR LOOP or FORALL with string-indexed associative arrays. Instead, we step though the array using the NEXT (or PRIOR if going in reverse) pseudo-methods as follows.
SQL> DECLARE 2 3 TYPE aat_days_in_month IS TABLE OF NUMBER 4 INDEX BY VARCHAR2(30); 5 6 aa_days aat_days_in_month; 7 8 v_start DATE; 9 v_indx VARCHAR2(30); 10 v_days NUMBER; 11 12 BEGIN 13 14 /* Looping assignment... */ 15 FOR i IN 0 .. 11 LOOP 16 17 v_start := ADD_MONTHS(TRUNC(SYSDATE,'YEAR'),i); 18 v_indx := TRIM(TO_CHAR(v_start,'MONTH')); 19 v_days := EXTRACT(DAY FROM LAST_DAY(v_start)); 20 21 aa_days(v_indx) := v_days; 22 23 END LOOP; 24 25 /* Looping access... */ 26 v_indx := aa_days.FIRST; 27 WHILE v_indx IS NOT NULL LOOP 28 DBMS_OUTPUT.PUT_LINE( 29 'Days in ' || v_indx || ' = ' || aa_days(v_indx) 30 ); 31 v_indx := aa_days.NEXT(v_indx); 32 END LOOP; 33 34 END; 35 /
Days in APRIL = 30 Days in AUGUST = 31 Days in DECEMBER = 31 Days in FEBRUARY = 28 Days in JANUARY = 31 Days in JULY = 31 Days in JUNE = 30 Days in MARCH = 31 Days in MAY = 31 Days in NOVEMBER = 30 Days in OCTOBER = 31 Days in SEPTEMBER = 30 PL/SQL procedure successfully completed.
We can see that the elements are returned in index-order, rather than the order in which we assigned them. We cannot change this behaviour without intervening ourselves (for example, we could prepend each month with its month number from 01 to 12) or we could maintain a separate ordering "index". But generally we do not require string-indexed associative arrays to maintain any specific order.
caching reference data with associative arrays
Many developers will read about string-indexed associative arrays and immediately see the potential for caching lookup data. This has been a common application for the older index-by tables, but these had the limitation of requiring integer-based keys to the lookups (though there were some complicated "solutions" to this using hashing of strings to get a numeric index for string keys).
We will look at how we can use string-indexed associative arrays to cache reference data and compare the lookups for performance against direct table access. Of course, it should be noted at this stage that if the reference table can be joined to the source query, then this will be preferable. Cached lookups, using associative arrays, tend to be a useful tuning technique when the original table lookup is encapsulated in a shared function or when the reference table cannot be joined directly to the driving query.
In the following example we will create a small lookup table based on ALL_TABLES. This will have approximately 900 rows. We will then create a small package with two functions to lookup this reference table and use these to run some time comparisons. First we can create the "reference table" as follows.
SQL> CREATE TABLE lookup_table 2 CACHE 3 AS 4 SELECT owner || '.' || table_name AS primary_key 5 , DBMS_RANDOM.STRING('u',50) AS description 6 FROM all_tables;
Table created.
SQL> ALTER TABLE lookup_table ADD ( 2 CONSTRAINT lookup_table_pk 3 PRIMARY KEY (primary_key) 4 );
Table altered.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'LOOKUP_TABLE',cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT COUNT(*) FROM lookup_table;
COUNT(*) ---------- 929 1 row selected.
Our lookup functions are contained in the following package. Note that the cache-based function (using the associative array) needs to be initialised. We include the code for this in the package initialisation section, which is executed the first time the package is called.
SQL> CREATE PACKAGE lookup_pkg AS 2 3 FUNCTION table_lookup( 4 p_key IN lookup_table.primary_key%TYPE 5 ) RETURN lookup_table.description%TYPE; 6 7 FUNCTION array_lookup( 8 p_key IN lookup_table.primary_key%TYPE 9 ) RETURN lookup_table.description%TYPE; 10 11 END lookup_pkg; 12 /
Package created.
SQL> CREATE PACKAGE BODY lookup_pkg AS 2 3 TYPE aat_lookup IS TABLE OF lookup_table.description%TYPE 4 INDEX BY lookup_table.primary_key%TYPE; 5 6 ga_lookups aat_lookup; 7 8 ----------------------------------------------------------- 9 10 FUNCTION table_lookup( 11 p_key IN lookup_table.primary_key%TYPE 12 ) RETURN lookup_table.description%TYPE IS 13 14 v_return lookup_table.description%TYPE; 15 16 BEGIN 17 18 SELECT description INTO v_return 19 FROM lookup_table 20 WHERE primary_key = p_key; 21 RETURN v_return; 22 23 EXCEPTION 24 WHEN NO_DATA_FOUND THEN 25 RETURN NULL; 26 END table_lookup; 27 28 ----------------------------------------------------------- 29 30 FUNCTION array_lookup( 31 p_key IN lookup_table.primary_key%TYPE 32 ) RETURN lookup_table.description%TYPE IS 33 BEGIN 34 RETURN ga_lookups(p_key); 35 EXCEPTION 36 WHEN NO_DATA_FOUND THEN 37 RETURN NULL; 38 END array_lookup; 39 40 ----------------------------------------------------------- 41 42 BEGIN 43 44 /* Initialisation... */ 45 FOR r IN (SELECT primary_key, description FROM lookup_table) LOOP 46 ga_lookups(r.primary_key) := r.description; 47 END LOOP; 48 49 END lookup_pkg; 50 /
Package body created.
We can now run a comparison of the two functions, using an oracle-developer.net version of Tom Kyte's RUNSTATS utility. We will run the associative array example first because we want this to include the cost of pre-loading the array. We will execute each function approximately 90,000 times by "inflating" the data we use as our source for the lookup keys.
SQL> exec runstats_pkg.rs_start;
PL/SQL procedure successfully completed.
SQL> DECLARE 2 v_description lookup_table.description%TYPE; 3 BEGIN 4 FOR r IN (SELECT primary_key AS key 5 FROM lookup_table 6 , (SELECT ROWNUM FROM dual CONNECT BY ROWNUM <= 100)) 7 LOOP 8 v_description := lookup_pkg.array_lookup(r.key); 9 END LOOP; 10 END; 11 /
PL/SQL procedure successfully completed.
Now we can run the same example but using the table lookup.
SQL> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.
SQL> DECLARE 2 v_description lookup_table.description%TYPE; 3 BEGIN 4 FOR r IN (SELECT primary_key AS key 5 FROM lookup_table 6 , (SELECT ROWNUM FROM dual CONNECT BY ROWNUM <= 100)) 7 LOOP 8 v_description := lookup_pkg.table_lookup(r.key); 9 END LOOP; 10 END; 11 /
PL/SQL procedure successfully completed.
SQL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 273 hsecs Run2 ran in 844 hsecs Run1 ran in 32.35% of the time Name Run1 Run2 Diff STAT..calls to get snapshot sc 8 92,906 92,898 STAT..execute count 6 92,906 92,900 STAT..index fetch by key 0 92,900 92,900 STAT..recursive calls 93,406 186,306 92,900 STAT..rows fetched via callbac 0 92,900 92,900 STAT..table fetch by rowid 0 92,900 92,900 LATCH.library cache 557 185,990 185,433 LATCH.library cache pin 237 185,879 185,642 STAT..buffer is not pinned cou 1 185,801 185,800 LATCH.cache buffers chains 3,064 280,588 277,524 STAT..consistent gets 937 279,635 278,698 STAT..session logical reads 937 279,635 278,698 STAT..consistent gets - examin 0 278,700 278,700 Run1 latches total versus run2 -- difference and pct Run1 Run2 Diff Pct 5,740 653,197 647,457 .88% PL/SQL procedure successfully completed.
We can see that the associative array lookup is approximately three times faster in this example. We can repeat the comparison but this time in SQL (using Autotrace to limit the screen output).
SQL> set autotrace traceonly statistics SQL> exec runstats_pkg.rs_start;
PL/SQL procedure successfully completed.
SQL> SELECT lookup_pkg.array_lookup(primary_key) AS description 2 FROM lookup_table 3 , (SELECT ROWNUM FROM dual CONNECT BY ROWNUM <= 100);
92900 rows selected. Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 935 consistent gets 0 physical reads 0 redo size 1261660 bytes sent via SQL*Net to client 68622 bytes received via SQL*Net from client 6195 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 92900 rows processed
SQL> exec runstats_pkg.rs_middle;
PL/SQL procedure successfully completed.
SQL> SELECT lookup_pkg.table_lookup(primary_key) AS description 2 FROM lookup_table 3 , (SELECT ROWNUM FROM dual CONNECT BY ROWNUM <= 100);
92900 rows selected. Statistics ---------------------------------------------------------- 92900 recursive calls 0 db block gets 279635 consistent gets 0 physical reads 0 redo size 1261660 bytes sent via SQL*Net to client 68622 bytes received via SQL*Net from client 6195 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 92900 rows processed
SQL> exec runstats_pkg.rs_stop(1000);
Run1 ran in 224 hsecs Run2 ran in 836 hsecs Run1 ran in 26.79% of the time Name Run1 Run2 Diff STAT..index fetch by key 0 92,900 92,900 STAT..recursive calls 503 93,403 92,900 STAT..rows fetched via callbac 0 92,900 92,900 STAT..table fetch by rowid 0 92,900 92,900 STAT..execute count 4 92,905 92,901 STAT..calls to get snapshot sc 6 92,909 92,903 STAT..buffer is not pinned cou 1 185,802 185,801 LATCH.library cache pin 24,827 210,648 185,821 LATCH.library cache 43,434 229,269 185,835 STAT..consistent gets - examin 0 278,700 278,700 STAT..consistent gets 935 279,638 278,703 STAT..session logical reads 935 279,638 278,703 LATCH.cache buffers chains 1,870 280,588 278,718 Run1 latches total versus run2 -- difference and pct Run1 Run2 Diff Pct 82,726 733,358 650,632 11.28% PL/SQL procedure successfully completed.
In SQL, using the cached lookup is almost four times faster than repeated table access. The RUNSTATS output shows the amount of additional work done by the table lookups, whereby the time taken by the associative array access is largely accounted for by the context-switching incurred by accessing PL/SQL functions from SQL.
a final note on cached lookups
Caching lookups using associative arrays, indexed by string or integer, is an extremely useful technique for tuning PL/SQL and SQL when our business rules or reference data are encapsulated in functions. Of course, the quickest method for accessing reference data is to join to the lookup table directly in our cursors and SQL statements. The following example is a useful reminder of why we should try to use SQL as much as possible. The SQL-only solution takes less than half the time of our quickest associative array solutions.
SQL> set autotrace traceonly statistics SQL> set timing on SQL> SELECT (SELECT lt2.description 2 FROM lookup_table lt2 3 WHERE lt1.primary_key = lt2.primary_key) AS description 4 FROM lookup_table lt1 5 , (SELECT ROWNUM FROM dual CONNECT BY ROWNUM <= 100);
92900 rows selected. Elapsed: 00:00:01.00 Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3724 consistent gets 0 physical reads 0 redo size 1261660 bytes sent via SQL*Net to client 68622 bytes received via SQL*Net from client 6195 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 92900 rows processed
further reading
For more information on associative arrays, read the PL/SQL User's Guide and Reference. The RUNSTATS and TIMER packages used in the examples in this article can be downloaded from the Utilities page of this site.
source code
The source code for the examples in this article can be downloaded from here.
Adrian Billington, May 2002
Back to Top