Previous | Table of Contents | Next

developer.com - Reference Library

Page 847

APPENDIX C
New Features of Oracle8

In this appendix

Page 848

Release 8 of the Oracle RDBMS adds new functionality to all aspects of the database environment. The first step in taking advantage of these new features is to know they exist. The following sections present a concise overview of the major changes in the database. Most of the topics mentioned here are covered in greater detail in other chapters of the text, but by going over the material covered in this appendix, you will know what to look for and expect from your Oracle8 database.

You will soon realize from looking at Oracle8 that Oracle has revamped its object relational strategy. Oracle8 has been optimized for large database support and operations, and the new features focus on this aspect of the database environment. Although it does introduce the beginnings of object-oriented database support, taking advantage of the new features for support of larger and more active database environments is the real reason to move to Oracle8. Oracle's own documentation describes this release as "evolutionary rather than revolutionary." This comes as a relief to many, who want a powerful, robust database engine to crunch huge amounts of data, rather than support for the newest bells and whistles to come out of the computer science departments. Oracle is moving cautiously into the object database arena, slowly introducing object-oriented features and capabilities for the early innovators, while concentrating on core relational database features and the backward compatibility that current customers need.

Changing from Oracle7 to Oracle8

Many familiar database components have been reworked and changed in Oracle8. The following sections will summarize the major changes.

Enhanced Database Datatypes

Oracle8 extends the maximum size of many database datatypes, as well as adding a few new ones. Table C.1 summarizes the database datatype changes from version 7 to version 8.

Table C.1Changes of Oracle7 Datatypes in Oracle8

Datatype Oracle7 Range Oracle8 Range
CHAR 1_255 bytes 1_2,000 bytes
VARCHAR2 up to 2,000 bytes up to 4,000 bytes
VARCHAR up to 2,000 bytes up to 4,000 bytes
LONG up to 2GB up to 2GB
NUMBER up to 21 bytes up to 21 bytes
RAW up to 255 bytes up to 2,000 bytes
LONG RAW up to 2GB up to 2GB
ROWID 6 bytes 10 bytes (extended) or 6 bytes (restricted)

Page 849

Datatype Oracle7 Range Oracle8 Range
BLOB N/A up to 4GB
CLOB N/A up to 4GB
BFILE N/A up to 4GB
NCLOB N/A up to 4GB
NCHAR N/A up to 2,000 bytes
NVARCHAR2 N/A up to 4,000 bytes
DATE Jan 1, 4712 B.C. to Dec 31, 4712 A.D. Jan 1, 4712 B.C.E. to Dec 31, 4712 C.E.

New Database Datatypes

As Table C.1 shows, there are several new additions to the core database datatypes. There are two categories of new datatypes:

The LOB datatypes solve a common problem that plagued application developers and DBAs throughout the Oracle7 days. Previously, in order to store binary or large amounts of data in a single table column, it was necessary to use the LONG, RAW, or LONG RAW datatype. These datatypes worked, but they were inherently difficult to work with and manage, in most cases requiring custom application code to manipulate and handle them. They also provided limited functionality; for example, you could not have more than one LONG column per table.

Oracle8 attempts to address these problems with the addition of three new datatypes:

CLOB is the character large object, BLOB is the binary large object, and BFILE is the binary file. CLOBs hold character values, BLOBs hold binary data, and BFILEs implement within the database kernel a common workaround for storing large columns. The BFILE is a pointer to a file stored externally to the database and managed by the database server OS. The LOB datatypes can be stored inline with the rest of the table's rows, or you can specify separate storage parameters for them.

The National Character Set datatypes (NCHAR, NVARCHAR2, NCLOB) are stored in the database according to the value of the National Character Set parameter value. By using National Character Set datatypes, the database can store data in a character set other than the database character set. The National Character Set value is controlled by the value of the NLS_NCHAR parameter. If this parameter is not specified, it will default to the value of the NLS_LANG parameter.

Page 850

Enhanced ROWID Format

Oracle8 has changed the format of the ROWID to support new features of the database. This is important to application developers who explicitly use the ROWID in applications. Oracle8 introduces the extended ROWID, which is a 10-byte format containing the data object number, data block address, and row number of the row the ROWID references. The digits in the extended rowids are represented as base 64 and are similar in structure to the rowids in Oracle7. Extended rowids are defined as:

000000 The data object number. This identifies the segment the row is in.
FFF The datafile the row is contained in.
BBBBBB The block in the datafile the row is contained in.
RRR The row in the block.

An extended rowid may look like the following: AAAAaoAATAAABrXAAE, where AAAAao is the object identifier, AAT is the datafile, AAABrX is the block in the datafile the row is contained within, and AAE is the row in the block. To support backward compatibility of Oracle7 applications, Oracle8 also supports a restricted ROWID, which is a 6-byte ROWID that is compatible with the Oracle7 ROWID format. The DBMS_ROWID is also packaged with the database and contains routines to provide the same information that would be available from the Oracle7 ROWID format.

Heterogeneous Services

The transparent gateways of Oracle7, which allowed access to non-Oracle data sources through the Oracle interface, have been revamped in Oracle8 and are now referred to as heterogeneous services. Many of the open gateway features have been pulled into the database kernel, and administration of different gateways is accomplished using the same administrative tools.

With heterogeneous services, agents running on any computer between the Oracle and target database translate Oracle requests into language that the foreign database can understand. Oracle as well as third-party vendors can provide agents to interact with heterogeneous services.

Internal Changes to the Database Engine

Several other changes to the internal database structure have been implemented in Oracle8. These include the complete reworking of Oracle Replication (Advanced Replication Option) that implements replication internally using kernel operations, rather than through the triggers used in Oracle7. Table column restrictions have also been relaxed. You can now have 1,000 columns per table, up from the limit of 254 in Oracle7.

NOTE
Although the first reaction to 1000-column tables may be that any table needing more than 254 columns should go back to the drawing board, some mainframe migrations and data warehousing implementations have the possibility of running up against this limitation.

Page 851

Oracle has also promised significant reductions in memory overhead for user connections in the database. Coupled with the 65,535 unique names restriction, this supports the "more data, more users" promise of Oracle8.

Supporting Large Databases

For most Oracle shops, the added features supporting large database installations are the most important reasons to consider upgrading to Oracle8. As you will see, this is one of the greatest areas of improvement. Oracle8 provides many new features that will help many administrators cope with the ever-growing mountain of data that needs to be "immediately available."

Partitioned Tables and Indexes

Mainframe databases have long had the capability of separating a large table into smaller pieces, each of which can be handled separately and thus managed easier. In Oracle7, many shops used table views to implement this functionality, creating separate tables and using a UNION ALL view to handle them. Oracle8 brings this functionality under the control of the database kernel, in the form of partitioned tables and indexes.

A table is partitioned by specifying a number of discrete files (partitions) in which to store the table data. Each partition of a table holds a certain range of data, as determined by a key value. To use an obvious example, consider a table that stores order information. The table holds the order line items for a year's worth of orders. A possible partitioning scheme would partition the table into four portions based on the quarter in which the sale was made. Here is the SQL to create this table:

CREATE TABLE line_item (
  invoice_id   NUMBER NOT NULL,
  stock_id     VARCHAR2(45) NOT NULL,
  quantity     NUMBER,
  order_date   DATE )
PARTITION BY RANGE (order_date) (
  PARTITION order_q1
  VALUES LESS THAN (`04-01-1998')
  TABLESPACE order_q1,
  PARTITION order_q2
  VALUES LESS THAN (`07-01-1998')
  TABLESPACE order_q2,
  PARTITION order_q3
  VALUES LESS THAN (`10-01-1998')
  TABLESPACE order_q3,
  PARTITION order_q4
  VALUES LESS THAN (`01-01-1999')
  TABLESPACE order_q4
);

The value of partitioned views lies in the fact that each partition of the table can be maintained separately from its related partitions. Each partition can be stored in separate tablespaces, and they can be taken off- and online independently of each other. Although each partition has the

Page 852

same logical attributes—such as columns and column constraints—the specific storage characteristics of each, such as PCTFREE, PCTUSED, PCTINCREASE, and so on, can differ.

Indexes can also be partitioned, in much the same way as tables. When an index is partitioned in the same way that the referencing table is partitioned, it is called a local index. An index that is partitioned differently than its referencing table is called a global index. A partitioned index can be created on a nonpartitioned table, and vice versa. The benefits of using partitioned indexes are the same as those of using partitioned tables.

Oracle8 has expanded its DML syntax to include partition-specific operations, as well as making many of its tools aware of partitions. You can specify inserts, updates, and deletes on specific partitions, as well as specify a specific partition to use in SQL*Loader or Export/Import. DML operations have been expanded to easily enable select, insert, update, and delete operations to be performed on partitions in parallel. Finally, the ALTER TABLE command has been modified to enable partitioned table maintenance and support operations, such as adding a partition, merging adjacent partitions, converting a partition to its own nonpartitioned table, and converting a table into a partitioned table.

Direct Load Insert and NOLOGGING

The bane of the DBA that must support large warehousing and OLTP applications in the same database is managing the redo and archive logs. During the day, it is essential that archived redo logs are generated and kept safe, to ensure that recovery of all online transactions is possible. At night, when the batch jobs that refresh your data tables or load data from other sources are running, the time involved with logging redo as well as the space taken up by archived redo logs is a pain.

In Oracle8, you have the option of inserting data directly into database blocks, similar to using the direct-load option of SQL*Loader. This avoids the overhead involved in transferring blocks to the block cache and back into the datafiles, as well as keeping cached data blocks in the block cache. Direct-load inserts are specified by using the APPEND hint in the INSERT statement, as in the following example:

INSERT /*+ APPEND */
INTO emp
SELECT * FROM big_emp;

Using a direct-load insert with NOLOGGING enabled maximizes insert performance. NOLOGGING is a new table, index, and partition attribute specified on object creation or with the ALTER command. It specifies that minimal redo information should be written when transactions affect the object. Only certain operations, including direct load INSERTs, index creations, CREATE TABLE AS SELECT, and index rebuilds can be performed as NOLOGGING. Unfortunately, transactions that make use of distributed database objects cannot be performed as NOLOGGING. The combination of a direct load INSERT with NOLOGGING will prove invaluable to those running databases as described previously or any shop that frequently inserts data that does not need to be logged to redo.

Page 853

Enhanced Parallel Processing Support

Oracle8 extends the capability of working with very large data sets with parallel DML support of INSERT, UPDATE, and DELETE operations. In Oracle7, you were given a taste of parallel support with index creations and select statements. In Oracle8, parallel operations are supported for the following:

To make use of the enhanced parallel processing of Oracle8, the ALTER SESSION ENABLE PARALLEL DML statement must be issued at the beginning of the transaction. Configuring parallel execution is specified as it was in Oracle7, with the init.ora parameters and with the PARALLEL hint or PARALLEL table clause used to specify the degree of parallelism to use in the operation. When parallel DML is coupled with other Oracle8 enhancements (direct load INSERTs, NOLOGGING, and partitioned tables and indexes in particular), significant time and resource savings can be realized when compared to previous methods of execution.

Index Fast Full Scan

Finally, an alternative to the full table scan is introduced with Oracle8: the index fast full scan (FFS). An index fast full scan can be performed on a table when the index contains all the columns requested in the query. The entire index will be read using multiblock reads, which can be significantly faster than the related full table scan. An index fast full scan is specified using the INDEX_FFS hint, and it should be used when the size or number of columns in the index is significantly less than the size or number of columns in the table. To specify that an index fast full scan should be performed, include a hint as shown in the example below:

SELECT /*+ INDEX_FFS(emp, emp_idx) */ emp_id, lname, first_name
FROM emp;

Supporting Object-Relational Features

For all the hype and anticipation, the initial object relation support offered by Oracle 8.0 is fairly tame and thankfully easy for the DBA to grasp. In the following sections, you look at the object relational support bundled with the first release of the Oracle8 server.

Page 854

Abstract Datatypes

As has been said before, Oracle release 8.0 is Oracle's first tentative step into the realm of the object-oriented database. The focus of the object features in this release concerns the capability of creating abstract and user-defined objects and using these objects to build other more complex database constructs. The creation of these objects is accomplished by creating types or descriptions of the objects, which are then stored in the data dictionary and used as column and variable datatypes. This enables the developer to conceptualize business rules into formal database constructs, which can be used in a natural way to create applications and implement business logic.

An object must be defined before it can be used. The syntax to create the object structures uses the CREATE TYPE command, as illustrated in the following:

CREATE OR REPLACE TYPE person_object AS OBJECT (
  ssn          NUMBER,
  last_name    VARCHAR2(50),
  first_name   VARCHAR2(50) );

Once defined, a type can be used in a table declaration as a reference (REF) to an instance of the object. You create an instance of a type using the CREATE TABLE command, as in the following:

CREATE TABLE person_table OF person_object;

CREATE TABLE hr_detail (
  emp_id       NUMBER,
  dept         NUMBER,
  supervisor   NUMBER,
  hired        DATE,
  person       REF person_table

Objects can also have methods or procedures that perform logical functions based on the contents of the object. An example could be a work ticket object that contains the problem type, start time, end time, and person assigned to. A method could be assigned to the object to determine the amount of time a work ticket was open. The DDL to create this example is shown in the following:

CREATE OR REPLACE TYPE work_ticket_o AS OBJECT (
  ticket_id     number,
  open_date     date,
  close_date    date,
  assigned_to   varchar2(50),
  dept_id       number );

CREATE OR REPLACE TYPE BODY work_ticket_o IS
  MEMBER FUNCTION time_open RETURN NUMBER IS
    BEGIN
      RETURN (open_date _ close_date);
    END;
END;

Page 855

Variable Arrays

Variable arrays, or VARRAYs, are an Oracle8 datatype created to support the storage of repeating groups in the parent table. They enable arrays of data, as specified by the type declaration of the VARRAY, to be stored inline with table data. A VARRAY is specified in the same way an object is declared, as in the following example:

CREATE TYPE students_varray
AS VARRAY(50) of student_object;

A VARRAY can be declared as one of the built-in Oracle datatypes or as a previously defined type. After the type is created, the VARRAY can be used in table declarations, as follows:

CREATE class (
  class_id     NUMBER,
  location     VARCHAR2(15),
  attendants     students_varray );

This table will now hold the class_id, location, and up to 50 student records for students attending the class. In addition to table columns, VARRAYs can be used as object type attributes or PL/SQL variables.

Nested Tables

Another new method for storing table data is in the form of the nested table. A nested table is merely a table stored within another table. The nested table exists only in the context of the parent table, and at present time only one level of nesting is allowed. Future versions of Oracle may feature nested tables in nested tables. The nested table is defined using a table of an object, as in the following example:

CREATE OR REPLACE TYPE class_o AS OBJECT (
  id                   NUMBER,
  semester             VARCHAR2(10),
  name                 VARCHAR2(25),
  concentration        VARCHAR2(25),
  professor            VARCHAR2(50) );

CREATE OR REPLACE TYPE class_t AS TABLE OF class_o;

CREATE TABLE student (
  id           NUMBER,
  SSN          NUMBER,
  lname        VARCHAR2(50),
  fname        VARCHAR2(50),
  classes      class_t)
  NESTED TABLE classes STORE AS student_classes;

The decision to use nested tables should be made more from a design view. The only performance gain we get from using nested tables comes from the fact that fewer disk blocks will need to be searched or read for the data. On the other hand, because the data is stored together, if you are not retrieving data from the nested table structure, you will still have to read the blocks. Thus, the performance gain you might achieve on the one hand is canceled out on the other.

Page 856

DML operations on nested tables require you to reference the nested table in a subquery. The subquery identifies the target nested table from the parent table. To perform these actions, Oracle has added a new keyword to their SQL language: THE. Using the THE keyword, you can specify what nested table object you are referencing in the DML statement and perform your desired action.

To understand this usage, consider the following example. Suppose you have a table CLASS, with the following definition:

class_id number
class_name varchar2(45)
participant student_type
professor_id number
semester varchar2(10)
year date

The participant row in this table is actually a nested table of Student_type. Student_type is defined as:

student_id number,
major varchar2(35),
grade_level varchar2(15)

To update data within the participant nested table, it is necessary to refer to the nested table in terms of its parent row. This is accomplished using a subquery that utilizes the THE keyword. The following examples illustrate this point.

INSERT INTO THE
  (SELECT participant FROM class WHERE class_id = 42)
VALUES
  (12,'CIS','SENIOR');

The previous example shows how a new value would be inserted into the nested table. The nested table is first selected from the parent table with the query identified by the THE keyword. The DML statement modifying that table is issued as usual. Another example is shown below:

UPDATE THE
  (SELECT participant FROM class WHERE class_id = 23)
SET major = `ENGINEERING';

While the query itself makes little sense, the general format and method when working with nested tables should be obvious. In this statement, all rows in the nested participant table for classes with a class_id of 23 will have their major set to ENGINEERING.

Object Views

Object views are views that join relational tables and perhaps object tables into a view that is represented as an object. This can be used by object-oriented development tools as an object,

Page 857

or as a first step in migrating an application to an object-oriented schema. Object views can help ease the transition from relational to object-oriented thinking, as well as provide a physical means to view data in an object-oriented manner—as well as relational.

To create an object view, you must first create a type that contains all the columns in the relational tables on which you want to create the view. The syntax to create the object view is similar to normal view creation, with the addition of the WITH OBJECT OID (unique_column) clause, which tells Oracle which column in the view to use to uniquely identify each row.

Administering Oracle8

Oracle8 is not all about application enhancements. The administrator is also given quite a few new tools and commands to help ease the ever-more-demanding job of administering the enterprise databases.

Enhancements to Password Administration

The Oracle8 release brings Oracle user and password maintenance out of the dark ages and provides long-awaited features and functions critical to administering the enterprise database. The many enhancements to password administration include automatic account locking when a specific number of invalid login attempts are registered, password expiration, a password history that provides the ability to enforce unique passwords, and the option of enforcing password complexity. All these options are enabled through extensions of the user profile.

Backup and Recovery Optimizations

Often, the motto of the DBA is "back up or die." The database backup is the single most important aspect of the database administrator's job. Oracle8 provides much needed support for backup and recovery of the database, through kernel integration of many backup and recovery features as well as the introduction of the Recovery Manager.

The Recovery Manager tool consists of a character or GUI front-end with its own command language and provides an interactive interface to database backup and recovery. It is usually used with a recovery catalog—a set of database objects that store information on all activities related to backup and recovery performed against the databases. Using Recovery Manager, you can do the following:

The command language is powerful enough to enable you to script many common backup tasks, which can be set to run at various times or when various thresholds are reached. Many DBAs will find Recovery Manager an invaluable tool in easing the administrative workload associated with running an Oracle database.

Page 858

shutdown transactional

Oracle8 adds one more method of shutting down an Oracle database: shutdown transactional. You can use this method when you want to minimize client interruption and prevent the loss of data, while at the same time not wait for users logged in to the database to exit.

When you issue the shutdown transactional command from Server Manager, no new clients will be allowed to connect to the database, and no new transactions will be allowed to start. When currently running transactions are completed or aborted, the database will shut down immediately—disconnecting the remaining clients.

Disconnect Session Post Transactional

The ALTER SESSION DISCONNECT command has also been enhanced, with the addition of the POST_TRANSACTION flag. When a disconnect command is issued with this flag, the disconnect will occur only after the transaction currently executing has completed. This prevents any interruption of current client activity from occurring.

Minimizing Database Fragmentation

An additional storage parameter, MINIMUM EXTENT, reduces fragmentation and space wastage in a tablespace by forcing new extents to allocate space in multiples of the MINIMUM EXTENT value.

New Replication Options

Replication has had several major modifications to improve the performance and scalability of distributed databases. Perhaps most important is that the replication logic is no longer handled with database triggers, but has been encapsulated in the database kernel.

Replication can also be performed in parallel, which results in large performance improvements. Replication has also been updated to allow new Oracle8 structures, such as LOBs and partitions, to be replicated.

Developing Applications

Although not obvious from the items focused on so far, Oracle8 is not all about the database administrators. There are several new items of particular interest to Oracle application developers. The biggest change most application developers are going to face is learning how to deal with the new objects, the syntax for referencing object types, and the right place to use these new features. The major features of interest to application developers are described in the following sections.

External Procedures

With Oracle8's support of external procedures, code can be written in other languages and called from within the database, through PL/SQL routines. External procedures give you the flexibility of writing routines in external 3GL languages, while at the same time keeping all code internalized within the Oracle database environment.

Page 859

Release 8.0.3 of the Oracle Server supports only procedures written in C, but future support of other languages (including Java) is promised. External procedures must be stored in dynamic link libraries (.so extension on UNIX, .dll on Windows NT). To use an external procedure, you must first create a PL/SQL library, which is a pointer to the operating system file where the DLL is stored. Then you must register the procedure you want to use by encapsulating the procedure within a PL/SQL block. The procedure can be registered in an anonymous block, package, procedure, or trigger and in an object type's method. An example of using an external procedure follows.

Consider the math utility library mathlib.so. You want to use several of the routines in this library, because they are considerably faster than their counterparts written in PL/SQL. Before you can see any of the procedures in this DLL, you must create a PL/SQL library, as shown in the following statement:

CREATE LIBRARY math_lib IS
`/usr/local/lib/mathlib.so';

Note that the complete path to the DLL must be specified. You decide that you want to use the SQRT function in this package. You cannot call the function directly; you must register the function. The following SQL shows this process:

CREATE FUNCTION SQRT (X BINARY_INTEGER)
RETURN BINARY_INTEGER AS
-- math_lib is the PL/SQL library name
  EXTERNAL LIBRARY math_lib
-- SQRT is name of procedure in library
  NAME "sqrt" LANGUAGE C;

Now, you can call the SQRT function in your PL/SQL programs, which will reference the sqrt procedure in the external DLL.

Index-Only Tables

Index-only tables are new database segments introduced to eliminate the redundancy of lookup tables and their indexes in the database. In a typical lookup table (for example, a STATE or ZIPCODE table), there are only two (or maybe three) columns: the key you have, and the value you are looking up. Most implementations will index all the columns in the table to maximize performance. In Oracle8, these structures can be stored as index-only tables—tables stored only as index segments.

By storing lookup tables and the like in index-only table segments, you reduce the space usage of the data as well as the complexity of the database design.

Reverse Key Indexes

A common problem in active tables that use sequences (or similar structures) to generate primary keys is contention for the last free leaf block. This happens because everyone is inserting an index value at the end of the index. Reverse order indexes attempt to solve this problem by storing the index key in reverse byte order. For example, a column with a key value of ORACLE would be stored as ELCARO (or a sequence value of 122 would be stored as 221, 123 as 321).

Page 860

Storing the key values in reverse order eliminates the contention for the last leaf block because values are more likely to be distributed throughout the index. However, reverse order indexes can be used only for equality lookups, because by storing the index in reverse order, the relationship or sequence of any data (which would be used for range scans) is lost. For example, consider an index on first_name. The index contains values for Janet, Richard, William, Janis, Russell, Jay, Tracy, and David. If these columns are stored in a normal B*-Tree index, and you issue a query searching for all rows with first_name columns that start with J, the first J entry could be found in the index, and the leaf blocks would be traversed until the last J was found. In a reverse key index, however, the actual values stored are tenaj, drahcir, mailliw, sinaj, llessur, yaj, ycart, and divad. Obviously, the algorithm above cannot hold true for this data set!

Instead Of Triggers

Views have long been used to enforce security and data integrity rules that were impossible to implement using built-in database functions. With release 7.3, you were given a taste of updateable views; however, the conditions under which a view can be naturally updated is of such severity that the actual application is of limited scope. Oracle8 attempts to address these shortcomings with the addition of Instead Of triggers, which is a new class of triggers that are defined on views. These triggers intercept any insert, update, or delete operation against the view and perform their actions, "instead of" the DML statement affecting the view itself.

Instead Of triggers can be used to implement all manner of business logic at the database level. The natural usage would be to allow a view to be updated where it does not meet the strict requirements for an updateable view. Application developers as well as DBAs will no doubt find many creative uses for these triggers.

Data Integrity Management

Oracle8 introduces two new ways for application developers and DBAs to manage data integrity: deferred constraint checking, and the ENFORCE CONSTRAINT command. Deferred constraint checking will come as a blessing to many application developers and solves a common problem arising from using Oracle's methods to enforce data integrity—that of a relationship that is mandatory at both entities.

To illustrate this problem, consider a publishing company that prints books. In this company, each book must have one or more authors, but because it doesn't allow freeloaders, each author must also be assigned to a book. The application has a book table and an author table. The problem arises when creating the initial book and author records. Because of the mandatory one-to-many relationship, you can't create the book record first and then the author (or vice versa), but you really don't care if this situation occurs briefly as long as the final product fulfills the mandatory requirement. A deferred constraint enables you to delay the constraint checking until the transaction is committed. So in the example, the rows for the book and author tables could be inserted one after another, with the mandatory relationship being enforced when you commit the transaction.

Page 861

The other addition to the DBA arsenal is minor, but provides welcome relief to those managing large warehousing or online databases. The ENFORCE CONSTRAINT option of the ALTER TABLE command allows you to enable a disabled constraint without checking the existing data for compliance to the constraint. This is excellent for shops that disable constraints for data loads and refreshes and enable them when finished, because checking the existing constraints is often a timely and resource-intensive operation.

Page 862

Previous | Table of Contents | Next