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
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
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:
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
( Street VARCHAR2(80),
Zip VARCHAR2(10) );
Once defined, we can treat
full_mailing_address_type as a valid datatype and use it to
Now that the Oracle table is defined, we can reference
full_mailing_address_type in our SQL just as if it were a
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
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
Next let’s look at the most revolutionary change to
Oracle8 SQL, the ability to place repeating groups within a table
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.