RelationalDBDesign RelationalDBDesign 

Data Structures   «Prev 

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:
  1. 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.
  2. 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.
  3. 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.