|Getting to Know Oracle8i
The following topics are included in this chapter:
Very large databases present significant challenges for administrators and application developers. Large amounts of data complicate administrative tasks and affect the availability of the database.
To improve availability, ease administration, and enhance query and DML performance, the Oracle8 Enterprise Edition allows tables and indexes to be partitioned, or broken up, into smaller parts based on a range of key values. Because partitions operate independently of each other, data in a partitioned table are available even if one or more partitions are unavailable. Partitions also make large tables easier to manage by breaking up administrative operations into smaller tasks, which in turn can be performed in parallel. Finally, partitioning a table or index can improve performance of operations on the data by eliminating unneeded partitions from the execution plan of the operation.
Partitioning is transparent to both applications and users, so standard SQL statements in existing applications can run against partitioned tables. The Oracle optimizer is partition-aware, and partitions which do not contain any data required by a query are eliminated from the search, often resulting in a substantial performance increase.
A table or index can be partitioned or divided into smaller pieces. You define a table or index partitioning strategy when creating the structure. Pick a column or set of columns to act as a partition key, and this key will determine which data is placed into each partition. Data from insert operations is automatically placed into the appropriate partitions, so applications do not need to be rewritten to take advantage of partitioning.
All operations you perform on regular tables can be performed on individual partitions of a partitioned table. For example, you can export or back up single partitions of a table to avoid exporting or backing up the entire table in one operation. You can also perform the ANALYZE command on each partition concurrently to compute statistics needed for cost-based optimization more quickly.
Each partition of a partitioned table or index operates independently. Therefore, operations on one partition are not affected by the availability of other partitions. If one partition becomes unavailable because of a disk crash or administrative operations, both query and DML operations on data in other partitions can still continue.
Administrators can specify storage attributes for each partition and the placement of the partition within the host file system, increasing the granularity of control for very large databases. Partitions can be individually taken off-line or brought on-line, backed up, recovered, exported and imported, and loaded--thereby limiting the time required for management operations. An individual index partition can be built for one table partition, reducing the time required for index maintenance operations. Partition operations can be performed in parallel. Partitions increase availability by isolating media and application failures--applications not requiring data from an inaccessible partition continue to run without impact.
The Oracle8 Enterprise Edition provides a set of easy-to-use management commands for partitions. For example, you can implement a table containing a rolling time window of data with a partitioned table so that only one year's worth of data is ever contained in the table. To accomplish this, just add new partitions to the end of the table and drop partitions containing data more than a year old. You can also split and merge partitions easily to balance data among partitions. Also, stand-alone tables that use Oracle7 Release 7.3 UNION ALL views can be converted into partitions of a single table. This allows users with very large Oracle7 databases to quickly benefit from Oracle8 Enterprise Edition partitioning without having to rebuild large tables.
The Oracle8 Enterprise Edition introduces new features that improve data warehousing performance:
The Oracle8 Enterprise Edition introduces performance improvements to the processing of star queries, which are common in data warehouse applications. A star query, or star schema, occurs when one or more very large tables, often called fact tables, have relationships to multiple smaller tables called dimension tables. Oracle7 introduced the functionality of star query optimization, which provides performance improvements for these types of queries. In the Oracle8 Enterprise Edition, however, star-query processing has been improved to provide even better optimization for star queries.
In the Oracle8 Enterprise Edition, a new method for executing star queries has been introduced. Using a more efficient algorithm, and utilizing bitmapped indexes, the new star-query processing provides a significant performance boost to data warehouse applications.
The Oracle8 Enterprise Edition has superior performance with several types of star queries, including star schemas with "sparse" fact tables where the criteria eliminate a great number of the fact table rows. Also, when a schema has multiple fact tables, the optimizer efficiently processes the query. Finally, the Oracle8 Enterprise Edition can efficiently process star queries with large or many dimension tables, unconstrained dimension tables, and dimension tables that have a "snowflake" schema design.
The Oracle8 Enterprise Edition's star-query optimization algorithm, unlike that of Oracle7, does not produce any Cartesian-product joins. Star queries are now processed in two basic phases. First, the Oracle8 Enterprise Edition retrieves exactly the necessary rows from the fact table. This retrieval is done via bit mapped indexes and is very efficient. The second phase joins this result set from the fact table to the relevant dimension tables. This allows for better optimizations of more complex star queries, such as those with multiple fact tables. The new algorithm uses bit-mapped indexes, which offer significant storage savings over previous methods that required concatenated column B-tree indexes. The new algorithm is also completely parallelized, including parallel index scans on both partitioned and non-partitioned tables.
For more information, see Oracle8i Concepts.
Insert, update, and delete operations can now be run in parallel in the Oracle8 Enterprise Edition. These operations, known as parallel DML, are executed in parallel across multiple processes. By having these operations execute in parallel, the statement will be completed much more quickly than if the same statement were executed in a serial fashion. Parallel DML complements parallel query by providing parallel transaction execution as well as queries. Parallel DML is useful in a decision support (DSS) or data warehouse environment where bulk DML operations are common. However, parallel DML operations can also speed up batch jobs running in an OLTP database.
The Oracle8 Enterprise Edition supports parallel inserts, updates, and deletes into partitioned tables. It also supports parallel inserts into non-partitioned tables. The parallel insert operation on a non-partitioned table is similar to the direct path load operation that is available in Oracle7. It improves performance by formatting and writing disk blocks directly into the datafiles, bypassing the buffer cache and space management bottlenecks. In this case, each parallel insert process inserts data into a segment above the high watermark of the table. After the transaction commits, the high watermark is moved beyond the new segments.
To use parallel DML, it must be enabled prior to execution of the insert, update, or delete operation. Normally, parallel DML operations are done in batch programs or within an application that executes a bulk insert, update, or delete. New hints are available to specify the parallelism of DML statements.
For more information, see Oracle8i Parallel Server Concepts and Administration.
The Oracle8 Enterprise Edition can manage databases of hundreds of terabytes in size because of partitioning, administrative improvements, and internal enhancements. Many size limitations in earlier versions of Oracle have been raised, such as the number of columns per table, the maximum database size, and the number of files per database.
Demanding OLTP applications benefit from a number of new features which improve scalability, performance, and manageability.
Oracle8 and the Oracle8 Enterprise Edition's server-managed backup and recovery provides a better backup and recovery functionality integrated within the Oracle database server. Detailed information is maintained on when backups are performed, exactly which parts of the database are backed up, and where the files are stored. Should a recovery be necessary, Recovery Manager analyzes the state of the database and determines the operations necessary to repair the database. It then automatically performs those operations, greatly simplifying the recovery for the administrator and reducing the possibility of human error. A simple GUI interface within Oracle Enterprise Manager controls backup and recovery. An API is also available for third parties who may wish to provide an alternative interface. Media management layer interfaces to popular third-party tape management products are available. Legato Storage Manager is provided free with both Oracle8 and the Oracle8 Enterprise Edition.
The Oracle8 Enterprise Edition provides multilevel, incremental backups that greatly reduce the size of the backups, because only the changed blocks are backed up. This can substantially reduce the time required to back up a datafile.
Tablespace point-in-time recovery allows one or more tablespaces to be recovered to an earlier time, while the remainder of the database is up and running. This allows many types of user errors to be easily corrected. For example, if a user runs a batch job that incorrectly updates many records in a table, the table can be restored to a time previous to the batch job. Also, if a table is accidentally dropped or truncated, it can be restored to a time before this operation.
For more information, see Oracle8i Backup and Recovery Guide.
Numerous enhancements throughout the Oracle8 and Oracle8 Enterprise Edition database servers and Net8 increase the utilization of operating system and networking resources. Connection pooling temporarily drops the physical connection for idle users (and transparently re-establishes the connection when needed), thus increasing the number of users that can be supported. Oracle Connection Manager can be used to configure a middle tier that manages the connections of very large user populations. By configuring multiple connection managers, the Oracle8 Enterprise Edition can support tens of thousands of concurrent users. Shared database links multiplex many users into a database server with a single connection, reducing resource requirements, especially for multitier application architectures.
The Oracle8 Enterprise Edition Advanced Queuing feature supports database messaging, or queuing, through a set of queue tables and queue functions.
Advanced Queuing adds direct support in the database for deferring transaction execution to a later time and executing transactions in a particular order. This capability allows you to decouple distributed applications and eliminates dependency on external systems for applications requiring high scalability. Enqueue and dequeue operations can be used to shift processing from within a transaction to a background process, thereby improving transaction response time. Also, queuing can be used to implement work flow applications that move data to a system as the state of the data changes. An example of this is moving orders from an order-entry application to a shipping application, and then to a billing system during the life of an order. Advanced Queuing can also work in conjunction with popular TP monitor queuing systems.
Messages can be enqueued and dequeued by applications or other queues. The propagation feature enables applications to communicate with each other without having to be connected to the same database or to the same queue. Using the familiar database links and Net8, messages can be propagated from one queue to another, irrespective of whether these are local or remote.
For more information, see Oracle8i Application Developer's Guide - Advanced Queuing.
Numerous enhancements in Oracle Parallel Server improve performance, scalability, memory usage, and availability. A common and integrated distributed lock manager replaces the lock managers provided by the different operating system vendors, improving performance and portability on most platforms. Several performance enhancements make the Oracle Parallel Server significantly faster. Also, new global V$ tables improve manageability.
Oracle Parallel Server now uses its own integrated distributed lock manager (DLM) for processing inter-node requests for resources. Previously, Oracle relied on operating system vendors to supply DLM capability for the Parallel Sever to run on a given platform. Integrating the DLM within the Oracle8 Enterprise Edition allows the Oracle Parallel Server to be available on platforms that previously did not support cross-node sharing of resources.
The Oracle8 Enterprise Edition introduces numerous improvements to Oracle Parallel Server performance. System change number (SCN) generation is now optimized for providing SCNs across instances more efficiently. This improvement alone can amount to a 10 to 15 percent improvement in Oracle Parallel Server performance. Also, the DLM now caches locks to avoid cross-node communication of lock information. Pinging, or contention for blocks across nodes, is also reduced by improving the algorithm for controlling access to contended blocks. Reverse-key indexes reduce "hot spots" in indexes, especially primary key indexes, by reversing the bytes of the leaf blocks and thus eliminating the contention for leaf blocks across instances. Partitions also help Parallel Server performance by allowing database administrators to map partitions to specific nodes, which can dramatically reduce pinging. Finally, the Oracle8 Enterprise Edition reduces the memory overhead associated with fine-grain locking in an Oracle Parallel Server.
The Oracle8 Enterprise Edition allows designation of groups of instances for parallel query or parallel DML processing. You can specify to which group an instance belongs and then use the group to process statements from certain applications. This is especially useful for separating OLTP from data warehouse processing among your clustered servers. Using separate instances in an Oracle Parallel Server ensures that data warehouse queries do not affect the performance of OLTP applications.
The Oracle8 Enterprise Edition introduces global fixed views (GV$) for the Oracle Parallel Server. This allows administrators to log into one instance of a Parallel Server and view global views that contain data from all the V$ views in the cluster. This makes administration operations significantly simpler and improves the productivity of database administrators.
Should a node in the Oracle Parallel Server fail, transparent application failover will migrate your connections and automatically re-establish their sessions on another node. Your applications will continue to run, and you may be unaware of the failure. This provides continuous availability in the event of scheduled and unscheduled outages. Even if you are not using the Oracle Parallel Server option, TAF will automatically reconnect and reestablish your session.
Depending on the amount of overhead you are willing to incur on the client and backup machines, the instance failure could result in a completely transparent migration of user connections to the failover node. You can specify that you want all queries to be cached on the client so that they can be reinstantiated on the failover node. Also, you can have a session pre-connected to the failover node, eliminating the time required to reconnect to a failover instance.
Transparent application failover is useful not only for availability, but also for manual load-balancing or orderly shutdown of the system. If too many users connect to an instance, you can terminate their sessions and have them transparently migrate to another node. You can also shut down a node and have users transparently migrate to a failover node after their current transaction completes. Shutdowns no longer need to interrupt users work.
Note: To take advantage of this functionality, applications must be written specifically using new Oracle8 OCI calls.
For more information, see Oracle8i Administrator's Guide.
Dynamic XA support improves performance for multitier applications with industry-standard XA-compliant Transaction Processing (TP) monitors. Oracle8 and the Oracle8 Enterprise Edition improve support of the XA interface with support for dynamic registration and loosely-coupled transaction branches. They also offer better performance and recovery when used with Oracle Parallel Server.
For more information, see Oracle8i Application Developer's Guide - Fundamentals or Oracle8i Parallel Server Concepts and Administration.
Oracle8 and the Oracle8 Enterprise Edition include a Security Server which provides a single sign-on environment for centrally administering users and roles. Oracle Security Server is compliant with the X.509 certificate-based security standard for public/private key authentication. Mutual authentication between client and server is also supported for protection against "rogue" databases designed to capture client communication. Also, a digital signatures tool kit is provided for creating applications designed to identify unauthorized tampering with data.
Oracle8 and the Oracle8 Enterprise Edition also contain improved password maintenance and administration. You can now define a password profile to enforce a security scheme. Passwords can expire after a certain time, or be checked for complexity (e.g., minimum length). You can define your own policy or use the standard stored function for checking the length, content, or reuse of new passwords. Also, you can create user accounts so that the user must immediately change the password upon the first access to the system.
Oracle8 and the Oracle8 Enterprise Edition also add support for privileged database links, which make it unnecessary to embed a password in a database link, and data encryption services callable from OCI and PL/SQL.
Oracle8 and Oracle8 Enterprise Edition replication provides improved support for mass-deployment distributed systems, higher throughput failover configurations, and specialized data warehouse systems.
Increased replication performance is now possible. Changes to replica sites can be propagated and applied in parallel, effectively removing the throughput limit on replication, while maintaining transactional integrity. Also, much of the replication functionality has been rewritten and moved from PL/SQL triggers into C code inside the Oracle engine, providing a significant boost in performance. Finally, the amount of data sent over the network for each replicated transaction has been reduced, and network round-trips are kept to a minimum.
Salesforce automation and other mass deployment systems are now easier to administer and design with the subsetting capabilities of both Oracle8 and the Oracle8 Enterprise Edition. Complex subquery snapshots can now include a fast refresh clause which allows you to build a snapshot of select rows based on a query to another table. For example, you can build a snapshot for each salesman based on the ORDERS table. That snapshot can contain only the orders by selecting the orders based on criteria from an ASSIGNMENTS table. Each salesman sees only his orders and only has to interact with the master table when uploading new orders.
Oracle8 Enterprise Edition replication also provides numerous improvements in manageability and ease of use. Significant enhancements to Oracle Replication Manager, including wizards, make replicated environments easier to set up and maintain. Additional improvements to replication manageability include snapshot registration, which provides valuable information about which sites have associated snapshots; primary key snapshots, which allow faster snapshot refresh after a table reorganization; a new security algorithm, which is easier to administer; and fine-grain quiesce, which allows an administrator to make schema-level changes to one replication group while others continue to operate.
For further information, see Oracle8 Replication.
The Oracle8 Enterprise Edition makes a major leap in data management technology with the introduction of an object-relational paradigm. Database schemas and applications today are becoming increasingly complex. Often, several separate applications with similar data, such as customer information, billing, and shipping, exist in different database schemas and an MIS department must manage the interoperation. Corporate management of the information becomes a difficult task of integrating different relational objects and different applications, possibly from different vendors, into a more coherent end-user data model. By enhancing the relational database with object extensions, Oracle addresses the need to simplify data modeling and extend the database with new datatypes.
The new, object-relational features include the following:
Object types provide a way to extend Oracle's relational datatype system. Relational databases support three datatypes: characters, numbers, and dates. Object types allow you to define new datatypes and use them as you would regular relational datatypes. For example, you can create a new type called Address. This object type can have data, called attributes, such as Street, City, and Postal Code. The object type can also have methods or stored procedures, such as Distance, for computing the distance between addresses. These methods can be written in either PL/SQL or C. An address can then be used anywhere a regular datatype could, whether in column definitions, in PL/SQL variables, or even as the definition for an object table.
Oracle's object types can use powerful object modeling techniques for complex objects. For example, you can represent collections of similar objects in array structures or nested tables. You can also store references to objects for fast traversal without joining tables.
Object types allow application developers to code application logic in the database or the middle-tier application server, as opposed to using client-side code. All applications can then share the logic of the new datatypes so developers do not need to rewrite the code. This feature provides the advantages of creating re-usable code components and transparent application partitioning so that the code can reside and execute on the tier that will yield the best performance: client, application server, or database server.
The Oracle8 Enterprise Edition follows the emerging SQL3 standard for object type definition and object modeling techniques. SQL3 defines syntax for creating and modifying object types, generating and storing object identifiers (OIDs), creating references or pointers to objects, and modeling collections of similar objects.
For more information, see Oracle8i Concepts and Oracle8i Application Developer's Guide - Fundamentals.
Oracle8 and the Oracle8 Enterprise Edition provide a safe, fast way for the database to make a call to an external program. The call also can be made through open protocols like HTTP or IIOP (a CORBA standard). External procedures allow you to use existing application code, or write highly-optimized code for specific purposes, such as a computationally complex algorithm like Fast Fourrier Transform (FFT). Also, you can use external procedures to interface with other applications or with specialized devices like embedded systems.
For more information, see PL/SQL User's Guide and Reference and Oracle8i Application Developer's Guide - Fundamentals.
The client-side object cache allows user applications to retrieve a complex hierarchy of objects into an application cache. The application can then traverse the objects without performing additional network retrievals. This provides a convenient and fast way to use objects in a client application and write code that is more like the native object-oriented code.
A new utility, the Object Type Translator, has been introduced as a quick way of generating header and implementation files for applications running against object schemas.
The Oracle8 Enterprise Edition is designed to allow users to easily evolve into using the new object-oriented functionality, as all existing applications are upwardly compatible. The new object-relational extensions are built on the same foundation as the relational functionality, which means that users do not have to discard or rewrite their existing relational applications before migrating to the Oracle8 Enterprise Edition. Unlike other object-relational databases, this design allows the older relational applications, which still read and write rows and columns, to coexist with new object-oriented applications, which read and write objects. The Oracle8 Enterprise Edition provides object views to retrieve relational data and represent the data to a client as if it were an object and vice-versa.
For example, an existing relational order-entry system might need a new front-end for the World Wide Web. The existing applications accessing the relational schema can remain in operation, and a new set of object views can be developed as an object representation for the client. New and old applications can be based on the same data, but each has its own representation.
For more information, see Oracle8i Concepts.
The Object Database Designer offers object support in a design tool. It helps you design, create, and access object-based Oracle8 Enterprise Edition systems.
For more information, see Object Database Designer.
Large Objects (LOBs) handle unstructured data such as images, sounds, video, and text, and have much richer functionality than their predecessors, LONG and LONG RAW. Character LOBs (CLOB or NCLOB), Binary LOBs, and BFILES (externally stored LOBs), can be replicated and can be an attribute of an object. You can also have more than one LOB per table. LOBs also have a greater maximum size than LONGs and have different mechanisms for maintaining read-consistency and random access.
LOB data is indexed for fast access starting at a specified byte. For example, you can read/write at specific byte-offsets. You can also read/write LOBs through the Oracle8 buffer cache or access them directly from disk.
LOB functionality is available with both Oracle8 and the Oracle8 Enterprise Edition and does not require the Objects option to be licensed and installed.
For more information, see Oracle8i Concepts.
Oracle has a long history of supporting cross-platform environments. Oracle8 and the Oracle8 Enterprise Edition build upon that with strong support for Java.
Oracle currently has two methods for accessing Oracle data from Java programs: an Oracle-provided JDBC driver integrated with Oracle's object types, and JSQL for embedding SQL statements into Java code. Oracle provides its own JDBC drivers for better performance. JSQL allows you to include SQL statements in a Java application. The JSQL precompiler then converts the SQL into JDBC calls, which allows you to use existing SQL code in new Java applications.
Oracle8i JDBC Developer's Guide and Reference
Extensibility allows you to define your own datatypes. You can extend the capabilities of your current database by creating new datatypes for your specific applications. These new datatypes can be used in the same manner as the ones you have now; the same operations can be performed. Image, Spatial, Time Series, Visual, and ConText cartridges are now available as extensions to the Oracle database server.
A simple and fast migration utility rebuilds the data dictionary and converts the control files, log files, and data blocks. The migration utility converts any Oracle 7.1, 7.2, or 7.3 database into an Oracle8 or Oracle8 Enterprise Edition database. Oracle7 applications run unchanged against either of the Oracle8 products. Distributed commands from either of the Oracle8 products run against Oracle7, and vice-versa.
For more information, see Oracle8i Migration.
Index-organized tables store the data columns of a table within the leaf nodes of the Oracle8 B-tree index structure. This reduces overall storage requirements when most columns are indexed by storing the columns only once, rather than in both an index and a separate table. Index-organized tables also reduce access time by retrieving all columns from one location instead of two.
For more information, see Oracle8i Concepts.
Reverse key indexes reduce the "hot spots" in indexes, especially ascending indexes. Unbalanced indexes can cause the index to become increasingly deep as the base table grows. Reverse key indexes reverse the bytes of leaf-block entries, therefore preventing "sliding indexes".
For more information, see Oracle8i Concepts.
Constraint processing has been dramatically improved. You can now use non-unique indexes to enforce UNIQUE and PRIMARY KEY constraints. This eliminates redundant indexes and permits indexes to remain valid while constraints are disabled. Deferred-constraint checking shifts integrity-constraint checking from the end of statement execution to when a commit is issued. This simplifies the coding of certain operations involving integrity constraints. Also, all constraints can be enabled concurrently and in parallel with other constraints, and permit concurrent DML while the enabling continues.
In both Oracle8 and the Oracle8 Enterprise Edition, a new NCHAR datatype allows a second character set in one database. This improves performance and storage predictability for some Asian language, multibyte character set databases. Also, fixed-length native Unicode 2.0 and Chinese GBK support is new.
A non-updatable view (i.e., a joined view) can be updatable through the use of a new type of trigger, the INSTEAD OF trigger. This trigger allows you to replace INSERT, UPDATE, and DELETE operations on views with your own logic so that even views based on complex joins can be modified. Also, you can place subqueries in the select list of other queries to return result sets from an arbitrary list of detail tables without performing join operations. By placing a cursor expression in a select list, you can fetch rows from detail tables in a 3GL program without specifying complex join conditions in the FROM clause.