PL/SQL   «Prev  Next»

Lesson 2Concepts for modifying nested Tables and varrays
Objective Describe the main concepts used when modifying nested tables and varrays.

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

Nested Table Capabilities

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.

When is it more efficient to use a nested table?

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.

When is it more efficient to use a varray?

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.

varray Concepts - Quiz

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.