Column object storage is similar to storage of an equivalent set of scalar columns that collectively make up the object.

The difference is the additional overhead of maintaining the atomic null values of any noncollection columns objects and their embedded object attributes. These values, called null indicators (or sometimes, null images), specify for every column object, whether or not the column object is null and whether or not each of its embedded object attributes is null.

Note that null indicators do not specify whether the scalar attributes of a column object are null. Oracle uses a different method to determine whether scalar attributes are null.

Consider a table that holds the identification number, name, address, and phone numbers of people within an organization. You can create three different object types to hold the name, address, and phone numbers and an object employee_objtyp that contains the name and address objects. Because each person may have more than one phone number, you need to create a nested table type based on the phone number object type

First, enter the SQL statements in Example 9-1 to create the four object types and a table for phone number objects.

CREATE TYPE name_objtyp AS OBJECT (
  first       VARCHAR2(15),
  middle      VARCHAR2(15),
  last        VARCHAR2(15));
/
CREATE TYPE address_objtyp AS OBJECT (
  street       VARCHAR2(200),
  city         VARCHAR2(200),
  state        VARCHAR2(2),
  zipcode      VARCHAR2(20));
NOT FINAL;
/
CREATE TYPE phone_objtyp AS OBJECT (
  location     VARCHAR2(15),
  num          VARCHAR2(14));
/

CREATE TYPE employee_objtyp AS OBJECT (
  name name_objtyp,
  address address_objtyp);
/

CREATE TYPE phone_ntabtyp AS TABLE OF phone_objtyp;
/

Example 9-2 Creating a Table with Column Objects

CREATE TABLE people_reltab (
  id            NUMBER(4)   CONSTRAINT pk_people_reltab PRIMARY KEY,
  employee      employee_objtyp
  phones_ntab   phone_ntabtyp)
  NESTED TABLE  phones_ntab STORE AS phone_store_ntab;

Figure 9-1 Representation of the people_reltab Relational Table

Description of "Figure 9-1 Representation of the people_reltab Relational Table"

The people_reltab table has two column objects: employee and phones_ntab. The phones_ntab column object is a nested table, a collection type of column object.

The storage for each object in the people_reltab table is that of the attributes of the object plus overhead for the null indicator.

The null indicators for an object and its embedded object attributes occupy one bit each. Thus, an object with n embedded object attributes (including objects at all levels of nesting) has a storage overhead of CEIL(n/8) bytes. There is one null indicator column for each noncollection column object, name_obj and address_obj. The null indicator column length is one byte, as one bit represents the object itself, which translates to CEIL(1/8) or 1.

Since the null indicator is one byte in size, the overhead of null information for each row of the people_reltab table is two bytes, one for each object column.

Every noncollection object has a null indicator column, whether or not the object is FINAL. The columns in these examples are FINAL.

See Also:

Oracle Database SQL Language Reference for more information about CEIL

Row objects are stored in object tables. An object table is a special kind of table that holds objects and provides a relational view of the attributes of those objects. An object table is logically and physically similar to a relational table whose column types correspond to the top level attributes of the object type stored in the object table. The key difference is that an object table can optionally contain an additional object identifier (OID) column and index.

System-generated OIDs are the default for row objects in an object table.

Oracle assigns to each row object a unique system-generated OID, 16 bytes in length, that is automatically indexed for efficient OID-based lookups. The OID column is the equivalent of having an extra 16-byte primary key column. In a distributed environment, the system-generated unique identifier lets Oracle identify objects unambiguously.

The object identifier column is a hidden column that Oracle uses to construct references to the row objects. Oracle provides no access to the internal structure of object identifiers. This structure can change at any time. Applications are only concerned with using object references for fetching and navigating objects.

Oracle allows the option of specifying the primary key value of a row object as its object identifier, if there is a primary key column.

Instead of using the system-generated OIDs, you use a CREATE TABLE statement with this clause, OBJECT IDENTIFIER IS PRIMARY KEY. This specifies that the system use the primary key column(s) as the OIDs of the objects in the table. That way, you can use existing columns as the OIDs of the objects or use application generated OIDs that are smaller than the 16-byte globally unique OIDs generated by Oracle.

You can enforce referential integrity on columns that store references to these row objects in a way similar to foreign keys in relational tables.

Primary-key based identifiers make it faster and easier to load data into an object table. By contrast, system-generated object identifiers need to be remapped using some user-specified keys, especially when references to them are also stored.

If you use system-generated OIDs for an object table, Oracle maintains an index on the column that stores these OIDs. A system-generated OID requires extra storage space for this index and an extra 16 bytes of storage for each row object.

However, if each primary key value requires more than 16 bytes of storage and you have a large number of REFs, using the primary key might require more space than system-generated OIDs because each REF is the size of the primary key.