Concepts for Modifying Nested Tables and Varrays in Oracle 23ai

Before writing DML against nested tables and varrays, it is important to understand how Oracle stores each collection type and what operational constraints follow from that storage model. The storage architecture of a collection type determines which DML operations are available, how element-level access works, and when one collection type is more appropriate than the other. This lesson establishes those foundational concepts before the subsequent lessons demonstrate the specific INSERT, UPDATE, and DELETE syntax for each type.

The Most Important Operational Difference

The single most important constraint to understand before working with varrays is that partial updates are not supported. Within a varray, it is not possible to update only one element using a standard DML statement. The entire varray must be replaced with a new set of values via an UPDATE statement.

For example, consider a PHONE_LIST varray column in a CUSTOMER_OBJ_TABLE. To change one phone number for a customer, the entire varray must be replaced:

UPDATE customer_obj_table
SET    phone_list = phone_list_type('1-808-876-6543', '1-808-209-0987')
WHERE  cust_id = 1;

There is no syntax for targeting a single element within the varray using a standard UPDATE ... SET col = value WHERE ... pattern against the parent table. Individual element assignment (varray_col(n) = value) requires a PL/SQL block, not a single SQL statement.

Nested tables, by contrast, support element-level INSERT, UPDATE, and DELETE through the TABLE() operator, which treats the nested table as a queryable and modifiable set for DML purposes. This asymmetry is the architectural reason the two collection types have different DML patterns throughout this module.

How Oracle Stores Nested Tables

Oracle stores the rows of a nested table in a separate storage table, not inline with the parent row. When a nested table column is defined on a parent table, Oracle creates a corresponding storage table to hold the nested table elements. A system-generated NESTED_TABLE_ID — 16 bytes in length — correlates each parent row with its corresponding rows in the storage table. Each element of the nested table occupies one row in the storage table, and the NESTED_TABLE_ID is used to track which elements belong to which parent row.

This out-of-line storage model is why the TABLE() operator is required for nested table DML. The nested table elements do not exist as columns within the parent row — they exist as rows in a separate table. The TABLE() operator with a subquery that identifies the parent row provides the mechanism for directing DML to the correct set of nested table rows in the storage table.

A primary key can be defined on a nested table to improve access performance. Because nested table elements are stored as rows in a separate table, standard B-tree indexes can be created on the storage table columns, providing faster element retrieval when filtering on specific attribute values.

Nested tables are unordered and unbounded. There is no guaranteed retrieval order for nested table elements unless an ORDER BY clause is applied, and there is no fixed limit on the number of elements a nested table can contain.

How Oracle Stores Varrays

Oracle stores varrays differently from nested tables. A varray is stored as a single unit — either as raw data or as BLOB data — determined by the total declared size of the varray type at creation time.

• If the total declared size of the varray is 4 KB or less, Oracle stores the varray as raw data inline with the parent row, within the same data block as the parent record.
• If the total declared size exceeds 4 KB, Oracle stores the varray as BLOB data. In this case, the first 4 KB are stored inline with the parent row, and the remainder is stored out of line in a separate LOB segment.

The size of a varray at any point in time is determined by the current count of its elements, not by the declared maximum. However, because Oracle also stores NULL values for the empty positions within the varray, a slight storage overhead exists. The actual storage size of a varray is marginally higher than the sum of the sizes of its populated elements.

Varrays are ordered and bounded. Elements are accessible by index position, and the maximum number of elements is fixed at type creation time and cannot be exceeded. The EXTEND method can add elements up to but not beyond the declared maximum.

Collection Type Selection Guidance

The storage difference between nested tables and varrays translates directly into collection type selection criteria based on application access patterns.

The slight storage overhead associated with varray null-element tracking means that for very large collections, nested tables are generally more storage-efficient. For small, ordered, bounded collections that are always accessed as a complete unit, varrays are appropriate and their inline storage model provides faster retrieval for small declared sizes.

DML Operations — Nested Tables

All three DML operations are available for nested table elements through the TABLE() operator in Oracle 23ai:

• INSERT — The TABLE() operator treats the nested table as a regular table for insert operations. New elements are added as rows in the storage table for the specified parent row.
• UPDATE — Element-level updates are performed using the TABLE() operator with a WHERE clause that filters the specific nested table rows to modify.
• DELETE — Element-level deletion uses the TABLE() operator with an optional WHERE clause. Omitting the WHERE clause deletes all elements for the specified parent row.

In addition to standard DML, Oracle 23ai supports multiset operations between two nested tables of compatible types: MULTISET UNION, MULTISET INTERSECT, and MULTISET EXCEPT. These set operations return a new nested table that is the union, intersection, or difference of the two operand collections.

DML Operations — Varrays

The varray DML model is more constrained than the nested table model due to the inline storage architecture:

• INSERT — A varray is inserted into a parent row as a complete unit using a constructor expression that initializes all elements. Individual element insertion after the parent row exists requires a PL/SQL block to fetch, modify, and replace the varray.
• UPDATE — Full varray replacement is the standard approach. Element-level assignment using index notation (varray_col(n) = value) is available within PL/SQL blocks but requires fetching the varray, modifying it in memory, and writing it back.
• DELETE — Element-level deletion is not directly supported. The approaches are setting the entire varray to NULL via UPDATE, setting a specific element to NULL using index notation in a PL/SQL block, or reconstructing the varray without the unwanted element. These patterns are covered in detail in Lesson 7.

Collection Methods in Oracle 23ai

Oracle 23ai PL/SQL provides built-in collection methods that operate on both nested tables and varrays within PL/SQL blocks. These methods are called using dot notation on a collection variable.

The DELETE collection method operates on the in-memory PL/SQL collection variable, not on the database storage. It is distinct from the SQL DELETE statement used with the TABLE() operator. When DELETE is called on a nested table variable in PL/SQL, it removes elements from the variable's in-memory representation. The changes are not persisted to the database until an UPDATE statement writes the modified collection back to the table.

Type Evolution

Oracle 23ai supports type evolution — the ability to modify an existing object type definition after it has been created and used in database tables. Type evolution allows attributes to be added or dropped from a type, and the changes propagate to tables and stored collections that use that type. When a type is altered, Oracle updates the metadata for dependent tables and marks existing data for upgrade. Queries against the affected tables trigger the data upgrade transparently. Type evolution is relevant to nested table and varray schemas when the underlying object types that define collection elements need to change over time.

varray Concepts - Quiz