Associative Arrays


If you have ever used Perl you will familiar with the term 'associative array'. If you're not a Perl user, then think of them as the ability to index an array with something other than a numeric datatype. Since 7.2, Oracle has always hinted that this might be coming with PL/SQL with the "INDEX BY BINARY_INTEGER" option on PL/SQL tables. We now have the additional option of "INDEX BY VARCHAR2(n)".

Funtionally they appear to work fine, although they are a little slower than the binary integer equivalents.

Example:

SQL> set serverout on
SQL> declare
  2   type vtab is table of number
  3     index by varchar2(100);
  4   p ntab;
  5   type ntab is table of number
  6     index by binary_integer;
  7   p1 vtab;
  8   q number;
  9  begin
 10   q := dbms_utility.get_time;
 11   for i in 1 .. 100000 loop
 12     p(i) := i;
 13   end loop;
 14   dbms_output.put_line(dbms_utility.get_time-q);
 15   q := dbms_utility.get_time;
 16   for i in 1 .. 100000 loop
 17     p1('STUFF'||i) := i;
 18   end loop;
 19   dbms_output.put_line(dbms_utility.get_time-q);
 20  end;
 21  /
113
144

PL/SQL procedure successfully completed.

SQL> /
105   <=== conventional indexing
132   <=== varchar indexing

PL/SQL procedure successfully completed.

SQL> /
105
131

PL/SQL procedure successfully completed.