Choosing a Collection Type
Which collection type makes sense for your application? In some cases, the choice is obvious. In others, there may be several acceptable choices. This section provides some guidance. Table 11-3 illustrates many of the differences betweenassociative arrays, nested tables, and VARRAYs.
As a PL/SQL developer, I find myself leaning toward using associative arrays as a first instinct. Why is this? They involve the least amount of coding. You don't have to initialize or extend them. They have historically been the most efficient collection type (although this distinction will probably fade over time). However, if you want to store your collection within a database table, you cannot use an associative array. The question then becomes: nested table or VARRAY?
Beyond these very high-level determinants, review the following guidelines for additional assistance in making your choice:
· If you intend to store large amounts of persistent data in a column collection, your only option is a nested table. Oracle will then use a separate table behind the scenes to hold the collection data, so you can allow for almost limitless growth.
· If you want to preserve the order of elements stored in the collection column and if your dataset will be small, use a VARRAY. What is "small"? I tend to think in terms of how much data you can fit into a single database block; if you span blocks, you get row chaining, which decreases performance. The database block size is established at database creation time and is typically 2K, 4K, or 8K.
· Here are some other indications that a VARRAY would be appropriate: you don't want to worry about deletions occurring in the middle of the data set; your data has an intrinsic upper bound; or you expect, in general, to retrieve the entire collection simultaneously.
· If you need sparse associative arrays (for example, for "data-smart" storage), your only practical option is an associative array. True, you could allocate and then delete elements of a nested table variable (as illustrated in the later section on NEXT and PRIOR methods), but it is inefficient to do so for anything but the smallest collections.
· If your PL/SQL program needs to run under both Oracle7 and Oracle8, associative arrays are again your only option. And if your PL/SQL application requires negative subscripts, you also have to use associative arrays.
| Table 11-3. Comparing Oracle collection types | |||
| Characteristic | Associative array | Nested table | VARRAY |
| Dimensionality | Single | Single | 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 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, assignment | Via constructor, fetch, assignment |
| In PL/SQL, elements referenced via | BINARY_INTEGER(-2,147,483,647 .. 2,147,483,647) VARCHAR2 (Oracle9i Release 2 and above) | Positive integer between 1 and 2,147,483,647 | 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 |