Oracle RDBMS Technical Feature Summary

See below a fairly extensive listing and in depth explanation of the technical features of the Oracle RDBMS.

I hope this information is helpful to answer some of the Oracle vs whoever comparison requests and for the Oracle faq. Please let me know if you think it is wasted bandwidth, or if you'd like to see more of this kind of stuff (I have lots still)

Regards,

• Dan

Daniel Druker
Anderson Graduate School of Management at UCLA                    

| Dan Druker                                                               |
| agsm mail 	: ddruker                                                  |
| internet 	: ddruker_at_agsm.ucla.edu                                    |
| oracle*mail	: unix:ddruker_at_agsm.ucla.edu                               |
----------------------------------------------------------------------------

Disclaimer: None. I'm a student now and I don't care what you think.

ORACLE RDBMS The ORACLE RDBMS is a portable, distributed, and open Database Management System based on the Structured Query Language (SQL), plus a complete set of integrated software tools for application development and decision support.  

The ORACLE RDBMS is the kernel of the ORACLE product. It features an unprecedented degree of data independence, allowing changes to the structure of the databases without affecting existing programs and queries. It is based on the relational model, and has an active, integrated Data Dictionary and full security facilities.

ORACLE Transaction Processing Option

The ORACLE Transaction Processing Option (TPO) provides features to meet the demanding requirements of on-line transaction processing applications:

• High transaction rates (over 1000 transactions/second)
• Fast response (subsecond response times)
• Many on-line users (100's of users)

It includes a sophisticated concurrency control mechanism, the Row Lock Manager, which provides row-level locking for maximum update concurrence. The Row Lock Manager also provides row-level multi-versioning for consistent queries that do not block simultaneous updates.

Truly Relational
ORACLE is a true relational database. It is structured as a collection of two-dimensional tables. The data in the dictionary is identical in form to the tables in the database. Security is implemented in the same manner for both.

• Data Dictionary ORACLE includes an integrated and active Data Dictionary. As tables are defined to the system, their definition is recorded immediately in the Data Dictionary, as are modifications of existing table definitions. Data definition functions can be performed interactively, without impact on other users of the system.

The ORACLE Data Dictionary is involved in all access to the database, whether that access is from an interactive SQL user, an application program, or a utility program, interactive or batch. The Data Dictionary records information about the database, about the users of the database, about the tables in the database, etc. Among other information, the Data Dictionary contains definitions of all tables, data elements, views, indexes, access privileges, and storage allocations for tuning purposes.   

The Data Dictionary is structured as a collection of tables that can be queried using the same SQL commands used to query ordinary user tables.

• Record Format ORACLE's internal table/record format consists of variable length fields and records. ORACLE truncates trailing spaces from character data. Numbers are stored as packed decimal and leading zeroes are eliminated. Dates are stored as fixed length, fields of 7 bytes. The concept of null, or missing values is fully supported. The null value is stored as one byte if it falls between columns with data values. If a null value falls at the end of a row, it is not stored, and does not take up any space.

ORACLE Supports:

    Char
    Varchar
    Text
    Number
    Integer (up to 38 digits of precision, supports K (multiply by 1024) and M (multiply by 1048576) syntax)

    Float (ranging from 1.0 X 10e-129 to 9.99 X 10e- 124)

    Date (date and time with extensive date arithmetic and functions) Valid Ranges: January 1, 4712 BC to December 31, 4712 AD.

    Long (65,535 bytes) (next release: up to 2 Gig)     Long Raw (64K of binary)
    Raw (255 bytes binary) (note: SQL*NET converts between EBCDIC and ASCII)

    Rowid (the value that uniquely identifies each row in every table).

• SQL ORACLE's SQL is an extension to ANSI standard SQL. ORACLE's extensions to the SQL standard fall into several areas including datatypes, and query expression operators. Unlike other relational systems, matching other than equality can also be specified as the criteria for the joining of two or more tables with SQL*Plus. In addition, ORACLE supports the "outer-join", which retrieves the non-matching rows, as well as the matching rows. ORACLE supports a Date datatype which affords validation upon input, various date arithmetic functions, and a multitude of format choices.

Also included are a number of scalar functions for operating on numeric and character data. These functions can be used anywhere that a simple column name can be used. The following list is just a subset of the supported functions:

VAR Variance
STD Standard Deviation
SQRT Square Root
ABS Absolute Value
SUBSTR Substring
UPPERUpper Case
LOWERLower Case
ROUNDRounding Function
TRUNCTruncation Function
MOD Remainder Function
LPAD, RPADString padding
LTRIM, RTRIM String trimming
SOUNDEX Finds similar sounding names

SQL*Plus:

SQL*Plus is a 4th generation tool that delivers a full implementation of SQL as well as powerfull report-writing and data transfer capabilities. All users of the system can work with SQL, from non-technical persons, using SQL for simple data retrieval; to programmers, who embed SQL commands within programs to manipulate data; to database administrators, who use SQL to control access to data and tune the database for performance. The same language is used in all ORACLE environments; online and from batch, interactively or from programs, all facilities of SQL are used in the same way.

SQL*Plus is a very powerful, and flexible 4th generation language used for searching and joining data. Its search features include, but are not limited to:

• Nested Queries - there is no limit to the number of queries which can be nested inside parent queries
• Correlated Queries - used to answer multi-part questions whose answer depends on the value in each row of the parent query, rather than the entire query
• Views - logical tables, which give the appearance of data structures differently from those actually present in the database. Views can be queried the same way as tables. A view might present a vertical (certain columns) or a horizontal (certain rows) subset of the base table.
• Partial String Searches - via the use of single and multiple character wildcards.
• Boolean Expressions
• Query Expression Operators include UNION, INTERSECT and MINUS.
• (+) to indicate an outer join and CONNECT BY to allow representation of hierarchical.

ORACLE does not place limits on the number of joined tables in a SQL query, however, a practical limit is 16.

Security

The ORACLE RDBMS provides an extensive security system, defined by the ANSI committee when the SQL language was standardized. The primary security facility evolves from the recognition of a "user" by the ORACLE RDBMS. Each user must be explicitly "granted" access to a table; during this grant operation Add, Change, Delete, or Select authority is requested. In this way, a user who has not been granted authority to select a table does not even have the capability to determine if the table exists. Views, or logical windows, into single or multiple tables are another way to add a layer of security to the system. With a view, for example, a user may be granted access only to a certain department's data within a certain date range. The variations are limitless. Views appear as tables to the application developer or enduser.   A user may be granted access to a view without being granted access to the underlying physical table(s), or other views. Only the creator of a table or view has the authority to grant access to that table or view.

SQL security provides the ability to restrict access by access type (SELECT, UPDATE, DELETE, INSERT, etc), table, and column (for UPDATE). Using VIEWs and GRANTs together, any number of SQL condition statements can be used to restrict access to any combination of tables, columns and rows of data.

For example, the following SQL statements restrict "username" to read-only ("select") access on those rows in table "dept" which have department number 101:

CREATE VIEW dept101 AS
SELECT *
FROM dept
WHERE deptno = 101;

GRANT SELECT ON dept101 TO username;

The DBA can grant special access to controlled resources to special people, such as the University Registrar and Bursar. The set of resources to which each is granted access may be entirely different. The DBA may also grant these users the authority to grant access to subsets of their resource to others. This provides a hierarchy of security which can be used to support distributed or non-distributed installations.

ORACLE Version 7.0, has introduced role-based security management. This includes, new "system privileges" which can be granted to roles to authorize selective system administration tasks such as creating new user accounts or altering database characteristics. Authorized users can readily define new database administrative roles such as System Security Officer (SSO) or Database Operator (DBOP) to satisfy the operational and security policies of each ORACLE installation.

ORACLE Auditing

Oracle auditing, primarily a security feature, can be used to monitor user activity on an Oracle database/data dictionary. By default, auditing is disabled. Setting the AUDIT_TRAIL parameter in the configuration file to TRUE enables the audit feature. The Database Administrator may enable system-wide defaults for auditing such as:

• auditing successful/unsuccessful attempts to access the database
• auditing successful/unsuccessful attempts to ALTER an ORACLE table

The auditing feature has provisions for:

• The use of SQL statements to choose auditing options;
• Auditing successful/unsuccessful attempts to access tables or views;
• Selectively auditing different types of SQL operations (UPDATE, INSERT, DELETE, SELECT, etc.);
• Controlling the level of detail recorded in the audit trail (session vs. access);
• Monitoring successful/unsuccessful attempts to log on and log off Oracle;
• Monitoring GRANT and REVOKE privileges. Different levels of auditing may be specified for each database table.

Fault-tolerance, Backup, Recovery Facilities

The ORACLE RDBMS is a high-performance, fault-tolerant database management system, especially designed for on-line transaction processing and large database applications.

The following are some of the fault tolerant features provided by ORACLE:

• Software Disk Mirroring

The ORACLE RDBMS fully supports disk mirroring (shadowing) on operating systems that provide this facility. With this facility, media failure should never disrupt applications users.

• On-line Backup

Backups can be performed while the database is up and operational without any shutdown required. Normal activity continues during the backup process. To backup and restore ORACLE data you can use your normal image backup facility or ORACLE's Export utility. The ORACLE Export utility can be used to perform backups of the entire database, or selected table(s). The Oracle Export utility allows for incremental or cumulative backups. This allows a backup of only the changes made to your tables since the last backup was completed. Whenever you do a backup, even during maintenance, you are assured of a consistent backup of the entire table(s) as of the time the backup began. Even during backups, your tables are fully usable. This is accomplished by entering the BEGIN BACKUP and END BACKUP commands in SQL*DBA before and after the backup takes place. All of these commands can be entered on-line, or included in a batch job stream along with the image backup procedure itself. The types of backup media supported are disk, single-file tape, or multi-file tape.   

Oracle's backup (Export/Import) facility is quite simple to use. The operator is prompted for information (i.e. names of tables to back up, etc.) from the Export/Import script.

• On-line Recovery

On-line recovery is also supported. If a disk storing some of the database information should fail, applications not accessing that data can continue to run unaffected. The database administrator merely recovers the failed disk and brings it back online, so that applications using that data resume operation.

All recovery from backup modes leave the data consistent. All committed transactions up to point-in-time are included, all others are rolled back. Consistency is maintained across the scope of recovery, either tablespace or database wide.

• Automatic Roll Forward

Automatic rollback and rollforward operations to protect the database against data loss during system or media failure are supported.

ORACLE includes sophisticated features to protect the data from damage by malfunction of hardware or software through its transaction backout and recovery system, and its after image journal capability.

Every process accessing an ORACLE database is considered an independent transaction. As a transaction modifies data in the database, unmodified copies of the data are preserved. Should the transaction not complete normally, the database is restored to the consistent state it was in before the aborted transaction began. This transaction rollback automatically occurs if a process aborts abnormally, or in the case of a system crash, or when a deadlock is detected.  

To guard against a failure of the device(s) on which an ORACLE database is stored, ORACLE can optionally record the changes to the database in an "after image journal." The after image journal files contain a physical image of database blocks as they exist after a transaction is completed. Should the database files become damaged due to a head crash or other media failure, a supplied utility program can apply the after image journal files to a saved copy of the database, thus moving the captured image of the database forward in time to the point of the failure.

Data Integrity

Every process accessing an ORACLE database is considered an independent transaction. A transaction begins when the first executable SQL statement is encountered. After one transaction ends, the next executable SQL statement will automatically start the next transaction. The transaction is automatically committed once a DDL or DML command completes.

When updating data through ORACLE's Screen Processor, standard validations (value range, upper case, uniqueness, mandatory, and fixed or maximum length) are provided. Also, field-level and record-level SQL statements may be used in the screen definition to perform additional validation. Typical validations include referential integrity (the value does or does not exist in a database table), lists of valid values, generation of unique sequence numbers, etc. Record level SQL statements, which are executed at transaction commit time, permit validation of the entire transaction. Such statements can also be used to propagate changes to other database tables, maintaining the consistency of the database. These triggered SQL statements provide the ability to update or insert rows in other tables based on an action taken in the current table. Thus, if a department number is changed in the DEPARTMENTS table, a trigger could update all records in the EMPLOYEES table to reflect the new department number.

Version 7.0 of the ORACLE RDBMS provides enforcement of these constraints at the database level. Users can define referential integrity rules to enforce parent/child or master/detail relationships between rows in tables with foreign key references to primary key fields. Also, CHECK and UNIQUE constraints and DEFAULT values enable entity integrity rules to be defined and enforced at the field and row level.

Inherent in the SQL language, are certain data integrity rules. These rules may enforce data type integrity. For example, no field defined as DATE or NUMBER will be allowed to have data entered which is inconsistent with that format. In the case of a NOT NULL column, a value must be entered. The SQL WHERE clause can guarantee a range of values, or aderence to a vast array of criteria. These rules are enforced in all ORACLE tools, therefore, misuse of the language is unlikely to occur.

SQL*Forms, Oracle's 4GL application builder, stores edit rules in applications and provides other data accuracy functionality. For example it can ensure that a field must be input, or that a field be a specific length, etc. The user is prompted with a data entry error message if the data entered does not meet the specified rules.   

Concurrency

A major strength of ORACLE is its ability to maintain high concurrency in a multi-user environment - multi-version readconsistency.   Its precision locking (updates don't block queries and queries don't block updates) contributes to this high degree of concurrency.

ORACLE provides complete facilities for concurrency control to ensure data integrity during attempted simultaneous access. Automatic (implicit) and explicit locking at the table and row levels occurs during updates to the database.

When the ORACLE RDBMS detects a deadlock, it signals an error to one of the participating transactions and rolls back the current statement of that transaction. Typically, the statement rolled back is the one belonging to the transaction detecting the deadlock. The signalled user may rollback the transaction, or retry the statement after waiting a few minutes. There is no need to "reboot" the system to resolve a deadlock.

The high concurrency of ORACLE allows full utilization of the processing power available in multiprocessor machines as well. ORACLE's row-level locking, multi-threaded table access and a no-lock concurrency model provide maximum utilization of any SMP (symmetric multiprocessor) hardware platform that supports shared memory among the processors. Process scheduling and priorities are left completely up to the native operating system.

Row-level Locking

ORACLE uses row-level locking to insure data integrity while allowing maximum concurrent access to the data by unlimited users. ORACLE locking is fully automatic and requires no user action. Implicit locking occurs for all SQL statements, depending on the action requested. Users need never explicitly lock any resource; ORACLE's default locking mechanisms will protect the data. Unlike most other DBMS's, ORACLE supports an unlimited number of locks. Thus, it never has to degrade concurrency by escalating lock levels above:

• Data locks (intent and update) at the row level
• B*Tree index locks (intent and update) at the entry level)
• Dictionary locks at the row level
• Multi-versioning at the row level without locks for queries

This locking mechanism has been chosen to provide the finest locking granularity possible, minimize contention for resources, and maximize multi-user concurrency.

Performance

ORACLE's high performance is provided through a combination of an optimal client/multi-server architecture and concurrency control mechanisms that supports simultaneous online transaction processing and decision support. A number of state-of-the-art features have been implemented in the ORACLE RDBMS. Among them are:    

• the ability to back up the database while it is running
• support of the null value concept
• automatic sequence number generation for table rows
• rollback and rollforward operations to protect the database against data loss during system or media failure.
• deferred writes
• row-level locking
• row-level multi-version read consistency
• very large database support.

ORACLE's I/O algorithms guarantee that:

• Minimal data is written very quickly
• A maximum of one sequential write is required per transaction
• Frequently, less than one sequential write is required per transaction
• Commits do not require that changes be written to the database.

Because of ORACLE's multi-version snapshot model, queries can read rows without locks. Consequently,  

• queries do not block queries
• queries do not block updates
• updates do not block queries.

ORACLE is able to achieve faster performance by means of its array processing, which minimizes data traffic, and PL/SQL which minimizes command traffic.

While other DBMS products move data between application programs and the database one row at a time, ORACLE's array interface allows the client to process (select, insert, update) multiple rows in a single client/server exchange by passing array-defined host variables. There is no theoretical limit on the array size.

PL/SQL is a powerful procedural language extension to SQL. Application developers can use PL/SQL to give transaction processing performance an extra boost. They can send multiple SQL statements - even multiple transactions - into ORACLE with a single request. This reduces back and forth communications between applications and the DBMS, and boosts performance especially in distributed environments where network communications often bottleneck.

Additionally, ORACLE Version 7.0 supports stored PL/SQL procedures which are stored in the database and invoked only by "EXECUTE" calls from the client.

ORACLE has many performance related features in the RDBMS. Overall performance of ORACLE is optimized automatically at a system level by the use of features such as shared memory and re-entrant code.

Performance of a particular application can be optimized by the Database Administrator (DBA) or application designer. The principal methods used to optimize performance are the construction of indexes on frequently accessed items, and the clustering of tables which are frequently joined in queries. Indexes in ORACLE are maintained automatically, and are stored in the database. The indexing method used is the balanced tree (B*tree). It should be emphasized this tuning requires absolutely no change to the SQL statements used to access the data since SQL is completely independent of the physical structure of the stored data.

ORACLE supports the concept of physical clustering of data from two or more tables based on common data values. Clustering permits data from several tables to be stored on the same physical disk block where the rows all share a common data value. The physical storage technique offers the performance advantages that traditional hierarchical files provide (with related data physically proximate), while retaining the productivity, data independence and ease of use advantages of the relational model.

This clustering of data is totally transparent to all queries against the data. If a cluster contains more than one table, each table can be queried as if it were stored separately.

Optimizer

The optimizer is rules-based. The ORACLE query optimizer uses information from the Data Dictionary and the contents of each SQL statement to determine the best access path to the data. It chooses index usage, join strategy, nested sub-query consolidation and aggregate access path usage by ranking the efficiency of access paths into the database objects.

ORACLE Version 7.0 will also allow Cost-Based optimization.

Standards

SQL Standards:

ANSI SQL Level 2
IBM's System Application Architecture (SAA) Standards.

SQL*Plus, a full implementation of ANSI SQL, is ORACLE's implementation of SQL and is compatible with, and expands upon, IBM's implementation in SQL/DS and DB2.

Oracle is certified to ANSI SQL89 Level 2.

Oracle not only complies with key SAA specifications, it delivers the full promise of the SAA concept today through:

Portable Applications; Enterprise-wide applications; Consistent User-Interfaces; Portable programmings Skills.

Networking Standards

A variety of protocol options are supported by SQL*Net and more than one protocol driver may be linked into the client/server programs at a time, providing support for simultaneous multiple heterogeneous client/server connections.

ORACLE supports TCP/IP as well as DECNET, SNA (VTAM, 3270, and APPC/LU6.2), ASYNC (terminal emulation), LANs(Novell SPX, NetBIOS, Named Pipes, VINES), and many other proprietary network protocols.

Oracle is GOSIP compliant. Oracle also adheres to and cooperates with all levels of the ISO/OSI model. Oracle continues to add new protocols as they become embraced as either industry standards or platform specific protocols.  

In line with Oracle Corp's philosophy of adherence to standards, ORACLE products will make plans to support the ISO RDA standard when the standard approaches final form.

Open System Approach

Oracle Corporation has an extended history of supporting emerging standards. For example, shortly after IBM declared the framework for relational databases and the SQL data language in the late 1970's, Oracle was the first commercial organization to deliver to the market a relational database management system using SQL. Thereafter, the ANSI committee adopted SQL as the industry standard query language. Oracle recognized early the advantages of portable software, and completely recoded the RDBMS in the 'C' language in the early 1980's. Thus, Oracle's corporate strategy of connectability, capability, portability and compatibility has paralleled emerging industry standards.

Oracle is committed to the "Open System" approach and compliance with standards. ANSI SQL is a prime example. SQL89 (ANSI X3.135-1989) is the current official SQL standard in the U.S. ORACLE Version 6.0 is approximately 98% compliant, and Version 7.0 is 100% compliant with ANSI SQL89, as per the NIST 127-1 ANSI SQL test suite.

Oracle is an active member of the SQL Access Group consortium. We were an active participant in the development of the SAG interoperability prototype demonstrated in New York City on July 16, 1991. Oracle had a wider participation in this event then any other member of the SQL Access Group. The ORACLE RDBMS, SQL*Forms, ORACLE*Graphics, Pro*C, and SQL*Connect to DB2 were demonstrated. As the SQL*Access standards are finalized, Oracle will incorporate the standard into its products.

If more than one standard exists, Oracle implements a layered architecture that presents multiple standards through a single consistent interface. Oracle has implemented layering of code to accommodate multiple operating systems (OSD layer), multiple network protocols (SQL*Net drivers and new SQL*Net V2.0 TNS layer), multiple user-interface/GUI standards (toolkit layer), and multiple database servers (SQL*Connect product).

Oracle Corporation firmly believes that its philosophy of open systems and standards compliance is the main reason for Oracle's success, and it remains the primary catalyst for our continued ability to rapidly adapt to new technology and new standards as they evolve.

ORACLE v7 Features

ORACLE Version 7.0 represents a major milestone in the evolution of RDBMS technology. Oracle provides extensive and robust new functionality in the areas of server enforced integrity, distributed database, support for large numbers of users, security management, query optimization, database administration and standards compliance.

Declarative Database Enforced Integrity
Oracle supports the full complement of ISO/ANSI SQL standard declarative data integrity rules for both entity and referential integrity. Users can define referential integrity rules to enforce parent/child or master/detail relationships between foreign and primary key fields. CHECK and UNIQUE constraints and DEFAULT values enable entity integrity rules to be defined and enforced at the field and row level. The Declarative model allows specification of Relational Integrity rules through a robust high Level Language facility for ease of implementation and maintenance. These distinctions are key to the success of complex applications with many data dependencies.

ORACLE's declarative approach to integrity rule definition and enforcement:

• Eliminates the potential for programmer coding errors.
• Significantly reduces application development cost.
• Allows integrity rules to be clearly defined within the data dictionary for easy access.
• Enables application tools such as SQL*Forms Version 3.0 to provide immediate user feedback when data entry violates integrity constraints.
• Allows automatic enforcement of integrity rules after fast "direct load" operations by SQL*Loader.
• Provides RDBMS performance optimizations customized for each specific declarative rule.
• Is 100% compliant with the ISO/ANSI Integrity Enhancement Feature.

Options are also provided to dynamically add, drop, enable/validate and disable integrity constraints.

PL/SQL Stored Procedures and Triggers
Application developers can use Oracle's advanced PL/SQL language to define database procedures for enhanced data integrity, security, and improved performance. Procedures containing one or more SQL statements together with PL/SQL procedural language extensions are stored in a shared, compiled format within the database. Procedures can also access global variables which persist for the duration of a user's session. Database procedures can be invoked from:

• Oracle tools such as SQL*Forms, SQL*Plus, and the ORACLE Programmatic Interfaces such as Pro*C
• Other PL/SQL stored procedures
• Triggered actions such as INSERTs or DELETEs to specified tables, or UPDATES to specified columns in tables
• Remote procedure calls are supported in V7. All changes made by RPCs will be automatically protected by ORACLE's transparent two-phase commit mechanism

Application developers can define and enforce the allowed operations on tables, views and other database objects by limiting direct access to database procedures. Database administrators grant users EXECUTE privilege on callable stored procedures and need not grant any direct access to underlying database objects such as tables and views.

Application developers can use triggered procedures to enforce more complex integrity rules. For example, a trigger can generate new orders whenever inventory falls below a specified threshold. Triggered procedures can also perform content based auditing and maintain derived fields.

The ORACLE trigger mechanism is based on proposed ISO/ANSI SQL standard specifications. ORACLE triggered procedures can activate other triggers to an unlimited depth and can coexist with ISO/ANSI declarative integrity constraints.

New Distributed Database Capabilities

Distributed Updates
Transactions containing data manipulation statements including INSERT, UPDATE, DELETE, LOCK, and SELECT ... FOR UPDATE may now be executed at multiple sites within one transaction. Two-phase commit logic to protect against system and network failures is provided with full transparency to user applications. Application programs use SQL COMMIT statements to commit distributed transactions, and need not be concerned with the physical location of data, deadlock recovery, or other complexities of two-phase commit coordination. Facilities are also provided to protect system resources from transactions blocked in prepared states by long term system failures. These features are generally not available from smaller suppliers.

Distributed Data Capabilities
Oracle has had distributed query capability since V5 of the RDBMS in 1985. Through its suite of SQL*Connect products, Oracle has achieved distributed queries against local and remote ORACLE and Non-ORACLE databases and file systems. The current list of SQL*Connect Products includes: IBM's SQL/DS, DB2, IMS; DEC RMS; Tandem's NS/SQL; HP's MPE/XL; and Teradata DBC.

Interoperability
ORACLE Version 7.0 can function as a two-phase commit coordinator for non-ORACLE database systems and as a participant in two-phase commits coordinated by external services such as IBM's CICS and AT&T TUXEDO/T. All of the Oracle SQL*Connect products listed above are read-write capable except for the non-relational file systems.

Interoperability is also achieved by the implementation of teleprocessing monitors. TP monitors allow a user application to access more than one brand of database service (RDBMS) through the XA call interface.

Snapshots
Version 7 will support transparent asynchronous table replication (snapshots). The system will support multiple read-only snapshots of an updatable master table which are refreshed at user-defined intervals. When a snapshot is refreshed, ORACLE will either copy the entire master table or just the changes since the last time the snapshot was refreshed. Snapshots are defined in terms of a query, giving users great flexibility in replicating sets of rows and columns or even joins or aggregates. ORACLE performs all snapshot updating and management transparently to users and applications.

Architectural Highlights
ORACLE Version 7 introduces shared SQL and a multi-threaded server architecture. Combined, these two features reduce resource utilization and enhance ORACLE's ability to support more users with fewer resources on low-end platforms and many hundreds or even thousands of users on high-end platforms. Oracle SQL*Forms further reduces memory utilization and network traffic as transactions increase in volume.

Shared SQL significantly reduces RDBMS memory utilization and application startup time by allowing many users to share a single copy of parsed SQL statements and procedures. Application startup time is reduced because statements to be executed are already prepared for use.

The Oracle Multi-Threaded Server allows a shared server process to support multiple client processes. Instead of taking requests directly from client processes, shared server processes pick up requests from a request queue. Client requests are placed on this request queue by a new ORACLE process known as the dispatcher. Many hundreds of users can be supported with only a few shared server processes. This decrease in the number of operating system processes, reduces overhead and increases the total number of users which can be supported. The system can be configured to support multiple dispatchers and shared server processes.

Role-based Security Management
Version 7 introduces a new security architecture based on roles, which are named collections of privileges. Roles make security administration much more manageable and hence secure. The ORACLE roles facility was developed in cooperation with the National Computer Security Center and has been accepted by the ISO and ANSI SQL committees as the basis for security management in future versions of the SQL standard.

Group Access Controls
Roles significantly reduce the burden of security management by allowing privileges on tables and other database objects to be grouped together and granted to individual users or groups of users. Security administrators can authorize users to run applications with a single GRANT statement.

Specialized Database Administration Roles New "system privileges" can be granted to roles to authorize selective system administration tasks such as creating new user accounts or altering database characteristics. Authorized users can readily define new database administrative roles such as System Security Officer or Database Operator to satisfy the operational and security policies of each ORACLE installation.

Administrative responsibility for granting roles can be readily reassigned without the cumbersome complexities of cascaded revokes. Applications can selectively activate and deactivate roles to limit the privilege domain of the users running each application. Users can deactivate highly privileged roles during routine database use. Application designers can control the activation of authorized roles by using passwords on roles. This allows, for example, designers to give users more privileges when using SQL*Forms than when using SQL*Plus. On selected platforms, external security facilities such as IBM's RACF can control role assignments.

Cost-based Query Optimization
ORACLE Version 7.0 will use database statistics such as the number of rows in a table, the selectivity of indexes and storage characteristics in determining the query execution plan. This will make query execution time less dependent on the wording of the query and will often speed query execution time. The statistics used by the optimizer are gathered by the ANALYZE TABLE facility. This facility can compute exact database statistics or estimate the statistics without requiring a full scan of the table. For large tables, this greatly reduces the overhead required to keep the statistics up-to-date.

Multiplexed Log Files
Redundant log files may be maintained on multiple disk devices to provide additional protection against media failures. All writes to log files are done in parallel so that there is no loss of performance.

Standards Compliance
ORACLE V7.0 is 100% compliant with level 2 of the SQL89 standard (ISO 9075-1989, ANSI X3.135-1989) as measured by the U.S. Government's National Institute of Science and Technology (NIST) SQL Test Suite. ORACLE Version 7.0 achieved this result on official NIST tests in March of 1991. In September 1991, Version 7.0 also passed the NIST test suite on three UNIX platforms. An ORACLE Precompiler (V1.5) option is provided to flag the use of SQL extensions as required by FIPS 127. In addition, ORACLE Version 7.0 is designed to comply with Orange Book C2 level requirements. Version 7.0 is currently being evaluated by the U.S. Government National Computer Security Center (NCSC) for compliance with the C2 rating. Trusted ORACLE is being evaluated by the NCSC at the B1 level.

Improved Database Administration
Significantly Improved SQL*Loader Performance

The new SQL*Loader "direct load" capability loads data several times faster by minimizing RDBMS overhead and by using special optimizations to build indexes concurrently during loads. Direct loads are fully protected by the recovery capabilities of the RDBMS, and have the option to automatically validate database integrity rules after loads complete.

SQL*DBA Enhancements include a new menu-driven user interface, redefined MONITOR screens, and other functional capabilities to facilitate tuning and database administration.

ANALYZE TABLE and TRUNCATE TABLE
Database administrators can use the ANALYZE TABLE command to validate the structure and format of tables and indexes, determine physical storage characteristics, identify rows that span blocks and analyze data distributions. The TRUNCATE TABLE command allows all data to be removed from a table without the overhead of logging. In addition, unlike DROP TABLE, TRUNCATE TABLE preserves all of the integrity rules and privileges associated with the table. Received on Mon Mar 15 1993 - 10:11:15 CET