Using Oracle8
Oracle8 introduces six new datatypes and improves on five existing datatypes. This section describes the six new datatypes. (This section doesn't cover object-relational user-defined datatypes, which are described later in the section "Object-Relational Features.")
The four new LOB (large object) datatypes--BLOB, CLOB, NLOB, and BFILE--are much easier to use than the old LONG RAW, LONG, and RAW datatypes. Oracle8 has provided many packages that make it easy to deal with these new datatypes. These packages, used in SQL and PL/SQL, include DBMS_LOB to manipulate the data. With the old LONG datatype, you couldn't search the data or concatenate it with other fields, in addition to many other disadvantages. LONG data typically stored large text, and so the inability to search the text has been a problem for Oracle for years. Another improvement is that a table may contain multiple LOB columns; in earlier versions there could be only one LONG column for each table.
BLOB, CLOB, and NCLOB datatypes store up to 4GB of data. BLOB holds large binary objects, CLOB holds large character objects, and NCLOB holds large character objects stored according to the value of the National Character Set parameter value. The BFILE datatype is a new concept; it's a pointer to an external file, which is managed by the underlying operating system.
There are two more National Character Set datatypes in addition to NCLOB: NCHAR and NVARCHAR2. They store data according to the value of the NLS_NCHAR initialization parameter; if NLS_NCHAR is undefined, the data is stored according the value of NLS_LANG. NCHAR holds up to 2,000 bytes, NVARCHAR2 up to 4,000 bytes.
In addition to the new datatypes, Oracle8 increases the maximum size of many previously existing datatypes. CHAR and RAW can store 2,000 bytes, up from 255 in version 7.x. VARCHAR and _VARCHAR2 can store 4,000 bytes, up from 2,000 in version 7.x. This should help many applications immediately, as 2,000 bytes is just too small for some description fields.
In addition, a new Extended ROWID format supports new Oracle8 database features, namely tablespace-relative data block addresses and partitioned tables. The Extended ROWID is 10 bytes and contains the data object number (identifying the segment), the data file, the data block address, and the row number within the block. Oracle8's ROWID format varies from version 7.x.
To maintain backward compatibility with Oracle7 and earlier versions, the original ROWID format is supported (and now called the Restricted ROWID). This is the 6-byte format, which includes the data file number, the data block address, and the row number within the block. Oracle8 supplies the DBMS_ROWID package, which can convert between Extended and Restricted ROWID formats or provide detailed information about the ROWID. Table B.1 summarizes the differences in datatypes between Oracle8 and Oracle7.
Datatype: Oracle7 Range: Oracle8 Range:
CHAR up to 255 bytes up to 2,000 bytes
VARCHAR up to 2,000 bytes up to 4,000 bytes
VARCHAR2 up to 2,000 bytes up to 4,000 bytes
NCHAR N/A up to 2,000 bytes
NVARCHAR2 N/A up to 4,000 bytes
NUMBER up to 21 bytes up to 21 bytes
BLOB N/A up to 4GB
CLOB N/A up to 4GB
NCLOB N/A up to 4GB
BFILE N/A -external file; limited by the OS
LONG up to 2 gigabytes up to 2GB
LONG RAW up to 2 gigabytes up to 2GB
RAW up to 255 bytes up to 2,000 bytes
ROWID 6 bytes -10 bytes (extended) or 6 bytes (restricted) DATE from Jan1, 4712 B.C. from Jan1, 4712 B.C._ to Dec 31, 4712 A.D. to Dec 31, 4712 A.D.
Oracle8 has improved many aspects of table usage. One of the first things people notice is that tables may now contain 1,000 columns each, up from 254 in version 7.x. This is useful for some data-warehouse and mathematical databases. Oracle8 provides for new types of tables, including partitioned tables and index-only tables. There are also new object-relational features, such as nested tables and user-defined datatypes, which are described later in the "Object-Relational Features" section. Index-only tables are described later in the "Improved Indexes" section.
Oracle8 automates what was possible only by using complex and difficult triggers and views in Oracle7--partitioned tables. A table is partitioned by being separated into many pieces, each one containing a range of key values. Oracle8's query optimizer is aware of partition definitions, and so a range scan will search only those partitions containing the range of values requested by the query. When data is inserted into the table, Oracle automatically determines which table partition it should go into.
Storage parameters in partitions Storage parameters, such as the tablespace and the INITIAL extent size, can vary for each partition. However, the logical structure--including field order, datatypes, and field sizes--should be identical for each partition of a table.
Each piece, or partition, can contain its own storage clauses. Full table scans can be greatly improved, as each partition can be placed in separate tablespaces spread out over multiple physical drives. The full table scan will then occur with the disk I/O spread out over several drives in parallel. In addition, query performance may be further improved by knowing the data well. For example, partitions that aren't updated much can have _PCTFREE 10 and PCTUSED 90, whereas partitions that are greatly updated could have PCTFREE 40 and PCTUSED 60.
Partitioning a table also increases the data's availability and maintenance. For instance, if a disk-one partition crashes, only the data within that partition goes offline; the rest of the table's data can still be selected, updated, inserted, or deleted. Statistics can be performed on each partition independently. Exports, imports, SQL*Loader, and other maintenance can be performed on individual partitions without affecting other parts of the table.
This clause is really used for tablespaces but directly affects how Oracle8 allocates table extents. By defining the minimum extents for a tablespace, you can ensure that each extent of any object in the tablespace is a multiple of the specified size. This is important because it can reduce free space fragmentation in a tablespace, as each extent must meet a minimum size requirement. Minimum extents are defined in bytes.
Oracle8 introduces a completely new concept--the Object Relational database. Although it's not a true object-oriented database, its implementation is about halfway there. This actually works out well, because it's fairly easy to learn and contains object views used to display data from object-relational tables as if they were traditional relational, and vice-versa. In addition, Oracle isn't abandoning its relational roots. Object views ease the migration into the object-relational environment, and help connect object-relational databases with Oracle7 and other strictly relational databases.
Basically, the new object features allow the creation of abstract and user-defined datatypes and objects. What this means is that at the database level users can define their own datatypes based on subsets of columns, embed arrays of columns into a table, and nest tables within other tables. These are all described in more detail in the following sections.
Before Oracle8, developers had to try to create their business models with tables limited to a rigid collection of scalar datatypes. Business rules can now be conceptualized with object types, which are user-defined datatypes. For example, you can define a type, which we'll call baseball_player, based on three columns. Look at this example:
CREATE TYPE baseball_player AS OBJECT (ssn NUMBER, name VARCHAR2(100), batting_average NUMBER);
You can then define a column in another table as the baseball_player datatype (as opposed to NUMBER, VARCHAR2, and _so on).
In addition to defining a column as an ADT, you can use what are known as REFs, which point a column in a table to an object table. Object tables consist of one ADT, as in this example:
CREATE TABLE baseball_player_table OF baseball_player;
This new table can be referenced as a column in another table by using the datatype's REF clause, as in REF BASEBALL_PLAYER_ TABLE. Only object tables can be referenced in Oracle8. In theory, REFs can improve the performance of a parent/child relationship. Here is a sample CREATE TABLE command that uses the REF clause:
CREATE TABLE injury_list (team_id NUMBER NOT NULL, player REF baseball_player NOT NULL, injury VARCHAR2(4000), expected_recovery DATE);
ADTs and object tables can also encapsulate executable code to define business rules in ways that traditional constraints couldn't. This concept is known as a method. You can create a function in PL/SQL that tells Oracle how the object should work, and it is quite flexible. You define the method with the CREATE OR REPLACE TYPE BODY command, encapsulating a user-defined function. _For example, the following code creates a simple TYPE of basic player information (called player_level in this example). The METHOD, composed like a procedure, will return Major League if the player is in the Major League (league="ML") or Minor League (league="AAA","AA","A","R").
CREATE OR REPLACE TYPE player_level AS OBJECT (
team_id NUMBER NOT NULL,
player REF baseball_player NOT NULL,
league VARCHAR2(3) );
CREATE OR REPLACE TYPE BODY player_level IS
MEMBER FUNCTION what_level RETURN CHAR IS
BEGIN
IF league = `ML' THEN
RETURN (`Major League');
END IF;
IF league in (`AAA','AA','A','R') THEN
RETURN (`Minor League');
END IF;
END;
END;
Oracle8 now supports column arrays, in which a column can be repeated many times within a table. This is called a VARRAY, and the maximum number of entries is clearly defined. For example, a local softball team can have up to 25 players on the roster, but no more. A VARRAY can be defined with the maximum of 25 entries, and this will be stored in the table without having to define 25 columns, nor will it require an additional table as was required prior to Oracle8. This is shown as follows:
CREATE TYPE player_roster_varray AS VARRAY(25) of baseball_player;
Now a new table that contains each team's manager and roster can be created:
CREATE team ( team_id NUMBER, manager_name VARHCAR2(100), manager_phone VARCHAR2(20), roster player_roster_varray);
VARRAYs aren't always easy to implement when there is a large number of detail elements, so Oracle8 provides the nested table, which is essentially one table stored within another. This allows for a tight bundling of a many-to-one parent/child relationship, where each record of the table can contain many records in the nested table. The nested table is stored physically separately from the outer table in the database.
First you make an object table, and then you can nest the object table in another table. The following example creates an employee table and nests it in a department table:
CREATE TYPE employee_type AS OBJECT (emp_no NUMBER NOT NULL, last_name VARCHAR2(40), first_name VARCHAR2(40) middle_initial CHAR(1)); CREATE TABLE department (dept_no NUMBER NOT NULL, dept_name VARCHAR2(20), employees employee_type) NESTED TABLE employees STORE AS dept_emps;
As discussed briefly before, object views can show relational data in an object-relational format, and vice versa. This will help in the migration process from Oracle7 to Oracle8; it will also assist in exporting data from an Oracle8 database into an Oracle7 database. Due to the new object-relational technology, the _CREATE VIEW command has one additional clause: WITH OBJECT IOD. This will tell Oracle how to uniquely identify each record within the object view, as there may be many varied objects, ADTs, and tables joined within the view.
The new Recovery Manager--a user-friendly, GUI front-end tool utility that comes with Oracle8--is used to create, manage, and restore Oracle database backups. With Oracle7, it was necessary to purchase third-party scheduling software or to write scripts to physically copy files to tape or disk, as well as to communicate with the Oracle database if necessary. Setting up such environments is a full-time job for some people, is error-prone, and requires underlying operating-system scripting skills.
The Recovery Manager allows for easy and flexible backup environment management. You can schedule when backups start and stop, specify which objects are to be backed up, delete, copy, or move files, and inspect log files. It has its own CLI (Command-Language Interpreter) scripting language for increased flexibility and control. The utility operates by storing all backup-related information for several databases within a central repository, which must be an Oracle8 database. If there's a problem with the recovery catalog in the repository, the Recovery Manager can use information stored in a control file to perform recoveries.
More new Recovery Manager features will improve the speed of backups:
For a more in-depth look at using the Recovery Manager, see Chapter 15, "Using Recovery Manager for Backup and Recovery."
You can now apply archived redo logs to certain types of exports. The export is a logical dump of all object structures and data. Before Oracle8, when a database was recovered with an export/import, all transactions performed after the export took place were lost. This is no longer the case; transactions performed after the export can be applied through online and archived redo logs, which allows the Export/Import utilities _to be a more integral part of a full backup scheme. _The POINT_IN_TIME_RECOVER option for each tablespace must be specified in the Export utility's parameter file or at the Export command line for this to be true. Chapter 25, "Using SQL*Loader and Export/Import," explains how to use the Export and Import utilities.
Oracle8 provides for new types of indexes and improves the performance of Oracle7's regular and bitmapped index types. The new index types are reverse-key indexes, partitioned indexes, and index-only tables.
Reverse-key indexes are a creative new approach to improving certain kinds of searches. Regular indexes are stored in a b*tree format, where a tree-like hierarchy exists. To find a record, Oracle looks down branches to find the leaves. This is very efficient, except when there's a clustering of data beginning with the same characters. For instance, imagine indexing a column of last names with thousands of names beginning with the letter S or P, but only a few starting with X. Oracle would have to traverse more of the index tree structure to find the records starting with S, slowing performance by using more block hits. Reverse-key indexes are suited for this precise situation, as they store data backward, byte by byte. For instance, ETHAN would be stored as NAHTE in the index b*tree. This reverse order is useful only with equality searches.
Another advantage is that reverse-key indexes reduce contention for the last free leaf block in a sequence's indexed column; incrementing values are stored throughout the index tree, not just at the last leaf.
Oracle8 allows you to partition indexes, just as it does tables. This can improve performance and control of indexes. Each index partition can have its own unique storage parameters and can be stored in different tablespaces. You can explicitly put multiple partitions on different physical drives to reduce I/O contention on index scans.
Partitioned indexes can be local or global. Locally partitioned indexes are the easiest to set up and manage because they're partitioned the way the underlying table is partitioned. If a table has 10 partitions, the locally partitioned index will also have 10 partitions. A globally partitioned index is used for indexing columns that aren't a part of the underlying table partition key. You can just as easily use a non-partitioned index, but by indexing you can improve performance if you know the data's characteristics well.
You can create a table stored in a b*tree format through the _CREATE TABLE command's ORGANIZATION INDEX clause:
A good case for an index-only table is a table in which most columns are indexed. Another good case is to use a table that includes fields such as state, city, and zip code that can each be stored in an index-only lookup table.
Oracle8 provides many new options that can improve SQL queries, data manipulation, and object definitions. Many of these options are incorporated automatically by Oracle8 with no additional coding, including the partition-aware optimizer and the index fast full scan. Others require a small amount of additional coding in order for them to be used by Oracle8, including the NOLOGGING option of some SQL statements, parallel DDL (Data Definition Language), and parallel DML (Data Manipulation Language).
One issue database administrators have with managing large databases is that running in ARCHIVELOG mode to ensure point-in-time recovery slows data loads significantly. For instance, loading millions of records into a data warehouse would generate large amounts of archived redo logs, taking up physical disk space and slowing performance. Oracle8 solves this problem by introducing the NOLOGGING option to a few operations; these operations include direct load INSERTs, index creation and rebuilding, and CREATE TABLE AS SELECT...FROM.... With NOLOGGING, only minimal information is kept in the redo logs, so performance is improved greatly in the loading aspect and in any database recovery actions. Although NOLOGGING doesn't yet apply to all DML statements, it will prove invaluable to many large Oracle8 environments.
Oracle8 expands on Oracle7's parallel support for SELECT statements and index creations. Many additional operations can now be performed in parallel:
You also can do rollbacks in parallel. In the event of an explicit ROLLBACK command or a failed transaction (such as running out of free space while trying to extend a segment), Oracle8 performs parallel rollbacks. Keep parallel DML statements transaction-consistent
The parallel DML statement must be the only DML statement in a transaction. A _COMMIT or ROLLBACK _must be issued afterward. Unfortunately, replication doesn't support parallel DML.
You need to issue the following command in SQL*Plus, Server Manager, or any other product connecting to the Oracle Server in order for parallel DML to work:
ALTER SESSION ENABLE PARALLEL DML;
Oracle8 is aware of the characteristics of partitioned tables, thus the SQL optimizer can use this information to improve query speed. For instance, if a range scan only needs data from 2 out of 50 partitions of a table, Oracle scans only those 2 partitions. This works the same for locally partitioned indexes.
Oracle8 introduces a replacement to the full table scan: the FFS (index fast full scan). If an index contains all the requested columns for the query, Oracle scans the entire index instead of the entire table. If there are many more table columns, or even a few additional large table columns, FFS can improve performance significantly. For example, if there are 10 VARCHAR2(4000) columns in a table but not in the index, a full table scan reads (and ignores) these columns--FFS wouldn't even have to read the additional columns. The FFS is used with the INDEX_FFS query optimization hint.
Oracle8 provides a powerful suite of password management and control that greatly improves the security of Oracle databases. Much-needed security and password-related improvements included in competing databases yet lacking in versions before Oracle8 are now available. These new capabilities ensure that passwords conform to company standards. These include forcing users to change their passwords on a regular basis, locking a user account out of the database after several invalid login attempts, using password history to ensure that a password isn't repeated, and ensuring that the password conforms to DBA-designated syntactical standards.
All the new password functionality is controlled by new clauses in the CREATE PROFILE and ALTER PROFILE commands:
FAILED_LOGIN_ATTEMPTS PASSWORD_GRACE_TIME PASSWORD_LIFE_TIME PASSWORD_LOCK_TIME PASSWORD_REUSE_MAX PASSWORD_REUSE_TIME PASSWORD_VERIFY_FUNCTION
Security is enforced for a user by creating a profile with the appropriate limitations and assigning the profile as the default for the user account.
The new Oracle8 password management features follow:
Many new Oracle8 features greatly improve the application development environment. This runs from improved PL/SQL commands and packages to new types of triggers, views, and constraints. The following sections outline some of the more important changes to the application development environment.
Data in Oracle7 is checked against all applicable table constraints each time a DML statement is executed. Oracle8 allows for a new kind of constraint--the deferred constraint--which allows several DML statements to execute without checking the data against the constraint. The data is checked only when the COMMIT command is issued. This allows child records to be inserted before parent records. If there are constraint problems after the COMMIT, all transactions are rolled back and a related error is returned.
A constraint can be enabled without checking existing data with the ALTER TABLE...ENFORCE CONSTRAINT command. Only new data added after the command is checked. This is great for some environments, as there would be an iterative process of loading data, enabling constraints and pushing bad data into temporary tables, cleaning data, and reloading. The ENFORCE constraint is _a welcome new feature to data managers and application _developers.
Oracle7 allowed for updates to underlying tables of simple views. If a view were complicated, such as containing multiple tables, the underlying tables couldn't always be updated. Oracle8 introduces INSTEAD OF triggers, which can finally make all multiple table views updateable.
When a DML statement is executed against a view, the INSTEAD OF triggers fire with the appropriate UPDATE, DELETE, or INSERT statement. The triggers perform their actions in place of the DML statements. Because their logic can be defined individually, they can completely change the behavior of updating the views. The INSTEAD OF triggers are so flexible that they can be used to replace the expected UPDATE, DELETE, and INSERT behavior for even regular views.
There is now embedded SQL support for C++ with the new Pro*C++ product. Pro*Java may arrive in Oracle8.1. There is current support for JDBC drivers so that you can embed SQL commands in Java.
PL/SQL can execute external 3GL executables. C, at the time of this writing, is the only language supported; Java is coming next and will greatly expand Oracle's functionality. For example, Oracle doesn't have built-in advanced mathematical functions, such as integration, normalization, or cosine. At present, you can only call out to a C routine that has such functions.
PL/SQL also supports all the new object-relational data manipulations. Many new database packages have been added to increase database development, including DBMS_AQ (for the Advanced Queuing facility), DBMS_AQADM (also for the Advanced Queuing facility, but more administrative), DBMS_LOB (for LOB datatype manipulation), and DBMS_ROWID (for new ROWID formatting and conversion). All these packages are explained elsewhere in this appendix. More than a dozen changes were made to Oracle's internal handling of PL/SQL, all to improve performance or lower memory usage.
For a good introduction to PL/SQL, see Appendix A, "Essential PL/SQL: Understanding Stored Procedures, Triggers, and Packages," also on the Web site.
Queuing is a system that allows jobs to be executed after a task completes. Oracle8 introduces many improvements to Oracle's queuing mechanisms. Many such systems require middleware geared toward messages, or use transaction monitors as a necessity. Oracle8 simplifies all this by making the queues through PL/SQL or Tuxedo. They're managed in PL/SQL through the DBMS_AQADM package. Some features include ensuring that a request is executed only once, handling exceptions, supporting repeat execution, and expiring messages.
It's now possible to do parallel replication. Before, if a million-record table were being snapshot to another database, all records were sent serially every time there was a complete refresh. With even one snapshot like this, replication became a burden on the network and was slow. Larger replication schemes became extremely slow. Now that same table can be replicated in parallel, greatly reducing the replication time and making true large replication a reality.
New replication logic and _streaming The logic for replication has been moved from the use of triggers to the internals of the database kernel. With the new Net8 architecture, there's also a new replication streaming, where snapshot refreshes use fewer network round trips, reducing the overall network traffic and improving overall performance.
Other new replication features include the replication of tables with LOB fields, which was difficult to do in version 7.x. The new replication features also allow for replication of table _partitions.
A new WITH clause has been added for snapshot-related DDL statements. For example, using the WITH ROWID clause forces the snapshot to do fast refreshes by using the master table's ROWID. Using the WITH PRIMARY KEY clause forces the snapshot to do fast refreshes by using the master table's defined primary key.
Oracle8 provides new functionality for database management. _In addition to OEM (the Oracle Enterprise Manager) and _Server Manager, Oracle has included the SHUTDOWN TRANSACTIONAL option and the DB_VERIFY utility. You can use SHUTDOWN _TRANSACTIONAL to shut down the database quickly and cleanly. DB_VERIFY can check for database corruption on online and offline data files.
OEM and Server Manager haven't changed much since version 7.x, so they aren't explained in this appendix. For a detailed description of OEM, see Chapter 4, "Managing with Oracle Enterprise Manager (OEM)."
Oracle7 provided three shutdown options:
Similar to the new shutdown option, there's a new way to kill user connections with the POST_TRANSACTION option. Rather than instantly roll back uncommitted transactions and disconnect the user, POST_TRANSACTION specifies that any transactions now running should finish before disconnecting the user. This _is done with the ALTER SYSTEM DISCONNECT SESSION (SID, _SERIAL#) POST_TRANSACTION _statement. Oracle8 adds another shutdown option: SHUTDOWN TRANSACTIONAL. When this is issued, no new users may connect to the database, and those already connected may not start new transactions. Oracle waits for all transactions running during the SHUTDOWN TRANSACTIONAL command to finish. When all transactions are completed, Oracle disconnects all users and shuts down the database. This new option is useful as a kinder, gentler SHUTDOWN IMMEDIATE and doesn't interrupt transactions as drastically.
A new utility, DB_VERIFY, checks data files for possible corruption. It can run against a live database or against offline files. This comes in handy when checking to see if a backup ran properly on a data file before storing them to tape. DB_VERIFY is run from the underlying operating system, where parameters are passed at the command line, similar to the Export and Import utilities.
Oracle8 requires much less memory for each user to connect to the database. This takes effect with no database or application changes and is an immediate benefit after migrating from Oracle7. In some cases, memory is reduced by as much as 30 to 60 percent. In addition, more users can connect to the database simultaneously. This is especially good for Internet applications, where tens of thousands of users may be hitting the database simultaneously.
For most environments, having more than 1,500 users connect uses significant memory and should use a three-tier architecture to handle this volume. The three tiers would be the server using a network pooling MTS architecture, the transaction manager handling the user authentication, security, and connection pooling, and the client.
The new Net8 networking architecture improves pooling of resources to further improve memory management. For two-tier environments, the dispatcher automatically suspends inactive connections until they become active again. The result is that Oracle8 can support more clients than connections through this connection pooling mechanism.
With the introduction of Oracle8, the familiar SQL*Net networking software has new features and has been renamed Net8. Net8 increases the overall performance, management, and reliability of Oracle's networking environment. All the features of SQL*Net 2.3 have been kept, such as the Names Server and Multithreaded Server. The two biggest new features are connection pooling and session multiplexing.
Net8 comes with the Connection Manager utility, which has network-level multiplexing. When run in MTS (Multithreaded Server) mode, the Connection Manager reduces the total physical connections to the server. This is known as multiplexing connections, otherwise known as concentrating. It allows more users to connect to the database.
A new internal Net8 buffer improves MTS performance. There's also the possibility of a client-side cache that will pull some resources off the server and onto the client. Another feature that should give relief to some ports of SQL*Net is Net8's capability to automatically detect "zombie" processes that hang around the server after users disconnect from the database.
For an in-depth discussion of the Net8 features, see Chapter 24, "Configuring and Using Net8 Features."
© Copyright, Macmillan Computer Publishing. All rights reserved.