Back Up Next

Chapter 22 *

Oracle8: New Objects, ROWID, and Object Relational Concepts *

Using Oracle8 New Objects *

Bigger Databases, NLS Datatypes, and New Integrity Constraints *

Exercises *

Reverse-Key Indexes and Index-Organized Tables *

Exercises *

The Large Pool *

Exercises *

Using External Procedures *

Exercises *

Oracle8 New ROWID Format *

Describing the New ROWID Format *

Exercises *

Using the New ROWID Format *

Exercises *

Using the DBMS_ROWID Package *

Migrating to Extended ROWIDs *

DBMS_ROWID Information Functions *

Exercises *

Oracle8 Object Relational Concepts *

Defining an Object Relational Database *

Exercises *

Describing the Object Concepts in Oracle8 *

Exercises *

Creating a Basic Object Type *

Creating Attributes *

Creating Methods *

Exercises *

Reference and Collection Types *

Reference Types *

Collection Types *

Exercises *

Creating and Using an Object View *

Exercises *

Chapter Summary *

Two-Minute Drill *

 

Chapter 22

Oracle8: New Objects, ROWID, and Object Relational Concepts

In this chapter, you will understand and demonstrate knowledge in the following areas:

Using Oracle8’s new objects
Oracle8’s new ROWID format
Oracle8’s object relational concepts

This chapter covers several new features of the Oracle database architecture. In order to accommodate other aspects of large databases, Oracle8 adds some new objects and features for data storage. These features fall into two categories: new objects and the new ROWID format. The new objects are designed to store more information, while the new ROWID format is designed to handle access to the increased amount of information stored in an Oracle database. The final category, using object relational features, is perhaps the most advanced and interesting. This area of the Oracle database represents a new way for DBAs and developers to take organizational information in new directions.

Using Oracle8 New Objects

In this section, you will cover the following areas of using Oracle8 new objects:

Bigger databases, NLS datatypes, and new integrity constraints
Reverse-key indexes and index-organized tables
The large pool
Using external procedures

Oracle8 offers the DBA many new objects in which to store data. The databases Oracle8 supports are larger than ever before. New features for national language support datatypes are present in Oracle8 as well. Also, the success of Oracle7’s declarative integrity constraints has been advanced with new types of constraints and enhancements to existing constraints. This section will cover the facts related to these three areas for OCP Exam 5.

Bigger Databases, NLS Datatypes, and New Integrity Constraints

Oracle8 supports increased-size ceilings in several different areas of database administration. The first area, that of table and index partitions, offers a high level of partitioning on database tables and indexes—64,000 to be exact. Other areas of the database have received increases in capacity as well. One caveat exists for Oracle8’s new capacity limits—the operating system on the host machine running Oracle8 must also support these capacity limits. These increases are as follows:

The total capacity for database is 512 petabytes.
The maximum number of tablespaces allowed is about 2 billion.
The number of datafiles per tablespace is 1,022.
The number of columns supported in a table now 1,000.
The maximum number of indexed columns is 32.
The CHAR datatype now supports 2,000 bytes.
The VARCHAR2 datatype now supports 4,000 bytes.

There are two new datatypes in the Oracle8 architecture provided for national language support as well. These two new datatypes are the NCHAR and the NVARCHAR2 datatypes. NCHAR and NVARCHAR2 are designed to store character-based data for multibyte character sets. They are identical in function and in size to their single-byte counterparts, CHAR and VARCHAR2. To access data in the columns declared to be type NCHAR or NVARCHAR2, the user must use the syntax column = N’value. That letter N must be specified exactly as that—a capital N in front of the desired value. The columns that are declared as NCHAR and NVARCHAR2 should be used to store data in the same character set as that declared for the database in the create database statement.

Oracle furthers the success of declarative integrity constraints with new features for their usage. The first change made to the declarative integrity constraints in the Oracle database is the differentiation between deferred and immediate constraints. Immediate constraints are those integrity constraints that are enforced immediately, as soon as the statement is executed. If the user attempts to enter data that violates the constraint, Oracle signals an error and the statement is rolled back. Up until Oracle8, all declarative integrity constraints in the database were immediate constraints. However, Oracle8 also offers the DBA an option to defer database integrity checking. Deferred integrity constraints are those that are not enforced until the user attempts to commit the transaction. If at that time the data entered by statements violates an integrity constraint, Oracle will signal an error and roll back the entire transaction. The user can defer any and all constraints that are deferrable during the entire session using the alter session set constraints=deferred statement. Alternately, the user can defer named or all constraints for a specific transaction using the set constraint name deferred or set constraint all deferred. This form of "lazy evaluation" temporarily allows data to enter the database that violates integrity constraints. For example, in Oracle7 there was no way to insert data into a child table for which there wasn’t also data in the parent. In Oracle8, the user can conduct the insert on the child table before inserting data into the parent simply by deferring the foreign-key constraint. The user may also set constraints for immediate enforcement using the set constraint name immediate or set constraint all immediate statement.

The definition of a constraint will determine whether the constraint is deferrable by users. Two factors play into that determination. The first is the overall deferability of the constraint. If a constraint is created with the deferrable keyword, then the constraint is deferrable by user processes until the time the transaction is committed. In contrast, if the constraint is created with the not deferrable keywords, then user process statements will always be bound by the integrity constraint. The not deferrable status is the default for the constraint. If a constraint has been created with the not deferrable status, then the alter session and set statements above for deferring integrity constraints cannot be used. The second factor is the default behavior of the constraint. The first option is to have the constraint deferred, defined with the initially deferred keyword. This option and the not deferrable keyword option described are mutually exclusive. The other option is to have the integrity constraint enforced unless explicitly deferred by the user process, specified by the initially immediate keywords.

CREATE TABLE employees
(empid NUMBER(10) NOT NULL,
name VARCHAR2(40) NOT NULL,
salary NUMBER(10) NOT NULL,
CONSTRAINT pk_employees_01
PRIMARY KEY (empid) NOT DEFERRABLE);

The other new features for integrity constraints are a new status for constraints and the use of nonunique indexes to enforce unique constraints. Prior to Oracle8, there are two statuses for the integrity constraints: enable and disable. Oracle8 changes the enable status to enable validate and adds a third status for integrity constraints, enable novalidate. The new status allows Oracle to enforce the constraint on new data entering the table (enabling), but not on data that already exists on the table (no validating). These statuses can be used with the issuance of the alter table table_name enable novalidate constraint constraint_name or alter table name enable validate constraint constraint_name statements. Also, Oracle can support unique constraints with nonunique indexes. The columns in the unique constraint should be the first columns in the nonunique index, in any order. Other columns can also be present in the index to make it nonunique. This feature speeds the process of enabling primary-key or unique constraints on the table. The nonunique index supporting the unique or primary-key constraint cannot be dropped.

Exercises

  1. What is the maximum allowed size for an Oracle8 database? How large can the VARCHAR2 datatype be in Oracle8?
  2. Name two new NLS datatypes available in Oracle8 that relate to the CHAR and VARCHAR2 datatypes.
  3. What is the new status available for integrity constraints?

Reverse-Key Indexes and Index-Organized Tables

Several new features in the Oracle database have been created that relate to the use of traditional B-tree indexes. The first is the ability to reverse the key data of an index. Oracle has increased its support of parallel processing with the use of reverse-key indexes. A reverse-key index will actually reverse the order of the data in the key; thus, if a column contains data in the following set: (‘SAM’, ‘JILL’,’FRANK’,’SITA’), the resultant reverse-key index data will look like (‘MAS’,’LLIJ’,’KNARF’,’ATIS’). This new indexing option is used to increase Oracle’s ability to retrieve and modify indexed data in parallel configurations and to minimize the chance that changes to data in the index will cause the index to be less effective in retrieving and modifying data. The index key can be reversed at index creation time with the addition of the reverse keyword. The index key in an already existing index can be reversed, or a reverse-key index can be placed back into its original order using the noreverse keyword. The following code displays the creation of a reverse-key index, the alteration of a regular index to be a reverse-key index, and the alteration of a reverse-key index to be a regular index, respectively:

CREATE INDEX x_empl_rev_01
ON employees (empid, name)
REVERSE;

ALTER INDEX x_sales_01
REBUILD REVERSE;

ALTER INDEX x_empl_rev_01
REBUILD NOREVERSE;

Another new feature available in Oracle that builds on the use of indexes is the index-organized table. Consider the following scenario: In Oracle7, the DBA can create a table with few columns, and index all columns in the table, effectively creating a table that is easier to search but requires significant storage for the combined size of table and index. In Oracle8, those two objects can be combined to minimize storage requirements. The resultant object is the index-organized table, an object with the structure of a B-tree index, but with all data of all columns of the table stored in it, accessible quickly by the primary key with Oracle’s index scan operation. Figure 22-1 illustrates the concept of an index-organized table. Since the data in an index-organized table is stored by indexed primary key, no SQL statement searching for data on an index-organized table will ever use a full table scan.

Fig22-01.jpg (18608 bytes)

Figure 1: Table/index relationships and the index-organized table

Index-organized tables store all data in a B-tree structure. In traditional B-tree indexes, the column data would be stored in the index, along with a ROWID for accessing the data in the table. Since the data is actually stored in the tree, however, there is no need for a correlated ROWID between table and index. Thus, row data in index-organized tables do not have ROWIDS. It is not possible to create another index on an index-organized table, so users must remember to use the primary key for all data searches on the index-organized table. Other restrictions include the inability to cluster, partition, or use LONG columns or unique constraints in index-organized tables. These database objects work best in applications that store spatial data. A good example for use of index-organized tables is to improve text searches, because the location of all copies of a particular word can be stored together in one index node for that word. An example of the create table statement used to create an index-organized table appears in the following code block:

CREATE TABLE prglang_keywords
(keyword VARCHAR2 CONSTRAINT pk_manual_keywords_01 PRIMARY KEY,
page_number NUMBER(10))
ORGANIZATION INDEX
PCTTHRESHOLD 25
OVERFLOW TABLESPACE data_oflow_01;

There are several new keywords in this create table statement to discuss. First, the usage of the organization index keywords will be discussed. This is perhaps the most important component in defining an index-organized table, because it is the component that actually defines the table as index-organized. Prior to Oracle8, this parameter is set implicitly for all tables as organization heap. In Oracle8, the DBA can define the method used to organize data in regular tables using the organization heap keywords, but this behavior is the one Oracle uses by default. Only when organization index is used does Oracle create an index-organized table. The next parameter used in defining an index-organized table is the pctthreshold num clause. Use of this clause is optional. When specified, pctthreshold indicates a threshold limit that any row of data can occupy in an index-organized table block. In the example, the value set for this clause is 25, which means that any row in the table that takes up 25 percent or more of its data block for storage will have nonkey column data moved into an overflow tablespace defined using the overflow tablespace keywords. If overflow tablespace is specified without defining a pctthreshold, the default pctthreshold of 50 will be used.

Information about index-organized tables created in the database can be found with the use of the USER_, ALL_, and DBA_TABLES dictionary views. A new column called IOT stores information about index-organized tables. Data in rows for this column can have three values—IOT for index-organized tables, IOT_OVERFLOW for overflow segments, or NULL for any table that is not index-organized. Any data manipulation or load operation can be performed on index-organized tables, including alter table, drop table, truncation, EXPORT/IMPORT, and direct path data loads, with the exception of operations based on ROWID, as mentioned earlier.

Exercises

  1. What is a reverse-key index? What purpose does it serve? What actually happens to data in the index key of the reverse-key index? What keyword is used in a create index statement to reverse the key?
  2. Once reversed, what keyword can be used in an alter index statement to put the key back into original order?
  3. What is an index-organized table? What keywords specify the organization of a table into index-organized order? What is data overflow in an index-organized table, and how is it defined in the index-organized table?
  4. What feature common to other rows of data in the Oracle database does the index-organized table not have?

The Large Pool

Oracle8 supports use of an optional new area in database SGA called the large pool. This area of memory stores session memory for the multithreaded server (MTS) configuration. Oracle allocates buffers for server I/O processes and for backup and restore operations. In creating a new area of memory, Oracle relieves the burden on existing areas like the shared pool to give up memory for caching SQL parse trees in favor of MTS session information and backup/recovery processes. One item the large pool doesn’t have that other areas of memory do have is an LRU list to support paging items out of memory.

There are two new parameters used to support the creation of the large pool at instance startup in the init.ora file. The first is LARGE_POOL_SIZE, and the second is LARGE_POOL_MIN_ALLOC. The LARGE_POOL_SIZE parameter is used to define the size of the large pool, either in kilobytes or megabytes. The minimum size of the large pool is 300 kilobytes, while the maximum size of the large pool can be 2,000 megabytes or more, depending on the operating system. Within the large pool, there is a minimum allocation size for space in the large pool. This size is defined by the LARGE_POOL_MIN_ALLOC parameter, whose default and minimum value is usually 16 megabytes. The maximum value for this parameter is operating system specific.

Exercises

  1. What is the large pool? How might the DBA want to use the large pool? What objects may be stored in the large pool?
  2. What initialization parameters are used to define the size of the large pool?

Using External Procedures

Prior to Oracle8, developers could define PL/SQL procedures that would access the Oracle database and perform operations based on data returned. Further, PL/SQL procedures allowed the developer to put data into the database. However, all code used to manipulate data or to execute in a stored procedure had to be coded "internally"—that is, the code had to be in the PL/SQL procedures. No external procedures were allowed. Oracle8 transcends this restriction using library schema objects. PL/SQL stored procedures can now execute other procedures written in C and stored in a shared library. The reference to the C procedure is called a callout, while the return of data from the external procedure is a callback. The C routine executes in a separate memory address space than the Oracle instance.

There are some strong benefits for using external procedures in Oracle. Instead of requiring developers to rewrite processes written in other languages such as C in PL/SQL in order to use it in the database, the original procedure can be referenced directly by a PL/SQL procedure. Although the first call to an external procedure can be costly for Oracle to process, subsequent calls will run more quickly. The process used for executing an external procedure from PL/SQL is illustrated in Figure 22-2. When a PL/SQL procedure wants to execute an external procedure, the first thing it must do is look up the external procedure using the alias that maps to the external process, also known as the library schema object. Then, the PL/SQL program passes the request to the network listener. The listener process then executes a special process called extproc. The tnsnames.ora file contains a definition called EXTPROC_CONNECTION_DATA that links the listener to extproc. This process then runs for the rest of the time the session that called the external procedure runs. Extproc then loads the shared library that is used for executing the external procedure, after which time the external procedure can be run. The result of this external procedure’s execution is then returned to the PL/SQL or SQL operation that called it.

Fig22-02.jpg (35322 bytes)

Figure 2: Process for executing external procedures

The creation of the library object is accomplished with the create or replace library name is path statement, shown in the code block following this paragraph. Following the creation of the library object, PL/SQL procedures can then reference external procedures. To do so, the user must have execute privileges on the library object. Definitions for library objects can be found in the USER_, ALL, and DBA_LIBRARIES dictionary views. To create a library in their own schema, the user must have create library privileges, while to create a library anywhere in the database, the user must have create any library privileges. The path and filename defined for a library object is not checked until the object is referenced.

CREATE OR REPLACE LIBRARY sort_algorithm
IS ‘/DISK05/usr/bin/progsort’;

To understand how the PL/SQL, C, and library components work in conjunction with one another, the following example is presented. This is a PL/SQL function called gcd( ), which registers an external procedure c_gcd( ), as follows:

CREATE FUNCTION gcd (
-- find greatest common divisor of x and y
x BINARY_INTEGER,
y BINARY_INTEGER)
RETURN BINARY_INTEGER AS EXTERNAL LIBRARY c_utils
NAME "c_gcd" -- quotes preserve lower case
LANGUAGE C;

The library definition then would be created with a code block similar to the following:

CREATE LIBRARY c_utils AS ‘/DLLs/utils.so’;

In this example, you call PL/SQL function gcd( ) from an anonymous block. PL/SQL passes the two integer parameters to external function c_gcd( ), which returns their greatest common divisor. The anonymous block is listed below:

DECLARE
g BINARY_INTEGER;
a BINARY_INTEGER;
b BINARY_INTEGER;

BEGIN

g := gcd(a,b); -- call function
IF g IN (2,4,8) THEN …

Exercises

  1. What is a library schema object? What statement is used to create it?
  2. What is the extproc process? What process executes it? How does this process know how to connect to extproc? What parameter file is this information stored in?

Oracle8 New ROWID Format

In this section, you will cover the following topics related to Oracle8 new ROWID format:

Describing the new ROWID format
Using the new ROWID format
Using the DBMS_ROWID package

With new objects and greater size comes the need for accessing those new objects in a new and bigger way. In the versions that preceded Oracle8, all access to data in the database is managed with the use of ROWID data consisting of three elements: data block identifier, row number in the block, and datafile identifier. Additional reference information for objects in Oracle8—such as table and index partitions, and the increases in the amount of data Oracle8 can store—require that new components be included in the ROWID format. This section will detail those changes.

Describing the New ROWID Format

The new ROWID format uses the following conventions. It is an 18-character representation of the location of data in the database, with each character represented in a base-64 format consisting of A–Z, a–z, 0–9, + and /. The new ROWID format consists of four components, described here and in Figure 22-3:

Database object number
Relative datafile number
Data block number
Row number (also called the slot number)

 

Fig22-03.jpg (9414 bytes)

Figure 3: Extended ROWID format in Oracle8

Note that the name of the second component of the new ROWID format is a "relative" datafile number. Relative is the operative term. ROWID format in Oracle7 is a unique method of identification for row data for the life of the row in the database. In contrast, Oracle8 uses ROWID as a unique identifier, but not as an absolute locator address because each database object can belong to a different tablespace, and each tablespace can have the same datafile numbers as another tablespace. However, the numbering of datafiles in one tablespace must be unique. This idea of tablespace-relative addressing is crucial for the support of VLDBs. One of the key new elements in the Oracle8 ROWID format is the database object number. This feature of the new ROWID format corresponds to the value stored in the data dictionary view called DBA_OBJECTS in the OBJECT_ID and DATA_OBJECT_ID columns. With the object number, Oracle8 can identify which tablespace contains the object. Oracle increments this number as the DBA truncates the table or moves table partitions.

Exercises

  1. What are the three components of the Oracle7 ROWID format? What are the four components of the Oracle8 ROWID format? What are some differences between the purpose of the ROWID in Oracle7 and the ROWID in Oracle8?
  2. What notation does each character in the ROWID use?

Using the New ROWID Format

Oracle8 supports the Oracle7 ROWID format for purposes of supporting database migration. For the most part, the Oracle7 ROWID format is useful enough for locating information because Oracle can derive the tablespace in which an object is stored in, and since Oracle7 objects cannot span multiple tablespaces, the datafile number can be determined accurately. Oracle7 ROWID format cannot be used for global indexes on tables that are partitioned because tables with partitions can span more than one tablespaces, making it impossible to determine absolutely which relative datafile is referenced by the datafile number in the restricted ROWID format. Oracle7 ROWID format takes 6 bytes to store.

In contrast, Oracle8 ROWID format takes 10 bytes to store. When row location must be absolute, Oracle uses the extended format provided by Oracle8 ROWIDs. The situations where Oracle uses this format is when handling global indexes on partitioned tables and in several internal processes. Backward compatibility works best, where Oracle8 can query Oracle7 ROWIDs with little difficulty. To understand Oracle8 ROWID information from Oracle7 databases, the DBMS_ROWID package must be used.

For the most part, there should be little problem with the new ROWID format used in Oracle8. However, there may arise situations where the new ROWID format may cause issues with the operation of database applications. If the application accesses data via ROWID directly, the developers of the application will have to utilize the DBMS_ROWID package to manipulate ROWID data. Also, if a table contains a column declared to be of type ROWID, there may be some migration issues. In general, if an application creates ROWID information or interprets ROWID information without the assistance of the Oracle database, then the DBMS_ROWID package must be used to ease migration concerns. Other than those situations, there should be few, if any, issues for migration related to Oracle8 ROWIDs.

Exercises

  1. What are some situations where the migration of the database needn’t encounter difficulty with ROWID data?
  2. What are some situations where the migration of the database may encounter difficulty with ROWID data? What is the name of the package that may be used to overcome difficulties with ROWID migration?

Using the DBMS_ROWID Package

The DBMS_ROWID package is created by the dbmsutil.sql script, which in turn is created by executing the catproc.sql script. This package creates several stored procedures used for manipulating ROWIDs. There are two uses for the DBMS_ROWID package. The first is to migrate restricted Oracle7 ROWID information to the extended Oracle8 ROWID format. The other is to interpret the ROWID information.

Migrating to Extended ROWIDs

A list of DBMS_ROWID functions to be used for database ROWID migration are as follows, along with explanations:

ROWID_TO_EXTENDED( ) Converts Oracle7 ROWID format to Oracle8
ROWID_TO_RESTRICTED( ) Converts Oracle8 ROWID format to Oracle7
ROWID_VERIFY( ) Identifies whether or not an Oracle7 ROWID can be converted to Oracle8 using the rowid_to_extended( ) function

Perhaps the most important function of this set is the rowid_to_extended( ) function. This function performs the actual conversion of data from the old ROWID format to the new. It is not necessary to apply this function to the tables of the database to convert the ROWID pseudocolumn present on tables to use for locating data quickly in the table. However, if the table has a column defined on it of type ROWID that contains this type of data for the application’s use, the rowid_to_extended( ) function can be used to convert these columns. There are four variables that are passed to the function: old_rowid, schema_name, object_name, and conversion_type. The old_rowid variable identifies the name of the table column to be converted whose datatype is ROWID. The schema_name variable is the name of the user who owns the table undergoing conversion. The object_name variable identifies the name of the table containing a column declared with datatype ROWID about to be converted. The conversion_type variable can have two values: 0 for an actual conversion of data stored in a ROWID column and 1 for the representation of the Oracle7 restricted ROWID in Oracle8 extended format.

The rowid_verify( ) procedure uses the same parameters, but simply returns 0 if the ROWID can be converted or 1 if the ROWID cannot be converted. Conversion can run in the other direction also with the use of the rowid_to_restricted( ) function. This function uses only two variables passed into it: old_rowid and conversion_type. The old_rowid variable identifies the ROWID data in extended Oracle8 format. The conversion_type variable is the same as above, and can have two values: 0 for an actual conversion of data stored in a ROWID column and 1 for the representation of the Oracle7 restricted ROWID in Oracle8 extended format.

DBMS_ROWID Information Functions

A list of DBMS_ROWID functions to be used for gaining database ROWID information are as follows, along with explanations:

ROWID_TYPE( ) Returns 0 if the ROWID is the restricted Oracle7 type, 1 if the ROWID is the extended Oracle8 type
ROWID_OBJECT( ) Returns the object number corresponding to the ROWID given, the first component of the extended ROWID
ROWID_RELATIVE_FNO( ) Returns the relative datafile number for the ROWID given, the second component of the extended ROWID
ROWID_BLOCK_NUMBER( ) Returns the data block number for the ROWID given, the third component of the extended ROWID
ROWID_ROW_NUMBER( ) Returns the block row or slot number for the ROWID given, the fourth component of the extended ROWID
ROWID_TO_ABSOLUTE_FNO( ) Returns the absolute datafile number for the ROWID given

All but one of these functions accepts one variable as input: row_id, which is the ROWID the executor of the function wishes to obtain information about. The one function that accepts multiple variables as input is the rowid_to_absolute_rowid( )function. This function accepts a row_id, schema, and object_name input variables, in that order, to give the absolute location on disk for the given ROWID. The most important functions in this set of functions are the rowid_type( ), rowid_object( ), and rowid_to_absolute_fno( ) functions, as they provide helpful information to the DBA that is useful for dissecting and interpreting ROWID data.

In addition to functions, there are two procedures in the DBMS_ROWID called rowid_info( ) and rowid_create( ). The rowid_info( ) procedure accepts one variable as input, a ROWID, and returns five variables as output. The first of the return variables is rowid_type, which may be equal to 0 to represent a restricted Oracle7 ROWID or 1 to represent an Oracle8 extended ROWID. The other four, object_number, relative_fno, block_number, and row_number give the four components of the extended ROWID. If the ROWID is a restricted Oracle7 format, then the return value for object_number will be NULL. The rowid_create( ) procedure is useful for testing in that it creates a test ROWID.

Exercises

  1. Which function converts Oracle7 restricted ROWID data to extended Oracle 8 format? What are the four input variables it accepts?
  2. What procedure takes a ROWID and returns its type and all of its components?

Oracle8 Object Relational Concepts

In this section, you will cover the following topics related to Oracle8 object relational concepts:

Defining an object relational database
Describing the object concepts in Oracle8
Creating a basic object type
Reference and collection types
Creating and using an object view

The final, and perhaps most interesting, area of Oracle8 is the introduction of the object relational database architecture, which has been called Oracle’s revolutionary, or at least evolutionary, approach to the future of object-oriented database design. This section will cover several basic areas of Oracle8 object relational database design concepts, including the definition of an object relational database, the object concepts available in Oracle8, the creation of a basic object type, and the creation and use of an object view. These terms, as well as their use, will be defined shortly.

Defining an Object Relational Database

There are several features in an object relational database. Fundamental to the use of an object relational database is the existence of a system for users to define their own information or datatypes. Once defined, these types are usable everywhere Oracle uses its own predefined datatypes. In addition, the object relational database should allow the developers of applications on the database to create a layer of abstraction on the relational tables already defined in the database to support the treatment of those tables as objects. This feature is called an object view.

An object relational database should also support the storage of large amounts of data, along with the storage of large individual units of information. In Oracle8, they are called large objects, and several new large object (LOB) datatypes have been created to support their storage and manipulation, both inside and outside the database. Oracle has defined object relational databases to support the use of data cartridges—pieces of software that can be "plugged into" the Oracle database to support additional features. The delivery of objects to the client and manipulation of those objects via the application are also supported by Oracle in Oracle8. Finally, an object relational database should deliver improved performance for data processing applications, online transaction processing, fast SQL processing, support for large databases, and security.

Use of object technology allows for several benefits in database design. With abstract datatypes and detail hidden behind object technology, maintenance costs may be reduced and applications produced faster than using relational technology. The code produced will also offer some ability for reuse once a sufficient body of code is established.

Exercises

  1. What are the features of an object relational database?
  2. What are some of the benefits for using object technology in a database?

Describing the Object Concepts in Oracle8

The importance of using objects is growing in the development of database applications. In the world of software development, objects have helped developers realize the dream of data abstraction behind two concepts: attributes and methods. In relational databases, the concepts of data abstraction provided through entity-relationship diagrams and logical data models often lose their simplicity when turned into physical database design. This loss of abstraction has a great deal to do with the fundamental premise of relational database design—the tables columns, foreign keys—in short, the details of the relational database itself.

The fundamental concept behind an object relational database is the concept of an object. Objects are designed to allow developers to model business situations and real things using advanced use of types. The object methodology allows the developer to hide complexity. Instead of creating tables designed to fit the needs of one or two situations, the database can be composed of more atomic types that can be combined with other types or individual columns to define object tables. Objects are composed of two parts: attributes and methods. An attribute is to an object as table columns are to a table. Interestingly, in E. F. Codd’s original presentation of relational database theory, the name for columns that comprise a "relation" (read: table) was "attributes." For example, the attributes of a paycheck object may consist of a payee, payer, check_number, date_issued, payment_authorization, payer_account_number, routing_number, and payment_amount. Even though there are hundreds of different banks and millions of payers and payees, all checks issued boil down to these components. The second area of objects is called the methods, or activities that take place in relation to the check. These methods may be written in PL/SQL, so they can operate internally within the Oracle database, or externally in C to be accessed as library objects. Some of these methods include issue, stop_payment and adjust_amount. Figure 22-4 illustrates the attributes and methods that may be used to define a check object.

Fig22-04.jpg (19425 bytes)

Figure 4: A check object with attributes and methods

There are several key features implemented in Oracle8 to support the use of object technology. Oracle8 supports object types, which are user-defined datatypes used to define the objects. Also, Oracle8 supports the retroactive application of object technology onto existing relational database components. There are extensions to SQL and PL/SQL to handle access to object tables as well. Finally, the use of Oracle precompilers such as Pro*C continues to be possible in the face of object technology with the advent of new features to access object tables as well.

Exercises

  1. What is an object? What two elements comprise objects?
  2. How does Oracle8 support the application of object technology to relational database design?
  3. How does Oracle8 support access to database objects?

Creating a Basic Object Type

The cornerstone of object relational design is the use of object types. Object types are user-defined datatypes, the design of which allows users to create the attributes that define an object. These attributes in object types are similar to columns for tables. The methods of an object type define the operations that can take place on the attributes of the object. Figure 22-4 defines an example of attributes and methods. The creation of methods is similar to the creation of packages in Oracle7.

Creating Attributes

The new datatypes available in Oracle8 relate to the creation of attributes. There are now two basic paths for which developers can define their types: user-defined and built-in datatypes. Most of the scalar built-in datatypes have already been defined. There are several datatypes in Oracle8 that were also available in Oracle7. They are CHAR, VARCHAR2, LONG, LONG RAW, RAW, NUMBER and DATE. Several new datatypes are available in Oracle8. They include the new NLS datatypes NCHAR and NVARCHAR2. Oracle8 includes new methods to deal with large database objects, also called LOBs: BLOB, CLOB, NCLOB, and BFILE. More information about the datatypes in Oracle8 appears in Chapter 23. In addition to scalar datatypes, Oracle8’s object programming allows for a few collection datatypes, called VARRAY (variable array) and TABLE (nested table), and a relationship datatype called REF (reference). User-defined object types are created as combinations of these scalar, collection, and reference datatypes. Creation of object types can be used to support several activities. Object types can be used in the following different ways:

As columns in relational tables
As attributes in objects
In object views to create retroactive object databases out of relational ones
As local variables in PL/SQL
As type definitions in order to create object tables

The create type statement can be used to create object types. Several restrictions on creating object types exist. First, the object type cannot contain the LONG or LONG RAW scalar datatypes, the ROWID type, or any referential datatype created with the %type keyword in PL/SQL. The names of attributes must be unique in any single object type definition, but the same attribute name can be used in different object type definitions, even in the same schema. The rules governing naming object types in Oracle8 are the same rules defining the naming of columns in Oracle7, which have already been discussed. Remember, they can be no longer than 30 alphanumeric characters, including A–Z, a–z, #, $, and _. An example of the creation of an object type appears in the following code block:

CREATE TYPE paycheck_type AS OBJECT (
check_number NUMBER(20),
payer VARCHAR2(40),
payee VARCHAR2(40),
date_issued DATE,
payment_authorization VARCHAR2,
payer_account_number VARCHAR2(20),
routing_number VARCHAR2(20),
payment_amount NUMBER(10));

There are three different means of identification for different user-defined types. Simple types contain all scalar, collector, and reference datatypes available in Oracle8. Composite object types have more than one attribute that is an object type. Self-referencing types contain attributes of the same object type as the one being defined. The object definition in the previous code block is a simple type, because all attributes are of the VARCHAR2, NUMBER, or DATE datatypes. Once the object type is created, a relational table can be created using the standard create table statement, or an object table can be defined using the create table of statement. A column is created implicitly in an object table to store a special value called an object identifier. The values stored in this column for each object can be used to identify the object as unique in the object table. No such object identifier exists in a relational table to identify unique objects. The following code block illustrates the creation of a relational table and an object table:

-- relational table
CREATE TABLE payroll_jul0613_reltbl
( empid VARCHAR2(10) NOT NULL,
emp_check paycheck_type,
CONSTRAINT pk_payroll_jul0613
PRIMARY KEY (empid));

-- object table
CREATE TABLE paycheck_jul0613_objtbl
OF paycheck_type;

There are different ways to reference data in a relational table containing object definitions and an object table. Selection of data from attributes of the object "column" (EMP_CHECK in this case) are done using dot notation to first reference the column name and then the attribute of the column. For example, user SALLY with EMPID 59385 can obtain the paycheck number for this pay period with a select statement that uses dot notation to refer to the check_number attribute on the EMP_CHECK column of the PAYROLL_JUL0613_RELTBL table. Examples of the select statements used for relational and object tables in this case appear in the following code block:

-relational table
SELECT p.emp_check.check_number
FROM payroll_jul0613_reltbl p
WHERE empid = 59385;

-object table
SELECT check_number FROM payroll_jul0613_objtbl
WHERE empid=59385;

To insert data into an object column, the user must refer to the object type that defines the column. This reference is called a constructor. The constructor is used for insertion of data into object tables as well. The use of insert statements on a relational and object table with use of constructors is listed in the following code block:

INSERT INTO paycheck_jul0613_reltbl
VALUES (39283,
paycheck_type(4596854,‘Acme’,‘HANSON’,‘14-JUL-2002’,
‘YES’,‘59439585’,‘00584857479’,2016.96);

INSERT INTO paycheck_jul0613_objtbl
VALUES(paycheck_type(4596854,‘Acme’,‘HANSON’,‘14-JUL-2002’,
‘YES’,‘59439585’,‘00584857479’,2016.96);

Creating Methods

The other component of an object is the set of methods that can be used to operate on it. Each method is a member function that contains input parameters and output values in the same way that procedures and functions have. In fact, the methods of an object are procedures and functions developed to operate specifically on the attributes defined as part of the object type. Unlike in Oracle7, however, where tables and procedures are designed, stored, and used separately, member functions and procedures are defined directly within the object type. One similarity the definition of the object type shares with something in Oracle7 is the use of package specifications and package bodies. In Oracle7, the developer can create a package specification to tell users of the package what procedures are available in the package. The body of the package is defined separately, and contains the application logic to be executed when someone references the procedure and passes the required parameters. The definition of an object type including some member functions is listed in the following code block:

CREATE TYPE paycheck_type AS OBJECT (
check_number NUMBER(20),
payer VARCHAR2(40),
payee VARCHAR2(40),
date_issued DATE,
payment_authorization VARCHAR2,
payer_account_number VARCHAR2(20),
routing_number VARCHAR2(20),
payment_amount NUMBER(10),
MEMBER FUNCTION adjust_amount (check_number IN NUMBER(10),
deduct_amount IN NUMBER(10)) RETURN NUMBER,
PRAGMA RESTRICT_REFERENCES (adjust_amount, WNDS));

Though not necessary, the developer can identify whether the type methods will modify database tables or not using the pragma restrict_references clause. The developer must identify the procedure or function that restrict_references refers to and the restrictions on the references of the procedure or function. In this case, wnds was used, which stands for "write no database state." Once the type is defined both with attributes and methods, then the developer can create the code body for the procedures, defined separately in the type body.

CREATE TYPE BODY paycheck_type AS MEMBER FUNCTION adjust_amount
( check_number in NUMBER(20),
deduct_amount in NUMBER(10)
) RETURN NUMBER IS
BEGIN
RETURN check_number - deduct_amount;
END; /* first end for member function end */
END; /* second end for type body end */

The applications can then refer to the methods for an object type using the same dot notation used to reference the attributes. The following PL/SQL statement demonstrates the use of a method from the PAYCHECK type to update the data in an attribute. The value( ) operation in the following PL/SQL block is designed to return all attribute values stored in an object so that those values may be placed into a variable declared to be the same type as the object table.

DECLARE
my_paycheck paycheck_type;
my_pay_amount NUMBER(10);
BEGIN
SELECT VALUE(p)
INTO my_paycheck
FROM paycheck_jul0613_objtbl p
WHERE check_number = 4596854;

UPDATE paycheck_jul0613_objtbl
SET payment_amount = my_paycheck.adjust_amount(my_paycheck.check_number,200)
WHERE check_number = my_paycheck.check_number;
END;

The creation of object types leads to the question of how to compare them. One handy method the developer may want to consider adding to every object type is the map or order method. It is simple to compare two columns in a relational table—the user can do so based on the value for some column, defined with a scalar datatype. This operation can extend into the world of object types as well, although inefficiently because the user must compare every attribute of one object to every attribute of the other. Oracle answers this question with the use of map methods and order methods. A map method is designed to tell the developer where the object appears in a group of objects, while the map method can be used to apply order to a set of objects. More information will be provided on these methods shortly.

Exercises

  1. What are the attributes of an object type? What are three different categories of object types? How do the attributes of the object type determine its category?
  2. What are the methods of an object type? What two components are there to the definition of the methods for an object type?
  3. What statement is used to create an object table? What is a constructor?

Reference and Collection Types

So far, all object types discussed have been simple types containing attributes declared with all scalar datatypes. Oracle8 also allows the definition of two new classes of datatypes, called reference types and collection types. These two datatypes are designed to allow object types to have referential integrity on a one-to-one and one-to-many level. However, reference and collection types extend the concept of referential integrity to a new level. In relational databases, foreign keys provide referential integrity between columns of the same datatype containing the same data. Reference datatypes are used to provide a one-to-one relationship between a row of an object table or object type column of a relational table and another object table or object type column of a relational table. Collection types are designed to store a grouping or collection of like-structured elements, to provide a one-to-many relationship in the same situation. This section will explain reference and collection types in further detail and provide syntax and examples for the creation of each.

Reference Types

Developers can use the reference type to define a "foreign key relationship" between two objects. The reference type can reference all columns in the table for a particular row—it is a pointer to a particular object within an object table. The definition of a reference type should include a definition of scope—the table to which the reference type will refer. A special keyword called ref should be used for defining the reference type. The definition of the reference type is demonstrated with the following code block:

CREATE TYPE debit_payroll_type AS OBJECT
(acc_wd_num NUMBER(10),
debit REF paycheck_type);

CREATE TABLE debit_jul0613_objreftbl
OF debit_payroll_type
(SCOPE FOR (debit) IS paycheck_jul0613_objtbl)

Once the reference type is defined, a special operation can be used to insert the pointer contained in columns defined as reference types into other tables so that the columns can reference the data for that object in another table. When handling data in conjunction with a reference type, the ref( ) operation can be used as demonstrated in the following code block:

INSERT INTO debit_jul0613_objreftbl
SELECT 1, REF(pc)
FROM paycheck_jul0613_objtbl pc
WHERE pc.check_number=4596854;

Important to remember in the example code block is that the paycheck object from object table PAYCHECK_JUL0613_OBJTBL whose check_number equals 4596854 is not actually inserted into object table DEBIT_JUL0613_OBJREFTBL. Instead, Oracle8 has created a pointer to the object! Recall, a reference type is a pointer to an object, not the object itself. Thus, table DEBIT_JUL0613_OBJREFTBL is less a table in and of itself, and more a new perspective on the PAYCHECK_JUL0613_OBJTBL table. Thus, selecting the reference type itself using the ref( ) operation produces not the data in the object, but the pointer used to refer to the object. The user can use the pointer value created as the reference type and execute a special function called deref( ) to produce the actual values from the pointer to those values.

SELECT DEREF(d.debit)
FROM debit_jul0613_objreftbl d
WHERE d.check_number = 4596854;

DEREF(D.DEBIT)
-----------------------------------------------------------------------------
PAYCHECK_TYPE(4596854,’Acme’,’HANSON’,’14-JUL-2002’,’NO’,59439585,00584857479 2016.96)

Collection Types

Objects can be collected into types. A collection is a gathering of like-defined elements. The two types of collection types available in Oracle8 are variable-length arrays with the VARRAY type and nested tables with the TABLE type. Focus first on VARRAYs. A VARRAY can be thought of as a "list" of objects, defined to have two special attributes in addition to those attributes contained in each of the objects contained therein, called a count for the number of elements in the VARRAY, and the limit for the maximum number of elements that can appear in a VARRAY. The limit is user defined, and if the number of elements stored in the VARRAY exceeds 4K, Oracle will store the data in the VARRAY in overflow. If the amount of data stored in the VARRAY is less than 4K, the data will be stored with the rest of the information for the object. Constraints and default values may not be created for elements in a VARRAY, and once created, the user only refers to an individual element in a VARRAY with PL/SQL (although SQL can be used to access the entire VARRAY). Using the payroll examples already covered, a user-defined type can be created for each employee of an organization, along with all the paychecks the employee received for the year, by check_number, and the amount the check was made out for, using a VARRAY created in the following way. Once created, the developer has an object table containing three elements: the employee’s unique ID, the employee name, and a 52-element array of check numbers and payment amounts.

CREATE TYPE pmt_rec_type AS OBJECT
(check_num NUMBER(20),
amount NUMBER(10));

CREATE TYPE ann_pmt_rec_type AS VARRAY(52)
OF pmt_rec_type;

CREATE TYPE emp_paycheck_type AS OBJECT
(empid VARCHAR2(10),
name VARCHAR2(40),
emp_ann_pmt ann_pmt_rec_type);

CREATE TABLE emp_paycheck
OF emp_paycheck_type;

The other collection type is the nested table. A nested table is exactly that—a table within a table. The nested table architecture is exceptionally suited for applications that in Oracle7 have parent/child tables with referential integrity. An example of the type of application suited for the use of nested tables from earlier in the Guide is the EXPENSE and EXPENSE_DETAIL tables, where the employee’s expenses were identified at a high level with the EXPENSE table, and individual expenses were identified as EXPENSE_DETAIL records. Use of nested table object creation for employee expenses is demonstrated in the following code block:

CREATE TYPE expense_detail_type AS OBJECT
(expense_detail_num NUMBER(10),
expense_detail_desc VARCHAR2(40),
expense_detail_amount NUMBER(15,4),
chargeback_acct VARCHAR2(40));

CREATE TYPE expdtl_nest_tbl_type AS TABLE
OF expense_detail_type;

CREATE TABLE expense
(expense_num NUMBER(10),
empid VARCHAR2(20),
reimburse_amount NUMBER(15,4),
expense_details expdtl_nest_tbl_type)
NESTED TABLE expense_details STORE AS expense_detail_nesttbl;

Nested tables and variable-length arrays share many similarities, but also have many differences. The differences between the nested table and a variable-length array can be identified as follows. The VARRAY works well for storing like units of information. These units ideally have few attributes, all of which may be populated and to which a specific order can be placed. Consider the example given of employee annual paycheck information. Each element in the array corresponds directly to the pay week for the year, in which order is important. With the presence of a limit attribute, the VARRAY also stores only a limited number of elements, while the nested table may store an unlimited number of objects, just as the base object table can. Another key difference between VARRAY and nested table data is indexing. An object column composed of VARRAY types cannot be indexed, while a nested table can be indexed. Storage for VARRAY data is also integrated with the storage segments of the data in the object table, up to a certain size. Data in nested tables is stored in another segment. In order to determine which object to use, consider the following rules of thumb:

If the amount of data to be stored in the collection object is under 4K, well defined, and limited in number, use VARRAY; otherwise, use TABLE.
If individual items in the collection object must be accessed, use TABLE; otherwise, use VARRAY.
If the data in the collection object must be indexed for performance, use TABLE; otherwise, use VARRAY.

Exercises

  1. What is a reference type? What is a collection type? How do reference and collection types model referential integrity? What ordinality is provided by reference and collection types within that referential integrity model?
  2. What data is stored in the reference type? What special keyword must be used in conjunction with defining a reference type? What is the deref( ) operation?
  3. How is a variable-length array defined? How is a nested table defined? Identify key differences between the variable array and the nested table.

Creating and Using an Object View

The final area of object relational databases to be discussed is the process of evolving a relational application into an object application. Although there is no pressing need to migrate a relational application into an object one, the organization may want to do so for several reasons, including the fact that object applications have more features for advanced data definition and usage. To ease the transition, object views can be created to allow object applications to access relational database objects, allowing for coexistence of both types of applications using the same base data. Object views are similar to Oracle7 views in that both views provide a method for simplifying the underlying complexity of data. In addition, object views allow the user to apply object concepts to relational data. Object views even allow for advanced update of data in relational tables via object views using instead of triggers.

Creation of object views begins with the creation of relational database objects like tables. Since the object view is designed to assist in the evolution of a relational database into an object database, it is assumed the developer understands the creation of relational tables. The creation of relational tables is covered in Chapter 3 and 6.

CREATE TABLE employee
(empid VARCHAR2(10) NOT NULL,
lastname VARCHAR2(30),
firstname VARCHAR2(30),
salary NUMBER(15,4),
CONSTRAINT pk_employee_01
PRIMARY KEY (empid));

CREATE TABLE address
(empid VARCHAR2(10) NOT NULL,
street1 VARCHAR2(30),
street2 VARCHAR2(30),
city VARCHAR2(30),
state_prov VARCHAR2(30),
postcode VARCHAR2(30),
CONSTRAINT pk_address_01
PRIMARY KEY (empid));

From the columns in these tables, the developer can create a more aggregate employee object view to represent the employee’s name, salary, and address using user-defined types that reflect the definitions of the object. First, the creation of some object types to facilitate object design is in order. In this example, the most easily identifiable object is the collection of columns that define an address, as displayed in the example below:

CREATE TYPE address_type AS OBJECT
(street1 VARCHAR2(30),
street2 VARCHAR2(30),
city VARCHAR2(30),
state_prov VARCHAR2(30),
postcode VARCHAR2(30));

After creating the object type, the developer can then create the object view, using the object type and the other columns from the original relational tables. The following block identifies that view:

CREATE OR REPLACE VIEW employee_obj_view AS
SELECT e.empid, e.lastname, e.firstname, e.salary,
address_type(a.street1,a.street2,a.city,a.state_prov,a.postcode)
FROM employee e, address a
WHERE e.empid = a.empid;

After the view is created, the developer can create a special trigger designed to ease the insertion of data into the relational table from the object application. This trigger is called an instead of trigger because the syntax for defining the trigger is create trigger instead of. The creation of an instead of trigger to support insertion of data on the object view created in this section appears in the following code block:

CREATE OR REPLACE TRIGGER empl_trigger INSTEAD OF
INSERT ON employee_obj_view FOR EACH ROW
BEGIN
INSERT INTO employee VALUES
(:NEW.empid, :NEW.lastname, :NEW.firstname, :NEW.salary);
INSERT INTO address VALUES
(:NEW.street1, :NEW.street2, :NEW.city,
:NEW.state_prov, :NEW.postcode);
END;

Once the instead of trigger is created, the user can insert data into the relational tables via object views. A constructor (described earlier in this chapter for use during the insertion of data into object types in relational tables or object tables) can be used for inserting data into object views.

INSERT INTO employee_obj_view VALUES
(49384,‘MANFRAN’,‘HARVEY’,‘90000’,
address_type(‘506 Pudding Street’,‘Apt. Q’,‘Moan’,‘WY’,‘70506’));

Exercises

  1. What is an object view?
  2. What device is used to insert data into relational tables via object views?
  3. How does the use of constructors relate to the insertion of data into relational tables via the object views?

Chapter Summary

This chapter discusses three new areas of database usage operation in Oracle8. The topics include new database structures related to indexes, the new extended ROWID structure used for referencing an increased number of objects in more tablespaces and datafiles, and the new object development architecture available for server-side object relational database development.

The first area discussed was the new database structures available in Oracle8. The new release of the Oracle8 database supports larger databases than ever before. Total database capacity is 512 petabytes, the maximum number of tablespaces allowed is about 2 billion, the number of datafiles per tablespace is 1,022, the number of columns supported in a table is now 1,000, and the maximum number of indexed columns is 32. The CHAR datatype now supports 2,000 bytes and the VARCHAR2 datatype now supports 4,000 bytes, and Oracle supports national language character sets more easily with the new NCHAR and NVARCHAR types. Oracle8 also supports new features for declarative integrity constraints. This includes new enable validate and enable novalidate statuses. The enable validate status in Oracle8 is the same as the old enabled status of Oracle7. The enable novalidate status of Oracle8 specifies that the constraint affects the entry of new data into the database but doesn’t apply to old data. These statuses can be used with the issuance of the alter table table_name enable novalidate constraint constraint_name or alter table name enable validate constraint constraint_name statements. Users now have the ability to defer the checking of integrity constraints until the time of a commit, rather than at the time the statement is executed. To defer all constraints that are set to be deferrable, the user can issue the alter session set constraints=deferred statement.

There are many new relational database objects available for the use of developers and DBAs to store and retrieve information. They include the presence of a reverse-key index, an index that stores key data reversed to improve index search performance in parallel configurations. Reverse-key indexes are created with the same create index statement as regular indexes, only the reverse keyword is used. An index can be reversed or unreversed with the use of the alter index reverse/noreverse statement. Another new feature is the index-organized table. This object stores all the data of a table in the format of a B-tree index, which is useful for storing data that will mainly be retrieved according to the primary key. Since the features of the table and the index are consolidated into one object, less space is used. An index-organized table is created with the create table organization index statement, to which the pctthreshold and overflow tablespace options can be added to keep key data for each row in the main index block structure while allowing extra space for row growth. An interesting point to be made here is that regular tables are created with an implicit organization heap clause, which can now be stated explicitly if the DBA would like.

There is an additional memory area the DBA can set up with Oracle8 called the large pool. The large pool stores session memory for the multithreaded server (MTS) configuration, I/O processes, and backup and recovery processes. Some of these processes require memory from the shared pool that can better be used to store shared SQL parse information. The large pool does not have an LRU algorithm for paging items out of memory. The large pool is configured with the LARGE_POOL_SIZE and LARGE_POOL_MIN_ALLOC parameters, which identify the size of the large pool and the minimum size a process can allocate, respectively. The defaults for these parameters are 300K and 16K, respectively, although LARGE_POOL_SIZE will default to LARGE_POOL_MIN_ALLOC if the second parameter is larger than 300K. The large pool can be at least as large as 2G, with physical/virtual memory permitting. Don’t ever size the large pool such that the entire SGA cannot fit into real memory—it is a performance killer!

Another new feature of the Oracle8 database is its ability to allow PL/SQL processes to call external procedures using library objects. Library objects can be thought of as aliases to the external processes, which must be written in C or converted into C to be executed. To create a library, use the create or replace library name as file statement. To run an external procedure, the PL/SQL procedure calls the library object, which then passes the request to the listener. The listener then spawns a special new process called extproc, whose location and characteristics are listed in the listener.ora file. Extproc then loads the external procedure, executes it with any parameters specified by the PL/SQL procedure that called it, and then returns any return data (if any) to the PL/SQL procedure.

To support the increased size for the Oracle8 database, an extended ROWID format is used. The Oracle7 ROWID format consists of three elements: block identifier, row identifier, and absolute datafile identifier. In contrast, the Oracle8 extended format for ROWID information consists of an object identifier, a relative datafile identifier, a block identifier, and a row or slot number. The Oracle7 ROWID format takes 6 bytes to store, while the Oracle8 ROWID format takes 10. The Oracle8 ROWID format representation is 18 characters long, with each character in a base-64 format. The range of characters used in ROWID representation are A–Z, a–z, 0–9, + and /.

Most applications should never have to worry about the new ROWID configuration in Oracle8. However, developers with applications that either read, create, or store ROWID information in any part of the database other than the ROWID pseudocolumn used to identify rows in the Oracle7 database should be aware of the existence of a special package called DBMS_ROWID. This package is designed to convert Oracle7 ROWID (also called restricted) format to Oracle8 (also called extended) ROWID format, and to display components of the extended ROWID format for better readability. Some important functions and procedures in the DBMS_ROWID package include rowid_to_extended( ), rowid_to_restricted( ), and rowid_info( ).

Perhaps the most interesting new feature in the Oracle8 architecture is the set of development structures that support the object relational features. This section covered the definition of Oracle’s object relational database, which is the existence of mechanisms for users to create their own datatypes, support of multimedia and large datatypes, and mechanisms called object views that allow the developer to begin building object-oriented database design on existing relational database objects. Other features of the Oracle8 object relational architecture include use of software add-ons, called cartridges, that are designed to extend the functionality of the database in whatever manner necessary, and overall support of larger databases that provide additional storage and performance for accessing data.

In the Oracle8 architecture, every object starts with the definition of an object type. An object type is created with the create or replace type as object statement. An object type is defined to have two parts: attributes and methods. Attributes are features of the object that store data in predefined or user-defined datatypes, while methods are features of the object that allow a prescribed action to be performed in conjunction with the object. The first part of an object type is its attributes. The attributes of an object can fall into three definition types: scalar datatypes (such as CHAR, VARCHAR2, NUMBER, and DATE), collection types (VARRAY and TABLE), and reference (REF). The scalar datatypes are the same as Oracle7 scalar datatypes, except that an object type cannot be defined with a LONG or LONG RAW attribute, or with any of the %type referential datatypes used in PL/SQL. The collection types are designed to store defined arrays of data (VARRAY) inline with the object, while the TABLE collection type is designed to allow the developer to create nested tables. The final collection type, REF, is designed to allow the user to create pointers to specific data elsewhere in the database by referencing it. The attribute defined as a REF type does not actually contain the data, only a pointer to it, but the pointer can be used to derive the data with the use of the deref( ) operation on relational tables containing columns defined with REF object types, or with the value( ) operation on object tables defined with REF types. The value( ) operation is used to obtain the row object, but needn’t be used in conjunction only with the REF type.

Once the object type attributes are defined, the developer can focus on creating methods. Methods are defined in the type as member functions, while the executable code that is associated with the object is defined as a type body. This setup is similar in concept to the creation of packages in PL/SQL. To create the type body, the create or replace type body statement can be used. Once the attributes and methods for the type are defined, the developer can create relational or object tables in the database based on the type. To manipulate or insert data into the database, the user must use the name of the object type in the insert statement to identify the values as attributes for that object. This reference to the object type is called a constructor.

Once the appropriate types are defined, the developer can use those types to create actual database objects like object tables and object views, or incorporate the object types into relational tables. A relational table containing object types is created with the same syntax as a relational table containing predefined scalar types. An object table is created after defining the object type using the create table name of object_type statement. An object view is a special view on relational tables that allows object applications to access them as though they were relational tables containing object types. The create view statement can be used for defining an object view so long as an appropriately defined object type is included in the definition. To update, delete, or insert data into the underlying relational tables associated with the object view, the developer can use a special trigger called an instead of trigger. This trigger inserts data into the underlying relational tables as defined by the trigger definition. The statement used to create this trigger is create trigger name instead of.

Two-Minute Drill

Oracle8 supports increased database size and new database objects, as well as a new object relational database architecture.
Total database capacity for database is 512 petabytes.
The maximum number of tablespaces allowed is about 2 billion.
The number of datafiles per tablespace is 1,022.
The number of columns supported in a table now 1,000.
The maximum number of indexed columns is 32.
The CHAR datatype now supports 2,000 bytes.
The VARCHAR2 datatype now supports 4,000 bytes.
The NCHAR and NVARCHAR2 datatypes are new in Oracle8, and are designed for national language support.
Declarative integrity constraints have a new status—enable novalidate—which is used to enforce the constraint on new data coming into the database but not on data already in the database.
Another status for constraints in Oracle7 called enable has been changed in Oracle8 to enable validate to enable the constraint and validate existing table data based on that constraint.
An integrity constraint can be put into enable novalidate status using the alter table name enable novalidate constraint constraint_name statement.
An integrity constraint can be put into enable validate status using the alter table name enable validate constraint constraint_name statement.
Constraint enforcement can be deferred until a transaction commits. Normally, constraints are enforced when a statement executes.
The ability to defer a constraint at all is built into the create table statement with clauses like deferrable and not deferrable, and the constraint’s default behavior is defined with the initially immediate or initially deferred clauses.
To defer constraints in the current session, issue the alter session set constraints=deferred.
Data in index keys can be reversed or set to their original order using the alter index name reverse/noreverse statement. The index can be created with index keys reversed using the create index reverse statement.
Reversing index keys is used to make data more accessible in parallel configurations.
To minimize storage on tables that will only be accessed via the primary key, an index-organized table can be used.
An index-organized table contains table data stored in a B-tree index structure. No additional indexes can be created on an index-organized table. However, the storage for the index-organized table is less than the storage required for an index and a table.
An index-organized table is created with the create table organization index statement. Since the index is the table, there is no need for a ROWID on the row data stored in an index-organized table.
A threshold limit for storage of any particular row in the blocks of the index-organized table can be set using the pctthreshold num overflow tablespace tblspc clause.
A new column on DBA_, ALL_, and USER_TABLES called IOT indicates whether the table is index-organized. This column can contain iot for the index-organized table segment, iot_overflow for the index-organized table overflow segment, and NULL for any other table.
The large pool is a new memory object in Oracle8 designed to store session information for multithreaded server and parallel server configurations. It can also support I/O processes and backup/recovery processes.
Total large pool size is determined by the LARGE_POOL_SIZE initialization parameter.
Minimum allocation of large pool space is determined by the LARGE_POOL_MIN_ALLOC initialization parameter.
Maximum size of the large pool can be 2G or more. DBAs should be careful not to size the large pool such that the SGA will be larger than real memory can support.
PL/SQL can call external procedures written in C using library objects.
A library object is created with the create or replace library statement.
The PL/SQL process calling the external procedure via the library object triggers the library object to call the listener process.
The listener process spawns another process, called extproc, that actually loads and runs the external process, passing it any parameters supplied from PL/SQL. Extproc can also pass return values to the library object to be used by the PL/SQL process calling the database.
The specification for loading and executing extproc can be found in the listener.ora file.
Oracle8 supports a new extended ROWID format for accessing more data. This format consists of an object identifier, a relative filename identifier, a block identifier, and a row or slot identifier.
Extended ROWID information is represented with 18 characters of base-64 format, with values in the ranges A–Z, a–z, 0–9, +, and /.
Developers needn’t worry about the changes to ROWID format unless they have an application that creates, reads, or stores ROWID data directly.
If the application creates, reads, or stores ROWID data directly, then the DBMS_ROWID package can be used to manage transition.
Key functions in DBMS_ROWID include rowid_to_extended( ) and rowid_to_restricted( ), used to convert a ROWID to extended (Oracle8) or restricted (Oracle7) format.
A key procedure in DBMS_ROWID is rowid_type( ), which takes a ROWID as input and returns the components of that ROWID for easy readability.
Oracle8 supports object relational databases with the incorporation of object types for definition of user-defined datatypes, more built-in datatypes, larger capacity, faster performance, and use of data cartridges for add-on functionality.
Users may define their own datatypes with object types.
The two components of an object type are attributes and views. Object types are created with the create or replace type name as object statement.
Attributes are data storage components of the object defined to have either predefined scalar, collection, and reference datatypes, or other user-defined types.
A scalar datatype is any datatype available in Oracle7 (object types cannot include LONG, LONG RAW, NCHAR, and NVARCHAR2 attributes or attributes defined with referential datatypes using the %type keyword) and the new LOB datatypes available in Oracle8.
A collection datatype can be either a variable-length array (VARRAY) or a nested table (TABLE).
Though similar in that both variable arrays and nested tables can store multiple object "rows" in connection to a single "row" of data in another object, there are key differences between the two.
If the amount of data to be stored in the collection object is under 4K, well defined, and limited in number, use VARRAY; otherwise, use TABLE.
If individual items in the collection object must be accessed, use TABLE; otherwise, use VARRAY.
If the data in the collection object must be indexed for performance, use TABLE; otherwise, use VARRAY.
A reference type allows the developer to create pointers in one row of one object table to objects in another object table. The reference type doesn’t contain the actual data; rather, it contains a pointer to the data in another object table.
To obtain the actual data in another table using the pointer, the deref( ) operation can be used for a relational table and the value( ) operation can be used in object tables.
Methods are the other component of an object type. They are used to define activities that can be performed in association with the object.
Definition of methods is similar to use of packages, with a specification included in the type definition and a body containing the application logic. The type body is defined with a separate create type body statement.
To insert data into a relational table or object table defined with an object type, that object type must be referenced by name in the insert statement. This reference is called a constructor.
Object views are designed to ease transition from relational databases to object databases by creating an object structure over underlying relational tables.
To insert data into the underlying relational data using object views, special triggers can be created, called instead of triggers. The syntax used is create trigger name instead of.
Hosted by uCoz