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 that get stored in the collection column and 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 dataset; your data has an intrinsic upper bound; or you expect, in general, to retrieve the entire collection simultaneously.
If you need sparse PL/SQL tables, say, for "data-smart" storage, your only practical option is an index-by table. True, you could allocate and then delete elements of a nested table variable as illustrated in the 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, you also have only one option: index-by tables. Or, if your PL/SQL application requires negative subscripts, you also have to use index-by tables.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
|This HTML Help has been published using the chm2web software.