An object type is like a tree structure, where the branches represent the attributes. Attributes that are objects sprout subbranches with their own attributes.

Ultimately, each branch ends at an attribute that is a built-in type; such as NUMBER, VARCHAR2, or REF, or a collection type, such as VARRAY or nested table. Each of these leaf-level attributes of the original object type is stored in a table column.

Leaf-level attributes that are not collection types are called the leaf-level scalar attributes of the object type.

The following topics relate to the discussion of object tables and relational tables in "How Objects are Stored in Tables ".

In an object table, Oracle database stores the data for every leaf-level scalar or REF attribute in a separate column.

When you retrieve or change attributes of row objects in an object table, the database performs the corresponding operations on the columns of the table. Accessing the value of the row object itself invokes the default constructor for the type, using the columns of the object table as arguments and produces a copy of the object.

The database stores the system-generated object identifier in a hidden column. The database uses the object identifier to construct REFs to the object.

When a table (relational table) is defined with a column of an object type, the database adds hidden columns to the table for the leaf-level attributes of the object type.

Each object-type column also has a corresponding hidden column to store the NULL information for the column objects (that is, the atomic nulls of the top-level and the nested objects).

A substitutable column or object table has a hidden column not only for each attribute of the object type of the column but also for each attribute added in any subtype of the object type.

Hidden columns store the values of those attributes for any subtype instances inserted in the substitutable column.

Besides the type-discriminant column and the null-image column, the following are associated with a substitutable column of person_typ, created by Example 8-1

• A hidden column for each of the attributes of person_typ: idno, name, and phone

• Hidden columns for attributes of the subtypes of person_typ

Thus, the following might be associated with a substitutable column of person_typ: the attributes dept_id and major (for student_typ) and number_hours (for part_time_student_typ).

When you create a subtype, the database automatically adds hidden columns for new attributes in the subtype to tables containing a substitutable column of any of the ancestor types of the new subtype. These retrofit the tables to store data of the new type. If, for some reason, the columns cannot be added, creation of the subtype is rolled back.

When you drop a subtype using DROP TYPE with the VALIDATE option, the database automatically drops hidden columns for attributes unique to the subtype that do not contain data. Errors are raised if these columns contain data.

Example 8-1 creates types needed for related examples.

-- drop any of these objects created for Ex.7-10
CREATE TYPE person_typ AS OBJECT (
  idno           NUMBER,
  name           VARCHAR2(30),
  phone          VARCHAR2(20),
  MAP MEMBER FUNCTION get_idno RETURN NUMBER )
  NOT FINAL;
/
CREATE TYPE BODY person_typ AS
  MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
  BEGIN
    RETURN idno;
  END;
END;
/
CREATE TYPE student_typ UNDER person_typ (
    dept_id NUMBER,
    major VARCHAR2(30))
NOT FINAL;
/
CREATE TYPE part_time_student_typ UNDER student_typ (
  number_hours NUMBER);
/
CREATE TYPE employee_typ UNDER person_typ (
    emp_id NUMBER, 
    mgr VARCHAR2(30));
/
CREATE TABLE person_obj_table OF person_typ;  // an object table
INSERT INTO person_obj_table 
  VALUES (person_typ(12, 'Bob Jones', '650-555-0130'));
INSERT INTO person_obj_table 
  VALUES (student_typ(51, 'Joe Lane', '1-650-555-0140', 12, 'HISTORY'));
INSERT INTO person_obj_table 
  VALUES (part_time_student_typ(52, 'Kim Patel', '1-650-555-0135', 14,
          'PHYSICS', 20));

When the database constructs a REF to a row object, the constructed REF is made up of the object identifier (OID), some metadata of the object table, and, optionally, the ROWID.

The size of a REF in a column of REF type depends on the storage requirements associated with the column, as follows:

• If the column is declared as a REF WITH ROWID, the database stores the ROWID in the REF column. The ROWID hint is ignored for object references in constrained REF columns.

• If a column is declared as a REF with a SCOPE clause, the column decreases due to the omission of the object table metadata and the ROWID. A scoped REF is 16 bytes long.

The rows of a nested table are stored in a separate storage table. Each nested table column has a single associated storage table. The storage table holds all the elements for all of the nested tables in that column. The storage table has a hidden NESTED_TABLE_ID column with a system-generated value that lets Oracle database map the nested table elements back to the appropriate row.

You can speed up queries that retrieve entire collections by making the storage table index-organized. Include the ORGANIZATION INDEX clause inside the STORE AS clause.

A nested table type can contain objects or scalars:

• If the elements are objects, the storage table is like an object table: the top-level attributes of the object type become the columns of the storage table. However, you cannot construct REFs to objects in a nested table because a nested table row has no object identifier column.

• If the elements are scalars, the storage table contains a single column called COLUMN_VALUE that contains the scalar values.

All the elements of a VARRAY are stored in a single column. Depending upon the size of the array, it may be stored inline or in a BLOB.