BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










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:

     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:


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.

     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
values (
    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_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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter