RelationalDBDesign RelationalDBDesign 

Data Structures   «Prev  Next»
Lesson 5Using nested tables
ObjectiveCreate a nested table.

Using nested tables in Oracle

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 Nested Tables
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.

  1. We create type empobj to contain 3 data elements
  2. We then create type empobj_tab table as a table of this data type.
  3. 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)

  1. A variable-size array (varray) is an item of the data type VARRAY.
  2. A varray has a maximum size, which you specify in its type definition
  3. Can contain a varying number of elements, from zero (when empty) to the maximum size.
  4. 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