News -- Oracle 8i: The Materialized View

In our book Oracle Essentials, we tried to give a complete but compact view of the entire range of functionality in the Oracle database. Naturally, there were some features which we couldn't cover in as much depth as we'd like. This article will expand on topics we covered briefly in Oracle Essentials.

What is the purpose of a materialized view?

Before beginning any exploration of the how of the materialized view, it is important to understand the why behind this new data structure.

The materialized view is another in a long line of gofast tricks implemented in the Oracle database. Introduced with Oracle8i, a materialized view is designed to improve performance of the database by doing some intensive work in advance of the results of that work being needed.

In the case of a materialized view, the data for the view is assembled when the view is created or refreshed. Later queries that need this data automatically use the materialized view, thus saving the overhead of performing the work already done by the view.

The work avoided by a materialized view is essentially twofold:

• A materialized view can be used to pre-collect aggregate values.
• A materialized view can be used to assemble data that would come from many different tables, which would in turn require many different joins to be performed.

You may recognize these two requirements as being characteristic of data warehousing. And, in fact, the materialized view is most frequently used in data warehouse applications. Data warehousing applications also do not typically require access to real-time data, so the need to assemble data into a materialized view does not necessarily impact the usefulness of the view.

What is a materialized view?

In Oracle8, a new type of database structure, the materialized view, was introduced. A materialized view is a polyglot structure, resembling other existing structures in some ways, yet different in others.

A materialized view is like a view in that it represents data that is contained in other database tables and views; yet it is unlike a view in that it contains actual data. A materialized view is like an index in that the data it contains is derived from the data in database tables and views; yet unlike an index in that its data must be explicitly refreshed. Finally, a materialized view is very much like a snapshot in that an administrator can specify when the data is to be refreshed; but it is unlike a snapshot in that a materialized view should either include summary data or data from many different joined tables.

How is a materialized view created?

Since a materialized view is just another data structure, you create a materialized view with a piece of Data Definition Language (DDL) syntax.

The syntax used is made up of several parts and keywords. The most commonly used syntax options are described in the following table:

Related Reading Oracle Essentials: Oracle9i, Oracle8i and Oracle8 By Rick Greenwald, Robert Stackowiak, Jonathan Stern Table of Contents Index Sample Chapter Read Online--Safari Search this book on Safari:   Only This Book All of Safari Code Fragments only

There is one more clause associated with the creation of materialized views, but before you can understand this clause, you must gain a deeper understanding of how a materialized view is used by your Oracle8i database.

How does it work?

At this point, it may be difficult to understand the importance of a materialized view. After all, why not just use one of the other structures that have similar characteristics, such as a summary table or a pre-joined view?

The answer lies in the interaction between a materialized view and the Oracle8i cost-based optimizer. The cost-based optimizer can automatically substitute a materialized view for a standard table or group of tables if appropriate. This capability in the optimizer means that you can add materialized views to your database and reap the performance benefits, without having to rewrite any of your applications.

The cost-based optimizer will examine a particular query to see if it can be satisfied by a materialized view. If it can, the cost-based optimizer will simply use the materialized view instead of the original data structures. The cost-based optimizer has some intelligence, so even if the data in a materialized view can only satisfy part of the query, the optimizer will still use the materialized view for as much of the data as possible.

If you want a materialized view to be considered for use by the cost-based optimizer, you must include the keyword phrase ENABLE QUERY REWRITE in your specification of the view, or as part of an ALTER VIEW statement after the materialized view has been created.

As with other potential choices for the cost-based optimizer, you can use a hint to explicitly direct a query to the materialized view.

What are the management options?

As mentioned above, you can specify some management options for materialized views, like the timing and type of refresh, as part of the DDL used to create the view.

There are a few functions in two of the prebuilt PL/SQL packages that can be effectively used to manage materialized views, which are summarized in the following table.

There are more conditions and considerations revolving around materialized views than I am able to discuss in this brief article, but this should give you the idea of how and when to consider using materialized views in your Oracle8i database.

All in all, the materialized view is a nice addition to the arsenal of data structures in Oracle8i. Although your own particular implementation may not need materialized views, these new views can potentially deliver greater throughput without having to rewrite the applications that use the underlying data.