 |
|
Oracle Tips by Burleson |
Creation of Object Tables
Object tables differ from relational tables
in that an object table has an object identifier that is
system-generated and -maintained.
Object Table CREATE Command Definition
Oracle8, Oracle8i, and Oracle allow the
creation of OBJECT tables as well as relational tables. An object
table is made up of object types or a combination of standard and
object types. Prior to Oracle8i, an object table could not be
partitioned. Object tables have OIDs and can be used for a REF call.
To use a standard nonobject relational table in a REF, it must be
masked with an object view.
The details of the command to create an
object table are contained in the SQL reference in the Web
documentation on the Oracle Technet site. Here is a simplified
version:
See Code Depot
The object_properties clause includes column
and attribute information, as well as default value, constraint, and
table constraints, along with any REF information. A REF is similar
to a foreign key reference in straight relational tables, but
instead of holding an actual value used to tie back to the parent
table, it holds a pointer (in the form of an OID) to the actual row
in the parent table.
The [[NOT] SUBSTITUTABLE AT ALL LEVELS]
clause indicates that the object table types are or are not
substitutable at all levels. If a column is substitutable, it means
that if a TYPE is a subtype or is somewhere in a chain of subtypes,
any type or supertype for which this type is a subtype can be
inserted to its place in the table simply by calling the supertype
or type’s constructor rather than the subtype constructor. For
example, assume we have the PERSON_T ? EMPLOYEE_T ? PARTTIME_EMP_T
type hierarchy, where PERSON_T is the main type, EMPLOYEE_T is a
subtype of PERSON_T, and PARTTIME_EMP_T is a subtype of the
EMPLOYEE_T type. This would indicate that PARTTIME_PERSON_T has its
own attributes, plus all those in EMPLOYEE_T, including any the
EMPLOYEE_T inherited from PERSON_T. If a table were defined using
the PARTTIME_EMP_T type, you could also use the EMPLOYEE_T or
PERSON_T constructor methods to insert a subset of data to the same
column location. Using NOT SUBSTITUTABLE turns off this capability.
The ON COMMIT clause is used only if the
object table is a GLOBAL TEMPORARY object table.
The OID_clause is used to tell Oracle
whether or not the OID is SYSTEM GENERATED (the default) or the
PRIMARY KEY. The OID_index_clause specifies how the OID is to be
indexed, and specifies the storage parameters for the index.
The physical_properties and table_properties
clauses are the same as for a relational table.
www.oracle-script.com |
