Difference between NESTED TABLE and VARRAY

Question

I know basic difference and uses of both. But, what I'm looking for as an answer is, why exactly VARRAY introduced ?

Since, we can do same thing using NESTED TABLE what can be done using VARRAY whereas vice-versa is not possible in some scenario. Also, I have noticed some places people mentioned VARRAY stores elements in-line. What does it mean ? Can anyone explain internal storage and processing of both ?

Answer 1

To summarize, you would use a Variable-Size Arrays a.k.a VARRAY when:

• Number of elements are known before populating it.
• Elements need to be accessed sequentially
• Guaranteed order of elements

For example, a VARRAY is declared as:

TYPE varray_emp IS VARRAY(14) OF emp%ROWTYPE;
emp_rec varray_emp;

So, you see the UPPER BOUND is fixed, in the above example it is 14.

See the documentation for more details.

Update Regarding storing VARRAY in database

Quoting from the above documentation link:

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.

Regarding in line storage:

A varray is normally stored in line, that is, in the same tablespace as the other data in its row. If it is sufficiently large, Oracle stores it as a BLOB

Read more about Storage Considerations for Varrays