Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Column Value Pointers to Whole Table

Oracle Database Tips by Donald Burleson

The new object/relational database contain a very interesting pointer structure that allows a single cell in an entity to contain a whole other entity.  In this fashion 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 single columns values 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. (Figure 6.4)

Figure 6.4 Nesting tables within tables

While the application of this new data structure is not apparent, it does present exciting possibilities for modeling complex aggregate objects.  In C++ object-oriented databases such as Ontos or Objectivity, we can create a structure where an object contains a list of pointers.  Each of these pointers, in turn will point to a separate list of pointers.  These pointers will point to other objects in the database.  In C language parlance, this structure is known as **char, which is called a pointer to a pointer to a character.

In the object/relational databases this structure is implemented by using what is called a "store table".  The top level table will contain a cell that is defined as a pointer to a table.  The column that is defined as a pointer to a whole table has one big restriction in that each and every pointer must point to a table with the exact same definition.  That is, each column value must contain a pointer to a table that is defined with the exact same definition.

While it appears that each cell points to a whole table, the object/relational databases implement this structure by defining a store table.  A store table is an internal table that is tightly coupled with the owner table, and the store table will inherit the data storage characteristics of the parent table.  These inherited characteristics will include the initial extent for the table and the size of new extents.

In essence, a store table is nothing more than an internal table that is defined as subordinate to the owner table with a fixed set of columns.  Let's illustrate the use of this data structure with a simple example. Suppose that we start with a University database that has a many-to-many relationship between courses and student entities.  That is, a student may take many courses, and a course has many students.  In a traditional relational system, this relationship between students and courses would be implemented by creating a junction table between the student and course entities, and copying the primary keys from the student and course tables into this entity.  In our example, this entity is called grade, and the grade entity contains the student_ID and the course_ID columns as foreign keys.  (Figure 6.5)

Figure 6.5  A sample many-to-many data relationship for a University

Now let's take a look at how this would be implemented using pointers to whole tables.  To produce a class schedule for a student in a traditional relational implementation, we would need to select the student row, and then join with the grade table, and finally join with the class table:

      student, grade, course
      student_last_name = 'Burleson'
      student.student_ID = grade.student_ID
      grade.course_ID = course.course_ID;

To avoid the three-way SQL join of the tables, we could choose to create a store table that is subordinate to the student table. This table would contain the course_name, the course_date and the grade for each student.

   student_list (
      student_full_name        full_name,
      student_full_address    full_address,
      grade                            char(1)); 

CREATE TYPE student_list_type AS TABLE OF student_list; 

       course_name      varchar(20),
       dept_ID               number(4),
       credit_hrs            number(2),
       student_roster     student_list_type);

Here we see that the student_roster column of the COURSE table contains a pointer to a table of type student_list.  Herein lies the illusion.  While it may appear to the application that each distinct column value points to a whole table, in reality the column points to a set or rows within the store table.  The store table is common to all of the columns that contain this pointer structure, and the store table contains a special OID that points to the "owner" of the row in the parent table (Figure 6.6)

Figure 6.6 The physical implementation of a store table

While the idea of nesting tables within tables is an excellent method for modeling hierarchical structures, there is still some question about the application of this data structure to real-world data models.  The one nice feature of nested tables is that a single select statement will return all of the applicable rows in the store table, thereby simplifying the query.  Since the nested table is really just another table with the same column structure for each row, there is still some question about whether it is better to use this structure or to simply create another table.




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.