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.

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?s 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.

When working with nested tables and varrays in Oracle 11gR2, several key concepts come into play for their modification and manipulation:
1. Nested Tables:

1. Definition: A nested table is an unordered set of elements that can be stored in a column of a relational table. Each row in the table can have a different number of elements in this column.
2. Main Concepts for Modification:
   
   • Collection Methods:
     • EXTEND: Adds one or more null elements to the end of the nested table.
     • TRIM: Removes elements from the end of the nested table.
     • DELETE: Removes elements from a specific position or range within the table.
     • FIRST and LAST: Returns the first or last index of the nested table.
     • PRIOR and NEXT: Used for navigation within the table.
   • DML Operations:
     • INSERT: You can insert rows into a nested table column using INSERT with the TABLE function to treat the nested table like a regular table for INSERT operations.
     • UPDATE: Updating can be done using UPDATE with the TABLE function, allowing you to modify individual elements or the whole nested table.
     • DELETE: Deleting elements or the entire nested table can also be performed using DELETE with the TABLE function.
   • Multiset Operations:
     • Oracle supports multiset operations like MULTISET UNION, MULTISET INTERSECT, MULTISET EXCEPT for set operations between two nested tables.
   • Storage:
     • By default, nested tables are stored out-of-line (in a separate table), but you can define them to be stored in-line (within the same block as the row) with STORE AS clause.

2. Varrays (Variable-size arrays):

1. Definition: A varray is an array with a maximum size defined when it's created. Unlike nested tables, varrays have a fixed upper bound on the number of elements.
2. Main Concepts for Modification:
   • Collection Methods:
     • Similar to nested tables, but with some differences:
       • EXTEND: Can only extend up to the maximum size defined for the varray.
       • TRIM, DELETE, FIRST, LAST, PRIOR, NEXT work similarly, but operations are constrained by the array's fixed upper limit.
   • DML Operations:
     • INSERT: Can insert varrays directly into a table column or use INSERT INTO table with TABLE function to add elements.
     • UPDATE: Elements of varrays can be updated. However, since varrays are stored as a single column, modifications typically involve replacing the entire varray or using PL/SQL to modify individual elements.
     • DELETE: More complex due to the fixed size; elements might need to be set to NULL instead of being deleted.
   • Storage:
     • Varrays are usually stored inline, meaning they are stored within the row of the parent table. However, if they exceed the block size, Oracle will store them out of line automatically.

Additional Notes:

• PL/SQL: Both nested tables and varrays are commonly manipulated inside PL/SQL blocks where you have more control over the collection elements.
• Type Evolution: Oracle allows for changes in the definition of collection types through type evolution, which can affect how you might modify these structures over time.
• Indexes: You can create indexes on nested tables to improve query performance, not directly on varrays since they are treated more like a single column value.

Understanding these concepts helps in efficiently designing and managing databases involving complex data structures like nested tables and varrays.

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.