Multilevel Collections

Overview

There are two schema-level collection prototypes: VARRAY and (nested) TABLE. Both define one-dimensional ordered arrays of elements of a specified type, and can be leveraged in the creation of user-defined schema-level types thus…

create type Arr_t is varray(255) of number;
/

…or…

create type Tab_t is table of varchar2(2000);

/

If appropriate, the element type can be an object type thus…

create type Obj_t is object ( a number, b varchar2(4000), c date );
/

Instances of schema-level types based on VARRAY or TABLE can be stored as fields of a column in a relational database table thus…

create type Arr_t is varray(255) of Obj_t;

/
create table t (id number, arr Arr_t);
/
insert into t ( id, arr ) values
  ( 1, Arr_t ( Obj_t ( 1, 'one', '1-Jan-01' ), Obj_t ( 2, 'two', '2-Jan-01' ) ) );
insert into t ( id, arr ) values
  ( 5, Arr_t ( Obj_t ( 5, 'five', '5-Jan-01' ), Obj_t ( 6, 'six', '6-Jan-01' ) ) );

The main differences between VARRAY and TABLE are:

• TABLE is unordered but VARRAY preserves order
• VARRAY has a defined upper bound whereas the TABLE is unbounded.
• A VARRAY field is stored inline for small sizes (<= 4000 bytes) and in an opaque system-managed LOB within the given relational table for larger sizes, while a TABLE field is stored as several rows in a separate opaque system managed relational table.

This impacts the efficiency of access, leading to a generically familiar trade-off: non-negotiable maximum collection size with faster access versus unlimited collection size with slower access.

PL/SQL allows variables of user-defined types and provides mechanisms for passing data stored in schema-level collections to and from the corresponding PL/SQL structures thus…

declare
  cursor c is select id, arr from t;

  v_id number;
  v_arr Arr_t;
begin
  open c;
  loop
    fetch c into v_id, v_arr; exit when c%notfound;
    Dbms_Output.Put_Line ( v_id );
    for j in v_arr.first..v_arr.last
    loop
      Dbms_Output.Put_Line ( v_arr(j).a, v_arr(j).b, v_arr(j).c );
    end loop;

  end loop;
  close c;
end;
/

PL/SQL also allows types based on VARRAY or TABLE to be declared within library units. This will typically be in a package for reuse across several library units. In addition, PL/SQL allows the index-by variant of TABLE. (This variant is not allowed as the basis of a schema-level type.) All the above is supported pre-Oracle9i.

Oracle9i Enhancements

• Collections of Collections - New in Orcale9i
• "Runner's Training Logs" Example Scenario

Business Benefits of Multilevel Collections

Storing data as collection instances in a column of a database table is a pre-optimization to favor certain access paths (typically accessing all the elements of the collection for each selected row). PL/SQL is needed to populate and query such collection instances. Modeling data as a collection in a PL/SQL program is essential for the implementation of certain algorithms (see for example the perfect triangles algorithm in code sample ). A collection can be used as the target of a bulk bind improve the performance of data transfer between the database and the PL/SQL processing.

• Previously only one-dimensional phenomena could leverage the above benefits. Multilevel collections now offer them for an arbitrarily enlarged set of real-world problems.