Establishing Data Relationships to Other Tables with Pointers Oracle Tips by Burleson Consulting

We have now violated first normal form and upset Chris Date, let's carry this argument one step further.  If it is possible to allow for repeating values within a table cell, why not a reference to an entirely new table?  Imaging a database which allows nesting of tables within tables such that a single cell of one table could be a pointer to another whole table.  While this concept may seem very foreign on the surface, it is not too hard to understand if we consider that many real-world "things”, or objects, are made up of sub-parts. 

A special note on pointers.  In order to establish data relationships between database entities, a pointer must be persistent, unique and non-expiring.  In pre object/relational databases a relational Row ID was used to identify a row.  Unfortunately, this Row ID was the number of the physical database block and row displacement within the data block.  As such, a relational row could “move” to another block as a result of routine maintenance, or be deleted.  To address this problem, the object/relational vendors have devised a way to create OIDs for each row that will always uniquely identify a row, regardless of the row’s status.  For example, a database row could be deleted, and the OID that was associated with that row will never be re-used by the database software.

To create a table with OIDs, a data type must be created that contains all of the necessary row information.  In the following example, assume that the data type customer_stuff contains all of the required data structures for a customer table.  In a traditional relational database, we could create the table like this:

CREATE TABLE customer (customer_data         customer_stuff);

With the introduction of OIDs, the table creation syntax has changed slightly.  The following example will create the exact same table as our earlier example, with the exception that the table will contain an OID for each row that is created within the customer table:

CREATE TABLE customer OF customer_stuff;

It has always been a shortcoming of the relational model that only atomic things could be directly represented, and relational "views" were required to assemble aggregate objects. The object technology professors always used to make fun of the relational model's inability to represent aggregate objects, stating that it would be like disassembling your car each evening when you are done driving it, only to re-assemble your car each time that you want to drive it.  At last, nested abstract data types allow Oracle users to represent real-world "things" without resorting to views.

Let's take a look at how this type of recursive data relationship might be represented within an object/relational database.  The following SQL creates a TYPE definition for a list of orders.  This list of pointers to orders might become a column within an Oracle table.

CREATE TYPE order_set
    AS TABLE OF order;

CREATE TYPE CUSTOMER_STUFF (
    customer_id                 integer,
    customer_full_name    full_name,
    customer_full_address customer_address,
    . . . 
    order_list                      order_set);

CREATE TABLE customer OF customer_stuff;

Here we see the new style of table creation syntax.  Both of the following table declarations are identical, except that the CREATE TABLE OF syntax will establish the Object ID’s so that other tables may contain references to rows in the customer table.

Without OID’s:             CREATE TABLE customer (cust_data   customer_stuff);
With OID’s:                 CREATE TABLE customer OF customer_stuff;

In either case, we have now defined a pointer column called order_list in the customer table.  This pointer will point to a list of pointers.  Each cell of this list will contain pointers to rows in the order  table.  (Figure 6.2)

Figure 6.2  Pointers to other database rows.

While figure 6.2 shows the pointer structure as it would look conceptually, the object/relational databases  must utilize internal arrays to implement these repeating groups of pointers.  In Oracle, the popular object/relational databases, variable-length arrays are used to represent this structure (Figure 6.3)

Figure 6.3  - The Oracle representation of repeating lists of pointers to rows.

Here we have nested a list of pointers within each column, and that each of the cells within a column contain a list of pointers  to rows in the order table.  Using object-oriented SQL extensions, we can now "pre-join" with the order table to add the three orders for this customer;

 UPDATE customer
    SET order_list (
        select REF(order)    /* this returns the OIDs from all order rows */
        from order
        WHERE
        order_date = SYSDATE
        and
        order.customer_ID = (123)
    )

Here we see the use of the REF operator, which returns the “reference”, or OID of the requested rows.  This is similar to the retrieval of Row ID’s in a relational database, except that we are now storing the row information inside a relational table.

Now let’s take a look at how we can navigate between tables without having to join tables together.  Remember from out earlier discussion, that the object/relational model provides for two ways to retrieve data from our database.  We can use SQL to specify the desired data and let the SQL optimizer choose the access path, or we can navigate our database, one row at a time, and gather the required information.

SELECT DEREF(order_list)
from CUSTOMER
where
customer_id = 123;  /* this will return 3 rows in the order table */

The important point in the above example is that we have navigated between tables without ever performing a SQL join.  Consider the possibilities.  We would NEVER need to embed the foreign key for the customer table in the order record, since we could store the pointers in each customer row.  Of course, we would never be able to perform a relational join between the customer and order tables, but this would not really make any difference as long as we have maintained the ability to navigate between customers and orders with pointers.

Of course, these are one-way pointers from customer to orders, and we would not have a method to get from the order table to the customer table unless we embedded a pointer to point to the row that contains the customer for each order.  We could do this by creating an “owner” reference inside each order row that would contain the OID of the customer who placed the order.

A nineteenth century philosophy professor named Augustus De Morgan create an interesting poem to demonstrate this fundamental truth:

 Great fleas have little fleas
 upon their backs to bite 'em
 And little fleas have lesser fleas,
 and so ad-infinitum

 The great fleas themselves in turn
 has greater fleas to go on,
 while these again have greater still
 and greater still,
 and so on.

Now, let's take a look how this type of recursive data relationship might be represented in an object/relational database such as Oracle:

CREATE TYPE order_set
    AS TABLE OF order;

CREATE TABLE CUSTOMER (
    customer_id                 integer,
    customer_full_name    full_name,
    customer_full_address customer_address,
    . . . 
    order_list                      order_set);

Here we have nested the order table within the customer table!  So, where do we go from here?  How do we populate this new structure?  Let's take a look at how this table might be populated:

INSERT INTO customer VALUES (
    full_name ('ANDREW','S.','BURLESON),
    customer_address('246 1st st.','Minot, ND','74635'); 

Now, we could add three orders for this customer;   

INSERT INTO ORDER values order_id, customer_id, order_date (
    9961
    123,
    SYSDATE); 

INSERT INTO ORDER values order_id, customer_id, order_date (
    9962
    123,
    SYSDATE);

INSERT INTO ORDER values order_id, customer_id, order_date (
    9962
    123,
    SYSDATE);

Now, here comes the best part.  We can now "pre-join" the customer table with the order table to add the three orders for this customer

 UPDATE customer
    SET order_list (
        select REF(order)    /* OID reference */
        from order
        WHERE
        order_date = SYSDATE
        and
        order.customer_ID = (123)
    )

So, what have we got here?  It appears that the order_list entry in the customer table will contain "pointers" to the three orders that have been placed by this customer.   As such, we are able to reference these pointers without having to perform a relational join.

SELECT DEREF(order_list)
from CUSTOMER
where
customer_id = 123;  /* this will return 3 rows in the order table */

This query will return a pointer to the three rows in the order table.  It should then be a simple matter to "de-reference" these pointers to retrieve the contents of the order table.  Depending on the vendor implementation of the SQL, it might look something like this:

SELECT DEREF(order_list)
FROM customer
where customer_ID = 123;

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.