Administration of Oracle Object Tables

As mentioned in Chapter 4, Oracle8 and Oracle8i introduced a number of new table objects or types that DBAs have to worry about. Probably the most basic of these is the straight object table. But Oracle8 also introduced nested tables, varray types, REF values, and the entire concept of types.

Oracle has enhanced the concept of TYPE to more closely resemble the full Object-oriented paradigm, adding limited inheritance and polymorphism. Thus, Oracle and beyond have become truly object-relational in context. This chapter addresses these new Oracle8 and Oracle8i object tables, the modifications and additions in Oracle, and the types used to build them.

The rowid Concept in Oracle

Under Oracle8, the concept of OBJECTS was introduced, then expanded in Oracle8i. In 9i, these OBJECTS identifiers have been added to the rowid, giving an EXTENDED ROWID format, which is 10 bytes long, versus the 6 bytes that was the norm in Oracle7. The Oracle8 and Oracle8i rowid is a  VARCHAR2 representation of a base-64 number (remember that base-64 starts at B, not A; A is used to designate 0). The rowid is displayed as follows:

OOOOOO.FFF.BBBBBB.SSS 

where:
   OOOOOO is the data object number.
   FFF is the relative file number.
   BBBBBB is the block number.
   SSS is the slot number.
       An example of the new rowid would be:
AAAAVJAAEAAAABEAAA 

where:
   AAAAVJ is the data object number.
   AAE is the relative file number.
   AAAABE is the block number.
   AAA is the slot number.

The new parts of the rowid are the object number and the relative file number. Multiple files can have the same relative file number because Oracle assigns the number based on a tablespace. This means that these numbers are unique only per tablespace, which means that you cannot derive an absolute address directly from the new rowid. This also means that the new rowid addressing scheme is tablespace-relative.

The new rowid contains the data object number, which increments when an object's version changes. An object's version changes whenever a table is truncated or a partition is moved. The data object number is not related to the object ID.

The rowid can be easily manipulated with the DBMS_ROWID package.

* Old rowids (called restricted rowids) will be automatically converted to the new format if:

* You use export/import to move data.

* You use the migration utility.

* You use the ODMA to upgrade to 9i.

If you used rowids in your Oracle7 application and stored them as columns in other tables, then these columns will have to be manually changed to the new format using the DBMS_ROWID package. If a column in a table has been designated as a datatype of rowid, it will be altered to accept the new format during migration; this will not affect the data in the column.

The Concept of Object Identifiers (OID) in Oracle

Oracle8 introduced the concept of objects as they apply to Oracle. Each object in Oracle8 and beyond has a 16-byte object identifier. This object identifier (OID) is guaranteed to be globally unique across all databases in your environment.

It is a 16-byte, base-64 number that allows for a ridiculously high number of objects to be identified (in the peta-region of countability?a quadrillion? The maximum is 2**128 (340,283,266,920,938,463,463,374,607,431,768,211,456)).

 The OID, as well as being a globally unique identifier, is used to construct REFs for nested tables. In some statements involving nested tables, if you don't specifically tell Oracle to bring back the UNREF value (i.e., translate the OID and get the data), you will get a 42- to 46-byte REF number, as useful as that sounds. The number itself is simply an identifier and contains no ?intelligence? such as would be in a rowid. The REF value can vary in size between 42 and 46 bytes of internal storage.

Oracle Object Types

Before we can begin to discuss objects in Oracle8, Oracle8i, and Oracle, we have to address object types. As their name implies, an object type is used to define an object. To bridge the gulf between Oracle7 and Oracle, you can think of an object type as a predefined row that you can then use to build Oracle objects.

Before you can build an object table in Oracle8, 8i or 9i, you must define its types. A table can consist of single columns, types, or a mix, as well as varrays (which are discussed later in this chapter). There are only object TYPEs. Under Oracle8i, the AUTHID clause was added to the CREATE TYPE command.

In Oracle, the CREATE TYPE command has been extended with the clauses required to support inheritance. Also in Oracle, type bodies support overloading, thus providing polymorphism.  Let's look at a simple type definition and how it is used to build an Oracle object table. The only instances the AS OBJECT clause is not used is with varray, TABLE, or incomplete type specifications.

Suppose we want to define a real-world situation, such as a collection of pictures. What are the attributes of pictures? How about topic, date and time taken, photographer, negative number, picture number, and the on-disk location of the actual image? Let's look at the type required to implement this structure:

Notice something odd? What are the person_t and bfile columns? The person_t is another type definition, ?person type,? which includes:

CREATE TYPE person_t AUTHID CURRENT_USER
AS OBJECT ( first_name  varchar2(32),
         last_name    varchar2(32),
         middle_init  varchar2(3),
         sex          char(1),
         address      address_t);      

Do you see the value of these type definitions--and the nightmare? To make things more complex, METHODS can be declared in the type definition; then a type body must also be created. The BFILE definition was a new BLOB (binary large object) definition added in Oracle8 that specifies it as a LOB-stored external to the database. 

Anyway, back to the example: Now we want to create our picture object. This becomes:

CREATE TABLE pictures OF picture_t (
CONSTRAINT pk_pictures PRIMARY KEY (negative#,picture#)
USING INDEX TABLESPACE indexes);

When creating types, the order of creation is critical, unless we use incomplete types (covered in the next section); in the preceding example, the creation order must be: address_t, person_t, picture_t, and then the table pictures. You create from the most atomic-level type to the most inclusive.

The AUTHID CURRENT_USER clause tells the kernel that any methods that may be used in the type specification (in the above example, none) should execute with the privilege of the executing user, not the owner. The default option for the AUTHID is DEFINER, which would correspond to the behavior in pre-Oracle8i releases, where the method would execute with the privileges of the user creating the type.

One of the nice things about this concept of types is that it allows us to create these type primitives, such as person_t, and then use them to define objects. If you must add an attribute to a TYPE, you use the:

command; the new attribute is added to the TYPE and all dependent TYPEs, as well as any tables that use the TYPE. This promulgation of changes is new in Oracle; it was not available in previous versions.

You can specify either CASCADE or INVALIDATE, but if there are dependent TYPEs of TABLEs, you must specify either one or the other. CASCADE cascades the change to all dependent TYPEs and TABLEs, while INVALIDATE marks them as INVALID. Added attributes are placed at the end of the TYPE attribute list. Let's examine a very simple example of this altering of types. Using the PICTURES table, we have already created, let's do an insert without making any changes to any of the TYPEs:

SQL> INSERT INTO PICTURES VALUES(1,1,'Test',SYSDATE,
  1  PERSON_T('Michael','Ault','R','M',
  2  ADDRESS_T('','','Mockingbird Lane','1313','Altoona','GA',
  3  30000,9999,'US')),NULL); 

1 row created.

If you haven't used TYPEs before, you may be wondering what the heck the PERSON_T( ) and ADDRESS_T( ) calls are for. These are called constructors, and are created for each TYPE unless you specify NOT INSTANTIABLE as an argument with your CREATE TYPE or ALTER TYPE command. All TYPEs are INSTANTIABLE by default. A constructor method takes all IN values, one for each attribute of the TYPE. If an ATTRIBUTE (such as ADDRESS) is another TYPE, then the call to that constructor is embedded in the call to the master TYPE constructor. Now let's alter the PERSON_T TYPE to include a new column IDENTIFYING_MARKS as a VARCHAR2(255) datatype.

SQL> ALTER TYPE person_t ADD ATTRIBUTE Identifying_marks VARCHAR2(255) CASCADE;
 
Type altered.

       Let's try the same type of insert as before, without the new attribute:

SQL> INSERT INTO PICTURES VALUES(1,2,'Test',SYSDATE,
  1  PERSON_T('Michael','Ault','R','M',
  2  ADDRESS_T('','','Mockingbird Lane','1313','Altoona','GA',
  3  30000,9999,'US')),NULL);

PERSON_T('Michael','Ault','R','M'
*
ERROR at line 2:
ORA-02315: Incorrect number of arguments for default constructor

Since we have added an attribute to PERSON_T, it will be placed after the ADDRESS_T attribute, so we must add a value or a NULL insert at that point in our INSERT command:

SQL> INSERT INTO PICTURES VALUES(1,2,'Test',SYSDATE,
  1  PERSON_T('Michael','Ault','R',
  2  ADDRESS_T('','','Mockingbird Lane','1313','Altoona','GA',
  3  30000,9999,'US'),'None'),NULL); 

Notice the addition of the value 'None' after all of the ADDRESS_T constructor call.

To create a TYPE hierarchy, the UNDER clause is specified in the CREATE TYPE?AS OBJECT command. An example would be if we wanted to create a subtype of EMPLOYEE under the existing PERSON_T TYPE we created in the previous example:

CREATE TYPE employee_t AUTHID CURRENT_USER
UNDER person_t (
Emp_Id NUMBER,
Dept_Id NUMBER,
Hire_date DATE);

A subtype can only be created on a TYPE that is not a FINAL TYPE. Unless the clause NOT FINAL is specifically added to a CREATE TYPE command, or it is specified through an ALTER TYPE command, then a TYPE is considered to be FINAL. To add the TYPE EMPLOYEE_T as a subtype to our PERSON_T TYPE, we will need to ALTER the PERSON_T TYPE to NOT FINAL. Let's look at an example of this:

SQL> ALTER TYPE person_t NOT FINAL CASCADE; 

Type altered. 

SQL> CREATE TYPE employee_t AUTHID CURRENT_USER
  1  UNDER person_t (
  2  emp_id NUMBER,
  3  dept_id NUMBER,
  4  hire_date DATE);
  5  / 

Type created.

But what exactly does this do for us? Any subtype inherits the attributes of its parent TYPE. So instead of just containing the attributes emp_id, dept_id and hire_date, the EMPLOYEE_T TYPE also contains all of the attributes of its parent TYPE PERSON_T:

SQL> DESC employee_t Employee_t extends SYSTEM.PERSON_T

Name              Null?     Type
----------------- --------- -------------------
FIRST_NAME                  VARCHAR2(32)
LAST_NAME                   VARCHAR2(32)
MIDDLE_INIT                 VARCHAR2(3)
SEX                         CHAR(1)
ADDRESS                     ADDRESS_T
EMP_ID                      NUMBER
DEPT_ID                     NUMBER
HIRE_DATE                   DATE

Notice anything odd? The attribute we added, IDENTIFYING_MARKS, is not shown. However, the attribute does show up if you do a describe on a table created with EMPLOYEE_T, and must be allowed for in any INSERT into a table created from EMPLOYEE_T. An example would be:

SQL> DESC EMPLOYEES 

Name              Null?     Type
----------------- --------- -------------------
FIRST_NAME                  VARCHAR2(32)
LAST_NAME                   VARCHAR2(32)
MIDDLE_INIT                 VARCHAR2(3)
SEX                         CHAR(1)
ADDRESS                     ADDRESS_T
IDENTIFYING_MARKS           VARCHAR2(255)
EMP_ID                      NUMBER
DEPT_ID                     NUMBER
HIRE_DATE                   DATE

SQL> INSERT INTO employees (EMPLOYEE_T('Michael','Ault','R','M',
  1  ADRESS_T('','','Mockingbird Lane','1313','Altoona','GA',30000,
  2  9999,'US'),'None',1,1,SYSDATE));

1 row inserted.

Also note that you don't specify the parent TYPE constructor, just the child TYPE constructor, in this case, EMPLOYEE_T. Any contained types such as ADDRESS_T must still have their constructor methods explicitly called.

This extension to the definitions of TYPEs will enable the simplification of some of the more complex database design into a more manageable real-world view. We will take a deeper look at types when we discuss tables, nested tables, and varrays later in this chapter.