Guidelines For Choosing Which COLLECTION Type to Use

How do you decide which collection type to use?

Choosing Between Nested Tables and Associative Arrays

Both nested tables and associative arrays (formerly known as Index-by tables) use similar subscript notation, but they have different characteristics when it comes to persistence and ease of parameter passing.

Nested tables can be stored in a database column, but associative arrays cannot. Nested tables can simplify SQL operations where you would normally join a single-column table with a larger table.

Associative Arrays are appropriate for relatively small lookup tables where the collection can be constructed in memory each time a procedure is called or a package is initialized. We cannot store them in DB. They are good for collecting information whose volume is unknown beforehand, because there is no fixed limit on their size. Their index values are more flexible, because Associative Array subscripts can be negative, can be non-sequential, and can use string values instead of numbers.

Choosing Between Nested Tables and Varray’s

Varray is a good choice when:

•        The number of elements is known in advance.

•        The elements are usually all accessed in a sequence.

When stored in the database, Varray keep their ordering and subscripts.

You must update or retrieve all elements of the Varray at the same time, which is most appropriate when performing some operation on all the elements at once. But you might find it impractical to store and retrieve large numbers of elements this way.

Nested tables are a good choice when:

•        The index values are not consecutive.

•        There is no set number of index values. However, a maximum limit is imposed.

•        You need to delete or update some elements, but not all the elements at once.

•        You would usually create a separate lookup table, with multiple entries for each row of the main table, and access it through join queries.

Nested tables can be sparse: you can delete arbitrary elements, rather than just removing an item from the end.

Nested table data is stored in a separate store table, a system-generated database table associated with the nested table. The database joins the tables for you when you access the nested table. This makes nested tables suitable for queries and updates that only affect some elements of the collection.

You cannot rely on the order and subscripts of a nested table remaining stable as the nested table is stored in and retrieved from the database, because the order and subscripts are not preserved in the database.

RELATED TOPICS:
Different types of COLLECTIONS in ORACLE
Examples of COLLECTIONS and COLLECTION Methods

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.