Concepts for Modifying Nested Tables in Oracle PL/SQL

In this lesson, we will look at the main aspects of inserting and modifying data within a nested table and varray. We will also look at the way Oracle stores data for nested tables and varrays. Within a varray, updating part of the data is not allowed. For example, within the PHONE_LIST column of the CUSTOMER_OBJ_TABLE table, it is not possible to update only one of the phone numbers. You must update the varray with a new set of phone numbers. For example, for CUST_ID = 1, the PHONE_LIST varray can be updated with the following two phone numbers:

1. 1-808-876-6543
2. 1-808-209-0987

A nested table allows you to update parts of the data. In other words, you can update one column within a nested table.
Let us look at the way Oracle stores data for nested tables and varrays, because it will provide insight into choosing the appropriate collection when designing a database. As you already know, a nested table is an unordered and unbounded collection, whereas a varray is an ordered and bounded collection. A varray is stored as raw or binary large object (BLOB) data. The current count of the elements within the varray determines the size of a varray. The size of a varray is not based on the maximum number of elements the varray can hold. A slight "overhead" is associated with the storage of varrays: Because the varray also stores the NULL values for the empty elements, the size of a varray is slightly higher than the sum of the size of each element. How a varray is stored is decided by Oracle, and it depends on the total size of the declared varray. If the total size is 4 KB or less, Oracle stores the varray as raw data. If it is more than 4 KB, Oracle stores it as BLOB data. Oracle also provides a feature of storing the BLOB data inline with the row of data, so if the varray exceeds 4 KB, the first 4 KB are stored within the column of the row itself.

Because a slight overhead is associated with the storage of varrays, it is more efficient to use a nested table than a varray. Additionally, if the collection defined within the application is very large and you need to query and modify only a subset of the data stored within the collection, a nested table is a better choice.

If the application requires querying and modifying the entire collection, a varray is a better alternative.
Oracle stores all the rows (elements) of a nested table within a separate storage table. A system-generated NESTED_TABLE_ID, which is 16 bytes in length, correlates the parent row with the rows within its corresponding storage table. The storage table contains the value for each nested table within a nested table column. Each value occupies one row within the storage table. The storage table uses the NESTED_TABLE_ID to track the nested table for each value. You can define a primary key for a nested table; this provides faster access to the data.

Click the Quiz link below to take a multiple-choice quiz about modifying varrays and nested tables.
varray Concepts - Quiz
In the next lesson, you will learn how to insert data into a nested table.