Release 8.1.5
A68003-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Application Developer's Guide - Fundamentals Release 8.1.5 A68003-01 |
|
This chapter explains the implementation and performance characteristics of Oracle's object-relational model. The information in this chapter enables database designers to understand the advantages and disadvantages of various ways of mapping a logical data model into an Oracle physical implementation. This chapter also enables application developers to be aware of the various design issues to consider so that the they can use the features of Oracle objects effectively.
Specifically, this chapter covers the following topics:
You should be familiar with the basic concepts behind Oracle objects before you read this chapter.
|
See Also: Oracle8i Concepts for conceptual information about Oracle objects, and see Oracle8i SQL Reference for information about the SQL syntax for using Oracle objects. |
Object types are abstractions of real-world entities, such as purchase orders, that interact with application programs. You can think of an object type as a template and a structured data unit that matches the template as an object. Object types can represent many different data structures; a few examples are line items, images, and spatial data.
You can use object types to map an object model directly to a database schema, instead of flattening the model to relational tables and columns. Objects enable you to bring related pieces of data together in a single unit, and object types allow you to store the behavior of data along with the data itself. Application code can retrieve and manipulate the data as objects.
You can store objects in columns of relational tables as column objects, or in object tables as row objects. Objects that have meaning outside of the relational database object in which they are contained, or objects that are shared among more than one relational database object, should be made referenceable as row objects. That is, such objects should be stored in an object table instead of in a column of a relational table.
For example, an object of object type CUSTOMER has meaning outside of any particular purchase order, and should be referenceable; therefore, CUSTOMER objects should be stored as row objects in an object table. An object of object type ADDRESS, however, has little meaning outside of a particular purchase order and can be one attribute within a purchase order; therefore, ADDRESS objects should be stored as column objects in columns of relational tables or object tables. So, ADDRESS might be a column object in the CUSTOMER row object.
The storage of a column object is the same as the storage of an equivalent set of scalar columns that collectively make up the object. The only difference is that there is the additional overhead of maintaining the atomic null values of the object and its embedded object attributes. These values are called null indicators because, for every column object, a null indicator specifies whether the column object is null and whether each of its embedded object attributes is null. However, 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 number. First, to create the name_objtyp object type, enter the following SQL statement:
CREATE TYPE name_objtyp AS OBJECT ( first VARCHAR2(15), middle VARCHAR2(15), last VARCHAR2(15)) /
Next, to create the address_objtyp object type, enter the following SQL statement:
CREATE TYPE address_objtyp AS OBJECT ( street VARCHAR2(200), city VARCHAR2(200), state CHAR(2), zipcode VARCHAR2(20)) /
Finally, to create the phone_objtyp object type, enter the following SQL statement:
CREATE TYPE phone_objtyp AS OBJECT ( location VARCHAR2(15), num VARCHAR2(14)) /
Because each person may have more than one phone number, create a nested table type phone_ntabtyp based on the phone_objtyp object type:
CREATE TYPE phone_ntabtyp AS TABLE OF phone_objtyp /
Once all of these object types are in place, you can create a table to hold the information about the people in the organization with the following SQL statement:
CREATE TABLE people_reltab ( id NUMBER(4) CONSTRAINT pk_people_reltab PRIMARY KEY, name_obj name_objtyp, address_obj address_objtyp, phones_ntab phone_ntabtyp) NESTED TABLE phones_ntab STORE AS phone_store_ntab;
The people_reltab table has three column objects: name_obj, address_obj, and phones_ntab. The phones_ntab column object is also a nested table.
The storage for each object stored in the people_reltab table is the same as that of the attributes of the object. For example, the storage required for a name_obj object is the same as the storage for the first, middle, and last attributes combined, except for the null indicator overhead.
If the COMPATIBLE parameter is set to 8.1.0 or higher, the null indicator 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. In the people_reltab table, for example, the overhead of the null information for each row is one byte because it translates to CEIL(3/8) or CEIL(.37), which rounds up to one byte. In this case, there are three objects in each row: name_obj, address_obj, and phones_ntab.
If, however, the COMPATIBLE parameter is set to a value below 8.1.0, such as 8.0.0, the storage is determined by the following calculation:
CEIL(n/8) + 6
Here, n is the total number of all attributes (scalar and object) within the object. Therefore, in the people_reltab table, for example, the overhead of the null information for each row is seven bytes because it translates to the following calculation:
CEIL(4/8) + 6 = 7
CEIL(4/8) is CEIL(.5), which rounds up to one byte. In this case, there are three objects in each row and one scalar.
Therefore, the storage overhead and performance of manipulating a column object is similar to that of the equivalent set of scalar columns. The storage for collection attributes are described in the "Collections" section.
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.
By default, Oracle assigns every row object a unique, immutable object identifier, called an OID. An OID allows the corresponding row object to be referred to from other objects or from relational tables. A built-in datatype called a REF represents such references. A REF encapsulates a reference to a row object of a specified object type.
By default, an object table contains a system-generated OID column, so that each row object is assigned a globally unique OID. This OID column is automatically indexed for efficient OID-based lookups. The OID column is the equivalent of having an extra 16-byte primary key column.
If a primary key column is available, you can avoid the storage and performance overhead of maintaining the 16-byte OID column and its index. Instead of using the system-generated OIDs, you can use a CREATE TABLE statement to specify that the system use the primary key column(s) as the OIDs of the objects in the table. Therefore, 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 compare objects by invoking the map or order methods defined on the object type. A map method converts objects into scalar values while preserving the ordering of the objects. Mapping objects into scalar values, if it can be done, is preferred because it allows the system to efficiently order objects once they are mapped.
The way objects are mapped has significant performance implications when sorting is required on the objects for ORDER BY or GROUP BY processing because 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. If the comparison semantics are extremely complex, or if the objects cannot be mapped into 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. In any one object type, you can implement either map or order methods, but not both.
Once again, consider an object type ADDRESS consisting of four character attributes: STREET, CITY, STATE, and 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 collection of LOB 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.
A REF is a logical "pointer" to a row object. REFs and collections of REFs model associations between objects and other objects. There are several scenarios in which REFs are useful in modelling relationships. For example, the relationship between a purchase order and a customer can be established using a REF attribute in the purchase order that references the customer. REFs provide an easy mechanism for navigating between objects. You can use the extended dot notation to follow the "pointers" without explicit joins.
REFs use object identifiers (OIDs) to point to objects. You can use either system-generated OIDs or primary-key based OIDs. The differences between these types of OIDs are outlined in "Row Object Storage in Object Tables". If you use system-generated OIDs for an object table, Oracle maintains an index on the column that stores these OIDs. The index requires storage space, and each row object has a system-generated OID, which requires an extra 16 bytes of storage per row.
You can avoid these added storage requirements by using the primary key for the object identifiers, instead of system-generated OIDs. You can enforce referential integrity on columns that store references to these row objects in a way similar to foreign keys in relational tables.
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. In addition, 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.
A REF contains the following three logical components:
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.
A scoped 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 REF. In general, you should use scoped REFs whenever possible instead of unscoped REFs because scoped REFs are stored more efficiently. Scoped REFs are stored on disk as just the OID, so each scoped REF is 16 bytes long. In addition to the smaller size, the optimizer often can optimize queries that dereference a scoped REF into 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.
However, 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.
Unscoped 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 below in the "WITH ROWID Option" section, outweighs the benefits of the storage saving and query optimization of using scoped REFs.
If the WITH 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 WITH ROWID option to specify a rowid hint. Maintaining the rowid requires more storage space because the rowid adds 16 bytes to the storage requirements of the REF.
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:
REF traversals an application does
The WITH 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, for REFs with referential integrity constraints, or for primary key-based REFs.
You can build indexes on scoped REF columns using the CREATE INDEX command. Then, you can 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 address_objtab:
CREATE TABLE address_objtab OF address_objtyp ;
Then, a people_reltab2 table can be created that has the same definition as the people_reltab table discussed in "Column Object Storage", except that a REF is used for the address:
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, -- REF specified phones_ntab phone_ntabtyp) NESTED TABLE phones_ntab STORE AS phone_store_ntab2 ;
Now, an index can be created on the address_ref column:
CREATE INDEX address_ref_idx ON people_reltab2 (address_ref) ;
The following query dereferences the address_ref:
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 above 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' ;
Oracle's 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 address_ref scoped REF column.
Collections model one-to-many relationships. For example, a purchase order has one or more line items; so, you may want to put the line items into a collection. Oracle supports two kinds of collections: varrays and nested tables.
There are two major differences between varrays and nested tables:
BLOB), whereas nested tables are stored in a storage table with every element mapping to a row in the storage table.
Given these differences, if you want efficient queryability of collections, then you should use nested tables. On the other hand, if you constantly need to retrieve and manipulate the entire collection as a value, such as in a 3GL application, then varrays are a better choice. However, if the collections are very large, then you probably do not want the entire collection to be retrieved as a value and are likely to retrieve only subsets. In such cases, the collection should be modelled as a nested table and retrieved as a locator. For example, a purchase order object may have a nested table of line items, while a geometry object may contain a varray of coordinates.
An unnesting query on a collection allows the data to be viewed in a flat (relational) form. You can execute unnesting queries on both nested tables and varrays. This section contains examples of unnesting queries.
Nested tables can be unnested for queries using the TABLE syntax, as in the following example:
SELECT p.name_obj, n.num FROM people_reltab p, TABLE(p.phones_ntab) n ;
Here, phones_ntab specifies the attributes of the phones_ntab nested table. To ensure that the parent rows with no children rows also are retrieved, use the outer join syntax as follows:
SELECT p.name_obj, n.num FROM people_reltab p, TABLE(p.phones_ntab) (+) n ;
In the first case, if the query does not refer to any columns from the parent table (other than the nested table column in the FROM clause), the query is optimized to execute only against the storage table.
You can also use the TABLE syntax to query varrays. For example, suppose the phones_ntab nested table is instead a varray named phones_var. In this case, you still can use the TABLE syntax to query the varray, as in the following example:
SELECT p.name_obj, n.num FROM people_reltab p, TABLE(p.phones_var) n ;
The unnesting query syntax is the same for varrays and nested tables.
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 location is 'home'. To create the home_phones() function, you enter code similar to the following:
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; -- extend the local collection 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:
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 following sections contain design considerations for using varrays.
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. The storage of varrays incurs some overhead, such as null information. Therefore, 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 BLOBs. 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 BLOBs. Otherwise, the varray is stored in the column itself as a raw value. In addition, Oracle supports inline LOBs; therefore, 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 itself.
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.
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 18-5 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 18-5, all of the values that belong to nested table A are identified, all of the values that belong to nested table B are identified, etc.
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 children rows together. So, when a parent row is accessed, all its children rows can be efficiently retrieved. When only parent rows are accessed, efficiency is maintained because the children rows are not inter-mixed with the parent rows.
Figure 18-6 shows how the storage table works when the nested table is in an IOT. The storage table groups the values for each nested table within a nested table column. In Figure 18-6, for each nested table in the NT_DATA column of the parent table, the data is grouped in the storage table. So, all of the values in nested table A are grouped together, all of the values in nested table B are grouped together, etc.
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 per group, as illustrated in Figure 18-7.
In general, Oracle Corporation 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.
For nested tables stored in heap tables (as opposed to IOTs), you should create an index on the NESTED_TABLE_ID column of the storage table. The index on the corresponding ID column of the parent table is created by Oracle automatically when the table is created. Creating an index on the NESTED_TABLE_ID column enables Oracle to access the child rows of the nested table more efficiently, because Oracle 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 children rows can be accessed on demand; the child-sets also can be filtered. Using nested table locators allows you to avoid unnecessary transporting of children rows for every parent.
You can perform either one of the following actions to access the children rows using the nested table locator:
Oracle Call Interface Programmer's Guide for more information about OCI collection functions.
See Also:
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 people_reltab:
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 semi-join. However, this optimization only happens if the ALWAYS_SEMI_JOIN initialization parameter is set. If you want to perform semi-joins, the valid values for this parameter are MERGE and HASH; these parameter values indicate which join method to use.
You can perform DML operations on nested tables. Rows can be inserted into or deleted from a nested table, and existing rows can be updated, by using the appropriate SQL command against the nested table. In these operations, the nested table is identified by a TABLE subquery. The following example inserts a new person into the people_reltab table, including phone numbers into the phones_ntab nested table:
INSERT INTO people_reltab values ( 0001, name_objtyp( 'john', 'william', 'foster'), address_objtyp( '111 Maple Road', 'Fairfax', 'VA', '22033'), phone_ntabtyp( phone_objtyp('home', '650.331.1222'), phone_objtyp('work', '650.945.4389'))) ;
The following example inserts a phone number into the nested table phones_ntab for an existing person in the people_reltab table whose identification number is 0001:
INSERT INTO TABLE(SELECT p.phones_ntab FROM people_reltab p WHERE p.id = '0001') VALUES ('cell', '650.331.9337') ;
To drop a particular nested table, you can set the nested table column in the parent row to NULL, as in the following example:
UPDATE people_reltab SET phones_ntab = NULL WHERE id = '0001' ;
Once you drop a nested table, you cannot insert values into it until you recreate it. To recreate the nested table in the phones_ntab nested table column object for the person whose identification number is 0001, enter the following SQL statement:
UPDATE people_reltab SET phones_ntab = phone_ntabtyp() WHERE id = '0001' ;
You also can insert values into the nested table as you recreate it:
UPDATE people_reltab SET phones_ntab = phone_ntabtyp(phone_objtyp('home', '650.331.1222')) WHERE id = '0001' ;
DML operations on a nested table lock the parent row. Therefore, only one modification at a time can be made to the data in a particular nested table, even if the modifications are on different rows in the nested table. However, if only part of the data in your nested table must support simultaneous modifications, while other data in the nested table does not require this support, you should consider using REFs to the data that requires simultaneous modifications.
For example, if you have an application that processes purchase orders, you might include customer information and line items in the purchase orders. In this case, the customer information does not change often and so you do not need to support simultaneous modifications for this data. Line items, on the other hand, might change very often. To support simultaneous updates on line items that are in the same purchase order, you can store the line items in a separate object table and reference them with REFs in the nested table.
An attribute of a collection cannot be a collection type (either varray or nested table). In other words, you cannot have collections within collections. Oracle allows only one level of direct nesting of collections. However, an attribute of a collection can be a reference to an object that has a collection attribute. Thus, you can have multiple levels of collections indirectly by using REFs.
For example, suppose you want to create a new object type called person_objtyp using the object types described in "Column Object Storage", which are name_objtyp, address_objtyp, and 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, issue the following SQL statement:
CREATE TYPE person_objtyp AS OBJECT ( id NUMBER(4), name_obj name_objtyp, address_obj address_objtyp, phones_ntab phone_ntabtyp) /
To create an object table called people_objtab of person_objtyp object type, issue the following SQL statement:
CREATE TABLE people_objtab OF person_objtyp (id PRIMARY KEY) NESTED TABLE phones_ntab STORE AS phones_store_ntab ;
The people_objtab table has the same attributes as the people_reltab table discussed in "Column Object Storage". The difference is that the people_objtab is an object table with row objects, while the people_reltab table is a relational table with three column objects.
Now 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 the following:
You can use REFs to 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 by issuing the following SQL statement:
CREATE TYPE projects_objtyp AS OBJECT ( id NUMBER(4), title VARCHAR2(15), proglead_ref REF person_objtyp, description CLOB, team_ntab personref_ntabtyp) /
Next, create the object table projects_objtab based on the projects_objtyp:
CREATE TABLE projects_objtab OF projects_objtyp (id PRIMARY KEY) NESTED TABLE team_ntab STORE AS team_store_ntab ;
Once the 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 in the following way:
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.339.9922'), phone_objtyp('work', '510.563.8792'))) ; 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.642.6722'), phone_objtyp('work', '650.891.7766'))) ; 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.804.4378'), phone_objtyp('work', '650.345.9232'), phone_objtyp('cell', '650.854.9233'))) ;
Then, you can insert into the projects_objtab relational table by selecting from the people_objtab object table using a REF operator, as in the following examples:
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))) ;
Methods are functions or procedures written in PL/SQL or Java and stored in the database, or written in a language such as C and stored externally. Methods implement operations the application can perform on the object.
Type methods can be implemented in any of the languages supported by Oracle, such as PL/SQL, Java, or C. Consider the following factors when you choose the language for a particular application:
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 routines. In contrast, a method implemented in Java or PL/SQL executes in the same process as the server.
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.
|
See Also: Chapter 11, "External Routines" and Oracle8i Java Stored Procedures Developer's Guide for more information. |
Here is the object type specification and its methods:
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 AS imgname VARCHAR2(100); BEGIN SELECT name INTO imgname FROM imgtab WHERE imgid = id; 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(color oracle.sql.NUMBER) RETURN oracle.sql.BLOB'; END; /
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.
The following example is 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:
CREATE OR REPLACE TYPE atype AS OBJECT(a1 NUMBER, STATIC PROCEDURE newa ( p1 NUMBER, tabname VARCHAR2, schname VARCHAR2)) / CREATE OR REPLACE 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', 'scott'); END; /
To create generic types that can be used in any schema, you must define the type to use invoker-rights. In general, use invoker-rights when both of the following conditions are true:
For example, you can grant user SARA execute privileges on type atype created by SCOTT in "Static Methods", and then create table atab based on the type:
GRANT EXECUTE ON atype TO SARA ; CONNECT SARA/TPK101 ; CREATE TABLE atab OF scott.atype ;
Now, suppose user SARA tries to use atype in the following statement:
BEGIN scott.atype.newa(1, 'atab', 'SARA'); -- raises an error END; /
This statement raises an error because the definer of the type (SCOTT) 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 recreate atype using invoker-rights by specifying the AUTHID CURRENT_USER option:
DROP TABLE atab ; CONNECT SCOTT/TIGER ; DROP TABLE atab ; CREATE OR REPLACE TYPE atype AUTHID CURRENT_USER AS OBJECT(a1 NUMBER, STATIC PROCEDURE newa(p1 NUMBER, tabname VARCHAR2, schname VARCHAR2)) / CREATE OR REPLACE TYPE BODY atype AS STATIC PROCEDURE newa(p1 NUMBER, tabname VARCHAR2, schname VARCHAR2) IS sqlstmt VARCHAR2(100); BEGIN sqlstmt := 'INSERT INTO '||schname||'.'||tabname|| ' VALUES (scott.atype(:1))'; EXECUTE IMMEDIATE sqlstmt USING p1; END; END; /
Now, if user SARA tries to use atype again, the statement executes successfully:
GRANT EXECUTE ON atype TO SARA ; CONNECT SARA/TPK101 ; CREATE TABLE atab OF scott.atype; BEGIN scott.atype.newa(1, 'atab', 'SARA'); -- executes successfully END; /
The statement is successful this time because the procedure is executed under the privileges of the invoker (SARA), not the definer (SCOTT).
Invoker-rights also is useful when you are writing methods that operate on REFs and LOB locators. To access the data through the REF or the locator, you need to check that the invoker of the method (and not the type definer) has the necessary privileges.
You can create function-based indexes on the return values of type methods. The following example creates a function-based index on the method afun() of the type atype2:
CREATE TYPE atype2 AS OBJECT ( a NUMBER, MEMBER FUNCTION afun RETURN NUMBER DETERMINISTIC ) / CREATE OR REPLACE TYPE BODY atype2 IS MEMBER FUNCTION afun RETURN NUMBER IS BEGIN RETURN self.a * 100; END; END; / CREATE TABLE atab2 OF atype2 ; CREATE INDEX atab2_afun_idx ON atab2 x (x.afun()) ;
For some methods, you can use function-based indexes to improve the performance of method invocation in SQL.
The following sections describe other factors you should consider when you implement Oracle objects.
In release 8.1, objects are stored in a new format that uses less storage space and has better performance characteristics than the previous format. The performance also is improved due to a more efficient transport protocol. If the COMPATIBLE parameter is set to 8.1.0 or higher, all the new objects you create are automatically stored and transported in the release 8.1 format.
In order to convert the objects created in a release 8.0 database to the release 8.1 format, complete following steps:
CREATE TABLE...AS SELECT... statement.
Oracle8i Migration for more information about compatibility and the
See Also:
COMPATIBLE initialization parameter.
Replication of object columns and object tables is not yet supported. If replication is a requirement, then you can use object views and store the application objects in relational tables, which can be replicated. Using object views, both the object model and the data to be replicated can be preserved in the database.
Inheritance is a technique used in object-oriented development to create objects that contain generalized attributes and behavior for groups of related objects. The more general objects created using inheritance are referred to as a super-types. The objects that "inherit" from the super-types (that is, are more specific cases of the super-type) are called subtypes.
A common case of inheritance is that of Person and Employee. Some instances of person are employees. The more general case, Person, is the super-type and the special case, Employee, the sub-type. Another example could involve a Vehicle as super-type and Car and Truck as its subtypes.
Inheritance can imply various levels of encapsulation for super-types. In cases where the super-type should not be exposed to other objects, a subtype should contain the methods and attributes necessary to make the super-type invisible. To understand the implementation consequences of the inheritance, it is also important to remember that Oracle8i is a strongly-typed system. A strongly-typed system requires that the type of an attribute is declared when the attribute is declared. Only values of the declared type may be stored in the attribute. For example, the Oracle8i collections are strongly-typed. Oracle8i does not allow the implementation of heterogeneous collections (collections of multiple types).
The Oracle type model does not support inheritance directly. However, you can map your current Oracle object types to Java classes and then leverage the inheritance features native to Java.
|
See Also: Oracle8i JDBC Developer's Guide and Reference and Oracle8i SQLJ Developer's Guide and Reference for more information about mapping Oracle objects to Java classes. |
In addition, inheritance can be simulated in Oracle. For example, you can use one of the following techniques to simulate inheritance:
The Subtype Contains Super-type technique hides the implementation of the abstractions/generalizations for a subtype. Each of the subtypes are exposed to other types in the object model. The super-types are not exposed to other types. To simulate inheritance, the super-type in the design object model is created as an object type. The subtype is also created as an object type. The super-type is defined as an embedded attribute in the subtype. All of the methods that can be executed for the subtype and it's super-type must be defined in the subtype.
The Subtype Contains Super-type technique is used when each subtype has specific relationships to other objects in the object model. For example, a super-type of Customer may have subtypes of Private Customer and Corporate Customer. Private Customers have relationships with the Personal Banking objects, while Corporate Customers have relationships with the Commercial Banking objects. In this environment, the Customer super-type is not visible to the rest of the object model.
In the Vehicle-Car/Truck example, the Vehicle (super-type) is embedded in the sub-types Car and Truck.
The Super-type Contains All Subtypes technique hides the implementation of the subtypes and only exposes the super-type. To simulate inheritance, all of the subtypes for a given super-type in the design object model are created as object types. The super-type is created as an object type as well. The super-type declares an attribute for each subtype. The super-type also declares the constraints to enforce the one-and-only-one rules for the subtype attributes. All of the methods that can be executed for the subtype must defined in the super-type.
The Super-type Contains All Subtypes technique is used when objects have relationships with other objects that are predominately one-to-many in multiplicity. For example, a Customer can have many Accounts and a Bank can have many Accounts. The many relationships require a collection for each subtype if the Subtype Contains Super-type technique is used. If the Account is a super-type and Checking and Savings are subtypes, both Bank and Customer must implement a collection of Checking and Savings (4 collections). Adding a new account subtype requires that both Customer and Bank add the collection to support the new account subtype (2 collections per addition). Using the Super-type Contains All Subtypes technique means that Customer and Bank have a collection of Account. Adding a subtype to Accounts means that only account changes.
In the case of the Vehicle-Car/Truck, the Vehicle is created with Car and Truck as embedded attributes of Vehicle.
In cases where the super-type is involved in multiple object-relationships with many for a multiplicity and the subtypes have specific relationships in the object model, the implementation of inheritance is a combination of the two inheritance techniques. The super-type is implemented as an object type. Each subtype is implemented as an object type. The super-type implements a referenced attribute for each subtype (zero referenced relationship). The super-type also implements an or-association for the group of subtype attributes. Each subtype implements a referenced attribute for the super-type (one referenced relationship). In this way, both the super-type and sub-type are visible to the rest of the object model.
In the case of the Vehicle-Car/Truck, the Vehicle is created as a type. The Car and Truck are created as types. The Vehicle type implements a reference to both Car and Truck, with the or-constraint on the Car and Truck attributes. The Car implements an attribute that references Vehicle. The Truck implements an attribute that references Vehicle.
Oracle does not support constraints and defaults in type specifications. However, you can specify the constraints and defaults when creating the tables:
CREATE OR REPLACE TYPE customer_type AS OBJECT( cust_id INTEGER) / CREATE OR REPLACE TYPE department_type AS OBJECT( deptno INTEGER) / CREATE TABLE customer_tab OF customer_type ( cust_id default 1 NOT NULL) ; CREATE TABLE department_tab OF department_type ( deptno PRIMARY KEY) ; CREATE TABLE customer_tab1 ( cust customer_type DEFAULT customer_type(1) CHECK (cust.cust_id IS NOT NULL), some_other_column VARCHAR2(32)) ;
You cannot change the definitions of types that have dependent data (in the form of column and/or row objects). However, you can modify tables with column objects by dropping and adding columns in a way similar to regular relational tables.
You cannot change tables containing row objects by dropping, adding, or modifying columns. If you need to modify tables containing row objects, a workaround is to perform the following steps:
If type evolution is a requirement and this workaround is not acceptable, you should use object views defined over relational tables, instead of column objects or row objects. You can then change the definitions of object types and views.
See Oracle8i Tuning for details on measuring and tuning the performance of your application. In particular, some of the key performance factors are the following:
ANALYZE command to collect statistics.
tkprof to profile execution of SQL commands.
EXPLAIN PLAN to generate the query plans.
Oracle8i supports parallel query with objects. However, there are the following restrictions:
ORDER BY, GROUP BY, and SET operations), a MAP function is required. In the absence of a MAP function, the query automatically becomes serial.
Oracle8i supports exporting Oracle objects using the Export utility, importing Oracle objects using the Import utility, and loading Oracle objects using SQL*Loader.
Objects that can be loaded with SQL*Loader include row objects, column objects, and objects with collections and references. However, SQL*Loader cannot perform direct path loading of objects. Therefore, use conventional path loading to load objects.
An alternative to conventional path loading is to first load the data into relational tables using direct path loading, and then create the object tables and tables with column objects using CREATE TABLE...AS SELECT commands. However, with this approach you need enough space to hold as much as twice the actual data.
|
See Also: Oracle8i Utilities for information about exporting, importing, and loading Oracle objects. |
