Oracle SQL and Object-Oriented Extensions Oracle Tips by Burleson Consulting

The movement of Oracle towards object-orientation resulted in some significant changes to Oracle SQL syntax. As the Oracle Remote DBA charged with tuning, it is important that we understand these extensions to Oracle SQL syntax and see how they affect performance. The object-oriented extensions of Oracle SQL fall into three areas.

• Abstract Datatypes – Oracle8 allows for the creation of abstract datatypes (sometime known as User-defined datatypes). These datatypes greatly simplify Oracle table structure and help to create uniformity within a database.

• Repeating columns within Oracle tables – Oracle8 now allows non-first-normal form tables with repeating groups of varray data items within a single row.

• Nested tables and SQL – Oracle8 introduced a new data structure whereby a column in a table has a pointer to a nested table.

We will take a closer look at each of these constructs and see how Oracle SQL has been extended to manage this new functionality.

Abstract Datatypes and Oracle SQL

Unlike pre-object release of Oracle (Oracle7), which only provide for primitive datatypes such as INT and VARCHAR, Oracle8 allows for the creation of abstract datatypes (ADTs). Oracle also calls them user defined datatypes or UDTs, and the Oracle documentation calls these constructs by both names. 

Oracle database designers are now beginning to realize that the ability to create abstract  datatypes can greatly simplify their Oracle database design. While abstract datatypes have been used for decades within programming languages, they have been slowly catching-on within the Oracle8 database.

As we may know, Oracle8 implements support for abstract data typing by extending Oracle SQL syntax to allow for a create type definition. At the most basic level, abstract datatypes are nothing more than a collection of smaller, basic datatypes that can be treated as a single entity. (Figure 2)

Figure 2:  An abstract datatype

There are several reasons why ADTs are useful within an Oracle8 database:

• Encapsulation - Because each abstract datatype exists as a complete entity, they include the data definitions, default values, and value constraints. Adding abstract datatypes ensures uniformity and consistency across the whole Oracle database. Once defined, an abstract datatype may participate in many other abstract datatypes, such that the same logical datatype always has the same definition, default values and value constraints, regardless of where it appears in the database.

• Reusability - As a hierarchy of common data structures are assembled, these can be re-used within many definitions, saving coding time and insuring uniformity. For example a datatype called full_mailing_address_type may be included in a customer table, an employee table, and any other table that requires a person’s full address.

• Flexibility - The ability to create real-world data representations of data allows the Oracle database object designer to model the real world, and create robust datatypes that can be uniformly applied to the design.

One of the shortcomings of Oracle7 databases was the inability to model grouped data columns. For example, if we want to select all of the address information for a customer, we are required to select and manipulate street_address, city_address, and zip_code as three separate column statements in our SQL. With abstract data typing, we can create a new datatype called full_mailing_address_type, and manipulate it as if it were an atomic datatype. While this may seem like an advanced new feature, it is interesting to note that pre-relational databases supported this construct, and the ancient COBOL language (Circa 1959 A.D.) had ways to create data "types" that were composed of sub-types. For example, in COBOL, we could define a full address as follows:

 05  CUSTOMER-ADDRESS.
     07 STREET-ADDRESS      PIC X(80).
     07 CITY-ADDRESS        PIC X(80).
     07 ZIP-CODE            PIC X(5).

 We can then manipulate the customer-address as if it were an individual entity:

 MOVE CUSTOMER-ADDRESS TO PRINT-REC.
 MOVE SPACES TO CUSTOMER-ADDRESS.

Fortunately, Oracle8 allows us to do the same type of grouping with their new create type syntax.

CREATE OR REPLACE TYPE full_mailing_address_type AS OBJECT
( Street       VARCHAR2(80),
  City         VARCHAR2(80),
  State        CHAR(2),
  Zip          VARCHAR2(10) );

Once defined, we can treat full_mailing_address_type as a valid datatype and use it to create tables.

CREATE TABLE
   customer
   (
     full_name                  full_name_type,
     full_address               full_mailing_address_type,
   );

Now that the Oracle table is defined, we can reference full_mailing_address_type in our SQL just as if it were a primitive datatype:

insert into
   customer
values (
    full_name_type('ANDREW','S.','BURLESON’),
    full_mailing_address_type('123 1st st','Minot’,’ND','74635');

Next, let’s select from this table. Below we see a very different output than from an ordinary select statement.

SQL> select * from customer;
FULL_NAME(FIRST_NAME, MI, LAST_NAME)
----------------------------------------
FULL_ADDRESS(STREET, CITY, STATE, ZIP)
--------------------------------------------------------------------------------
FULL_NAME_TYPE(‘Andrew’, ‘S’, ‘Burleson’)
FULL_MAILING_ADDRESS_TYPE('123 1st st', 'Minot', 'ND', '74635')

Again, the point of this exercise is to show the difference in syntax for this new construct. The execution plans for these ADTs will be the same as for any other intrinsic datatype, and the introduction of ADTs does not have any effect on SQL performance.

Next let’s look at the most revolutionary change to Oracle8 SQL, the ability to place repeating groups within a table row.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.