home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam    

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.

Previous: 19.8 Collections Housekeeping Oracle PL/SQL Programming, 2nd Edition Next: 20. Object Views
19.8 Collections Housekeeping Book Index 20. Object Views

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference