| 26. 1. 1. Collections stores sets of elements |
|
|
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 |