|Oracle® Database Object-Relational Developer's Guide
11g Release 2 (11.2)
Part Number E11822-03
This chapter explains the implementation and performance characteristics of the Oracle object-relational model. Use this information to map a logical data model into an Oracle physical implementation, and when developing applications that use object-oriented features.
This chapter covers the following topics:
This section discusses general storage considerations for various object types.
This section contains the following topics:
You can store objects in relational tables as column objects or in object tables as row objects. Those objects that have meaning outside of the relational database they reside in, should be made referenceable as row objects in an object table. Otherwise, they should be stored as column objects in a relational table.
See "How Objects are Stored in Tables" for an introduction to table storage.
This section describes the following topics:
The storage of a column object is similar to the 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.
Example 9-1 Creating Object Types for Columns in a Relational Table
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; /
See Also:"Design Considerations for Nested Tables" for more information about nested tables
Next, create a table to hold the information about the people in the organization with the SQL statement in Example 9-2. This statement also creates an id for people in the organization.
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
people_reltab table has two column objects:
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,
address_obj. The null indicator column length is one byte, as one bit represents the object itself, which translates to
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
See Also:Oracle Database SQL Language Reference for more information about
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.
This section discusses the two types of object identifiers for row objects in object tables and how they are stored and referenced.
An object identifier (OID) allows the corresponding row object to be referred to and from other objects or from relational tables. A built-in data type called a
REF represents such references.
REFs use object identifiers (OIDs) to point to row objects.
You can use either system-generated OIDs or primary-key based OIDs.
See Also:"Using References to Row Objects"
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
TABLE statement with this clause,
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.
See Also:Oracle Database SQL Language Reference for further information on
You can enforce referential integrity on columns that store references to these row objects in a way similar to foreign keys in relational tables.
Note:Each primary-key based OID is locally (but not necessarily globally) unique. If you require a globally unique identifier, you must ensure that the primary key is globally unique or use system-generated OIDs.
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.
You can compare objects by invoking either a map or order method. A map method converts objects into scalar values while preserving the ordering of the objects. Using a map method is preferable because it allows the system to efficiently order objects.
Note:For any one object type, you can implement either a map or an order method, but not both. Neither are required.
The way objects are mapped has significant performance implications when sorting the objects using
BY processes. An object may need to be compared to other objects many times, and it is much more efficient if the objects can be mapped to scalar values first (the map method). If the comparison semantics are extremely complex, or if the objects cannot be mapped to scalar values for comparison, you can define an order method that, given two objects, returns the ordering determined by the object implementor. Order methods are not as efficient as map methods, so performance may suffer if you use order methods.
Consider an object type
address consisting of four character attributes:
zipcode. Here, the most efficient comparison method is a map method because each object can be converted easily into scalar values. For example, you might define a map method that orders all of the objects by state.
On the other hand, suppose you want to compare binary objects, such as images. In this case, the comparison semantics may be too complex to use a map method; if so, you can use an order method to perform comparisons. For example, you could create an order method that compares images according to brightness or the number of pixels in each image.
If an object type does not have either a map or order method, only equality comparisons are allowed on objects of that type. In this case, Oracle performs the comparison by doing a field-by-field comparison of the corresponding object attributes, in the order they are defined. If the comparison fails at any point, a
FALSE value is returned. If the comparison matches at every point, a
TRUE value is returned. However, if an object has a LOB or
ANYDATA attributes, then Oracle does not compare the object on a field-by-field basis. Such objects must have a map or order method to perform comparisons.
This section discusses considerations when working with
REF contains the following three logical components:
OID of the object referenced. A system-generated OID is 16 bytes long. The size of a primary-key based OID depends on the size of the primary key column(s).
OID of the table or view containing the object referenced, which is 16 bytes long.
Rowid hint, which is 10 bytes long.
Referential integrity constraints on
REF columns ensure that there is a row object for the
REF. Referential integrity constraints on
REFs create the same relationship as specifying a primary key/foreign key relationship on relational data. In general, you should use referential integrity constraints wherever possible because they are the only way to ensure that the row object for the
REF exists. However, you cannot specify referential integrity constraints on
REFs that are in nested tables.
REF is constrained to contain only references to a specified object table. You can specify a scoped
REF when you declare a column type, collection element, or object type attribute to be a
In general, you should use scoped
REFs instead of unscoped
REFs because scoped
REFs are stored more efficiently. Whereas an unscoped
REF takes at least 36 bytes to store (more if it uses rowids), a scoped
REF is stored as just the OID of its target object and can take less than 16 bytes, depending on whether the referenced OID is system-generated or primary-key based. A system-generated OID requires 16 bytes; a primary key based (PK-based) OID requires enough space to store the primary key value, which may be less than 16 bytes. However, a
REF to a PK-based OID, which must be dynamically constructed upon selection, may take more space in memory than a
REF to a system-generated OID.
Besides requiring less storage space, scoped
REFs often enable the optimizer to optimize queries that dereference a scoped
REF into more efficient joins. This optimization is not possible for unscoped
REFs because the optimizer cannot determine the containing table(s) for unscoped
REFs at query-optimization time.
Unlike referential integrity constraints, scoped
REFs do not ensure that the referenced row object exists; they only ensure that the referenced object table exists. Therefore, if you specify a scoped
REF to a row object and then delete the row object, the scoped
REF becomes a dangling
REF because the referenced object no longer exists.
Note:Referential integrity constraints are scoped implicitly.
REFs are useful if the application design requires that the objects referenced be scattered in multiple tables. Because rowid hints are ignored for scoped
REFs, you should use unscoped
REFs if the performance gain of the rowid hint, as explained in the "Speeding up Object Access Using the WITH ROWID Option", outweighs the benefits of the storage saving and query optimization of using scoped
You can build indexes on scoped
REF columns using the
INDEX command. This allows you to use the index to efficiently evaluate queries that dereference the scoped
REFs. Such queries are turned into joins implicitly. For certain types of queries, Oracle can use an index on the scoped
REF column to evaluate the join efficiently.
For example, suppose the object type
address_objtyp is used to create an object table named
CREATE TABLE address_objtab OF address_objtyp ;
people_reltab2 table can be created that has the same definition as the
people_reltab table shown in Example 9-2, except that a
REF is used for the address. Next, an index can be created on the
Example 9-3 Creating an Index on Scoped REF Columns
CREATE TABLE people_reltab2 ( id NUMBER(4) CONSTRAINT pk_people_reltab2 PRIMARY KEY, name_obj name_objtyp, address_ref REF address_objtyp SCOPE IS address_objtab, phones_ntab phone_ntabtyp) NESTED TABLE phones_ntab STORE AS phone_store_ntab2 ; CREATE INDEX address_ref_idx ON people_reltab2 (address_ref) ;
The following query dereferences the
SELECT id FROM people_reltab2 p WHERE p.address_ref.state = 'CA' ;
When this query is executed, the
address_ref_idx index is used to efficiently evaluate it. Here,
address_ref is a scoped
REF column that stores references to addresses stored in the
address_objtab object table. Oracle implicitly transforms the preceding query into a query with a join:
SELECT p.id FROM people_reltab2 p, address_objtab a WHERE p.address_ref = REF(a) AND a.state = 'CA' ;
The Oracle query optimizer might create a plan to perform a nested-loops join with
address_objtab as the outer table and look up matching addresses using the index on the
ROWID option is specified for a
REF column, Oracle maintains the rowid of the object referenced in the
REF. Then, Oracle can find the object referenced directly using the rowid contained in the
REF, without the need to fetch the rowid from the OID index. Therefore, you use the
ROWID option to specify a rowid hint. Maintaining the rowid requires more storage space because the rowid adds 10 bytes to the storage requirements of the
Bypassing the OID index search improves the performance of
REF traversal (navigational access) in applications. The actual performance gain may vary from application to application depending on the following factors:
How large the OID indexes are.
Whether the OID indexes are cached in the buffer cache.
REF traversals an application does.
ROWID option is only a hint because, when you use this option, Oracle checks the OID of the row object with the OID in the
REF. If the two OIDs do not match, Oracle uses the OID index instead. The rowid hint is not supported for scoped
REFs with referential integrity constraints, or for primary key-based
This section discusses considerations when working with collections.
An unnesting query on a collection allows the data to be viewed in a flat (relational) form. You can execute unnesting queries on single-level and multilevel collections of either nested tables or varrays. This section contains examples of unnesting queries.
Nested tables can be unnested for queries using the
TABLE syntax, as in the following example:
Example 9-4 Unnesting a Nested Table with the TABLE Function
SELECT p.name_obj, n.num FROM people_reltab p, TABLE(p.phones_ntab) n ;
phones_ntab specifies the attributes of the
phones_ntab nested table. To retrieve even parent rows that have no child rows (no phone numbers, in this case), use the outer join syntax, with the
+. For example:
SELECT p.name_obj, n.num FROM people_reltab p, TABLE(p.phones_ntab) (+) n ;
SELECT list of a query does not refer to any columns from the parent table other than the nested table column, the query is optimized to execute only against the nested table's storage table.
The unnesting query syntax is the same for varrays as for nested tables. For instance, suppose the
phones_ntab nested table is instead a varray named
phones_var. The following example shows how to use the
TABLE syntax to query the varray:
SELECT p.name_obj, v.num
FROM people_reltab p, TABLE(p.phones_var) v;
You can create procedures and functions that you can then execute to perform unnesting queries. For example, you can create a function called
home_phones() that returns only the phone numbers where
home. To create the
home_phones() function, you enter code like the following:
Example 9-5 Creating the home_phones Function
CREATE OR REPLACE FUNCTION home_phones(allphones IN phone_ntabtyp) RETURN phone_ntabtyp IS homephones phone_ntabtyp := phone_ntabtyp(); indx1 number; indx2 number := 0; BEGIN FOR indx1 IN 1..allphones.count LOOP IF allphones(indx1).location = 'home' THEN homephones.extend; -- extend the local collection indx2 := indx2 + 1; homephones(indx2) := allphones(indx1); END IF; END LOOP; RETURN homephones; END; /
Now, to query for a list of people and their home phone numbers, enter the following:
Example 9-6 Using the TABLE Function to Unnest a Query
SELECT p.name_obj, n.num FROM people_reltab p, TABLE( CAST(home_phones(p.phones_ntab) AS phone_ntabtyp)) n ;
To query for a list of people and their home phone numbers, including those people who do not have a home phone number listed, enter the following:
SELECT p.name_obj, n.num FROM people_reltab p, TABLE(CAST(home_phones(p.phones_ntab) AS phone_ntabtyp))(+) n ;
The size of a stored varray depends only on the current count of the number of elements in the varray and not on the maximum number of elements that it can hold. Because the storage of varrays incurs some overhead, such as null information, the size of the varray stored may be slightly greater than the size of the elements multiplied by the count.
Varrays are stored in columns either as raw values or
LOBs. Oracle decides how to store the varray when the varray is defined, based on the maximum possible size of the varray computed using the
LIMIT of the declared varray. If the size exceeds approximately 4000 bytes, then the varray is stored in
LOBs. Otherwise, the varray is stored in the column itself as a raw value. In addition, Oracle supports inline LOBs which means that elements that fit in the first 4000 bytes of a large varray, with some bytes reserved for the LOB locator, are stored in the column of the row. See also Oracle Database SecureFiles and Large Objects Developer's Guide.
When changing the size of a
VARRAY type, a new type version is generated for the dependent types. It is important to be aware of this when a
VARRAY column is not explicitly stored as a LOB and its maximum size is originally smaller than 4000 bytes. If the size is larger than or equal to 4000 bytes after the increase, the
VARRAY column has to be stored as a LOB. This requires an extra operation to upgrade the metadata of the
VARRAY column in order to set up the necessary LOB metadata information including the LOB segment and LOB index.
CASCADE option in the
TYPE statement propagates the
VARRAY size change to its dependent types and tables. A new version is generated for each valid dependent type and dependent tables metadata are updated accordingly based on the different case scenarios described previously. If the
VARRAY column is in a cluster table, an
TYPE statement with the
CASCADE option fails because a cluster table does not support a LOB.
CASCADE option in the
TYPE statement also provides the
DATA option. The
DATA option only updates the metadata of the table, but does not convert the data image. In order to convert the
VARRAY image to the latest version format, you can either specify
DATA explicitly in
CASCADE statement or issue
If the entire collection is manipulated as a single unit in the application, varrays perform much better than nested tables. The varray is stored packed and requires no joins to retrieve the data, unlike nested tables.
The unnesting syntax can be used to access varray columns similar to the way it is used to access nested tables. See "Viewing Object Data in Relational Form with Unnesting Queries" for more information.
Piece-wise updates of a varray value are not supported. Thus, when a varray is updated, the entire old collection is replaced by the new collection.
The following sections contain design considerations for using nested tables.
Oracle stores the rows of a nested table in a separate storage table. A system generated
NESTED_TABLE_ID, which is 16 bytes in length, correlates the parent row with the rows in its corresponding storage table.
Figure 9-2 shows how the storage table works. The storage table contains each value for each nested table in a nested table column. Each value occupies one row in the storage table. The storage table uses the
NESTED_TABLE_ID to track the nested table for each value. So, in Figure 9-2, all of the values that belong to nested table
A are identified, all of the values that belong to nested table
B are identified, and so on.
Figure 9-2 Nested Table Storage
If a nested table has a primary key, you can organize the nested table as an index-organized table (IOT). If the
NESTED_TABLE_ID column is a prefix of the primary key for a given parent row, Oracle physically clusters its child rows together. So, when a parent row is accessed, all its child rows can be efficiently retrieved. When only parent rows are accessed, efficiency is maintained because the child rows are not inter-mixed with the parent rows.
Figure 9-3 shows how the storage table works when the nested table is in an IOT. The storage table groups the values for each nested table in a nested table column by
NESTED_TABLE_ID. In Figure 9-3, for each nested table in the
NT_DATA column of the parent table, the data is grouped in the storage table: all of the values in nested table
A are grouped together, all of the values in nested table
B are grouped together, and so on.
Figure 9-3 Nested Table in IOT Storage
In addition, the
COMPRESS clause enables prefix compression on the IOT rows. It factors out the key of the parent in every child row. That is, the parent key is not repeated in every child row, thus providing significant storage savings.
In other words, if you specify nested table compression using the
COMPRESS clause, the amount of space required for the storage table is reduced because the
NESTED_TABLE_ID is not repeated for each value in a group. Instead, the
NESTED_TABLE_ID is stored only once for each group, as illustrated in Figure 9-4.
Figure 9-4 Nested Table in IOT Storage with Compression
In general, Oracle recommends that nested tables be stored in an IOT with the
NESTED_TABLE_ID column as a prefix of the primary key. Further, prefix compression should be enabled on the IOT. However, if you usually do not retrieve the nested table as a unit and you do not want to cluster the child rows, do not store the nested table in an IOT and do not specify compression.
When creating nested tables stored in heap tables (as opposed to IOTs), Oracle database automatically creates an index on the NESTED_TABLE_ID column of the storage table and an index on the corresponding ID column of the parent table. Creating an index on the NESTED_TABLE_ID column enables the database to access the child rows of the nested table more efficiently, because the database must perform a join between the parent table and the nested table using the NESTED_TABLE_ID column.
For large child sets, the parent row and a locator to the child set can be returned so that the child rows can be accessed on demand; the child sets also can be filtered. Using nested table locators enables you to avoid unnecessarily transporting child rows for every parent.
You can perform either one of the following actions to access the child rows using the nested table locator:
Call the OCI collection functions. This action occurs implicitly when you access the elements of the collection in the client-side code, such as
OCIColl* functions. The entire collection is retrieved implicitly on the first access.
See Also:Oracle Call Interface Programmer's Guide for more information about OCI collection functions.
Use SQL to retrieve the rows corresponding to the nested table.
In a multilevel collection, you can use a locator with a specified collection at any level of nesting.
The following topics specify ways that a collection can be retrieved as a locator:
When the collection type is being used as a column type and the
TABLE storage clause is used, you can use the
LOCATOR clause to specify that a particular collection is to be retrieved as a locator.
For instance, suppose that
inner_table is a collection type consisting of three levels of nested tables. In the following example, the
LOCATOR clause specifies that the third level of nested tables is always to be retrieved as a locator.
Example 9-7 Using the RETURN AS LOCATOR Clause
CREATE TYPE inner_table AS TABLE OF NUMBER;/ CREATE TYPE middle_table AS TABLE OF inner_table;/ CREATE TYPE outer_table AS TABLE OF middle_table;/ CREATE TABLE tab1 ( col1 NUMBER, col2 outer_table) NESTED TABLE col2 STORE AS col2_ntab (NESTED TABLE COLUMN_VALUE STORE AS cval1_ntab (NESTED TABLE COLUMN_VALUE STORE AS cval2_ntab RETURN AS LOCATOR) );
A query can retrieve a collection as a locator by means of the hint
NESTED_TABLE_GET_REFS. Here is an example of retrieving the column
col2 from the table
tab1 as a locator:
SELECT /*+ NESTED_TABLE_GET_REFS +*/ col2 FROM tab1 WHERE col1 = 2;
Unlike with the
LOCATOR clause, however, you cannot specify a particular inner collection to return as a locator when using the hint.
Set membership queries are useful when you want to search for a specific item in a nested table. For example, the following query tests the membership in a child-set; specifically, whether the location
home is in the nested table
phones_ntab, which is in the parent table
SELECT * FROM people_reltab p WHERE 'home' IN (SELECT location FROM TABLE(p.phones_ntab)) ;
Oracle can execute a query that tests the membership in a child-set more efficiently by transforming it internally into a semijoin. However, this optimization only happens if the
ALWAYS_SEMI_JOIN initialization parameter is set. If you want to perform semijoins, the valid values for this parameter are
HASH; these parameter values indicate which join method to use.
Note:In the preceding example,
locationare child set elements. If the child set elements are object types, they must have a map or order method to perform a set membership query.
Chapter 5, "Support for Collection Data Types" describes how to nest collection types to create a true multilevel collection, such as a nested table of nested tables, a nested table of varrays, a varray of nested tables, or a varray or nested table of an object type that has an attribute of a collection type.
You can also nest collections indirectly using
REFs. For example, you can create a nested table of an object type that has an attribute that references an object that has a nested table or varray attribute. If you do not actually need to access all elements of a multilevel collection, then nesting a collection with
REFs may provide better performance because only the
REFs need to be loaded, not the elements themselves.
True multilevel collections (specifically multilevel nested tables) perform better for queries that access individual elements of the collection. Using nested table locators can improve the performance of programmatic access if you do not need to access all elements.
For an example of a collection that uses
REFs to nest another collection, suppose you create a new object type called
person_objtyp using the object types shown in Example 9-1, which are
phone_ntabtyp. Remember that the
phone_ntabtyp object type is a nested table because each person may have more than one phone number.
To create the
person_objtyp object type and an object table called
person_objtyp object type, issue the following SQL statement:
Example 9-8 Creating an Object Table with a Multilevel Collection
CREATE TYPE person_objtyp AS OBJECT ( id NUMBER(4), name_obj name_objtyp, address_obj address_objtyp, phones_ntab phone_ntabtyp); /
CREATE TABLE people_objtab OF person_objtyp (id PRIMARY KEY) NESTED TABLE phones_ntab STORE AS phones_store_ntab ;
people_objtab table has the same attributes as the
people_reltab table. The difference is that the
people_objtab is an object table with row objects, while the
people_reltab table is a relational table with column objects.
Figure 9-5 Object-Relational Representation of the people_objtab Object Table
You can reference the row objects in the
people_objtab object table from other tables. For example, suppose you want to create a
projects_objtab table that contains:
A project identification number for each project.
The title of each project.
The project lead for each project.
A description of each project.
Nested table collection of the team of people assigned to each project.
You can use
REFs in the
people_objtab for the project leads, and you can use a nested table collection of
REFs for the team. To begin, create a nested table object type called
personref_ntabtyp based on the
person_objtyp object type:
CREATE TYPE personref_ntabtyp AS TABLE OF REF person_objtyp; /
Now you are ready to create the object table
projects_objtab. First, create the object type
projects_objtyp, then create the object table
projects_objtab based on the
projects_objtyp as shown in Example 9-9.
Example 9-9 Creating an Object Table Using REFs
CREATE TYPE projects_objtyp AS OBJECT ( id NUMBER(4), title VARCHAR2(15), projlead_ref REF person_objtyp, description CLOB, team_ntab personref_ntabtyp); / CREATE TABLE projects_objtab OF projects_objtyp (id PRIMARY KEY) NESTED TABLE team_ntab STORE AS team_store_ntab ;
Figure 9-6 Object-Relational Representation of the projects_objtab Object Table
people_objtab object table and the
projects_objtab object table are in place, you indirectly have a nested collection. That is, the
projects_objtab table contains a nested table collection of
REFs that point to the people in the
people_objtab table, and the people in the
people_objtab table have a nested table collection of phone numbers.
You can insert values into the
people_objtab table as shown in Example 9-10.
Example 9-10 Inserting Values into the people_objtab Object Table
INSERT INTO people_objtab VALUES ( 0001, name_objtyp('JOHN', 'JACOB', 'SCHMIDT'), address_objtyp('1252 Maple Road', 'Fairfax', 'VA', '22033'), phone_ntabtyp( phone_objtyp('home', '650.555.0141'), phone_objtyp('work', '510.555.0122'))) ; INSERT INTO people_objtab VALUES ( 0002, name_objtyp('MARY', 'ELLEN', 'MILLER'), address_objtyp('33 Spruce Street', 'McKees Rocks', 'PA', '15136'), phone_ntabtyp( phone_objtyp('home', '415.555.0143'), phone_objtyp('work', '650.555.0192'))) ; INSERT INTO people_objtab VALUES ( 0003, name_objtyp('SARAH', 'MARIE', 'SINGER'), address_objtyp('525 Pine Avenue', 'San Mateo', 'CA', '94403'), phone_ntabtyp( phone_objtyp('home', '510.555.0101'), phone_objtyp('work', '650.555.0178'), phone_objtyp('cell', '650.555.0143'))) ;
Then, you can insert into the
projects_objtab relational table by selecting from the
people_objtab object table using a
REF operator, as in Example 9-11.
Example 9-11 Inserting Values into the projects_objtab Object Table
INSERT INTO projects_objtab VALUES ( 1101, 'Demo Product', (SELECT REF(p) FROM people_objtab p WHERE id = 0001), 'Demo the product, show all the great features.', personref_ntabtyp( (SELECT REF(p) FROM people_objtab p WHERE id = 0001), (SELECT REF(p) FROM people_objtab p WHERE id = 0002), (SELECT REF(p) FROM people_objtab p WHERE id = 0003))) ; INSERT INTO projects_objtab VALUES ( 1102, 'Create PRODDB', (SELECT REF(p) FROM people_objtab p WHERE id = 0002), 'Create a database of our products.', personref_ntabtyp( (SELECT REF(p) FROM people_objtab p WHERE id = 0002), (SELECT REF(p) FROM people_objtab p WHERE id = 0003))) ;
Note:This example uses nested tables to store
REFs, but you also can store
REFs in varrays. That is, you can have a varray of
This section discusses considerations when working with methods.
Ease of use
Speed of execution
Same/different address space
In general, if the application performs intense computations, C is preferable, but if the application performs a relatively large number of database calls, PL/SQL or Java is preferable.
A method implemented in C executes in a separate process from the server using external procedures. In contrast, a method implemented in Java or PL/SQL executes in the same process as the server.
Example: Implementing a Method
The example described in this section involves an object type whose methods are implemented in different languages. In the example, the object type
ImageType has an
ID attribute, which is a
NUMBER that uniquely identifies it, and an
IMG attribute, which is a
BLOB that stores the raw image. The object type
ImageType has the following methods:
get_name fetches the name of the image by looking it up in the database. This method is implemented in PL/SQL.
rotate rotates the image. This method is implemented in C.
clear returns a new image of the specified color. This method is implemented in Java.
For implementing a method in C, a
LIBRARY object must be defined to point to the library that contains the external C routines. For implementing a method implemented in Java, this example assumes that the Java class with the method has been compiled and uploaded into Oracle.
The object type specification and its methods are shown in Example 9-12.
Example 9-12 Creating an Object Type with Methods Implemented in Different Languages
CREATE LIBRARY myCfuncs TRUSTED AS STATIC / CREATE TYPE ImageType AS OBJECT ( id NUMBER, img BLOB, MEMBER FUNCTION get_name return VARCHAR2, MEMBER FUNCTION rotate return BLOB, STATIC FUNCTION clear(color NUMBER) return BLOB);/ CREATE TYPE BODY ImageType AS MEMBER FUNCTION get_name RETURN VARCHAR2 IS imgname VARCHAR2(100); sqlstmt VARCHAR2(200); BEGIN sqlstmt := 'SELECT name INTO imgname FROM imgtab WHERE imgid = id'; EXECUTE IMMEDIATE sqlstmt; RETURN imgname; END; MEMBER FUNCTION rotate RETURN BLOB AS LANGUAGE C NAME "Crotate" LIBRARY myCfuncs; STATIC FUNCTION clear(color NUMBER) RETURN BLOB AS LANGUAGE JAVA NAME 'myJavaClass.clear(oracle.sql.NUMBER) return oracle.sql.BLOB'; END; /
Restriction:Type methods can be mapped only to static Java methods.
Oracle Database Java Developer's Guide for more information
Chapter 4, "Object Support in Oracle Programming Environments" for more information about choosing a language
Static methods differ from member methods in that the
SELF value is not passed in as the first parameter. Methods in which the value of
SELF is not relevant should be implemented as static methods. Static methods can be used for user-defined constructors.
Example 9-13 shows a constructor-like method that constructs an instance of the type based on the explicit input parameters and inserts the instance into the specified table:.
Example 9-13 Creating an Object Type with a STATIC Method
CREATE TYPE atype AS OBJECT( a1 NUMBER, STATIC PROCEDURE newa ( p1 NUMBER, tabname VARCHAR2, schname VARCHAR2)); / CREATE TYPE BODY atype AS STATIC PROCEDURE newa (p1 NUMBER, tabname VARCHAR2, schname VARCHAR2) IS sqlstmt VARCHAR2(100); BEGIN sqlstmt := 'INSERT INTO '||schname||'.'||tabname|| ' VALUES (atype(:1))'; EXECUTE IMMEDIATE sqlstmt USING p1; END; END; / CREATE TABLE atab OF atype; BEGIN atype.newa(1, 'atab', 'HR'); END; /
In member procedures, if
SELF is not declared, its parameter mode defaults to
OUT. However, the default behavior does not include the
NOCOPY compiler hint. See "Member Methods".
Because the value of the
OUT actual parameter is copied into the corresponding formal parameter, the copying slows down execution when the parameters hold large data structures such as instances of large object types.
MEMBER PROCEDURE my_proc (SELF IN OUT NOCOPY my_LOB)
A function-based index is an index based on the return values of an expression or function. The function may be a method function of an object type.
A function-based index built on a method function precomputes the return value of the function for each object instance in the column or table being indexed and stores those values in the index. There they can be referenced without having to evaluate the function again.
Function-based indexes are useful for improving the performance of queries that have a function in the
WHERE clause. For example, the following code contains a query of an object table
CREATE TYPE emp_t AS OBJECT( name VARCHAR2(36), salary NUMBER, MEMBER FUNCTION bonus RETURN NUMBER DETERMINISTIC); / CREATE TYPE BODY emp_t IS MEMBER FUNCTION bonus RETURN NUMBER DETERMINISTIC IS BEGIN RETURN self.salary * .1; END; END; / CREATE TABLE emps OF emp_t ; SELECT e.name FROM emps e WHERE e.bonus() > 2000;
To evaluate this query, Oracle must evaluate
bonus() for each row object in the table. If there is a function-based index on the return values of
bonus(), then this work has already been done, and Oracle can simply look up the results in the index. This enables Oracle to return a result from the query more quickly.
Return values of a function can be usefully indexed only if those values are constant, that is, only if the function always returns the same value for each object instance. For this reason, to use a user-written function in a function-based index, the function must have been declared with the
DETERMINISTIC keyword, as in the preceding example. This keyword promises that the function always returns the same value for each object instance's set of input argument values.
The following example creates a function-based index on the method
bonus() in the table
Example 9-14 Creating a Function-Based Index on a Method
CREATE INDEX emps_bonus_idx ON emps x (x.bonus()) ;
To create generic object types that can be used in any schema, you must define the type to use invoker rights, through the
CURRENT_USER option of
TYPE. In general, use invoker rights when both of the following conditions are true:
There are type methods that access and manipulate data.
Users who did not define these type methods must use them.
For example, you can grant user
OE execute privileges on type
atype created by HR in "Static Methods", and then create table
atab based on the type:
GRANT EXECUTE ON atype TO oe; CONNECT oe; Enter password: password CREATE TABLE atab OF HR.atype ;
Now, suppose user
OE tries to use
atype in the following statement:
BEGIN -- follwing call raises an error, insufficient privileges HR.atype.newa(1, 'atab', 'OE'); END; /
This statement raises an error because the definer of the type (
HR) does not have the privileges required to perform the insert in the
newa procedure. You can avoid this error by defining
atype using invoker rights. Here, you first drop the
atab table in both schemas and re-create
atype using invoker rights:
DROP TABLE atab; CONNECT hr; Enter password: password DROP TABLE atab; DROP TYPE atype FORCE; COMMIT; CREATE TYPE atype AUTHID CURRENT_USER AS OBJECT( a1 NUMBER, STATIC PROCEDURE newa(p1 NUMBER, tabname VARCHAR2, schname VARCHAR2)); / CREATE TYPE BODY atype AS STATIC PROCEDURE newa(p1 NUMBER, tabname VARCHAR2, schname VARCHAR2) IS sqlstmt VARCHAR2(100); BEGIN sqlstmt := 'INSERT INTO '||schname||'.'||tabname|| ' VALUES (HR.atype(:1))'; EXECUTE IMMEDIATE sqlstmt USING p1; END; END; /
Now, if user
OE tries to use
atype again, the statement executes successfully:
GRANT EXECUTE ON atype TO oe; CONNECT oe; Enter password: password CREATE TABLE atab OF HR.atype; BEGIN HR.atype.newa(1, 'atab', 'OE'); END; / DROP TABLE atab; CONNECT hr; Enter password: password DROP TYPE atype FORCE;
The statement is successful this time because the procedure is executed under the privileges of the invoker (
OE), not the definer (
In a type hierarchy, a subtype has the same rights model as its immediate supertype. That is, it implicitly inherits the rights model of the supertype and cannot explicitly specify one. Furthermore, if the supertype was declared with definer rights, the subtype must reside in the same schema as the supertype. These rules allow invoker-rights type hierarchies to span schemas. However, type hierarchies that use a definer-rights model must reside within a single schema. For example:
CREATE TYPE deftype1 AS OBJECT (...); --Definer-rights type CREATE TYPE subtype1 UNDER deftype1 (...); --subtype in same schema as supertype CREATE TYPE schema2.subtype2 UNDER deftype1 (...); --ERROR CREATE TYPE invtype1 AUTHID CURRENT_USER AS OBJECT (...); --Invoker-rights type CREATE TYPE schema2.subtype2 UNDER invtype1 (...); --LEGAL
The use of roles in a subprogram depends on whether it executes with definer's rights or invoker's rights. Within a definer's rights subprogram, all roles are disabled. Roles are not used for privilege checking, and you cannot set roles.
Within an invoker's rights subprogram, roles are enabled (unless the subprogram was called directly or indirectly by a definer's rights subprogram). Roles are used for privilege checking, and you can use native dynamic SQL to set roles for the session. However, you cannot use roles to grant privileges on template objects because roles apply at run time, not at compile time.
Object tables and object views can be replicated as materialized views. You can also replicate relational tables that contain columns of an object, collection, or
REF type. Such materialized views are called object-relational materialized views.
All user-defined types required by an object-relational materialized view must exist at the materialized view site as well as at the master site. They must have the same object type IDs and versions at both sites.
This section contains the following topics:
To be updatable, a materialized view based on a table that contains an object column must select the column as an object in the query that defines the view: if the query selects only certain attributes of the column's object type, then the materialized view is read-only.
The view-definition query can also select columns of collection or
REFs can be either primary-key based or have a system-generated key, and they can be either scoped or unscoped. Scoped
REF columns can be rescoped to a different table at the site of the materialized view—for example, to a local materialized view of the master table instead of the original, remote table.
A materialized view based on an object table is called an object materialized view. Such a materialized view is itself an object table. An object materialized view is created by adding the
type keyword to the
VIEW statement. For example:
CREATE MATERIALIZED VIEW customer OF cust_objtyp AS
SELECT * FROM HR.Customer_objtab@dbs1;
As with an ordinary object table, each row of an object materialized view is an object instance, so the view-definition query that creates the materialized view must select entire objects from the master table: the query cannot select only a subset of the object type's attributes. For example, the following materialized view is not allowed:
CREATE MATERIALIZED VIEW customer OF cust_objtyp AS
SELECT CustNo FROM HR.Customer_objtab@dbs1;
You can create an object-relational materialized view from an object table by omitting the
type keyword, but such a view is read-only: you cannot create an updatable object-relational materialized view from an object table.
For example, the following
VIEW statement creates a read-only object-relational materialized view of an object table. Even though the view-definition query selects all columns and attributes of the object type, it does not select them as attributes of an object, so the view created is object-relational and read-only:
CREATE MATERIALIZED VIEW customer AS
SELECT * FROM HR.Customer_objtab@dbs1;
For both object-relational and object materialized views that are based on an object table, if the type of the master object table is not
FROM clause in the materialized view definition query must include the
ONLY keyword. For example:
CREATE MATERIALIZED VIEW customer OF cust_objtyp AS
SELECT CustNo FROM ONLY HR.Customer_objtab@dbs1;
FROM clause must omit the
See Also:Oracle Database Advanced Replication for more information on replicating object tables and columns
Example 9-15 Specifying Constraints on an Object Type When Creating a Table
CREATE TYPE customer_typ AS OBJECT( cust_id INTEGER); / CREATE TYPE department_typ AS OBJECT( deptno INTEGER); / CREATE TABLE customer_tab OF customer_typ ( cust_id default 1 NOT NULL); CREATE TABLE department_tab OF department_typ ( deptno PRIMARY KEY); CREATE TABLE customer_tab1 ( cust customer_typ DEFAULT customer_typ(1) CHECK (cust.cust_id IS NOT NULL), some_other_column VARCHAR2(32));
This section contains the following topics:
Once a type has evolved on the server side, all client applications using this type need to make the necessary changes to structures associated with the type. You can do this with OTT/JPUB. You also may need to make programmatic changes associated with the structural change. After making these changes, you must recompile your application and relink.
Types may be altered between releases of a third-party application. To inform client applications that they need to recompile to become compatible with the latest release of the third-party application, you can have the clients call a release-oriented compatibility initialization function. This function could take as input a string that tells it which release the client application is working with. If the release string mismatches with the latest version, an error is generated. The client application must then change the release string as part of the changes required to become compatible with the latest release.
rel IN VARCHAR2, errmsg OUT VARCHAR2)
rel is a release string that is chosen by the product, such as,
errmsg is any error message that may need to be returned
The function returns
0 on success and a nonzero value on error
When a type is altered, its default, system-defined constructors need to be changed in order (for example) to include newly added attributes in the parameter list. If you are using default constructors, you need to modify their invocations in your program in order for the calls to compile.
You can avoid having to modify constructor calls if you define your own constructor functions instead of using the system-defined default ones. See "Advantages of User-Defined Constructors".
When you alter a type
NOT FINAL, any attribute of type
T1 in the client program changes from being an inlined structure to a pointer to
T1. This means that you need to change the program to use dereferencing when this attribute is accessed.
Conversely, when you alter a type from
NOT FINAL to
FINAL, the attributes of that type change from being pointers to inlined structures.
For example, say that you have the types
T1(a int) and
T2(b T1), where
T1's property is
FINAL. The C/JAVA structure corresponding to
T2(T1 b). But if you change
T1's property to
NOT FINAL, then
T2's structure becomes
To make queries involving joins and sorts parallel (using the
SET operations), a
MAP function is required. In the absence of a
MAP function, the query automatically becomes serial.
Parallel queries on nested tables are not supported. Even if there are parallel hints or parallel attributes for the table, the query is serial.
Parallel DML and parallel DDL are not supported with objects. DML and DDL are always performed in serial.
Parallel DML is not supported on views with
INSTEAD-OF trigger. However, the individual statements within the trigger may be parallelized.
The following sections provide assorted tips on various aspects of working with Oracle object types.
This section contains the following topics:
As an application goes through its life cycle, the question often arises whether to change an existing object type or to create a specialized subtype to meet new requirements. The answer depends on the nature of the new requirements and their context in the overall application semantics. Here are two examples:
Changing a Widely Used Base Type
Suppose that we have an object type
address with attributes
CREATE TYPE address AS OBJECT ( Street VARCHAR2(80), State VARCHAR2(20), ZIP VARCHAR2(10)); /
We later find that we need to extend the
address type by adding a
Country attribute to support addresses internationally. Is it better to create a subtype of
address or to evolve the
address type itself?
With a general base type that has been widely used throughout an application, it is better to implement the change using type evolution.
Suppose that an existing type hierarchy of Graphic types (for example, curve, circle, square, text) needs to accommodate an additional variation, namely, Bezier curve. To support a new specialization of this sort that does not reflect a shortcoming of the base type, we should use inheritance and create a new subtype
BezierCurve under the
To sum up, the semantics of the required change dictates whether we should use type evolution or inheritance. For a change that is more general and affects the base type, use type evolution. For a more specialized change, implement the change using inheritance.
ANYDATA is an Oracle-supplied type that can hold instances of any Oracle data type, whether built-in or user-defined.
ANYDATA is a self-describing type and supports a reflection-like API that you can use to determine the shape of an instance.
While both inheritance, through the substitutability feature, and
ANYDATA provide the polymorphic ability to store any of a set of possible instances in a placeholder, the two models give the capability two very different forms.
In the inheritance model, the polymorphic set of possible instances must form part of a single type hierarchy. A variable can potentially hold instances only of its defined type or of its subtypes. You can access attributes of the supertype and call methods defined in the supertype (and potentially overridden by the subtype). You can also test the specific type of an instance using the IS OF and the TREAT operators.
ANYDATA variables, however, can store heterogeneous instances. You cannot access attributes or call methods of the actual instance stored in an
ANYDATA variable (unless you extract out the instance). You use the
ANYDATA methods to discover and extract the type of the instance.
ANYDATA is a very useful mechanism for parameter passing when the function/procedure does not care about the specific type of the parameter(s).
Inheritance provides better modeling, strong typing, specialization, and so on. Use
ANYDATA when you simply want to be able to hold one of any number of possible instances that do not necessarily have anything in common.
Chapter 6, "Applying an Object Model to Relational Data" describes how to build up a view hierarchy from a set of object views each of which contains objects of a single type. Such a view hierarchy enables queries on a view within the hierarchy to see a polymorphic set of objects contained by the queried view or its subviews.
As an alternative way to support such polymorphic queries, you can define an object view based on a query that returns a polymorphic set of objects. This approach is especially useful when you want to define a view over a set of tables or views that already exists.
For example, an object view of
Person_t can be defined over a query that returns
Person_t instances, including
Employee_t instances. The following statement creates a view based on queries that select persons from a
persons table and employees from an
CREATE VIEW Persons_view OF Person_t AS
SELECT Person_t(...) FROM persons
SELECT TREAT(Employee_t(...) AS Person_t) FROM employees;
INSTEAD OF trigger defined for this view can use the
VALUE function to access the current object and to take appropriate action based on the object's most specific type.
Polymorphic views and object view hierarchies have these important differences:
Addressability: In a view hierarchy, each subview can be referenced independently in queries and DML statements. Thus, every set of objects of a particular type has a logical name. However, a polymorphic view is a single view, so you must use predicates to obtain the set of objects of a particular type.
Evolution: If a new subtype is added, a subview can be added to a view hierarchy without changing existing view definitions. With a polymorphic view, the single view definition must be modified by adding another
DML Statements: In a view hierarchy, each subview can be either inherently updatable or can have its own
INSTEAD OF trigger. With a polymorphic view, only one
INSTEAD OF trigger can be defined for a given operation on the view.
This section discusses the SQLJ object type.
According to the Information Technology - SQLJ - Part 2 document (SQLJ Standard), a SQLJ object type is a database object type designed for Java. A SQLJ object type maps to a Java class. Once the mapping is registered through the extended SQL
CREATE TYPE command (a DDL statement), the Java application can insert or select the Java objects directly into or from the database through an Oracle JDBC driver. This enables the user to deploy the same class in the client, through JDBC, and in the server, through SQL method dispatch.
The extended SQL
CREATE TYPE command:
Populates the database catalog with the external names for attributes, functions, and the Java class. Also, dependencies between the Java class and its corresponding SQLJ object type are maintained.
Validates the existence of the Java class and validates that it implements the interface corresponding to the value of the
Validates the existence of the Java fields (as specified in the
EXTERNAL NAME clause) and whether these fields are compatible with corresponding SQL attributes.
Generates an internal class to support constructors, external variable names, and external functions that return
self as a result.
The SQLJ object type is a special case of SQL object type in which all methods are implemented in a Java class. The mapping between a Java class and its corresponding SQL type is managed by the SQLJ object type specification. That is, the SQLJ Object type specification cannot have a corresponding type body specification.
Also, the inheritance rules among SQLJ object types specify the legal mapping between a Java class hierarchy and its corresponding SQLJ object type hierarchy. These rules ensure that the SQLJ Type hierarchy contains a valid mapping. That is, the supertype or subtype of a SQLJ object type has to be another SQLJ object type.
The custom object type is the Java interface for accessing SQL object types. A SQL object type may include methods that are implemented in languages such as PLSQL, Java, and C. Methods implemented in Java in a given SQL object type can belong to different unrelated classes. That is, the SQL object type does not map to a specific Java class.
In order for the client to access these objects, JPublisher can be used to generate the corresponding Java class. Furthermore, the user has to augment the generated classes with the code of the corresponding methods. Alternatively, the user can create the class corresponding to the SQL object type.
At runtime, the JDBC user has to register the correspondence between a SQL Type name and its corresponding Java class in a map.
The following table summarizes the differences between SQLJ object types and custom object types.
Table 9-1 Differences Between SQLJ and Custom Object Types
|Feature||SQLJ Object Type Behavior||Custom Object Type Behavior|
Create a Java class implementing the
Supports external names, constructor calls, and calls for member functions with side effects.
There is no default class for implementing type methods as Java methods. Some methods may also be implemented in SQL.
Type mapping is automatically done by the extended SQL
Register the correspondence between SQL and Java in a type map. Otherwise, the type is materialized as
There are rules for mapping SQL hierarchy to a Java class hierarchy. See the Oracle Database SQL Language Reference for a complete description of these rules.
There are no mapping rules.
This section discusses miscellaneous tips for designing with Oracle objects.
If a column or table is of type
T, Oracle adds a hidden column for each attribute of type
T and, if the column or table is substitutable, for each attribute of every subtype of
T, to store attribute data. A hidden
typeid column is added as well, to keep track of the type of the object instance in a row.
The number of columns in a table is limited to 1,000. A type hierarchy with a number of total attributes approaching 1,000 puts you at risk of running up against this limit when using substitutable columns of a type in the hierarchy. To avoid problems as a result of this, consider one of the following options for dealing with a hierarchy that has a large number of total attributes:
Break up the hierarchy