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.
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 SQL
Nested Table Code
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.
CREATE TYPE
empobj AS
OBJECT
(
emp_name NUMBER,
emp_phone VARCHAR (20),
sal NUMBER
);
CREATE TYPE
empobj_tab AS
TABLE OF
empobj;
CREAT TABLE
deptnest
(
deptno NUMBER,
dname VARCHAR(20),
location VARCHAR2(20),
)
NESTED TABLE
emp
STORE AS emp_tab;
Object Relational Features
You can implement object-relational features such as abstract datatypes and object views in your applications. In this module, you will see how to use collectors—sets of elements that are treated as part of a single row.
The two types of collectors available are nested tables and varying arrays. This module explains the differences between these two types of collectors, as well as how to implement and manage them. Collectors make use of abstract datatypes. You should be familiar with the creation and implementation of abstract datatypes before attempting to use varying arrays and nested tables.
Varying Arrays:
A varying array allows you to store repeating attributes of a record in a single row.For example, suppose Dora Talbot wants to track which of her tools were borrowed by which of her workers. You could model this in a relational database by creating a BORROWER table:
The primary key of the BORROWER table is the combination of the Name column and the Tool column. Thus, if a single worker borrowed three tools, the worker’s name would be repeated in each of the three records.Even though the worker's Name value does not change, it is repeated in each record because it is part of the primary key. Collectors such as varying arrays allow you to repeat only those column values that change, potentially saving storage space.
You can use collectors to accurately represent relationships between datatypes in your database objects.
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.
Figure 5-1 Varray of Size 10
The next lesson discusses how to construct a table with repeating values.
Nested Table Code
