type my_tab_t is table of number index by binary_integer;
Version 9.2.0 introduces two new possibilities...
type my_tab_t is table of number index by pls_integer; type my_tab_t is table of number index by varchar2(4000);
The datatype for of is not remarkable in the above, but the datatype for index by is. The size of the varchar2 may be anything up to the legal limit of 32767.
Note: A declaration other than with index by pls_integer or index by varchar2(n) still fails, thus...
PLS-00315: Implementation restriction: unsupported table index type
...so if, say, it is required to index by date, then a To_Char conversion must be used.
The new ability for index by pls_integer removes the need to use the older binary_integer in any new coding exercise.
The new ability for index by varchar2 allows many new exciting coding possibilities, and is responsible for the name change for the feature to associative arrays, also in keeping with general terminology use when discussing 3GLs.
For example, index-by tables are often used to cache table values to give a performance boost in special situations, especially where very frequent lookup is called for. It has previously been necessary to code this explictly. This comparison shows how an index by varchar2 table simplifies the coding. And not only is the coding simpler, it's more efficient!
For completeness, this comparison allows you to time populating an index by varchar2 table with one million tuples, and to time populating a database table with the same data. The PL/SQL table is about 20 times faster. It also shows the paradigm for stepping through the elements of an index by varchar2 table...
idx := the_table.First; while idx is not null loop -- do something idx := the_table.Next(idx); end loop;This scenario provides an illustration of the power of an index by varchar2 table to deliver a uniquely elegant and efficient solution.
Bryn Llewellyn, PL/SQL Product Manager, Oracle Corp
last updated 13-May-2002