Glossary of Collection Terms
The following glossary should help you understand and absorb collections more smoothly.
Collection
A term that can have several different meanings:
· A nested table, associative array, or VARRAY datatype
· A PL/SQL variable of type nested table, associative array, or VARRAY
· A table column of type nested table or VARRAY
Regardless of the particular type or usage, however, a collection is a list of items.
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.
Homogeneous elements
A collection can have only a single column, and that column must have a datatype declared when the collection TYPE is defined. Thus, all rows in a collection contain values of the same datatype and the collection is, therefore, homogeneous. This datatype can, however, be a composite or complex datatype itself; you can declare a table of records, for example. And with Oracle9i, you can define multilevel collections. This kind of collection has, as its column datatype, another collection or a record that contains a collection.
Unbounded versus bounded
A collection is said to be bounded if there are predetermined limits to the possible values for row numbers in that collection. It is unbounded if there are no upper or lower limits on those row numbers. VARRAYs or variable-sized arrays are always bounded; when you define them, you specify the maximum number of rows allowed in that collection (the first row number is always 1). Nested tables and associative arrays are only theoretically bounded. We describe them as unbounded, because from a theoretical standpoint there is no limit to the number of rows you can define in them.
The theoretical limits on an associative array indexed by BINARY_INTEGER are:
Lowest value: -231 + 1
Highest value: 231 - 1
The theoretical limits of a nested table are:
Lowest value: 1
Highest value: 231 - 1
This means that you can define up to approximately 4.3 billion rows in an associative array, and about 2.14 billion rows in a nested table. This is only a theoretical bound, because you will run out of real memory in your computer before you use that many rows. We therefore say that associative arrays and nested tables are unbounded.
Sparse versus dense
A collection (or array or list) is called dense if all rows between the first and last row are defined and given a value (including NULL). A collection is considered sparse if rows do not have to be defined and populated sequentially; there can be gaps between defined rows, as demonstrated in the previous example. VARRAYs are always dense. Nested tables always start as dense collections, but can be made sparse. Associative arrays can be sparse or dense, depending on how you fill the collection.
Sparseness, it turns out, is a very valuable feature, as it gives you the flexibility to populate rows in a collection using a primary key or other intelligent key data as the row number. By doing so, you can define an order on the data in a collection or greatly enhance the performance of lookups.
Indexed by integers
All collections support the ability to reference a row via the row number, an integer value. The associative array TYPE declaration makes that explicit, but the same rule holds true for the other collection types.
Indexed by strings
In Oracle9i Release 2, Oracle makes it possible to index an associative array by string values instead of by numeric row numbers. This feature is not available for nested tables or VARRAYs.
Outer table
This refers to the "enclosing" table in which you have used a nested table or VARRAY as a column's datatype.
Inner table
This is the "enclosed" collection that is implemented as a column in a table; also known as a "nested table column."
Store table
This is the physical table that Oracle creates to hold values of the inner table (a nested table column).