Lesson 5	Using nested tables
Objective	Create a nested table.

Using nested tables in Oracle

In Oracle Database Management Systems, nested tables serve as a sophisticated data structure designed to enhance the relational model's capability by facilitating the storage of multi-valued attributes in a way that is both efficient and readily queryable. The primary utility of nested tables lies in their ability to encapsulate sets of rows within a single table column, effectively providing a mechanism to model one-to-many relationships within a single row of a parent table. This feature significantly simplifies complex data models and augments data integrity, leading to more optimized SQL queries and a robust data representation.

Architectural Principles

A nested table is essentially a table within a table. In a traditional relational model, the modus operandi for handling one-to-many relationships typically involves creating multiple tables and joining them via keys. Nested tables, however, bypass this limitation by allowing a single column to contain an arbitrary set of rows of a specified data type. These rows can then be manipulated in a set-oriented manner, similar to manipulating rows of ordinary tables.

Definition and Declaration

To implement a nested table, you first define the data type of the nested table, usually through the `CREATE TYPE` statement, and subsequently employ it within a table definition:

CREATE TYPE address_type AS OBJECT (
    street_name     VARCHAR2(50),
    city            VARCHAR2(50)
);
/

CREATE TYPE address_table_type AS TABLE OF address_type;
/

CREATE TABLE person (
    id      NUMBER,
    name    VARCHAR2(50),
    addresses  address_table_type
) NESTED TABLE addresses STORE AS addresses_table;

In this example, the `addresses` column in the `person` table is a nested table that holds a collection of `address_type` objects.

Data Manipulation

Manipulating data within nested tables can be accomplished using Oracle's set of DML operations extended for collections. For example, to insert data into the nested table:

DECLARE 
    my_addresses  address_table_type := address_table_type();
BEGIN
    my_addresses.EXTEND;
    my_addresses(1) := address_type('123 Main St', 'Springfield');
  
    INSERT INTO person (id, name, addresses) VALUES (1, 'John', my_addresses);
END;
/

To query nested tables, you can employ a specialized `TABLE` function to unnest the collection:

SELECT p.id, p.name, a.street_name, a.city
FROM person p, TABLE(p.addresses) a;

Performance Considerations

When properly indexed and partitioned, nested tables can significantly speed up queries that would otherwise require expensive joins. They can also reduce the complexity of SQL statements, making code easier to maintain and optimize.

Constraints and Limitations

While nested tables offer multiple advantages, they are not without their limitations. Notably, each nested table within a row is unbounded, meaning it can hold an indefinite number of elements. This characteristic, if not managed correctly, could potentially result in performance bottlenecks. Also, the lack of foreign key constraints within nested tables might require extra caution in maintaining data integrity.
In summary, nested tables in Oracle serve as a robust feature that extends the standard relational model, providing a data structure that enables efficient and structured representation of one-to-many relationships within a single row of a table. However, while they bring advantages in terms of data representation and query optimization, careful consideration must be applied to manage potential performance implications and maintain data integrity.

Nested tables and pointers

A construct within Oracle contains a very interesting pointer structure that allows a single cell in an entity to contain a whole other entity. Oracle calls these nested tables. They are called nested tables because it provides the illusion that one table has another table nested inside it.

1) A nested table is a pointer structure. In the example shown, the emp column in the deptnest table contains a nested table.

2) While this appears to be a recursive structure, in reality Oracle is using an OID to point to this single subordinate table or store table called emp_name.

3) The store table also has an OID back to each row of the owner table.

Using Oracle Nested Tables

A pointer is a unique reference to a row in a relational database table. The ability to store these row IDs inside a relational table extends the traditional relational model and enhances the ability of an object-relational database to establish relationships between tables. The new abilities of pointer data types include:

Referencing: Referencing sets of related rows in other tables: It is now possible to violate first normal form and have a cell in a table that contains a pointer to repeating table values. For example, an EMPLOYEE table could contain a pointer called JOB_HISTORY_SET, which in turn could contain pointers to all of the relevant rows in a JOB_HISTORY table. This technique also lets you pre-build aggregate objects, such that you could preassemble all of the specific rows that comprise the aggregate table.
Allow pointers to non-database objects in a flat file: For example, a table cell could contain a pointer to a flat file that contains a non-database object such as a picture in .gif or .jpeg format.
The ability to establish one-to-many and many-to-many data relationships without relational foreign keys: This would alleviate the need for relational JOIN operations, because table columns could contain references to rows in other tables. By dereferencing these pointers, you could retrieve rows from other tables without ever using the time-consuming SQL JOIN operator.

A nested table is a pointer structure. In the example shown, the emp column in the deptnest table contains a nested table.
While this appears to be a recursive structure, in reality Oracle is using an OID to point to this single subordinate table or store table called emp_name
The store table also has an OID back to each row of the owner table.

In theory, a nested table should get the data from the store table faster than a traditional Oracle7 data model where a JOIN would be required to access the data.

Nesting objects within other objects

In this way it is possible to create a structure where objects or tables may be nested within other objects or tables. For an object/relational database, this means that a single column value in a table may contain a whole table. These sub-table tables, in turn, may have single column values that point to whole tables, and so on, ad infinitum. The following diagram below comments on this basic code.

 CREATE TYPE
empobj AS
OBJECT
(
emp_name NUMBER,
emp_phone VARCHAR2(20),
sal NUMBER
);
CREATE TYPE
empobj_tab AS
TABLE OF 
empobj;

CREATE TABLE
deptnest
(
  deptno NUMBER,
dname VARCHAR2(20),
location VARCHAR2(20),
emp empobj_tab
)
NESTED TABLE
emp
STORE AS emp_tab;

We create type empobj to contain 3 data elements
We then create type empobj_tab table as a table of this data type.
We then create the deptnest table with the nested structure empobj_tab.

Oracle Nested Table Code
The next lesson discusses how to construct a table with repeating values.

Oracle Data Constructs

Before moving on to the next lesson, click the link below to read about nested tables and pointers.
Oracle Data Constructs

Understanding Variable-Size Arrays (Varrays)

A variable-size array (varray) is an item of the data type VARRAY.
A varray has a maximum size, which you specify in its type definition
Can contain a varying number of elements, from zero (when empty) to the maximum size.
A varray index has a fixed lower bound of 1 and an extensible upper bound.

To access an element of a varray, you use standard subscripting syntax. Figure 5-1 shows a varray named Grades, which has maximum size 10 and contains seven elements. The current upper bound for Grades is 7, but you can increase it to the maximum of 10. Grades(n) references the nth element of Grades.