Collections were first introduced into PL/SQL in Oracle7.A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.

PL/SQL offers these collection types:

1. Index-by tables , also known as associative arrays , let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)
2. Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
3. Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

1. Which collection type will have more benefits in terms of performance?

Associative arrays are pure memory structures and are very fast when compared to other collections. Since varray data is stored inline (in the same tablespace), retrieving and storing varrays involves fewer disk accesses. Varrays are thus more efficient than nested tables.Hence it is always recommended to use associative array when you do not need to stored the data back into the table. But when you need to store the data back into the tables then varrays or nested tables can be used. If you know the size of the array that needs to be defined, always use varrays because it will be stored inline (in the same tablespace), and hence invloves fewer disk accesses when compared to nested tables.

2. What is the size limit of Nested Tables, Varrays and Associative arrays ?

The allowed subscript ranges are:

* For nested tables, 1 .. 2147483647 (the upper limit of PLS_INTEGER).
* For varrays, 1 .. size_limit, where you specify the limit in the declaration (not to exceed 2147483647).
* For associative arrays with a numeric key, -2147483648 to 2147483647.
* For associative arrays with a string key, the length of the key and number of possible values depends on the VARCHAR2 length limit in the type declaration, and the database character set.

3. Choosing a Collection Type

o 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 , but it is inefficient to do so for anything but the smallest collections.
o If your PL/SQL application requires negative subscripts, you also have to use associative arrays.
o If you are running Oracle Database 10g and would find it useful to perform high-level set operations on your collections, choose nested tables over associative arrays.
o If you want to enforce a limit to the number of rows stored in a collection, use VARRAYs.
o 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.
o 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.

4. 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 , if < 4,000 bytes)
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
Subscript Limit	BINARY_INTEGER (-2,147,483,647 .. 2,147,483,647) VARCHAR2 - the length of the key and number of possible values depends on the VARCGAR2 length limit in the type declaration and db characterset.	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
Means of extending	Assign value to element with a new subscript	Use built-in EXTEND procedure (or TRIM to condense), with no predefined
Can be compared for equality?	No	Yes, from Oracle Database 10g	No
Can be manipulated with set operators	No	Yes, from Oracle Database 10g	No
Retains ordering and subscripts when stored in and retrieved from database?	N/A	No	Yes

5. Ways of querying from the different types of collection.

The different ways of querying from a collection is detailed out in one of the article published in OTN. Click Here to access the article.