Release 8.1.5
A68020-01
 Library |
 Product |
 Contents |
 Index |
| Getting to Know Oracle8i Release 8.1.5 A68020-01 |
|
This chapter describes new features, options, and enhancements available in Oracle8i.
The following topics are included.
The Web and the Internet bring many new challenges to organizations. Today's Web sites are often a series of Web pages that take too much time and too many human resources to develop, deploy, and manage. In addition, their infrastructure may not be stable or robust enough to support mission-critical applications. Everything necessary to transform data into powerful Internet applications and content-driven Web sites is included with Oracle8i. It provides a Web development environment that allows software developers to easily build dynamic, data-driven Web sites with a standard Web browser and the Oracle8i database.
WebDB is an HTML-based development tool for building HTML Web pages with content based on data stored in the Oracle database. Using a browser-based interface, Web pages can be created containing reports, charts, calendars, menus, and forms. WebDB provides build wizards for step by step guidance through the process of creating these components. The wizards include steps for building the PL/SQL statement that selects data used in the components, and steps for setting look and feel attributes of the component such as color and size.
There is no need to know PL/SQL to create a component. Selecting options in the build wizard guides WebDB to automatically write the PL/SQL code for building the component.
WebDB also provides a wizard to create a Web site within the Oracle database and assign a site administrator to control its development of the site. The Web site administrator in turn assigns owners who are responsible for providing content to the site. Content can include Web pages containing WebDB components, links to other URLs, and files uploaded to the Web site.
Once a site is created, it is maintained using options within the site itself. Content owners and end users never have to see the WebDB user interface. For example, the look and feel of the site can be updated using options provided within the site. End users perform searches of the site's content using options within the site.
For more information, see Oracle WebDB Release 2.0: Getting Started - Installation and Tutorial.
Oracle iFS combines the power of Oracle8i with the ease of use of a file system. Completely integrated with Oracle8i, the Internet File System is a Java application that runs within the Oracle8i Java Virtual Machine. It enables the database to become and Internet development and deployment platform.
Oracle iFS makes it possible to treat the database as if it were simply a shared network drive. Users can store and retrieve files managed by the database as if they were files managed by a file server. Because iFS supports a variety of standard protocols, users have universal access to their data. Whether a user accesses the contents of iFS through Windows Explorer, a Web browser, an FTP client, or an e-mail client, the files appear the same.
Not only can relational data appear as files, but hybrid documents that combine relational and non-relational data into single objects can be created and viewed. Files and folders in iFs are stored in a repository. They are indexed on content and file properties, allowing for intelligent text searches and queries.
From a developer's standpoint, iFS is the single data store containing the data for many different applications. For the system administrator, iFS provides a single system for file storage and messaging, rather than several separate systems to maintain and administer. In short, iFS is a simpler way of storing different types of files in the database.
Web applications require advanced data management services that support the rich datatypes used in Web repository, e-commerce, and other Internet applications. Oracle8i interMedia adds support that enables the database server to manage multimedia content, both for Internet and traditional applications that require access to image, audio, video, text, and location information. Oracle8i interMedia includes the following:
Integral to interMedia is the interMedia Clipboard and Web Agent. It provides a clipboard and Web agent working together to Web-enable interMedia. The clipboard can be used to:
The Oracle8i interMedia Web agent decodes URLs to retrieve multimedia objects for display by Web clients, such as Web browsers. The Oracle8i interMedia Web Agent supports Netscape Enterprise Server, Netscape FastTrack Server, Microsoft Internet Information Server, and Oracle Application Server.
For a more detailed summary of interMedia, and references to specific documentation, see below.
Oracle8i allows you to integrate all your data into its datastore. The Oracle8i extensibility framework and object relational technology have been exploited to extend the reliability, availability, and data management capabilities of the database server to multimedia data. A new multimedia option, Oracle8i interMedia, services text, document, image, audio, video, and locational data in a single integrated package. The Spatial, Time Series, and Visual Image Retrieval options, available separately, also have been designed to utilize the Oracle8i extensibility architecture.
For each of the media types, customers and partners can easily create "plug-ins" to support additional formats, new digital compression and decompression schemes (codecs), specialized indexes, custom query optimization and methods, external media data sources and even specialized data processing algorithms.
The following is a summary of functionality available with each option.
Oracle8i interMedia allows multimedia data to be managed in an integral fashion with other enterprise data. Applications can access interMedia through both object and relational interfaces. Database applications written in Java, C++, or traditional 3GLs can interface to interMedia through class libraries, PL/SQL, and the Oracle Call Interface (OCI).
For a more general description of interMedia, refer to "interMedia and the Web". This section presents specific features of the interMedia components and points you to supporting documentation.
Oracle8i interMedia Text delivers powerful text management and retrieval capabilities. It indexes any documents or textual content to deliver fast, accurate retrieval of information from document archives, online product catalogs, news services, media asset management systems, job postings, customer call reports, and other text information sources in multiple languages. Several techniques are used to ensure fast and accurate text searching, from traditional full-text search to document theme analysis.
The base for interMedia Text is Oracle's ConText Cartridge. Several new features have been added, in addition to its integration into interMedia. Discussions of these new features follow.
For more information, see Oracle8i ConText to interMedia Text Migration and Oracle8i interMedia Text Reference.
This is made possible through the tight integration of interMedia Text with SQL, PL/SQL, Oracle Enterprise Manager, SQL*Loader, and other components of the Oracle8i database engine. Some examples:
Creating an index is now much simpler because it is created and maintained using standard SQL. This is made possible through the Oracle8i extensible indexing framework as described in "User-Defined Operators and Extensible Indexing".
Through new functionality provided by the Oracle8i extensible optimizer, described in "Extensible Optimizer", the optimizer can be used to select the best plan for executing a CONTAINS query. It does so by analyzing collected statistics of all the tables and indexes affected by a CONTAINS query.
Application developers and users can import a thesaurus to augment the built-in KB. User-specific concepts, categories, words, or phrases can be added to improve theme capabilities. Information in the thesaurus takes precedence over information in the built-in KB when there is a conflict.
By default during indexing, interMedia Text uses the Inso Corporation filtering technology, which can filter most document formats. This filtering technology automatically recognizes document formats. Thus, this filter can be used to index single or mixed column formats. A custom or other filter may also be specified to filter documents during indexing.
For document presentation, the Inso filtering technology is used to create plain text or HTML versions of formatted documents.
Given a query expression, interMedia Text returns related query term information (broader term, narrower term, related term) that can help refine queries.
The behavior of theme highlighting procedures has been changed. For theme queries, interMedia Text procedures highlight and mark up words or phrases that best represent the theme query. This behavior is different from ConText, where paragraphs are highlighted for theme queries.
Certain languages use alternate spellings or representations for characters that cannot be stored as single characters: for instance, the letter "ö" in German is alternately represented as "oe". interMedia Text now checks for these alternate spellings during indexing and queries.
ConText allows stoplists to specify words that are not to be indexed (stopwords). The following additional stoplists are introduced in interMedia Text.
Also, stoplists can now be changed dynamically, after an index has already been created.
A new section type (field section), allows text within the field section to be indexed as a subdocument separate from the rest of the document.
Oracle8i interMedia provides foundational support for audio, image, and video digital information. Services provided by interMedia facilitate the integral storage of information in these forms into the Oracle8i datastore as BLOBs, or as references to external BFILEs. These interMedia services also include methods for retrieval of this multimedia digital information, inserting new information, and for limited manipulation.
The following table summarizes the support for audio, image, and video data provided by Oracle8i interMedia.
For more information, see the Oracle8i interMedia Audio, Image, and Video User's Guide and Reference.
Where a greater degree of manipulation or modification of image, audio, or video data is required, Oracle provides the Oracle8i interMedia Audio, Image, and Video Java Client.
The Oracle8i interMedia Audio, Image, and Video Java Client allows the user to use local (client side) applications to manipulate or modify multimedia data stored in a network-accessible (server side) database. It allows the user to connect to a server side multimedia object, copy that object from the server side to the client side, perform various operations on the client side object, and transfer the new multimedia object back to the server side.
For situations where the user does not have permission to modify the server side object, Oracle8i interMedia Audio, Image, and Video Java Client can retrieve the multimedia data from the server side for display purposes only.
In addition, Oracle8i interMedia Audio, Image, and Video Java Client gives the user the ability to integrate multimedia objects with various media frameworks, such as the Java Media Framework (JMF), RealPlayer, or Oracle Video Client (OVC).
For more information, see Oracle8i interMedia Audio, Image, and Video Java Client User's Guide and Reference.
Oracle8i interMedia Locator is the component of Oracle8i interMedia that supports online geocoding facilities for locator applications and proximity queries. It supports geocoding of spatial point data of interest, stores this geocoded data locally in the Oracle8i database server, and performs within-distance queries on the geocoded data.
Geocoding represents addresses and locations of interest (postal codes, demographic regions, etc.) as geometric features (points). Geocoding services can be used to add the exact location (latitude and longitude) associated with points of interest to existing data files stored in Oracle8i. Once data is geocoded, Oracle8i interMedia Locator enables distances to be calculated and sites to be represented graphically in Web, data warehousing, customer information system, and enterprise resource planning applications.
A sample application might use interMedia Locator to locate restaurants and hotels within a given point-to-point distance of a specified location, such as a business or tourist information kiosk.
Locator is not designed to be an end-user application in itself. It consists of an interMedia locator object datatype, a geocode result object datatype, geocoding functions, a spatial locator index, and a spatial operator for performing within-distance queries. It supports only point geometry types. Web application developers can use the basic function provided here to build specialized Web-enabled interMedia Locator applications. Oracle8i interMedia Locator also supports server-based geocoding and data scrubbing operations for data warehouse applications.
For more information, see Oracle8i interMedia Locator User's Guide and Reference.
Spatial data is any data with a location component. The location component could be the geocoded addresses of customers or suppliers, the course of a river and the outline of its floodplain, the locations of thousands of utility poles, or X and Y coordinates on a blueprint. Spatial data is not limited to the land surface but includes the subsurface, aquatic, marine, and lower atmospheric regions.
Oracle8i Spatial is designed for two groups of users:
This section describes the enhancements to Oracle8i Spatial, formerly known as the Oracle8 Spatial Cartridge, for the current release. Additionally, this release completes the implementation of the OpenGIS Consortium RFP1 - Simple Features guidelines. Oracle8i Spatial conforms to the specifications associated with SQL92 representation of points, lines, and polygons.
For more information about Oracle8i Spatial, see Oracle8i Spatial User's Guide and Reference.
The structure of the spatial data model has changed such that ordinates are stored using the VARRAY storage model. A spatial object type can be created that represents single element or multi-element geometries. Where in release 8.0, the cartridge had individual columns for geometry attributes, Oracle8i combines these into a single column of type SDO_GEOMETRY, similar to the way Oracle8i interMedia defines specialized objects for images, text, audio, and video.
For dynamic window queries, the release 8.0 cartridge required that users create and maintain a window layer that stored the definition of an area-of-interest and its corresponding spatial index entries. In Oracle8i, the spatial operators take a bind variable of type SDO_GEOMETRY, and the index creation and usage are handled internally.
The spatial index mechanism now uses Oracle8i extensible indexing features, including support for maintaining the index on insert, update, and delete. As a result, it is no longer necessary to explicitly call SDO_ADMIN.POPULATE_INDEX() or SDO_ADMIN.UPDATE_INDEX().
The RELATE() operator has been enhanced such that it can perform both primary and secondary filter operations. At query time, the Oracle8i extensible optimizer then determines which process to use based upon information in the query plan. The new syntax for the RELATE() operator greatly simplifies queries.
Two new spatial operators have been added.
Circle and circular arc geometric datatypes have been added, and several new spatial functions further extend the capabilities of this product.
Oracle8i Visual Information Retrieval provides content-based retrieval for images stored in Oracle8i. Content-based retrieval--where the query takes the form "find me objects that look like this one"--reduces the time and effort required to obtain image-based information.
Users can tap into image content with self-service applications using flexible query criteria. They can locate similar images in large databases by searching visual criteria, such as color, pattern, and texture. Examples of database applications where content-based retrieval is useful include:
Oracle8i Visual Information Retrieval includes foundational datatype support for images in Oracle8i, that complements and is completely compatible with Oracle8i interMedia. Oracle8i Visual Information Retrieval is based on the VIRTM technology from Virage, Inc., a leader in content-based retrieval software.
This section describes the enhancements to the Oracle8 Visual Information Retrieval Cartridge, now repackaged as Oracle8i Visual Information Retrieval. Also available with this release is the Oracle8i Visual Information Retrieval Java Client, similar in function to that described in "Oracle8i interMedia Audio, Image, and Video Java Client".
For more information, see Oracle8i Visual Information Retrieval User's Guide and Reference and Oracle8i Visual Information Retrieval Java Client User's Guide and Reference.
Oracle8i Visual Information Retrieval allows users to create an index to support image objects. Oracle8i and Visual Information Retrieval cooperate to define, build, and maintain an index for image data. Once created, the index automatically updates every time an image is inserted or removed from the database table. This feature significantly speeds up query time and improves performance.
Consider the case where a thin client requests a scaled-down image from the database. Rather than storing the scaled-down image in a table, it is now possible to use temporary LOBs, described in "Temporary LOBs". Methods are provided which allocate and operate on temporary LOBs. Additional performance benefits are achieved by utilizing OCI LOB buffering routines provided in Oracle8i, instead of local buffering mechanisms.
Scaling is now allowed to absolute dimensions. The process() method in release 8.0 only allowed scaling by a floating point number. The new scaling mechanism allows, for example, a much easier method to create a column of thumbnails for a table with images of various sizes in it.
Because it is not practical to provide native support for all of the image formats in use today, Visual Information Retrieval provides a canonical raw image format gateway to ease the import of proprietary image data. This benefits the many image processing programs that can directly support input and output of uncompressed image data.
Oracle8i Time Series enables time-stamped data to be stored efficiently in an Oracle database. It supports a basic set of functions--calendar, time series, and time scaling--to retrieve and process data. Its collection-based interface enables product developers and third-party providers to add functions to the base cartridge. By offering basic functions and datatypes, it allows customers and independent software vendors (ISVs) to develop data provisioning, data warehousing, and full-analysis time series applications using the power of Oracle8i.
Oracle8i Time Series provides the following kinds of functions:
A summary of the new features of Oracle8i Time Series, formerly called the Oracle8 Time Series Cartridge, is presented below.
For more information on Oracle8i Time Series, see the Oracle8i Time Series User's Guide.
Support is provided for irregular time series, that is, time series without an associated calendar. Irregular time series provide a means to conveniently handle high volumes of unpredictable data, as well as many applications with predictable data where timestamp validation is not needed.
Added are week, 10-day, semimonthly, quarter, and semiannual calendar frequencies.
Flexible timestamp precisions in Oracle8i Time Series relax the precision requirements of the previous release. For example, a monthly calendar now can be defined where all timestamps are on almost any specific day of the month (such as the 15th), whereas before they had to be on the first of the month.
The calendar pattern has been extended to provide flexible support of a repeated pattern of included and omitted timestamps. The pattern specification extensions allow for more semantic (behavioral) options with scaleup and scaledown functions.
Time scaling enhancements in Oracle8i include the implementation of scaledown functions and additional overloadings of scaling functions to provide an alternative to SQL null semantics.
Previously, the Time Series Cartridge supported a storage model for time series data based on a flat index-organized table (IOT). Oracle8i Time Series provides alternative support for a nested IOT model, enabling time series data to be stored in a nested IOT. Either method has advantages and disadvantages that must be weighed by the user to select the method providing the better performance and scalability.
Administrative tools and procedures are provided to simplify the creation and use of time series schema objects. For most users, these procedures are a convenient alternative to explicitly defining the tables, views, and triggers used by Oracle8i Time Series.
Java is becoming the standard language of the Internet. In Oracle8i, Oracle delivers an enterprise-class Java platform to develop and deploy Internet applications. Additionally, Oracle is making a significant strategic commitment to Java and is integrating it in a large part of its product offering.
Oracle8i's Java offering includes:
Each of these is summarized separately below.
For more information about Oracle's Java support, refer to the following books.
Oracle's Java VM is a complete JDK 1.1.6-compliant Java execution environment. The Java VM runs in the same process space and address space as the database server, sharing its memory heaps and directly accessing its relational data. This design optimizes memory use, increases throughput, and delivers an open, highly available, secure, and manageable Java server.
The Java VM provides a runtime environment for Java objects. It fully supports Java data structures, method dispatch, exception handling, language-level threads, and all the core Java class libraries. It embeds the standard Java namespace in database server schemas allowing Java programs access to Java objects stored in Oracle databases and application servers across the enterprise.
Components of the Java VM include a library manager, bytecode compiler, interpreter, class loader, verifier, native compiler (separate option), server-side JDBC driver, and server-side SQLJ translator. Only the native compiler (JServer Accelerator), JDBC driver, and SQLJ translator components are discussed in this document.
A complete description of Oracle's Java Virtual Machine can be found in Oracle8i Java Developer's Guide .
JServer Accelerator is a native code compiler that speeds up the execution of Java code by eliminating interpreter overhead. It translates standard Java binaries into C programs that are processed by a platform-dependent C compiler into native libraries that the Oracle Java VM can load dynamically. Unlike just-in-time (JIT) compilers, JServer Accelerator is portable to all OS and hardware platforms. To speed up applications, the Java VM is supplied with natively compiled versions of the core Java class libraries, ORB, and JDBC.
Oracle offers two programmatic interfaces (Application Programmer Interfaces, or APIs) for Java Developers: JDBC (Java Database Connectivity) and SQLJ (embedded SQL in Java).
JDBC is a standard Java interface for connecting to relational databases from Java. The JDBC standard was defined by Sun Microsystems, allowing individual providers to implement and extend the standard with their own JDBC drivers. JDBC is based on the X/Open SQL Call Level Interface, and complies with the SQL92 Entry Level standard. In addition to the standard JDBC API, Oracle drivers have extensions to properties, types, and performance.
Oracle offers three versions of its JDBC driver.
Using low-level entry points, a specially tuned JDBC driver runs directly inside the database server. As a result, it provides the fastest access to Oracle data from Java stored procedures. The Server JDBC Driver complies fully with the Sun JDBC specification. Tightly integrated with the database server, it supports Oracle-specific datatypes, NLS character sets, and stored procedures. Also, the client-side and server-side JDBC APIs are the same, allowing for flexibility in where the application is deployed.
This driver is meant for developers of client/server Java applications and Java-based middle tiers. It converts JDBC invocations into OCI calls, which are sent via Net8 to the Oracle database server.
The OCI JDBC Driver supports various configurations including stateful, multi-tier configurations in which browser-based applets communicate with middle-tier servlets. However, it is not downloadable and requires the installation of OCI and Net8 libraries on each client machine or on the middle-tier Java application server.
This driver is meant for developers of Java applications and applets. It establishes a direct connection to Oracle over Java sockets and implements a lightweight TCP/IP version of Net8 protocol. So, unlike the OCI JDBC Driver, it works only with TCP/IP-based networks.
Written in Java, the Thin JDBC Driver is about 150 K (300 K uncompressed), so it can be downloaded with Java applets into a Web browser or Network Computer. No preinstallation of software on the client is required.
SQLJ enables SQL statements to be embedded in Java programs. SQLJ code is much more concise and easier to write than JDBC and features static analysis and type checking.
The SQLJ translator, itself a Java program, takes as input a file of Java source code in which SQLJ clauses are embedded. Then, it translates the SQLJ clauses into Java class definitions that implement the specified SQL statements. The Java type system ensures that objects of those classes are called with the correct arguments. Oracle provides both a server-side and client-side SQLJ translator.
The server-side SQLJ translator is a highly optimized SQLJ translator that runs directly inside the database server, where it provides runtime access to Oracle data via the Server JDBC Driver. SQLJ forms can include queries, DML, DDL, transaction control statements, and calls to stored procedures. The client-side and server-side SQLJ APIs are the same, allowing for flexibility in where the application is deployed.
Java VM supports a variety of development models, including:
These are discussed in more detail in the following sections,
Stored procedures are Java procedures or methods published to SQL and stored in an Oracle database for general use. With stored procedures, you can implement business logic at the server level, thereby improving application performance, scalability, and security.
Except for GUI (Graphical User Interface) methods, any Java method can run in the database server as a stored procedure. The runtime contexts are:
To publish Java methods, call specifications (call specs for short) are written, which map Java method names, parameter types, and return types to their SQL counterparts. A call spec is not a wrapper. Without adding a layer of execution, a call spec simply publishes the existence of a Java method. So, when the method is called (through its call spec), the runtime system dispatches the call with minimal overhead. And, because the stored procedure is executing in the database, there is efficient access to SQL data.
Anything that can be written with PL/SQL can now be written with Java and run in the database server in the same contexts as PL/SQL. The database server allows a high degree of interoperability between Java and PL/SQL. Java applications can call PL/SQL stored procedures using an embedded JDBC driver. Conversely, PL/SQL applications can call Java stored procedures directly.
Enterprise JavaBeans (EJB) is a component model introduced by JavaSoft that enables developers to create custom component applications. These applications consist of server-side beans developed by yourself or by third parties. The beans provide the business logic in EJB applications.
The EJB model is flexible in that you can use different components from different vendors. For example, you can use configuration and management software from one company, bean containers from a second company, and business logic beans from a third company that specializes in providing beans for just that purpose.
CORBA is a standards-based distributed component model proposed by the Object Management Group (OMG). It supports a development environment for building, deploying, and managing distributed object applications that are interoperable across platforms.
CORBA objects communicate using OMGs Internet Inter-ORB Protocol (IIOP), the standard for communication between and among distributed objects running on the Internet, intranets, and in enterprise computing environments. Oracle8i integrates a Java-based CORBA 2.0 compliant Object Request Broker (ORB) that provides users with the ability to call into and out of the database server using CORBA's IIOP. CORBA components written in different languages, running on different platforms, can transparently communicate and interoperate.
Oracle provides utilities and tools for the development and deployment of Java applications. Some of these are discussed here.
JPublisher is a utility, written entirely in Java, that translates user-defined types to Java wrapper classes. It is similar to the Object Type Translator (OTT) utility used in C/C++ environments. Developers are required to have Java classes that correspond to user-defined types for developing:
JPublisher helps create Java language applications that use user-defined types in the Oracle server. For a Java-language application to access object data, it is necessary to represent the data in a host language format. JPublisher helps do this by creating the mapping between object types and Java classes, and between object attribute types and their corresponding Java types.
Using JPublisher to automatically generate Java wrapper classes for user-defined types is less time consuming and error prone than manually coding them.
With the release of Oracle8i, Oracle offers a new version of the JDeveloper product. JDeveloper 2.0 provides complete support for building and deploying Java applications on Oracle8i. It provides a standard GUI based Java development environment that is well integrated with Oracle's Application Server and Database.
The following are new features included in JDeveloper 2.0:
JDeveloper runs on Windows NT. General availability will follow the production Oracle8i release. For more information on JDeveloper 2.0, refer to the product specific documentation.
Provided with Java VM, the command-line utilities loadjava and dropjava manage Java library units. Java stored procedures can be written, compiled, and partially tested and debugged on the client side in JDeveloper, or other popular Java IDE. Then, the command-line utility loadjava can be used to upload the resulting Java sources, binaries, and resources into the database server. The companion utility dropjava can be used to drop given Java sources, binaries, and resources from a schema.
Oracle8i offers many new features and enhancements for the data warehousing and VLDB environment. The performance of queries is dramatically increased by materialized views and summary management, which allow frequently requested summaries to be stored and maintained. Parallelism is improved with more operations offered in parallel. The new database resource management facility, discussed in "Database Resource Management" , provides an effective way of managing CPU usage.
Moving data from one database to another is made simpler and faster with transportable tablespaces. New data mining and relational online analytical processing (ROLAP) functions are introduced. If you are using the partitioning option, the "Partitioning Enhancements" section discusses two new methods of partitioning, providing more partitioning options for the data warehouse environment.
Only a few of the many Oracle8i changes relating to data warehousing and the VLDB environment have been mentioned above. Read on for descriptions of these and allied new features and enhancements introduced in Oracle8i.
A materialized view is a stored summary containing precomputed results. Materialized views allow for significantly faster data warehouse query processing. With the Oracle8i summary management feature, the Oracle database server automatically rewrites queries to use the summary data, rather than retrieving data from detail tables by doing expensive joins and aggregate operations. This query rewrite facility is totally transparent to the application, which is not aware of the existence of the materialized view.
The DBA's first step in creating materialized views is to define dimensions. These represent the hierarchies that are present in the real world; for instance, multiple months make up a quarter, multiple districts make up a region, etc. The CREATE MATERIALIZED VIEW statement is used to create a materialized view. This statement includes a subquery, typically a join or a data aggregation (GROUP BY), the results of which comprise the materialized view.
A materialized view is maintained by a refresh process. The refresh process can be done automatically when a commit is done on a detail table, or it can be controlled manually by the DBA. A refresh is specified as complete or incremental. A complete refresh truncates existing data, then repopulates the summary with new data from the detail tables. An incremental refresh updates only changed data.
To create a materialized view in a users own schema, the user must have the CREATE SNAPSHOT or CREATE MATERIALIZED VIEW, CREATE TABLE, CREATE INDEX, and CREATE VIEW system privileges. To create a materialized view in another user's schema, a user must have the CREATE ANY SNAPSHOT or CREATE ANY MATERIALIZED VIEW system privilege.
To enable a materialized view for query rewrite:
The schema that contains the materialized view must have sufficient quota in the target tablespace to store the materialized view's base table and index or have the UNLIMITED TABLESPACE system privilege.
To create and refresh a materialized view, both the creator and materialized view owner must be able to issue the defining query of the materialized view. This capability depends directly on the database link that the materialized view's defining query uses.
For ease in managing materialized views, Oracle provides a collection of materialized view analysis and advisory functions that are callable from any PL/SQL program. These are packaged in the DBMS_OLAP package, which provides recommendations and supporting data such as the following:
For more information on summary management, see Oracle8i Concepts, Oracle8i SQL Reference, and Oracle8i Supplied Packages Reference.
This feature allows a user to move a subset of an Oracle database into another Oracle database. It is a lot like unplugging the subset from the original database and plugging it into another one. It is also possible to clone a tablespace in one database and plug it into another, thereby copying the tablespace between databases.
Moving data using the transportable tablespace feature can be orders of magnitude faster than either export/import or unload/load of the same data, because it involves only copying data files and integrating the tablespace metadata. When transporting tablespaces, index data can also moved, eliminating the need to rebuild indexes after importing or loading the table data.
In the data warehouse environment, where huge amounts of data flow from the initial OLTP databases into the enterprise data warehouse and on to data marts, this feature presents an opportunity for a faster and more innovative means of data movement. And, its use can be extended to the archiving of data or publishing data to other databases.
For more information, see Oracle8i Concepts and the Oracle8i Administrator's Guide.
The direct path load API is a set of OCI interfaces that provides an application access to the direct path load engine in the Oracle server. Previously, the only client of the direct path load engine has been Oracle's SQL*Loader utility. Now, it is possible for ISVs to develop applications which can take advantage of the performance benefits provided by the direct load engine, including parallel load. There is no longer a need to write SQL*Loader control and data files in order to access the direct path load engine.
For more information, see Oracle8i Utilities and the Oracle Call Interface Programmer's Guide.
The GROUP BY clause of SQL queries can now specify additional OLAP aggregate operations. These new operations are called ROLLUP and CUBE, and are effective for computing subtotals and cross-tabulations across multiple dimensions.
While these same operations could be done using previous SQL syntax, the SQL was difficult to generate, and the execution was inefficient because the optimizer had no hints about what the user wanted to accomplish. These new operations allow client reporting and analysis tools to perform aggregate operations with less effort and greater efficiently. They are useful for creating materialized views.
For more information, see Oracle8i Concepts and Oracle8i SQL Reference.
The FROM clause in a query has been modified with a SAMPLE clause to allow the user to specify that the results are to be based on a random (row or block) sample of a whole table. This feature can be a useful for data mining tools, and in a data warehousing environment can be used to avoid full table scans.
For more information, see Oracle8i Concepts and Oracle8i SQL Reference.
Indexes can now be created on functions and expressions that involve one or more columns in the table being indexed. A function-based index precomputes the value of the function or expression and stores it in the index. In a data warehouse, query performance is dramatically improved when a computationally intensive expression is used to build the index, rather than using that expression in the WHERE clause of a query.
The function used for building the index can be:
For more information, see Oracle8i Concepts.
The DESC keyword on the CREATE INDEX statement is no longer ignored. It specifies that the index should be created in descending order. Indexes on character data are created in descending order of the character values in the database character set. Neither this, nor the ASC keyword, may be specified for a domain index. DESC cannot be specified for a bitmapped index.
For more information, see Oracle8i SQL Reference.
Several changes enhance query performance when bitmap indexes are used. One of these changes lifts a restriction whereby the use of the ALTER TABLE statement could invalidate a bitmap index.
A new feature introduces two new ALTER TABLE statement options:
These options allow for tuning of the rowid-to-bitmap mapping. The MINIMIZE option can be used to optimize bitmap indexes for a query-only environment by requesting the most efficient possible mapping of bits to rowids. The NOMINIMIZE option disables this optimization.
For more information, see Oracle8i SQL Reference and Oracle8i Tuning.
The parallelization of aggregate distinct queries is now possible even if they do not contain a GROUP BY clause. This is done through new hints for specifying distribution methods.
For more information, see Oracle8i Tuning.
The sort routines used throughout the Oracle database server have been rewritten to eliminate performance problems. Sort now performs more predictably and consistently, given the same input, system configuration, and parameter settings. Memory is utilized more efficiently, enabling more rapid sorts when additional memory is specified. The following table shows initialization parameters that have been dropped or added as a result of this rewrite:
| Dropped Initialization Parameters | Added Initialization Parameter |
|---|---|
|
SORT_DIRECT_WRITES |
SORT_MULTIBLOCK_READ_COUNT |
|
SORT_WRITE_BUFFERS |
|
|
SORT_WRITE_BUFFER_SIZE |
|
|
SORT_READ_FAC |
|
|
SORT_SPACEMAP_SIZE |
|
For more information, see Oracle8i Tuning.
Using the new SINGLE TABLE option when creating a hash cluster specifies the creation of a hash cluster with only one table. The benefit is faster access to the rows in the table.
For more information, see Oracle8i Concepts, or for details about the SINGLE TABLE HASHKEYS clause of the CREATE CLUSTER statement, see Oracle8i SQL Reference.
To support the speed requirements of queries running in a data warehouse environment, it is critical that the optimizer selects the best plan of execution out of all those that are available for consideration. The optimizer selects the best plan based on cost, as determined by the statistics which have been gathered on the data. Therefore, it is vitally important that the optimizer has good and recent statistics on the data. Oracle8i introduces new options for the gathering and manipulation of statistics.
A new package, DBMS_STATS, is provided for:
The copying of statistics between databases is useful for tuning studies. Statistics can be copied from the production database into a test database. DBMS_STATS can also be used to back up statistics. The backup may be restored later, if necessary, or used to study the change in data characteristics over time.
Additionally, the syntax of the CREATE and ALTER INDEX statements has been changed to include a COMPUTE STATISTICS clause that allows statistics to be gathered incidentally, and at minimum cost, while the index is being built.
For more information, see Oracle8i Tuning, Oracle8i Concepts, Oracle8i SQL Reference, and Oracle8i Supplied Packages Reference.
This enhancement simplifies tuning of parallel query (PQ) execution and provides better system utilization. PQ users can simply specify that a query or table is to be parallelized, and the system will determine the optimal single user setting, then reduce it based on the system utilization at the time the query starts execution. There are four parts to this enhancement.
For more information, see Oracle8i Parallel Server Concepts and Administration and Oracle8i Tuning.
In large databases, operations such as complex queries, index builds, and backup operations can take a long time to complete. The dynamic V$SESSIONS_LONGOPS view provides a way for users and DBAs to monitor the progress of such operations and to estimate their completion times. In Oracle8i, this view is enhanced by more internal server components collecting a greater number of statistics. Additionally, the package DBMS_APPLICATION_INFO contains a procedure SET_SESSION_LONGOPS to populate the view from an application.
For more information, see Oracle8i Reference and Oracle8i Supplied Packages Reference.
The following new features are implemented through syntax changes to the ALTER TABLE and CREATE TABLE statements. For additional information on either of these enhancements, see the Oracle8i Administrator's Guide and Oracle8i SQL Reference.
It is now possible to modify the attributes of an existing constraint through the ALTER TABLE...MODIFY CONSTRAINT clause. This enhancement also introduces a new constraint property, RELY/NORELY. This flag can be used by the DBA to indicate validity of data without enabling or validating a constraint. It is used by the query rewrite function of materialized views.
Data warehouses may benefit from the DISABLE VALIDATE constraint state. This state saves space because it requires no index on a unique or primary key, yet it guarantees the validity of all existing data in the table. This is at the expense of disallowing DML and index lookups, but those may be less important considerations in some situations.
The DISABLE VALIDATE constraint state also enables efficient loading of data from a nonpartitioned table into a partitioned table using the EXCHANGE PARTITION clause of the ALTER TABLE statement.
To enable this feature, DISABLE/ENABLE and VALIDATE/NOVALIDATE have been made independent properties of constraints, where all four combinations are now allowed.
There are numerous enhancements for index-organized tables intended to make them usable for very large databases and mission-critical applications. These enhancements can be broadly classified into two areas:
Some of the key enhancements include:
For more information, see Oracle8i Concepts.
ROWIDs provide the fastest possible access to a given row in a given table. They contain the physical address of a row down to the specific block and allow retrieval of the row in a single block access. To expand this functionality to index-organized tables (IOTS), whose rows do not have permanent physical addresses, Oracle8i introduces primary key-based logical identifiers called logical ROWIDs.
Rows in index-organized tables do not have permanent physical addresses--they are stored in the index leaves and can move within the block or to a different block as a result of insertions--therefore their row identifiers cannot be based on physical addresses. Instead, Oracle provides index-organized tables with logical row identifiers, called logical ROWIDs, that are based on the table's primary key. Oracle uses these logical ROWIDs for the construction of secondary indexes on index-organized tables.
Also introduced is the UROWID datatype, which makes logical ROWIDs usable by applications in the same sense that ROWIDs are currently used (e.g., selecting ROWIDs for later update or as part of a cursor). UROWID can be used to store ROWIDs from other databases accessed via gateways, and it can be used to reference physical ROWIDs. PL/SQL fully supports the UROWID datatype.
For more information, see Oracle8i Concepts.
Other features are also useful in the data warehouse environment, but are discussed elsewhere in this document.
With Oracle8i, the Oracle database server provides a new extensibility framework, making it easier for ISVs and other developers to extend the database to meet their needs. Many object relational enhancements have been incorporated into this release, and this functionality has been extended to all Oracle8i product configurations.
Online transaction processing (OLTP) systems are usually concerned with entering and retrieving mission-critical data from day-to-day operations. Availability, reliability, and performance are extremely important for these systems. Oracle8i delivers significant enhancements in these areas, along with improvements in scalability, serviceability, and security.
Advance Queuing has been significantly enhanced, continuing to develop the publish/subscribe messaging paradigm. National Language Support now has a new book to help users better learn how to use its new and already existing functionality.
These and other new database features are discussed in this section.
The extensibility framework in Oracle8i extends the Oracle database server to support specialized datatypes and to provide core services for external use. Developers are able to provide their own implementations of database server services and register them with the server. The framework is safe and protects the integrity of Oracle8i.
ISVs and other developers can exploit this extensibility framework to more easily build user-defined software components, also referred to as data cartridges, that are easier to use, and that integrate seamlessly with each other and the Oracle database server. Multimedia users especially can benefit from the greater flexibility and fuctionality provided by this framework as it enables independent software components to interoperate in a seamless manner. For example, a query can now be based on both image and spatial data.
This section summarizes the features and enhancements that comprise the Oracle8i extensibility framework. For more information on the functionality described below, see the Oracle8i Data Cartridge Developer's Guide and Oracle8i Concepts.
Increasingly, databases are being used to store more varied types of data, such as spatial, audio, or video data. This brings a need for indexing complex datatypes and for specialized indexing techniques. Oracle8i provides an interface that enables developers to define domain-specific operators and indexing schemes and integrate them into the Oracle database server.
Oracle provides a set of built-in operators, for use in SQL statements, which include arithmetic operators (+, -, *, /), comparison operators ( =, >, <), logical operators (NOT, AND, OR), and set operators (UNION). These operators take as input one or more arguments (operands) and return a result. The extensibility framework of Oracle8i allows developers to define new operators. Their implementation is provided by the user, but the Oracle database server allows these user-defined operators to be used in SQL statements in the same manner as any of the predefined operators provided by Oracle.
The framework to develop new index types is based on the concept of cooperative indexing, where an application and the Oracle database server cooperate to build and maintain indexes for complex datatypes. The application software is responsible for defining the index structure, maintaining the index content during load and update operations, and searching the index during query processing. The index structure itself can be stored either in the Oracle database as tables or externally as files. Indexes created using these new index types are referred to as domain indexes.
For specific information about the extensions to SQL that provide this support, see Oracle8i SQL Reference.
The optimizer functionality has been extended to allow authors of user-defined functions and domain indexes to create statistics collection, selectivity, and cost functions that will be used by the optimizer in choosing a query plan.
The ANALYZE command can now make a call to a user-specified statistics collection function whenever a domain index is analyzed. User-defined statistics collection functions can also be defined for individual columns of a table and for user-defined datatypes.
Users can specify user-defined selectivity functions for predicates containing user-defined operators, standalone functions, or type methods.
Users can define costs for domain indexes and for user-defined standalone functions, package functions, and type methods. The user-defined costs can be in the form of default costs that the optimizer simply looks up, or they can be computed by functions called by the optimizer.
In addition to the above, other basic services have been packaged for use by user-defined components. These, and the other extensibility and object enhancements in Oracle8i, have allowed the uniform integration of Oracle's former data cartridge options (ConText, Image, Visual, Time Series, Spatial) into the Oracle database server.
The services include:
Additionally, the Oracle Software Packager, discussed in "Oracle Software Packager and Oracle Universal Installer", allows cartridges to be packaged and installed into Oracle8i.
The object relational paradigm introduced in Oracle8 gains new functionality in Oracle8i. Additionally, object relational technology is now available in all configurations of the Oracle database server. Enhancements target more flexible storage and retrieval of objects, and there is greater transparency between object views and object tables. Specifics of these changes are described below.
For more information on object relational enhancements, see the Oracle8i Concepts, Oracle8i Application Developer's Guide - Fundamentals, and PL/SQL User's Guide and Reference. Additional references may be provided below in specific descriptions.
Several LOB-related enhancements are included in this release. Additional references for these include the Oracle Call Interface Programmer's Guide, the Oracle8i Application Developer's Guide - Large Objects (LOBs), and Oracle8i SQL Reference.
Oracle8 provided support for permanently storing large unstructured data by means of large object (LOB) datatypes. But many applications have a need for temporary LOBs that act like local variables. These LOBs do not exist permanently in the database, but exist mainly for the purpose of performing transformations on LOB data.
Oracle8i supports the creation, freeing, access, and update of temporary LOBs. Their default lifetime is a session, but they may be explicitly freed sooner by the application. They are ideal as transient workspaces for data manipulation, and because no logging is done or redo records generated, they have better performance than persistent LOBs.
The system-generated object identifiers (OIDs) provide globally unique identifiers that are important for many applications, especially distributed processing and replication. However, they carry an overhead of increased storage requirements, and increased loading time. For applications that do not require the advantages of system-generated OIDs and which must save on overhead, Oracle8i allows users to specify their own identifier, such as a primary key, as an OID.
A related enhancement is referential integrity constraint support on REF columns.
These enhancements target making the handling and storing of large nested tables more efficient. Additional references for nested table enhancements are the Oracle Call Interface Programmer's Guide, Oracle8i SQL Reference, and Oracle8i Supplied Packages Reference.
Oracle8i provides for more efficient handling and storage of large varrays. Varray storage can now be specified. If specified, it is stored out of line in a LOB and the user can specify the storage characteristics of the LOB.
The notion of collection datatypes (nested tables and varrays) was introduced in Oracle8. Users are allowed to create columns of collection types, and functions and procedures with parameters of collection types. When it is necessary to view these collection types in a flat (relational) form, Oracle provides a way of unnesting them.
This unnesting is implemented using the TABLE() operator found in the table_collection_expression of the SQL statement. When introduced in release 8.0, the TABLE() expression took an argument of a set of rows and allowed that set to be treated like a table. Oracle8i extends this functionality so that a collection column of a table in a query may be used like an independent table.
Object views are an extension to the basic relational view mechanism to provide an object abstraction over existing relational and object data. They allow for the retrieval, update, insertion, and deletion of relational data as if such data were stored as object types. In this release, object views are enhanced to provide more features than are available in object tables, such as, updatability of nested tables and creation of new objects in the OCI object cache. The performance of object views is improved to be comparable with that of object or relational tables.
Triggers are PL/SQL procedures which are fired when various events occur in the system, such as updates to a row. In release 8.0, the trigger mechanism was defined over rows of tables or as an INSTEAD OF mechanism over views. Oracle8i expands the application of triggers to allow them to be defined over nested table view columns. As a result, it is now possible to update an element of a collection synthesized using the CAST...MULTISET operation.
The following enhancements are options that can be set for the object cache:
For more information, also see the Oracle Call Interface Programmer's Guide.
The ANALYZE command can now set dangling REFs to NULL. In this case, these REFs no longer raise an error condition and terminate execution.
For more information, also see Oracle8i SQL Reference.
In release 8.0, if a relational table or an object table contained REF type columns, there was no simple way of inserting a relational row or a row object into the table. First NULLs were required to be inserted into the columns and then an update statement was issued to set their values. This inefficiency is resolved in Oracle8i, by allowing a value subquery expression in the INSERT...VALUES list.
A value subquery expression may also be used as an argument to a function or a type constructor.
The SQL*Loader, used to move data from external files into tables in an Oracle database, has been extended to support the loading of objects, collections, and LOBs. For a summary of SQL*Loader enhancements, see "SQL*Loader Enhancements" .
For more information, see Oracle8i Utilities.
The partitioning of tables with columns containing objects, collections (varrays and nested tables), and REFs is provided in Oracle8i. The partitioning of object tables is also supported.
Oracle8i provides parallel query support on object tables.
After carefully tuning an application, a user may wish to ensure that the optimizer generates the same execution plan whenever the same SQL statements are executed. Oracle8i provides a method for stabilizing execution plans, regardless of changes to the system configuration, parameters, statistics, or even changes to the optimizer itself. This also benefits high-end OLTP sites by having SQL execute without having to invoke the cost-based optimizer at each SQL invocation.
This feature is especially useful for ISVs, who can distribute the execution plans of the SQL in their package applications so that all installed sites will be executing the same execution plans.
Through a new SQL statement, CREATE OUTLINE, the user is able to create a stored outline containing a set of attributes used by the optimizer to create an execution plan. Stored outlines can also be created automatically by setting the system parameter CREATE_STORED_OUTLINES to TRUE. The system parameter USE_STORED_OUTLINES can be set to TRUE, FALSE, or to a category name to indicate whether to make use of existing stored outlines for queries that are being executed. The OUTLN_PKG package provides procedures used for managing stored outlines.
For more information, see Oracle8i SQL Reference, Oracle8i Tuning, and Oracle8i Supplied Packages Reference.
Advanced Queuing (AQ) was introduced in release 8.0 of Oracle8 Enterprise Edition. It integrated a message queuing system with the Oracle database through a set of queue tables and queue functions, and allowed for either synchronous or asynchronous communication between programs. Oracle8i provides enhancements to the functionality of Advanced Queuing. These are discussed in this section.
For more information, see the Oracle8i Application Developer's Guide - Advanced Queuing.
Additional features are introduced in Oracle8i Advanced Queuing to support a publish/subscribe messaging paradigm. In this model, database queues serve as a repository for the messages, rules, and control information. There is a decoupling of addressing between message senders and message recipients. It is the recipients who specify which messages they wish to receive. Sending applications anonymously publish messages and receiving applications independently and autonomously subscribe to such messages.
The components of this publish/subscribe model are:
Publishers are entities that publish information (as messages) to queues. They do not know or care about the interest of other applications in the messages; they only know that they are to publish the information. There are two types of publishing: database event publishing where triggers cause messages to be enqueued when certain database or system events occur, and publishing from an application. Database event publishing is new in Oracle8i and is discussed separately later.
Subscribers are entities that receive information by expressing interest in certain types of messages. They do not care about the origins of the messages. They specify certain filtering rules for messages, allowing them to receive only the messages whose content satisfies the rules.
The rules engine, new in Oracle8i, provides rules-based subscriptions by evaluating incoming messages from publishers against a set of rules specified by subscribers. Only if the message satisfies a rule is it delivered to the subscriber. Subscribers can express rules using the full power of SQL. Since AQ messages are normal database objects, any SQL operation that applies to data objects can also be applied to messages.
An application (subscriber/client) may receive messages that are published in one of three ways: explicit dequeuing, dequeuing with listen, or by using OCI functions to register a callback. For more specific information, also see the Oracle Call Interface Programmer's Guide.
The database event publication feature allows applications to subscribe to database events just as they subscribe to messages from other applications. Trigger syntax is extended to support system and other data events on DATABASE and SCHEMA, Trigger syntax also supports a CALL to a procedure as the trigger body.
Users can enable the publication of the following events.
The database event publication subsystem tightly integrates with the AQ publish/subscribe engine. For a complete description of triggers for data and system events, see Oracle8i SQL Reference.
For increased scalability, the performance of basic queue operations has been enhanced and changes have been made to underlying data structures. Message propagation scheduling is improved. AQ propagation uses message streaming and eliminates the current two-phase commit mechanism. Addi