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:

SELECT
      student_full_name,
      course_name,
      course_date,
      grade
FROM
      student, grade, course
WHERE
      student_last_name = 'Burleson'
      AND
      student.student_ID = grade.student_ID
      AND
      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.

CREATE TYPE
   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; 

CREATE TABLE
   COURSE (
       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.

 

---