Oracle8i Application Developer's Guide - Object-Relational Features
Release 2 (8.1.6)

Part Number A76976-01





Go to previous page Go to next page

Advanced Topics for Oracle Objects

The other chapters in this book discuss the topics that you need to get started with Oracle objects. The topics in this chapter are of interest once you start applying object-relational techniques to large-scale applications or complex schemas.

If the terms in this chapter are unfamiliar to you, or you are not sure what their significance is, refer to Chapter 1, "Introduction to Oracle Objects" and Chapter 7, "Frequently Asked Questions about Programming with Oracle Objects".

Storage of Objects

Oracle automatically maps the complex structure of object types into the simple rectangular structure of tables.

Leaf-Level Attributes

An object type is like a tree structure, where the branches represent the attributes. Attributes that are objects sprout subbranches for 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.

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

How Row Objects are Split Across Columns

In an object table, Oracle stores the data for every leaf-level scalar or REF attribute in a separate column. Each VARRAY is also stored in a column, unless it is too large (see "Internal Layout of VARRAYs"). Oracle stores leaf-level attributes of table types in separate tables associated with the object table. You must declare these tables as part of the object table declaration (see "Internal Layout of Nested Tables").

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

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

Hidden Columns for Tables with Column Objects

When a table is defined with a column of an object type, Oracle adds hidden columns to the table for the object type's leaf-level attributes. Each column object also has a corresponding hidden column to store the NULL information of the object (that is, the atomic nulls of the top-level and the nested objects).


When Oracle constructs a REF to a row object, the constructed REF is made up of the object identifier, 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 properties associated with the column. For example, if the column is declared as a REF WITH ROWID, Oracle stores the ROWID in the REF column. The ROWID hint is ignored for object references in constrained REF columns.

If column is declared as a REF with a SCOPE clause, the column is made smaller by omitting the object table metadata and the ROWID. A scoped REF is 16 bytes long.

If the object identifier is primary-key based, Oracle may create one or more internal columns to store the values of the primary key depending on how many columns comprise the primary key.


When a REF column references row objects whose object identifiers are derived from primary keys, we refer to it as a primary-key-based REF or pkREF. Columns containing pkREFs must be scoped or have a referential constraint. 

Internal Layout of Nested Tables

The rows of a nested table are stored in a separate storage table. Each nested table column has a single associated storage table, not one for each row. 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 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:

For more information, see Nested Table Storage.

Internal Layout of VARRAYs

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. See Storage Considerations for Varrays for details.

Object Identifiers

Every row object in an object table has an associated logical object identifier (OID). By default, Oracle assigns each row object a unique system-generated OID, 16 bytes in length. Oracle provides no documentation of or access to the internal structure of object identifiers. This structure can change at any time.

The OID column of an object table is a hidden column. Once it is set up, you can ignore it and focus instead on fetching and navigating objects through object references.

The OID for a row object uniquely identifies it in an object table. Oracle implicitly creates and maintains an index on the OID column of an object table. In a distributed and replicated environment, the system-generated unique identifier lets Oracle identify objects unambiguously .

Primary-key Based Object Identifiers

In less demanding environments, where globally unique system-generated identifiers are not required, it may be inefficient to store sixteen extra bytes with each object and maintain an index on it. A space-saving technique is to reuse the primary key value of a row object as its object identifier.

Primary-key based identifiers also make it faster and easier to loading 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 persistently.

OCI Tips and Techniques for Objects

The following sections introduce tips and techniques for using OCI effectively by showing common operations performed by an OCI program that uses objects.

Initializing an OCI Program in Object Mode

To enable object manipulation, the OCI program must be initialized in object mode. The following OCI code initializes a program in object mode:

err = OCIInitialize(OCI_OBJECT, 0, 0, 0, 0);

When the program is initialized in object mode, the object cache is initialized. Memory for the cache is not allocated at this time; instead, it is allocated only on demand.

Creating a New Object

The OCIObjectNew() function creates transient or persistent objects. A transient object's lifetime is the duration of the session in which it was created. A persistent object is an object that is stored in an object table in the database. The OCIObjectNew() function returns a pointer to the object created in the cache, and the application should initialize the new object by setting the attribute values directly. The object is not created in the database yet; it will be created and stored in the database when it is flushed from the cache.

When OCIObjectNew() creates an object in the cache, it sets all the attributes to NULL. The attribute null indicator information is recorded in the parallel null indicator structure. If the application sets the attribute values, but fails to set the null indicator information in the parallel null structure, then upon object flush the object attributes will be set to NULL in the database.

In Oracle8i, if you want to set all of the attributes to NOT NULL during object creation instead, you can use the OCI_OBJECT_NEW_NOTNULL attribute of the environment handle using the OCIAttrSet() function. When set, this attribute creates a non-null object. That is, all the attributes are set to default values provided by Oracle and their null status information in the parallel null indicator structure is set to NOT NULL. Using this attribute eliminates the additional step of changing the indicator structure. You cannot change the default values provided by Oracle. Instead, you can populate the object with your own default values immediately after object creation.

When OCIObjectNew() is used to create a persistent object, the caller must identify the database table into which the newly created object is to be inserted. The caller identifies the table using a table object. Given the schema name and table name, the OCIObjectPinTable() function returns a pointer to the table object. Each call to OCIObjectPinTable() results in a call to the server to fetch the table object information. The call to the server happens even if the required table object has been previously pinned in the cache. When the application is creating multiple objects to be inserted into the same database table, Oracle Corporation recommends that the table object be pinned once and the pointer to the table object be saved for future use. Doing so improves performance of the application.

Updating an Object

Before you can update an object, the object must be pinned in the cache. After pinning the object, the application can update the desired attributes directly. You must make a call to the OCIObjectMarkUpdate() function to indicate that the object has been updated. Objects which have been marked as updated are placed in a dirty list and are flushed to the server upon cache flush or when the transaction is committed.

Deleting an Object

You can delete an object by calling the OCIObjectMarkDelete() function or the OCIObjectMarkDeleteByRef() function.

Controlling Object Cache Size

You can control the size of the object cache by using the following two OCI environment handle attributes:

You can get or set these OCI attributes using the OCIAttrGet() or OCIAttrSet() functions. Whenever memory is allocated in the cache, a check is made to determine whether the maximum cache size has been reached. If the maximum cache size has been reached, the cache automatically frees (ages out) the least-recently used objects with a pin count of zero. The cache continues freeing such objects until memory usage in the cache reaches the optimal size, or until it runs out of objects eligible for freeing. The object cache does not limit cache growth to the maximum cache size. The servicing of the memory allocation request could cause the cache to grow beyond the specified maximum cache size. The above two parameters allow the application to control the frequency of object aging from the cache.

Retrieving Objects into the Client Cache (Pinning)

Pinning is the process of retrieving an object from the server to the client cache, laying it in memory, providing a pointer to it for an application to manipulate, and marking the object as being in use. The OCIObjectPin() function de-references the given REF and pins the corresponding object in the cache. A pointer to the pinned object is returned to the caller and this pointer is valid as long as the object is pinned in the cache. This pointer should not be used after the object is unpinned because the object may have aged out and therefore may no longer be in the object cache.

The following are examples of OCIObjectPin() and OCIObjectUnpin() calls:

status = OCIObjectPin(envh, errh, empRef,(OCIComplexObject*)0, 
                      OCI_LOCK_NONE, (dvoid**)&emp);
/* manipulate emp object */
status = OCIObjectUnpin(envh, errh, emp);

The empRef parameter passed in the pin call specifies the REF to the desired employee object. A pointer to the employee object in the cache is returned via the emp parameter.

You can use the OCIObjectPinArray() function to pin an array of objects in one call. This function de-references an array of REFs and pins the corresponding objects in the cache. Objects that are not already cached in the cache are retrieved from the server in one network round-trip. Therefore, calling OCIObjectPinArray() to pin an array of objects improves application performance. Also, the array of objects you are pinning can be of different types.

Specifying which Version of an Object to Retrieve

When pinning an object, you can use the pin option argument to specify whether the recent version, latest version, or any version of the object is desired. The valid options are explained in more detail in the following list:

Specifying How Long to Keep the Object Pinned

When pinning an object, you can specify the duration for which the object is pinned in the cache. When the duration expires, the object is unpinned automatically from the cache. The application should not use the object pointer after the object's pin duration has ended. An object can be unpinned prior to the expiration of its duration by explicitly calling the OCIObjectUnpin() function. Oracle supports two pre-defined pin durations:

Specifying Whether to Lock the Object on the Server

When pinning an object, the caller can specify whether the object should be locked via lock options. When an object is locked, a server-side lock is acquired, which prevents any other user from modifying the object. The lock is released when the transaction commits or rolls back. The following list describes the available lock options:

How to Choose the Locking Technique

Depending upon how frequently objects are updated, you can choose which locking options from the previous section to use.

If objects are updated frequently, you can use the pessimistic locking scheme. This scheme presumes that contention for update access is frequent. Objects are locked before the object in the cache is modified, ensuring that no other user can modify the object until the transaction owning the lock performs a commit or rollback. The object can be locked at the time of pin by choosing the appropriate locking options. An object that was not locked at the time of pin also can be locked by the function OCIObjectLock(). A new locking function, OCIObjectLockNoWait(), has been added in Oracle8i. As the name indicates, this function does not wait to acquire the lock if another user holds a lock on the object.

If objects are updated infrequently, you can use the optimistic locking scheme. This scheme presumes that contention for update access is rare. Objects are fetched and modified in the cache without acquiring a lock. A lock is acquired only when the object is flushed to the server. Optimistic locking allows for a higher degree of concurrent access than pessimistic locking. To use optimistic locking effectively, the Oracle8i object cache detects if an object is changed by any other user since it was fetched into the cache. By turning on the object change detection mode, object modifications are made persistent only if the object has not been changed by any other user since it was fetched into the cache. This mode is activated by setting OCI_OBJECT_DETECTCHANGE attribute of the environment handle using the OCIAttrSet() function.

Flushing an Object from the Object Cache

Changes made to the objects in the object cache are not sent to the database until the object cache is flushed. The OCICacheFlush() function flushes all changes in a single network round-trip between the client and the server. The changes may involve insertion of new objects into the appropriate object tables, updating objects in object tables, and deletion of objects from object tables. If the application commits a transaction by calling the OCITransCommit() function, the object cache automatically performs a cache flush prior to committing the transaction.

Pre-Fetching Related Objects (Complex Object Retrieval)

Complex Object Retrieval (COR) can significantly improve the performance of applications that manipulate graphs of objects. COR allows applications to pre-fetch a set of related objects in one network round-trip, thereby improving performance. When pinning the root object(s) using OCIObjectPin() or OCIObjectPinArray(), you can specify the related objects to be pre-fetched along with the root. The pre-fetched objects are not pinned in the cache; instead, they are put in the LRU list. Subsequent pin calls on these objects result in a cache hit, thereby avoiding a round-trip to the server.

The application specifies the set of related objects to be pre-fetched by providing the following information:

For example, consider a purchase order system with the following properties:

Suppose you want to calculate the total cost of a particular purchase order. To maximize efficiency, you want to fetch only the objects necessary for the calculation from the server to the client cache, and you want to fetch these objects with the least number of calls to the server possible.

If you do not use COR, your application must make several server calls to retrieve all of the necessary objects. However, if you use COR, you can specify the objects that you want to retrieve and exclude other objects that are not required. To calculate the total cost of a purchase order, you need the purchase order object, the related line item objects, and the related stock item objects, but you do not need the customer objects.

Therefore, as shown in Figure 6-1, COR enables you to retrieve the required information for the calculation in the most efficient way possible. When pinning the purchase order object without COR, only that object is retrieved. When pinning it with COR, the purchase order and the related line item objects and stock item objects are retrieved. However, the related customer object is not retrieved because it is not required for the calculation.

Figure 6-1 Difference Between Retrieving an Object Without COR and With COR

Text description of adg81094.gif follows.

Text description of the illustration adg81094.gif.

Demonstration of OCI and Oracle Objects

For a demonstration of how to use OCI with Oracle objects, see the cdemocor1.c file in $ORACLE_HOME/rdbms/demo.

Using the OCI Object Cache with View Objects

We can pin and navigate objects synthesized from object views in the OCI Object Cache similar to the way we do this with object tables. We can also create new view objects, update them, delete them and flush them from the cache. The flush performs the appropriate DML on the view (such as insert for newly created objects and updates for any attribute changes). This fires any INSTEAD-OF triggers on the view and stores the object persistently.

There is a minor difference between the two approaches with regard to getting the reference to a newly created instance in the object cache.

In the case of object views with primary key based reference, the attributes that make up the identifier for the object need to be initialized before the OCIObjectGetObjectRef call can be called on the object to get the object reference. For example, to create a new object in the OCI Object cache for the purchase order object, we need to take the following steps:

.. /* Initialize all the settings including creating a connection, getting a 
      environment handle etc. We do not check for error conditions to make 
      the example eaiser to read. */
OCIType *purchaseOrder_tdo = (OCIType *) 0; /* This is the type object for the  	 	
                                               purchase order */
dvoid * purchaseOrder_viewobj = (dvoid *) 0;   /* This is the view object */

/* The purchaseOrder struct is a structure that is defined to have the same 
attributes as that of  PurchaseOrder_objtyp type. This can be created by the 
user or generated automatically using  the OTT generator. */
purchaseOrder_struct *purchaseOrder_obj;       

/* This is the null structure corresponding to the purchase order object's 
attributes */
purchaseOrder_nullstruct *purchaseOrder_nullobj;

/* This is the variable containing the purchase order number that we need to 
create */
int PONo = 1003;

/* This is the reference to the purchase order object */
OCIRef *purchaseOrder_ref = (OCIRef *)0;

/* Pin the object type first */
OCITypeByName( envhp, errhp, svchp, 
                              (CONST text *) "",  (ub4) strlen( "") ,
                              (CONST text *) "PURCHASEORDER_OBJTYP" ,
                              (ub4) strlen("PURCHASEORDER_OBJTYP"),
                              (CONST char *) 0, (ub4)0, 
                              OCI_DURATION_SESSION, OCI_TYPEGET_ALL, 

/* Pin the table object - in this case it is the purchase order view */
OCIObjectPinObjectTable(envhp, errhp, svchp, (CONST text *) "", 
                               (ub4) strlen( "" ),
                               (CONST text *) "PURCHASEORDER_OBJVIEW",
                               (ub4 ) strlen("PURCHASEORDER_OBJVIEW"),
                               (CONST OCIRef *) NULL,

/* Now create a new object in the cache. This is a purchase order object */
OCIObjectNew(envhp, errhp, svchp, OCI_TYPECODE_OBJECT, purchaseOrder_tdo,
                     purchaseOrder_viewobj, OCI_DURATION_DEFAULT, FALSE,
                     (dvoid **) *purchaseOrder_obj);

/* Now we can initialize this object, and use it as a regular object. But before 
getting the reference to this object we need to initialize the PONo attribute of 
the object which makes up its object identifier in the view */

/* Initialize the null identifiers */
OCIObjectGetInd( envhp, errhp, purchaseOrder_obj, purchaseOrder_nullobj);

purchaseOrder_nullobj->purchaseOrder = OCI_IND_NOTNULL;
purchaseOrder_nullobj->PONo = OCI_IND_NOTNULL;

/* This sets the PONo attribute */
OCINumberFromInt( errhp, (CONST dvoid *) &PoNo, sizeof(PoNo), OCI_NUMBER_SIGNED,
                                 &( purchaseOrder_obj->PONo));

/* Create an object reference */
OCIObjectNew( envhp, errhp, svchp, OCI_TYPECODE_REF, (OCIType *) 0,
                          (dvoid *) 0, (dvoid *) 0, OCI_DURATION_DEFAULT, TRUE, 
                          (dvoid **) &purchaseOrder_ref);

/* Now get the reference to the newly created object */
OCIObjectGetObjectRef(envhp, errhp, (dvoid *) purchaseOrder_obj, purchaseOrder_

/* This reference may be used in the rest of the program ..... */
/* We can flush the changes to the disk and the newly instantiated purchase 
order object in the object cache will become permanent. In the case of the 
purchase order object, the insert will fire the INSTEAD-OF trigger defined over 
the purchase order view to do the actual processing */

OCICacheFlush( envhp, errhp, svchp, (dvoid *) 0, 0, (OCIRef **) 0);

Partitioning Tables that Contain Oracle Objects

Partitioning addresses the key problem of supporting very large tables and indexes by allowing you to decompose them into smaller and more manageable pieces called partitions. Oracle8i extends your partitioning capabilities by letting you partition tables that contain objects, REFs, varrays, and nested tables. Varrays stored in LOBs are equipartitioned in a way similar to LOBs.

The following example partitions the purchase order table along zip codes (ToZip), which is an attribute of the ShipToAddr embedded column object. For the purposes of this example, the LineItemList nested table was made a varray to illustrate storage for the partitioned varray.


Nested tables are allowed in tables that are partitioned; however, the storage table associated with the nested table is not partitioned. 

Assuming that the LineItemList is defined as a varray:

CREATE TYPE LineItemList_vartyp as varray(10000) of LineItem_objtyp;

CREATE TYPE PurchaseOrder_typ AS OBJECT ( 
      PONo                NUMBER, 
      Cust_ref            REF Customer_objtyp, 
      OrderDate           DATE, 
      ShipDate            DATE, 
      OrderForm           BLOB, 
      LineItemList        LineItemList_vartyp, 
      ShipToAddr          Address_objtyp, 
      ret_value RETURN NUMBER, 
      total_value RETURN NUMBER
CREATE TABLE PurchaseOrders_tab of PurchaseOrder_typ  
    LOB (OrderForm) store as (nocache logging)  
      (PARTITION PurOrderZone1_part  
         VALUES LESS THAN ('59999')  
         LOB (OrderForm) store as (  
               storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100))  
         VARRAY LineItemList store as LOB (  
               storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100)),  
    PARTITION PurOrderZone6_part  
         VALUES LESS THAN ('79999')  
         LOB (OrderForm) store as (  
               storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100))  
         VARRAY LineItemList store as LOB (  
               storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100)),  
    PARTITION PurOrderZoneO_part  
       VALUES LESS THAN ('99999')  
        LOB (OrderForm) store as ( 
               storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100))  
        VARRAY LineItemList store as LOB (  
               storage (INITIAL 10 MINEXTENTS 10 MAXEXTENTS 100)));

Parallel Query with Object Views

Parallel query is supported on the objects synthesized from views.

To execute queries involving joins and sorts (using the ORDER BY, GROUP BY, and SET operations) in parallel, a MAP function is needed. In the absence of a MAP function, the query automatically becomes serial.

Parallel queries on nested table columns are not supported. Even in the presence of parallel hints or parallel attributes for the view, the query will be serial if it involves the nested table column.

Parallel DML is not supported on views with INSTEAD-OF trigger. However, the individual statements within the trigger may be parallelized.

How Locators Improve the Performance of Nested Tables

In Oracle8i, the collection typed value does not map directly to a native type or structure in languages such as C++ and Java. An application using those languages must access the contents of a collection through Oracle interfaces, such as OCI.

Generally, when the client accesses a nested table explicitly or implicitly (by fetching the containing object), Oracle returns the entire collection value to the client process. For performance reasons, a client may wish to delay or avoid retrieving the entire contents of the collection. Oracle handles this case for you by using a locator instead of the real nested table value. When you really access the contents of the collection, they are automatically transferred to the client.

A nested table locator is like a handle to the collection value. It attempts to preserve the value or copy semantics of the nested table by containing the database snapshot as of its time of retrieval. The snapshot helps the database retrieve the correct instantiation of the nested table value at a later time when the collection elements are fetched using the locator. The locator is scoped to a session and cannot be used across sessions. Since database snapshots are used, it is possible to get a "snapshot too old" error if there is a high update rate on the nested table. Unlike a LOB locator, the nested table locator is truly a locator and cannot be used to modify the collection instance.

Go to previous page Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.