Oracle9i offers a variety of data structures to create robust database systems. Oracle supports the full use of binary large objects (BLOB), non–first-normal-form table structures (VARRAY tables), nested tables, and object-oriented table structures. It even treats flat files as if they were tables within the Oracle database.

It is a challenge to many Oracle design professionals to know the proper times to use these Oracle data model extensions. This article provides a brief review of advanced topics and how they are used to design high-performance Oracle databases.

Data model extension capabilities
The new Oracle data model extensions provide the following capabilities:

  • ·        Removing unnecessary table joins—This is achieved by deliberately introducing redundancy into the data model. Queries that required complex and time-consuming table joins can now be retrieved in a single disk I/O operation.
  • ·        Modeling real-world objects—It is no longer a requirement for the relational database designer to model complex objects in their most atomic components and rebuild them at runtime. Using Oracle’s object-oriented constructs, real-world objects can have a concrete existence. Oracle can use arrays of pointers to represent these complex objects (see Figure A).
  • ·        Coupling of data and behavior—One of the important constructs of object orientation is the tight coupling of object behaviors with the objects themselves. In Oracle, member methods can be created upon the Oracle object. All processes that manipulate the object are encapsulated inside Oracle’s data dictionary. This functionality has huge benefits for the development of all Oracle systems. Prior to the introduction of member methods, each Oracle developer was essentially a custom craftsman writing custom SQL to access Oracle information. By using member methods, all interfaces to the Oracle database are performed using pretested methods with known interfaces. Thus, the Oracle developer’s role changes from custom craftsman to more of an assembly-line coder. You simply choose from a list pf prewritten member methods to access Oracle information.

Figure A
Oracle models complex objects inside the database.

Object orientation and Oracle
Oracle9i offers numerous choices for the introduction of object-oriented data model constructs into relational database design. Oracle9i offers abstract data types, the ability to dereference table row pointers, and limited polymorphism and inheritance support. In Oracle9i, data model constructs that are familiar to the C++ or Smalltalk programmer can be translated directly into an Oracle design structure. In addition, Oracle supports abstract data typing whereby you create customized data types with the strong typing inherent in any of the standard Oracle data types like CHAR, VARCHAR, NUMBER, and DATE.

For example, Listing A is an Oracle8 table created with abstract data types and a nested table.

Next, we use extensions to standard Oracle SQL to update these abstract data types as shown in Listing B.

Oracle nested tables
Using the Oracle nested table structure, subordinate data items can be directly linked to the base table by using Oracle’s newest construct: object ID (OID). One of the remarkable extensions of the Oracle database is the ability to reference Oracle objects directly by using pointers as opposed to relational table joins. Proponents of the object-oriented database approach often criticize standard relational databases because of the requirement to reassemble an object every time it is referenced. (They make statements such as “It doesn’t make sense to dismantle your car every time you are done driving it and rebuild the car each time you want to drive it.”)

Oracle has moved toward allowing complex objects to have a concrete existence. In order to support the concrete existence of complex objects, Oracle introduced the ability to build arrays of pointers with row references directly to Oracle tables. Just as a C++ program can use the char** data structure to have a pointer to an array of pointers, Oracle allows similar constructs whereby the components of the complex objects reside in real tables with pointers to the subordinate objects. At runtime, Oracle simply needs to dereference the pointers, and the complex object can be quickly rebuilt from its component pieces.

A nested table example
In this example, a nested table is used to represent a repeating group for previous addresses. Whereas a person is likely to have a small number of previous employers, most people have a larger number of previous addresses. First, we create a type using our full_mailing_address_type:
create type prev_addrs as object (prior_address
full_mailing_address_type );

Next, we create the nested object:
create type nested_address as table of prev_addrs;

Now, we create the parent table with the nested table as shown in Listing C.

A nested table appears as a part of the master table. Internally, it is a separate table. The store as clause allows the DBA to give the nested table a specific name (see Figure B).

Figure B
An Oracle nested table

In Listing C, the nested_prev_address subordinate table can be indexed just like any other Oracle table. Also, notice the use of the return as locator SQL syntax. In many cases, returning the entire nested table at query time can be time-consuming. The locator enables Oracle to use the pointer structures to dereference pointers to the location of the nested rows. A pointer dereference happens when you take a pointer to an object and ask the program to display the data the pointer is pointing to. In other words, if you have a pointer to a customer row, you can dereference the OID and see the data for that customer. The link to the nested tables uses an Oracle OID instead of a traditional foreign key value.

Performance of Oracle object extensions
To fully understand Oracle advanced design, we need to take a look at the SQL performance ramifications of using object extensions. Overall, the performance of Abstract Data Type (ADT) tables is the same as any other Oracle table, but we do see significant performance differences when implementing varray tables and nested tables:

  • ·        Tables with ADTs—Creating user-defined datatypes greatly simplifies Oracle database design. Doing so also provides uniform data definitions for common data items. There is no downside for SQL performance, and the only downside for SQL syntax is the requirement that all references to ADTs be fully qualified.
  • ·        Varray tables—Varray tables have the benefit of avoiding costly SQL joins, and they can maintain the order of the varray items based upon the sequence when they were stored. However, the longer row length of varray tables causes full-table scans to run longer, and the items inside the varray cannot be indexed. More importantly, varrays cannot be used when the number of repeating items is unknown or very large.
  • ·        Nested tables—Nested tables have the advantage of being indexed, and the repeating groups are separated into another table so as not to degrade the performance of full-table scans. Nested tables allow for an infinite number of repeating groups. However, it sometimes takes longer to dereference the OID to access the nested table entries as opposed to ordinary SQL tables join operations. Most Oracle experts see no compelling benefit of using nested tables over traditional table joins.

The evolution of Oracle9i has provided a huge number of extensions to standard relational database modeling. It is the challenge of all Oracle design professionals to use these Oracle extensions to improve both the performance and maintainability of Oracle databases. Relational professionals can no longer be content with understanding basic relational systems: Successful Oracle designers must master object-oriented concepts, including abstract data typing, nested tables, array tables, and those unique data structure extensions that make Oracle clearly one of the fastest and most robust databases in the marketplace.