Oracle Varray and Nested table tips

For a full treatment of the performance of nested tables vs. varray tables, see my book "Oracle Tuning: The Definitive Reference".

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

The ability of Oracle to support object types (sometimes called user-defined datatypes) has profound implications for Oracle design and implementation. User-defined datatypes will enable the database designer to:

• Create aggregate datatypes - Aggregate datatypes are datatypes that contain other datatypes. For example, you could create a type called FULL_ADDRESS that contains all of the subfields necessary for a complete mailing address.
   
• Nest user-defined datatypes - Datatypes can be placed within other user-defined datatypes to create data structures that can be easily reused within Oracle tables and PL/SQL. For example, you could create a datatype called CUSTOMER that contains a datatype called CUSTOMER_DEMOGRAPHICS, which in turn contains a datatype called JOB_HISTORY, and so on.

One of the new user-defined data types in the Oracle object-relational model is a "pointer" data type. Essentially, a pointer is a unique reference to a row in a relational table. The ability to store these row IDs inside a relational table extends the traditional relational model and enhances the ability of an object-relational database to establish relationships between tables. The new abilities of pointer data types include:

• Referencing "sets" of related rows in other tables - It is now possible to violate first normal form and have a cell in a table that contains a pointer to repeating table values. For example, an EMPLOYEE table could contain a pointer called JOB_HISTORY_SET, which in turn could contain pointers to all of the relevant rows in a JOB_HISTORY table. This technique also lets you pre-build aggregate objects, such that you could preassemble all of the specific rows that comprise the aggregate table.
   
• Allow "pointers" to non-database objects in a flat file - For example, a table cell could contain a pointer to a flat file that contains a non-database object such as a picture in .gif or .jpeg format.
   
• The ability to establish one-to-many and many-to-many data relationships without relational foreign keys - This would alleviate the need for relational JOIN operations, because table columns could contain references to rows in other tables. By dereferencing these pointers, you could retrieve rows from other tables without ever using the time-consuming SQL JOIN operator.

Data model extension capabilities

The Oracle table data model extensions provide the following capabilities:

• Modeling real-world objects - It is no longer required for the relational database designer to model complex objects in their smallest components and rebuild them at run-time. Using Oracle's object-oriented constructs, real-world objects can have a concrete existence just like c++ objects. Oracle can use arrays of pointers to represent these complex objects.
   
• 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.
   
• 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, a member method can be created upon the Oracle object, and 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 pre-tested methods with known interfaces. This way, the Oracle developer?s role changes from custom craftsman to more of an assembly-line coder. You simply choose from a list of prewritten member methods to access Oracle information.

Object orientation and Oracle

Oracle offers numerous choices for the introduction of object-oriented data model constructs into relational database design. Oracle offers the ability to dereference table row pointers, abstract data types, and limited polymorphism and inheritance support. In Oracle, data model constructs used in C++ or Smalltalk programming can be translated directly into an Oracle 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 NUMBER, CHAR, VARCHAR, and DATE.

For example, below is an Oracle table created with abstract data types and a nested table.

Next, we use extensions to standard Oracle SQL to update these abstract data types.

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:, the object ID (OID). One of the remarkable extensions of Oracle is the ability to reference Oracle objects directly by using pointers as opposed joining relational. Proponents of the object-oriented database model criticize standard relational databases because of the requirement to reassemble an object every time it is used. (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.)

Nested and varray tables use internal pointers

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.

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.

A varray table example

Before Oracle8, we would need to represent repeating groups in a table in a very clumsy and non-elegant fashion.

   create table employee (

       full_name               full_mailing_address_type,

       last_name                varchar(40),

       previous_employer_one    varchar(40),

       previous_employer_two    varchar(40),

       previous_employer_three  varchar(40)

   );

We begin by creating a Oracle type to hold the repeating group of prior employers.

CREATE OR REPLACE TYPE
   employer_name
AS OBJECT
(e_name varchar(40))
;

 

CREATE OR REPLACE TYPE
   prior_employer_name_arr
AS
   VARRAY(10) OF employer_name;

Next, we create the employee type, embedding our varray of prior employers.

CREATE OR REPLACE TYPE employee AS OBJECT

(

   last_name               varchar(40),

   full_address            full_mailing_address_type,

   prior_employers         prior_employer_name_arr

);

Next, we create the emp table, using the employee type.

SQL> create table emp of employee;
Table Created.

Now we insert rows into the object table. Note the use of the full_mailing_address_type reference for the ADT and the specification of the repeating groups of previous employers.

insert into emp
values
(
   'Burleson',
   full_mailing_address_type('7474 Airplane Ave.','Rocky Ford','NC','27445'),
   prior_employer_name_arr(
      employer_name('IBM'),
      employer_name('ATT'),
      employer_name('CNN')
   )
);
insert into emp
values
(
   'Lavender',
   full_mailing_address_type('7474 Bearpond Ave.','Big Lick','NC','17545'),
   prior_employer_name_arr(
      employer_name('Oracle'),
      employer_name('Sybase'),
      employer_name('Computer Associates')
   )
);

Next, we perform the select SQL. Note that we can select all of the repeating groups with a single reference to the prior_employers column.

select
   p.prior_employers
from
   emp p
where
   p.last_name = 'Burleson';

 

PRIOR_EMPLOYERS(E_NAME)
-----------------------------------------------------------------
PRIOR_EMPLOYER_NAME_ARR(EMPLOYER_NAME('IBM'), EMPLOYER_NAME('ATT'), EMPLOYER_NAM
E('CNN'))

This output can be difficult to interpret because of the nature of the repeating groups. In the example below, we use a new BIF called table that will flatten-out the repeating groups, re-displaying the information.

column l_name     heading "Last Name"     format a20;

SELECT
   emp.last_name           l_name,
   prior_emps.*
FROM
   emp                      emp,
   table(p.prior_employers) prior_emps
WHERE
   p.last_name = 'Burleson';

Here we see a flattened output from the query, and the single information is replicated onto each table row.

Last Name            E_NAME
-------------------- ----------------------------------Burleson             IBM
Burleson             ATT
Burleson             CNN

Performance of Oracle nested and varray tables

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:
 

• ADT tables - Creating user-defined datatypes simplifies Oracle database design. Doing ADTs 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.
   
• 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.
   
• 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.  Varray tables are also problematic because the non-standard SQL is very clumsy and hard to use:

SQL> SELECT * FROM person;

NAME  DOB
------------------------------ ---------
ADDRESS_V
---------------------------------------------ADDRESS_N
-------------------------------------------------------
Jones 01-JAN-60
ADDRESSES_V('Line 1', 'Line 2', 'Line 3')
ADDRESSES_N('Line 1', 'Line 2', 'Line 3')
 

Conclusion

The evolution of Oracle into an object-relational database has provided a huge number of extensions to the relational database model. It is the challenge of all Oracle design professionals to use these Oracle extensions to improve the performance and maintainability of Oracle databases. Relational professionals can no longer stay content with a basic understanding of relational algebra.