Oracle RDBMS Technical Feature Summary
Daniel Druker
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 : ddr...@agsm.ucla.edu |
| oracle*mail : unix:ddr...@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 end-
user. 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 read-
consistency. 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.
Tony Jambu
I believe the information is useful as long as it does not become a sales
pitch.
It should be useful to those already using Oracle as well as those considering
it.
What would help is to post them maybe once in a few week rather than every now
and then. Kind of following the FAQ format.
These are just my thoughts on it. Other may disagree. Keep up the good work.
--
_____ ________ / ____ |Tony Jambu, Database Administrator
/_ __ /_ __ / |Colonial Mutual Invest Mgmt,Aust (ACN 004021809)
/(_)/ ((_/ \_/(///(/_)/_( |EMAIL: TJa...@cmutual.com.au
\_______/ |PHONE: +61-3-6418448 FAX: +61-3-6076198
David Bath
>I believe the information is useful as long as it does not become a sales
>pitch. It should be useful to those already using Oracle as well as those
>considering it.
Yes, I agree, it was *very* glowing in the terms used. Oracle generally
does deserve praise tho. I wonder if Dan copied some of it from Oracle
literature.
>What would help is to post them maybe once in a few week rather than every now
>and then. Kind of following the FAQ format.
Once a week is perhaps a bit much for the non-DBA I am posting now. Version
0.3 should be released in a week or so. Unless there is a *huge* disagreement
from you people out there, I think somewhere between two and four weeks is
more reasonable.
I understand from Kevin (author of the DBA FAQ) that he is doing a *major*
revision of it. In the meantime, if you guys need it posted again, let me
know.
BTW - Any more ideas on things to put in the non-DBA FAQ. Get them to me
within the week and I'll try and merge them into the 0.3 version, which will
have a contents "page" and be much more formal.
--
David T. Bath | Email:d...@otto.bf.rmit.oz.au (131.170.40.10)
Senior Tech Consultant | Phone: +61 3 347-7511 TZ=AEST-10AEDST-11
Global Technology Group | 179 Grattan St, Carlton, Vic, 3153, AUSTRALIA
"The robber of your free will does not exist" - Epictetus