There are three types of collections:
Varrays,Nested tables,Associative arrays (formerly known as index-by tables)
A varray is similar to an array in Java.
You can use a varray to store an ordered set of elements having an index associated with it.
The elements in a varray are of the same type.
A varray has one dimension.
A varray has a maximum size that you set when creating it.
Elements in a varray can only be modified as a whole, not individually.
You can change the size of a varray later.
The elements stored in a varray are stored with the table when the size of the varray is 4KB or less, otherwise the varray is stored outside of the table.
When a varray is stored with the table, accessing its elements is faster than accessing elements in a nested table.
A nested table is a table that is embedded within another table.
You can insert, update, and delete individual elements in a nested table.
Because you can modify individual elements in a nested table, this makes them more flexible than a varray.
A nested table doesn't have a maximum size, and you can store an arbitrary number of elements in a nested table.
The elements for nested tables are stored in separate tables.
Associative arrays is formerly known as index-by tables.
An associative array is a set of key and value pairs.
You can get the value from the array using the key (which may be a string) or an integer.
An associative array is similar to a hash table.
You create a collection type using the SQL DDL CREATE TYPE statement.
Then you can use these types to define columns in a table.
An associative array is a PL/SQL construct, not a SQL construct.
An associative array cannot be stored persistently in a table.
You might be asking yourself why you would want to use collections in the first place.
After all, using two tables with a foreign key already allows you to model relationships between data.
The answer is that the data stored in the collection may be accessed more rapidly by the database than if you were to use two tables instead.
Typically, you'll want to use a collection if you have data that is only used by one table.
Quote from:
Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)
# Paperback: 608 pages
# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)
# Language: English
# ISBN-10: 0072229810
# ISBN-13: 978-0072229813