Collections

Collections - Introduction

In PL/SQL Version 2, Oracle introduced the TABLE data type as a way of storing singly dimensioned sparse arrays in PL/SQL. PL/SQL8 introduces two new collection structures that have a wide range of new uses. These structures are nested tables and variable-size arrays (VARRAYs). Like PL/SQL tables, these structures can also be used in PL/SQL programs. But what is new is the ability to use the new collections as the datatypes of fields in conventional tables and attributes of objects. While not an exhaustive implementation of user-defined datatypes, collections offer rich new physical (and, by extension, logical) design opportunities for Oracle practitioners.

Types of Collections

• PL/SQL tables are singly dimensioned, unbounded, sparse collections of homogeneous elements and are available only in PL/SQL. These are called index-by tables in version 8i and associative arrays in version 9i.
• Nested tables are also singly dimensioned, unbounded collections of homogeneous elements. They are initially dense but can become sparse through deletions. Nested tables are available in both PL/SQL and the database (for example, as a column in a table).
• VARRAYs, like the other two collection types, are also singly dimensioned collections of homogeneous elements. However, they are always bounded and never sparse. Like nested tables, they can be used in PL/SQL and in the database. Unlike nested tables, when you store and retrieve a VARRAY, its element order is preserved. Using a nested table or VARRAY, you can store and retrieve nonatomic data in a single column. For example, the employee table used by the HR department could store the date of birth for each employee’s dependents in a single column. This is illustrated in the table below:

Table 1: Storing a Nonatomic Column of Dependents in a Table of Employees

Id (NUMBER)	Name (VARCHAR2)	Dependents_ages (Dependent_birthdate_t)
10010	Zaphod Beeblebrox 4-JUL-1977 22-MAR-2021	12-JAN-1763
10020	Molly Squiggly 15-NOV-1968	15-NOV-1968
10030	Joseph Josephs
10040	Cepheus Usrbin 9-AUG-1996	27-JUN-1995
1	9-JUN-1997
10050	Deirdre Quattlebaum	21-SEP-1997

It’s not difficult to create such a table. First we define the collection type:

CREATE TYPE Dependent_birthdate_t AS VARRAY(10) OF DATE;

Now we can use it in the table definition:

CREATE TABLE employees (

   id NUMBER,

   name VARCHAR2(50),

   ...other columns...,

   Dependents_ages Dependent_birthdate_t

);

We can populate this table using the following INSERT syntax, which relies on the type’s default constructor to transform a list of dates into values of the proper data type:

INSERT INTO employees VALUES (42, 'Zaphod Beeblebrox', ...,

   Dependent_birthdate_t( '12-JAN-1763', '4-JUL-1977', '22-MAR-2021'));

While most of us have been trained to view nonatomic data as a design flaw, in some situations (for those in which you don’t need to scan the contents of all the values in all the rows), theoreticians and practitioners alike consider nonatomic data to be perfectly acceptable. Some database designers have believed for years that the large percentage of nonatomic data inherent in their applications demands a nonrelational solution. Setting aside theoretical arguments about "natural" data representations, Oracle collections allow to pass an entire collection between the database and PL/SQL using a single fetch. This feature alone could have significant positive impact on application performance.

As we’ve mentioned, within PL/SQL both nested tables and VARRAYs are ordered collections of homogeneous elements. Both bear some resemblance to the PL/SQL Version 2 table datatype, the elder member of the "collection" family. The new types are also singly dimensioned arrays, but they differ in areas such as sparseness (not exactly), how they’re initialized (via a constructor), and whether they can be null (yes).

One chief difference between nested tables and VARRAYs surfaces when we use them as column datatypes. Although using a VARRAY as a column’s datatype can achieve much the same result as a nested table, VARRAY data must be predeclared to be of a maximum size, and is actually stored "inline" with the rest of the table’s data.

Nested tables, by contrast, are stored in special auxiliary tables called store tables, and there is no pre-set limit on how large they can grow. For this reason, Oracle Corporation says that VARRAY columns are intended for "small" arrays, and that nested tables are appropriate for "large" arrays.

The old Version 2 table datatype is now called an index-by table, in honor of the INDEX BY BINARY_INTEGER syntax required when declaring such a type. Despite the many benefits of the new collection types, index-by tables have one important unique feature: initial sparseness. The table below illustrates many of the additional differences among index-by tables and the new collection types.

Table 2: Oracle Collection Types Comparison

Characteristic Dimensionality	Index-By Table Single	Nested Table Single	VARRAY Single
Usable in SQL?	No	Yes	Yes
Usable as column datatype in a table?	No	Yes; data stored "out of line" (in separate table)	Yes; data stored "in of line" (in same table)
Uninitialized state	Empty (cannot be null); elements undefined	Atomically null; illegal to reference elements	Atomically null; illegal to reference elements
Initialization	Automatic, when declared	Via constructor, fetch, or assignment. In PL/SQL, elements referenced via BINARY_INTEGER (-2,147,483,647 .. 2,147,483,647) and positive integer between 1 and 2,147,483,647	Via constructor, fetch, or assignment. Positive integer between 1 and 2,147,483,647
Sparse?	Yes	Initially, no; after deletions, yes	No
Bounded?	No	Can be extended	Yes
Can assign value to any element at any time?	Yes	No; may need to EXTEND first	No; may need to EXTEND first, and cannot EXTEND past upper bound
Means of extending	Assign value to element with a new subscript	Use built-in EXTEND procedure (or TRIM to condense), with no predefined maximum	EXTEND (or TRIM), but only up to declared maximum size
Can be compared for equality?	No	No	No
Retains ordering and subscripts when stored in and retrieved from database?	N/A	No	Yes

Click here for suggestions on which construct to use.

Collection Terminology

Collection

A term which can have several different meanings:

·         A nested table, index-by table, or VARRAY datatype

·         A PL/SQL variable of type nested table, index-by table, or VARRAY

·         A table column of type nested table or VARRAY

Inner table

The "enclosed" collection that is implemented as a column in a table; also known as a "nested table column"

Nested table

Unfortunately, the term "nested table" can be a bit misleading. A nested table, when declared and used in PL/SQL, is not nested at all! It is instead fairly similar to an array. Even when you use a nested table as a table column, in Oracle 8.0 you can only nest these structures to a single level. That is, your column cannot consist of a nested table of nested tables.

One-dimensional

(or single-dimensional) Essentially, a collection has just a single column of information in each row, and is in this way similar to a one-dimensional array. You cannot define a collection so that it can be referenced as follows:

my_table (10, 44)

This is a two-dimensional structure and not currently supported.

Outer table

A term referring to the "enclosing" table in which you have used a nested table or VARRAY as a column’s datatype

Store table

The physical table which Oracle creates to hold values of the inner table

VARRAY

"Variable-size array" is also a deceptive name; one might assume, based on the fact that it is supposed to be "variable size," that it can be arbitrarily extended; quite the opposite is true. Although a VARRAY can have a variable number of elements, this number can never exceed the limit that you define when you create the type