A collection is a list of elements of the same type. Each element in the list has a unique position number or label, called the "subscript".
To give a logical analogy, consider a list of colors = {red, blue, green, yellow}. This list has four elements, all names of colors. There is a unique position of each element in the list. If we number the positions sequentially starting from one, we could say:
color[1] = red, color[2] = blue, and so on.
Here, color is the name of the collection, and the numbers within [] are the subscripts.
PL/SQL has three collection types. In this article, we’ll look at a chart for comparing the three, their advantages and limitations, and which one to use for your needs.
To introduce the three collection types:
- Index by tables: Also called associative arrays.
- Nested tables
- Varrays: Also called variable arrays
The chart below lists the properties of the three collection types on a set of parameters such as size, ease of modification, persistence, etc.
| Index By Tables | Nested Tables | Varrays | |
| Size | Unbounded i.e. the number of elements it can hold is not pre-defined | Unbounded i.e. the number of elements it can hold is not pre-defined | Bounded i.e. holds a declared number of elements, though this number can be changed at runtime |
| Subscript Characteristics | Can be arbitrary numbers or strings. Need not be sequential. | Sequential numbers, starting from one | Sequential numbers, starting from one |
| Database Storage | Index by tables can be used in PL/SQL programs only, cannot be stored in the database. | Can be stored in the database using equivalent SQL types, and manipulated through SQL. | Can be stored in the database using equivalent SQL types, and manipulated through SQL (but with less ease than nested tables) |
| Referencing and lookups | Works as key-value pairs.
e.g. Salaries of employees can be stored with unique employee numbers used as subscripts sal(102) := 2000; |
Similar to one-column database tables.
Oracle stores the nested table data in no particular order. But when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. |
Standard subscripting syntax e.g. color(3) is the 3rd color in varray color |
| Flexibility to changes | Most flexible. Size can increase/ decrease dynamically. Elements can be added to any position in the list and deleted from any position. |
Almost like index-by tables, except that subscript values are not as flexible. Deletions are possible from non-contiguous positions. | Not very flexible. You must retrieve and update all the elements of the varray at the same time. |
| Mapping with other programming languages | Hash tables | Sets and bags | Arrays |
Which Collection Type To Use?
You have all the details about index by tables, nested tables and varrays now. Given a situation, will one should you use for your list data?
Here are some guidelines.
Use index by tables when:
- Your program needs small lookups
- The collection can be made at runtime in the memory when the package/ procedure is initialized
- The data volume is unknown beforehand
- The subscript values are flexible (e.g. strings, negative numbers, non-sequential)
- You do not need to store the collection in the database
Use nested tables when:
- The data needs to be stored in the database
- The number of elements in the collection is not known in advance
- The elements of the collection may need to be retrieved out of sequence
- Updates and deletions affect only some elements, at arbitrary locations
- Your program does not expect to rely on the subscript remaining stable, as their order may change when nested tables are stored in the database.
Use varrays when:
- The data needs to be stored in the database
- The number of elements of the varray is known in advance
- The data from the varray is accessed in sequence
- Updates and deletions happen on the varray as a whole and not on arbitrarily located elements in the varray
{ 2 comments… read them below or add one }
Thank you very very much!.
Thank you for this content. Nice summary!