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 







Implications of Pointers for Database Design

Oracle Database Tips by Donald Burleson

So, we can now see that the ability to support abstract data types provides us with a lot of very powerful features, including:

1. The ability to store repeating groups within a cell of a table.

2. The ability to "nest" tables within tables.

3. The ability to provide pointer-based navigation for relational data.

4. The ability to represent aggregate objects.

Consider the ramifications for item pointer-based navigation to relational data.  If we are allowed to bypass SQL, imagine the possibilities!  With Oracle we could navigate a data model without having the overhead of joining tables together.  More important, we now have the ability to represent "complex" objects.  This means that we can pre-create objects that are composed of sub-objects without having to build-them each time that we want to see them.  Since the aggregate objects have an independent existence, we can attach methods to these objects.  In addition, we can also save database overhead by pre-creating complex database objects, having them instantly available to the database.  To illustrate this concept, let's create an order_form for a customer.  When a customer places an order, they telephone the order department and indicate the following:

  • Their name, address and payment method

  • Their choices of items, and the quantities for each item

In a traditional relational database we would populate three tables to represent this relationship.  They would be the CUSTOMER, ORDER, ORDER_LINE, and ITEM tables.  Creating the order form would involve the following SQL:

   order_line.quantity_ordered * item.item_price
   order.customer_ID = customer.customer_ID
   order.order_ID = order_line.order_ID
   order_line.item_ID = item.item_ID
   customer.customer_ID = 123;

As we can clearly see, the re-creation of the data for an order form would involve a four-way table join.  Using the object-relational features of abstract data types ADTs), we could define a new object called order_form that would contain all of the pointers to the various components that comprise the order form.  Note that it is never necessary to re-build an order_form object unless items are deleted or added to the order_form.  For example, when the quantity_ordered column in the order_line table is changed, the order_form object will automatically pick-up this new value when the order_line row is de-referenced.

Here's how it might work.  We first need to define a pointer to return the row that corresponds to the customer who has placed the order.  Let's assume that we have already created an ADT for the entire customer row and defined the customer table as follows:

CREATE TABLE customer (customer_data   customer_adt);

We can now create a customer_ref type to hold the pointer to the customer:

CREATE TYPE customer_ref
AS TABLE OF customer_adt;

Since we will only be retrieving one order row, we can do the same thing for the order table row.

CREATE TYPE order_ref
AS TABLE OF order_adt;

Therefore, the first component of the order_form object will be the reference to the customer and order rows:

CREATE TABLE order_form (
   customer     customer_ref,
   order            order_ref);

OK, now we have the customer and order data.  Now we need to establish the pointers to represent the many-to-many relationship between the ORDER and ITEM tables.  Let's start by defining a repeating group of all of the order_lines for each order:

CREATE TYPE item_list
  AS TABLE OF order_line;

Now, that we have defined the item_list, let's take a stab at defining a order_form:

CREATE TABLE order_form (
   customer     customer_ref,
   order        order_ref,
   lines        item_list);

Now, we will need to establish pointers to all of the items that are referenced in the order_line table.  But how can we do this?  We do not know the item_ID numbers that participate in the order until we have retrieved the order_line rows.  In this case we need to establish "owner" pointers inside each order_line row so that we will be able to de-reference the item table.  Let's assume that the line_item table has been defined as shown below to include a reference pointer to the item table:

/* example of an owner pointer */
CREATE TYPE item_ref
   AS TABLE OF item;

CREATE TABLE line_item (
   order_ID         integer,
   item_ID          integer,
   item_pointer     item_ref,
   quantity_ordered integer);

So, let's see how we could display all of the data for the order_form object:

CREATE TABLE order_form (
   customer     customer_ref,
   order            order_ref,
   lines             item_list);

DEREF . . .
DEREF(item_list.item_name),  /* if item is a foreign key in line item */
   DEREF(DEREF(order_line.item_pointer))  /* this returns item data */

Note: Pointer twizzling

The concept of storing a pointer to an array of pointers (**char in C++) has another remarkable feature.  Since we have a pseudo-table with all of the pointers for rows in a table, we can change the sequence of these row references without ever accessing the rows themselves.  In object-oriented parlance this concept is known as pointer "twizzling".

NOTE:  a row can only be referenced if it has been defined with an OID.  To do this the table must be defined as:

CREATE TABLE customer  OF customer_stuff; 

as opposed to: 

customer_data     customer_stuff

These are the SAME table definitions;  the only difference is that the first has a column called OID$ that contains a unique object-ID for each row.




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.