Purpose of using different types of PL/SQL collections in Oracle

Question

What is the main purpose of using collections in oracle ?

1. Index by tables

2. Nested tables

3. Variable size ARRAY

Can you please explain the difference between the above types of collections ?

Answer 1

Let's start with Nested Tables, they are the most common form of collection and so represent a useful basis of comparison.

A nested table is a variable which can hold more than one instance of something, often a record from a database table. They might be declared like this:

type emp_nt is table of emp%rowtype;
emp_rec_nt emp_nt;

They are useful whenever we want to store multiple instances of data against which we want to do the same thing. The classic example is using BULK COLLECT to store multiple records:

select * 
bulk collect into emp_rec_nt
from employees;

This gives us a source of data we can loop round; crucially we can navigate backwards as well as forwards, even skip to the end or the beginning, which are things we cannot do with a cursor. Nested tables can be collections of any data type, including composites such as PL/SQL records or user-defined types.

An Index By table is better called (as the docs do) an Associative Array . These are simple collections of single attributes with an index. Nested tables also have indexes but their indexes are just row counts. With an associative array the index can be meaningful, i.e. sourced from a data value. So they are useful for caching data values for later use. The index can be a number, or (since 9iR2) a string which can be very useful. For instance, here is an associative array of salaries which is indexed by the employee identifier.

type emp_sal_aa is table of emp.sql%type
     index by emp.empno%type;
l_emp_sales emp_sal_aa;

Note that I could have declared that array using INDEX BY BINARY_INTEGER but it is clearer to use the %TYPE syntax instead (self-documenting code). Elements of that array can identified by an index value, in this case EMPNO:

l_emp_sals(l_emp_no) := l_emp_sal;

Other than caching reference tables or similar look-up values there aren't many use cases for associative arrays.

Variable arrays are just nested tables with a pre-defined limit on the number of elements. So perhaps the name is misleading: they are actually fixed arrays. There's little we can do with VArrays which we can't do with nested tables (except constrain the number of elements and it's pretty rare that we would want to do that). They are declared like this:

type emp_va is varray(14) of emp%rowtype;
emp_rec_va emp_va;

We can use bulk collect to populate a VArray ...

select * 
bulk collect into emp_rec_va
from employees;

However we must be certain the query will return at most the number of elements specified in the VArray's declaration. Otherwise the SELECT will hurl ORA-22165.

There are no known use cases for variable arrays. Okay that's a bit harsh, but almost all of the time you will use nested tables instead. The one big advantage of VArrays over nested tables is that they guarantee the order of the elements. So if you must get elements out in the same order as you inserted them use a VArray.

The PL/SQL documentation devotes an entire chapter to collections. Find out more.

Answer 2

PL/SQL offers these collection types:-

Associative arrays, also known as index-by tables, let you look up elements using arbitrary numbers and strings for subscript values. These are similar to hash tables in other programming languages.

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.

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. 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. 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 nonsequential, and can use string values instead of numbers.

PL/SQL automatically converts between host arrays and associative arrays that use numeric key values. The most efficient way to pass collections to and from the database server is to set up data values in associative arrays, then use those associative arrays with bulk constructs (the FORALL statement or BULK COLLECT clause).

2. Choosing Between Nested Tables and Varrays:-

Varrays are a good choice when:

The number of elements is known in advance.

The elements are usually all accessed in sequence.

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

Each varray is stored as a single object, either inside the table of which it is a column (if the varray is less than 4KB) or outside the table but still in the same tablespace (if the varray is greater than 4KB). 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.