19.9 Which Collection Type Should I Use?

It's not altogether obvious how to choose the best type of collection for a given application. Here are some guidelines:

• 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.