Oracle Database Tips by Donald Burleson
To understand multi-dimensional
pointers, let's begin with a simple example of a natural hierarchy
(Figure 6.7). In this example, we see that each university has many
departments, each department offers many courses, and that each course
offers many sections. This is a natural descending hierarchy of
Figure 6.7 A Natural Hierarchy of data
Let's take a look at how we could create a data
structure that would embed pointers to establish these relationships.
From this definition, we see that the department
table consists of the department name, followed by from one to 20 pointers to
courses. The course table contains the course number and course name followed
by from zero to ten pointers to sections and one "owner" pointer, pointing to
the department's row. The section table consists mostly of data items, except
for the pointer to the course row.
As we know there are many options for modeling
this type of descending one-to-many data relationship. In a ?vanilla?
relational database, each entity would exist as a table, with the primary key of
the owner table copied into the member table. But there is an alternative to
modeling this structure in the object/relational model. Let's take a look at
how a hierarchical data structure might be implemented in Oracle:
CREATE TYPE full_name (
CREATE TYPE section_type (
CREATE TABLE section OF section_type;
CREATE TYPE section_array AS VARRAY(10) OF
CREATE TYPE course_type (
CREATE TABLE course OF course_type;
CREATE TYPE course_array as VARRAY(20) OF
CREATE TYPE dept_type (
CREATE TABLE department OF dept_type;
This is what this data structure would look like
Figure 6.8 An implementation of
multidimensional row pointers
Here we see that the pointers allow fast access
from owner to member in the hierarchy. But where are the "owner" pointers? As
it turns out, we must first define the hierarchy before we have the necessary
definitions to include the pointers. Let's add them using the ALTER TYPE
ALTER TYPE section_type
ADD COLUMN course_owner_pointer course_type;
ALTER TYPE course_type
ADD COLUMN department_owner_pointer department_type;
We have now created a two-way pointer structure,
such that all owner rows in the hierarchy point to their member rows, while all
member rows will point up to their owners. However, we must bear in mind that
these are only data structures; it is up to the programmer, to assign these
pointers when the rows are created.
In a sense, this data structure is the
object/relational equivalent to the **char data structure. Essentially, a
**char data structure is a structure where we have a pointer to an array of
pointers to characters. In Oracle, the department has an array of pointers to
curses, which, in turn, contain arrays of pointers to sections.
But how do we query these pointers with SQL? In
order to accommodate the new object features, most object/relational vendors are
implementing the CAST and the MULTISET extensions to SQL. For example, here is
what the query to populate the student_list internal table:
GRADE.course_name = ?CS101?
As we can see, the new SQL extensions are rather
foreign to those who are accustomed to pure relational syntax.