8
Materialized Views

This chapter introduces you to the use of materialized views and discusses:

Overview of Data Warehousing with Materialized Views

Typically, data flows from one or more online transaction processing (OLTP) databases into a data warehouse on a monthly, weekly, or daily basis. The data is usually processed in a staging file before being added to the data warehouse. Data warehouses typically range in size from tens of gigabytes to a few terabytes, usually with the vast majority of the data stored in a few very large fact tables.

One technique employed in data warehouses to improve performance is the creation of summaries, or aggregates. They are a special kind of aggregate view that improves query execution times by precalculating expensive joins and aggregation operations prior to execution and storing the results in a table in the database. For example, a table could be created to contain the sum of sales by region and by product.

Prior to Oracle8i, organizations using summaries spent a significant amount of time manually creating summaries, identifying which ones to create, indexing the summaries, updating them, and advising their users on which ones to use. The introduction of summary management in the Oracle server eases the workload of the DBA and means the end user no longer has to be aware of which summaries have been defined. The DBA creates one or more materialized views, which are the equivalent of a summary. The end user queries the tables and views in the database and the query rewrite mechanism in the Oracle server automatically rewrites the SQL query to use the summary tables. This mechanism significantly improves the response time for returning results from the query and eliminates the need for the end user or database application to be aware of the materialized views that exist within the data warehouse.

The summaries or aggregates that are referred to in this book and in literature on data warehousing are created in Oracle using a schema object called a materialized view. Materialized views can perform a number of roles, such as improving query performance or providing replicated data, as described below.

Although materialized views are usually accessed via the query rewrite mechanism, an end-user or database application can construct queries that directly access the summaries. However, serious consideration should be given to whether users should be allowed to do this, because, once the summaries are directly referenced in queries, the DBA will not be free to drop and create summaries without affecting applications.

Materialized Views for Data Warehouses

In data warehouses, materialized views can be used to precompute and store aggregated data such as the sum of sales. Materialized views in these environments are typically referred to as summaries, because they store summarized data. They can also be used to precompute joins with or without aggregations. A materialized view eliminates the overhead associated with expensive joins or aggregations for a large or important class of queries.

Materialized Views for Distributed Computing

In distributed environments, materialized views are used to replicate data at distributed sites and to synchronize updates done at several sites with conflict resolution methods. The materialized views as replicas provide local access to data which otherwise would have to be accessed from remote sites. Materialized views are also useful in remote data marts.

Materialized Views for Mobile Computing

You can also use materialized views to download a subset of data from central servers to mobile clients, with periodic refreshes from the central servers and propagation of updates by clients back to the central servers.

This chapter focuses on the use of materialized views in data warehouses. See also Oracle8i Replication and Oracle8i Distributed Database Systems for details on distributed and mobile computing.

The Need for Materialized Views

Materialized views are used in data warehouses to increase the speed of queries on very large databases. Queries to large databases often involve joins between tables or aggregations such as SUM, or both. These operations are very expensive in terms of time and processing power. The type of materialized view that is created determines how the materialized view can be refreshed and used by query rewrite.

Materialized views can be used in a number of ways and almost identical syntax can be used to perform a number of roles. For example, a materialized view can be used to replicate data, which was formerly achieved by using the CREATE SNAPSHOT statement. Now CREATE MATERIALIZED VIEW is a synonym for CREATE SNAPSHOT.

Materialized views improve query performance by precalculating expensive join and aggregation operations on the database prior to execution time and storing the results in the database. The query optimizer can use materialized views by automatically recognizing when an existing materialized view can and should be used to satisfy a request. It then transparently rewrites the request to use the materialized view. Queries are then directed to the materialized view and not to the underlying detail tables. In general, rewriting queries to use materialized views rather than detail tables results in a significant performance gain.

Figure 8-1 Transparent Query Rewrite

When using query rewrite, you want to create materialized views that satisfy the largest number of queries. For example, if you identify 20 queries that are commonly applied to the detail or fact tables, then you might be able to satisfy them with five or six well-written materialized views. A materialized view definition can include any number of aggregations (SUM, COUNT(x), COUNT(*), COUNT(DISTINCT x), AVG, VARIANCE, STDDEV, MIN, and MAX) and/or include any number of joins. If you are unsure of which materialized views to create, Oracle provides a set of advisory functions in the DBMS_OLAP package to help in designing and evaluating materialized views for query rewrite.

If a materialized view is to be used by query rewrite, it must be stored in the same database as its fact or detail tables. A materialized view can be partitioned, and you can define a materialized view on a partitioned table and one or more indexes on the materialized view.

Materialized views are similar to indexes in several ways: they consume storage space, they must be refreshed when the data in their master tables changes, and, when used for query rewrite, they improve the performance of SQL execution and their existence is transparent to SQL applications and users. Unlike indexes, materialized views can be accessed directly using a SELECT statement. Depending on the type of refresh that is required, they can also be accessed directly in an INSERT, UPDATE, or DELETE statement.

Note:
Materialized views can also be used by Oracle Replication. The techniques shown in this chapter illustrate how to use materialized views in data warehouses. See Oracle8i Replication for further information.

Components of Summary Management

Summary management consists of:

Mechanisms to define materialized views and dimensions
A refresh mechanism to ensure that all materialized views contain the latest data
A query rewrite capability to transparently rewrite a query to use a materialized view
An advisor utility to recommend which materialized views to create, retain, and drop

Many large decision support system (DSS) databases have schemas that do not closely resemble a conventional data warehouse schema, but that still require joins and aggregates. The use of summary management features imposes no schema restrictions, and may enable some existing DSS database applications to achieve large gains in performance without requiring a redesign of the database or application. This functionality is thus available to all database users.

Figure 8-2 illustrates where summary management is used in the warehousing cycle. After the data has been transformed, staged, and loaded into the detail data in the warehouse, the summary management process can be invoked. This means that summaries can be created, queries can be rewritten, and the advisor can be used to plan summary usage and creation.

Figure 8-2 Overview of Summary Management

Understanding the summary management process during the earliest stages of data warehouse design can yield large dividends later in the form of higher performance, lower summary administration costs, and reduced storage requirements.

The summary management process begins with the creation of dimensions and hierarchies that describe the business relationships and common access patterns in the database. An analysis of the dimensions, combined with an understanding of the typical work load, can then be used to create materialized views. Materialized views improve query execution performance by precalculating expensive join or aggregation operations prior to execution time. Query rewrite then automatically recognizes when an existing materialized view can and should be used to satisfy a request, and can transparently rewrite a request to use a materialized view, thus improving performance.

Terminology

Some basic data warehousing terms are defined as follows:

Dimension tables describe the business entities of an enterprise, which usually represent hierarchical, categorical information such as time, departments, locations, and products. Dimension tables are sometimes called lookup or reference tables.

Dimension tables usually change slowly over time and are not modified on a periodic schedule. They are typically not large, but they affect the performance of long-running decision support queries that consist of joins of fact tables with dimension tables, followed by aggregation to specific levels of the dimension hierarchies. Dimensions and dimension tables are discussed in Chapter 9, "Dimensions".
Fact tables describe the business transactions of an enterprise. Fact tables are sometimes called detail tables.

The vast majority of data in a data warehouse is stored in a few very large fact tables. They are updated periodically with data from one or more operational online transaction processing (OLTP) databases.

Fact tables include measures such as sales, units, and inventory.
- A simple measure is a numeric or character column of one table such as FACT.SALES.
- A computed measure is an expression involving only simple measures of one table, for example, FACT.REVENUES - FACT.EXPENSES.
- A multi-table measure is a computed measure defined on multiple tables, for example, FACT_A.REVENUES - FACT_B.EXPENSES.
Fact tables also contain one or more keys that organize the business transactions by the relevant business entities such as time, product, and market. In most cases, the fact keys are non-null, form a unique compound key of the fact table, and join with exactly one row of a dimension table.
A materialized view is a pre-computed table comprising aggregated and/or joined data from fact and possibly dimension tables. Builders of data warehouses will know a materialized view as a summary or aggregation.

Schema Design Guidelines for Materialized Views

A materialized view definition can include any number of aggregates, as well as any number of joins. In several ways, a materialized view behaves like an index:

The purpose of a materialized view is to increase request execution performance.
The existence of a materialized view is transparent to SQL applications, so a DBA can create or drop materialized views at any time without affecting the validity of SQL applications.
A materialized view consumes storage space.
The contents of the materialized view must be updated when the underlying detail tables are modified.

Before starting to define and use the various components of summary management, you should review your schema design to, wherever possible, abide by the following guidelines:

Guideline 1:

Your dimensions should either be denormalized (each dimension contained in one table) or the joins between tables in a normalized or partially normalized dimension should guarantee that each child-side row joins with exactly one parent-side row. The benefits of maintaining this condition are described in "Creating a Dimension".

If desired, this condition can be enforced by adding FOREIGN KEY and NOT NULL constraints on the child-side join key(s) and PRIMARY KEY constraints on the parent-side join key(s). If your materialized view contains only a single detail table, or if it performs no aggregation, a preferred alternative would be to use queries containing outer joins in place of inner joins. In this case, the Oracle optimizer can guarantee the integrity of the result without enforced referential integrity constraints.

Guideline 2:

If dimensions are denormalized or partially denormalized, hierarchical integrity must be maintained between the key columns of the dimension table. Each child key value must uniquely identify its parent key value, even if the dimension table is denormalized. Hierarchical integrity in a denormalized dimension can be verified by calling the VALIDATE_DIMENSION procedure of the DBMS_OLAP package.

Guideline 3:

Fact tables and dimension tables should similarly guarantee that each fact table row joins with exactly one dimension table row. This condition must be declared, and optionally enforced, by adding FOREIGN KEY and NOT NULL constraints on the fact key column(s) and PRIMARY KEY constraints on the dimension key column(s), or by using outer joins as described in Guideline 1. In a data warehouse, constraints are typically enabled with the NOVALIDATE and RELY options to avoid constraint enforcement performance overhead.

Guideline 4:

Incremental loads of your detail data should be done using the SQL*Loader direct-path option, or any bulk loader utility that uses Oracle's direct path interface (including INSERT AS SELECT with the APPEND or PARALLEL hints). Fast refresh after conventional DML is not supported with views with aggregates and joins, but it is for single-table aggregate views. See Oracle8i SQL Reference for further details.

Guideline 5:

Range-partition your tables by a monotonically increasing time column if possible (preferably of type DATE). Different materialized views have different requirements for speeding up refresh.

Guideline 6:

After each load and before refreshing your materialized view, use the VALIDATE_DIMENSION procedure of the DBMS_OLAP package to incrementally verify dimensional integrity.

Guideline 7:

Horizontally partition and index the materialized view as you have the fact tables. Include a local concatenated index on all the materialized view keys.

Guidelines 1 and 2 are more important than guideline 3. If your schema design does not follow guidelines 1 and 2, it does not then matter whether it follows guideline 3. Guidelines 1, 2, and 3 affect both query rewrite performance and materialized view refresh performance.

If you are concerned with the time required to enable constraints and whether any constraints may be violated, use the ENABLE NOVALIDATE clause to turn on constraint checking without validating any of the existing constraints. The risk with this approach is that incorrect query results could occur if any constraints are broken. Therefore, the designer must determine how clean the data is and whether the risk of wrong results is too great.

Summary management can perform many useful functions, including query rewrite and materialized view refresh, even if your data warehouse design does not follow these guidelines. However, you will realize significantly greater query execution performance and materialized view refresh performance benefits and you will require fewer materialized views if your schema design complies with these guidelines.

Types of Materialized Views

The SELECT clause in the materialized view creation statement defines the data that the materialized view is to contain. There are only a few restrictions on what may be specified. Any number of tables can be joined together, however, they cannot be remote tables if you wish to take advantage of query rewrite or the warehouse refresh facility (part of the DBMS_OLAP package). Besides tables, views, inline views, subqueries, and materialized views may all be joined or referenced in the SELECT clause.

The types of materialized views are:

Materialized Views with Joins and Aggregates

In data warehouses, materialized views would normally contain one of the aggregates shown in Example 2 below. For fast refresh to be possible, the SELECT list must contain all of the GROUP BY columns (if present), and may contain one or more aggregate functions. The aggregate function must be one of: SUM, COUNT(x), COUNT(*),COUNT(DISTINCT x), AVG, VARIANCE, STDDEV, MIN, and MAX, and the expression to be aggregated can be any SQL value expression.

If a materialized view contains joins and aggregates, then it cannot be fast refreshed using a materialized view log. Therefore, for a fast refresh to be possible, only new data can be added to the detail tables and it must be loaded using the direct path method.

Here are some examples of the type of materialized view that can be created.

Create Materialized View: Example 1

CREATE MATERIALIZED VIEW store_sales_mv
  PCTFREE 0 TABLESPACE mviews
  STORAGE (initial 16k next 16k pctincrease 0)
  BUILD DEFERRED
  REFRESH COMPLETE ON DEMAND
  ENABLE QUERY REWRITE
  AS
  SELECT
   s.store_name,
     SUM(dollar_sales) AS sum_dollar_sales
      FROM store s, fact f
      WHERE f.store_key = s.store_key
      GROUP BY s.store_name;

Example 1 creates a materialized view store_sales_mv that computes the sum of sales by store. It is derived by joining the tables store and fact on the column store_key. The materialized view does not initially contain any data because the build method is DEFERRED. A complete refresh is required for the first refresh of a build deferred materialized view. When it is refreshed, a complete refresh is performed and, once populated, this materialized view can be used by query rewrite.

Create Materialized View: Example 2

CREATE MATERIALIZED VIEW store_stdcnt_mv
  PCTFREE 0 TABLESPACE mviews
  STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0)
  BUILD IMMEDIATE
  REFRESH FAST
  ENABLE QUERY REWRITE
  AS
  SELECT store_name, t.time_key,
     STDDEV(unit_sales) AS stdcnt_unit_sales
     AVG(unit_sales) AS avgcnt_unit_sales
     COUNT(unit_sales) AS count_days
     SUM(unit_sales) AS sum_unit_sales
   FROM store s, fact f, time t
      WHERE s.store_key = f.store_key AND
            f.time_key = t.time_key
      GROUP BY store_name, t.time_key;

The statement above creates a materialized view store_stdcnt_mv that computes the standard deviation for the number of units sold by a store on a given date. It is derived by joining the tables store, time, and fact on the columns store_key and time_key. The materialized view is populated with data immediately because the build method is immediate and it is available for use by query rewrite. In this example, the default refresh method is FAST, which is allowed because the COUNT and SUM aggregates have been included to support fast refresh of the STDDEV aggregate.

Single-Table Aggregate Materialized Views

A materialized view that contains one or more aggregates (SUM, AVG, VARIANCE, STDDEV, COUNT) and a GROUP BY clause may be based on a single table. The aggregate function can involve an expression on the columns such as SUM(a*b). If this materialized view is to be incrementally refreshed, then a materialized view log must be created on the detail table with the INCLUDING NEW VALUES option, and the log must contain all columns referenced in the materialized view query definition.

CREATE MATERIALIZED VIEW log on fact   
  with rowid (store_key, time_key, dollar_sales, unit_sales)  
  including new values;  

CREATE MATERIALIZED VIEW sum_sales  
  PARALLEL
  BUILD IMMEDIATE  
  REFRESH FAST ON COMMIT  
  AS  
  SELECT f.store_key, f.time_key,  
         COUNT(*) AS count_grp,  
  SUM(f.dollar_sales) AS sum_dollar_sales,  
        COUNT(f.dollar_sales) AS count_dollar_sales,  
  SUM(f.unit_sales) AS sum_unit_sales,  
        COUNT(f.unit_sales) AS count_unit_sales 
  FROM fact f  
  GROUP BY f.store_key, f.time_key;

In this example, a materialized view has been created which contains aggregates on a single table. Because the materialized view log has been created, the materialized view is fast refreshable. If DML is applied against the fact table, then, when the commit is issued, the changes will be reflected in the materialized view.

Table 8-1 illustrates the aggregate requirements for a single-table aggregate materialized view.

Table 8-1 Single-Table Aggregate Requirements

If aggregate X is present, aggregate Y is required and aggregate Z is optional
X	Y	Z
`COUNT(expr)`	`-`	`-`
`SUM(expr)`	`COUNT(expr)`	`-`
`AVG(expr)`	`COUNT(expr)`	`SUM(expr)`
`STDDEV(expr)`	`COUNT(expr)`	`SUM(expr * expr)`
`VARIANCE(expr)`	`COUNT(expr)`	`SUM(expr * expr)`

Note that COUNT(*) must always be present.

Incremental refresh for a single-table aggregate materialized view is possible after any type of DML to the base tables (direct load or conventional INSERT, UPDATE, or DELETE).

A single-table aggregate materialized view can be defined to be refreshed ON COMMIT or ON DEMAND. If it is ON COMMIT, the refresh is performed at commit time of the transaction that does DML on one of the materialized view's detail tables.

After a refresh ON COMMIT, you are urged to check the alert log and trace files to see if any error occurred during the refresh.

Materialized Views Containing Only Joins

Materialized views may contain only joins and no aggregates, such as in the next example where a materialized view is created which joins the fact table to the store table. The advantage of creating this type of materialized view is that expensive joins will be precalculated.

Incremental refresh for a materialized view containing only joins is possible after any type of DML to the base tables (direct load or conventional INSERT, UPDATE, or DELETE).

A materialized view containing only joins can be defined to be refreshed ON COMMIT or ON DEMAND. If it is ON COMMIT, the refresh is performed at commit time of the transaction that does DML on the materialized view's detail table.

If you specify REFRESH FAST, Oracle performs further verification of the query definition to ensure that fast refresh can be performed if any of the detail tables change. These additional checks include:

A materialized view log must be present for each detail table.
The rowids of all the detail tables must appear in the SELECT list of the materialized view query definition.
If there are outer joins, unique constraints must exist on the join columns of the inner table.

For example, if you are joining the fact and a dimension table and the join is an outer join with the fact table being the outer table, there must exist unique constraints on the join columns of the dimension table.

If some of the above restrictions are not met, then the materialized view should be created as REFRESH FORCE to take advantage of incremental refresh when it is possible. If the materialized view is created as ON COMMIT, Oracle performs all of the fast refresh checks. If one of the tables did not meet all of the criteria, but the other tables did, the materialized view would still be incrementally refreshable with respect to the other tables for which all the criteria are met.

In a data warehouse star schema, if space is at a premium, you can include the rowid of the fact table only because this is the table that will be most frequently updated, and the user can specify the FORCE option when the materialized view is created.

A materialized view log should contain the rowid of the master table. It is not necessary to add other columns.

To speed up refresh, it is recommended that the user create indexes on the columns of the materialized view that stores the rowids of the fact table.


CREATE MATERIALIZED VIEW LOG ON fact
  WITH ROWID;
 
CREATE MATERIALIZED VIEW LOG ON time
  WITH ROWID;
 
CREATE MATERIALIZED VIEW LOG ON store
  WITH ROWID;
 
CREATE MATERIALIZED VIEW detail_fact_mv 
       PARALLEL
       BUILD IMMEDIATE
       REFRESH FAST
       AS
       SELECT
    f.rowid "fact_rid", t.rowid "time_rid", s.rowid "store_rid",
      s.store_key, s.store_name, f.dollar_sales,
      f.unit_sales, f.time_key
       FROM fact f, time t, store s 
       WHERE f.store_key = s.store_key(+) AND 
       f.time_key = t.time_key(+);

In this example, in order to perform a REFRESH FAST, unique constraints should exist on s.store_key and t.time_key. It is also recommended that indexes be created on the columns fact_rid, time_rid, and store_rid, as illustrated below, which will improve the performance of refresh.

CREATE INDEX mv_ix_factrid  ON
  detail_fact_mv(fact_rid);

Alternatively, if the example shown above did not include the columns time_rid and store_rid, and if the refresh method was REFRESH FORCE, then this materialized view would be fast refreshable only if the fact table was updated but not if the tables time or store were updated.

CREATE MATERIALIZED VIEW detail_fact_mv 
       PARALLEL
       BUILD IMMEDIATE
       REFRESH FORCE
       AS
       SELECT
    f.rowid "fact_rid",
      s.store_key, s.store_name, f.dollar_sales,
      f.unit_sales, f.time_key
       FROM fact f, time t, store s 
       WHERE f.store_key = s.store_key(+) AND 
      f.time_key = t.time_key(+);

Creating a Materialized View

A materialized view can be created with the CREATE MATERIALIZED VIEW statement or using Oracle Enterprise Manager. The following command creates the materialized view store_sales_mv.

CREATE MATERIALIZED VIEW store_sales_mv
  PCTFREE 0 TABLESPACE mviews
  STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0)
  PARALLEL
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE
  AS
  SELECT
   s.store_name,
     SUM(dollar_sales) AS sum_dollar_sales
      FROM store s, fact f
      WHERE f.store_key = s.store_key
      GROUP BY s.store_name;

See Also:
For a complete description of CREATE MATERIALIZED VIEW, see the Oracle8i SQL Reference.

It is not uncommon in a data warehouse to have already created summary or aggregation tables, and the DBA may not wish to repeat this work by building a new materialized view. In this instance, the table that already exists in the database can be registered as a prebuilt materialized view. This technique is described in "Registration of an Existing Materialized View".

Once you have selected the materialized views you want to create, follow the steps below for each materialized view.

Do the physical design of the materialized view (existing user-defined materialized views do not require this step). If the materialized view contains many rows, then, if appropriate, the materialized view should be partitioned by a time attribute (if possible) and should match the partitioning of the largest or most frequently updated detail or fact table (if possible). Refresh performance generally benefits from a large number of partitions because it can take advantage of the parallel DML capabilities in Oracle.
Use the CREATE MATERIALIZED VIEW statement to create and, optionally, populate the materialized view. If a user-defined materialized view already exists, then use the PREBUILT clause in the CREATE MATERIALIZED VIEW statement. Otherwise, use the BUILD IMMEDIATE clause to populate the materialized view immediately, or the BUILD DEFERRED clause to populate the materialized view at a more convenient time. The materialized view is disabled for use by query rewrite until the first REFRESH, after which it will be automatically enabled, provided the ENABLE QUERY REWRITE clause has been specified.

See Also:
See Oracle8i SQL Reference for descriptions of the SQL statements CREATE MATERIALIZED VIEW, ALTER MATERIALIZED VIEW, ORDER BY, and DROP MATERIALIZED VIEW.

Naming

The name of a materialized view must conform to standard Oracle naming conventions. However, if the materialized view is based on a user-defined prebuilt table, then the name of the materialized view must exactly match that table name.

If you already have a naming convention for tables and indexes, you may consider extending this naming scheme to the materialized views so that they are easily identifiable. For example, instead of naming the materialized view sum_of_sales, it could be called sum_of_sales_mv to denote that this is a materialized view and not a table or view, for instance.

Storage Characteristics

Unless the materialized view is based on a user-defined prebuilt table, it requires and occupies storage space inside the database. Therefore, the storage needs for the materialized view should be specified in terms of the tablespace where it is to reside and the size of the extents.

If you do not know how much space the materialized view will require, then the DBMS_OLAP.ESTIMATE_SIZE package, which is described in Chapter 15, "Summary Advisor", can provide an estimate on the number of bytes required to store this materialized view. This information can then assist the design team in determining the tablespace in which the materialized view should reside.

See Also:
For a complete description of the STORAGE semantics, see the Oracle8i SQL Reference.

Build Methods

Two build methods are available for creating the materialized view, as shown in the following table. If you select BUILD IMMEDIATE, the materialized view definition is added to the schema objects in the data dictionary, and then the fact or detail tables are scanned according to the SELECT expression and the results are stored in the materialized view. Depending on the size of the tables to be scanned, this build process can take a considerable amount of time.

An alternative approach is to use the BUILD DEFERRED clause, which creates the materialized view without data, thereby enabling it to be populated at a later date using the DBMS_MVIEW.REFRESH package described in Chapter 14, "Loading and Refreshing".

Build Method Description

BUILD DEFERRED

Create the materialized view definition but do not populate it with data.

BUILD IMMEDIATE

Create the materialized view and then populate it with data.

Build Method	Description
BUILD DEFERRED	Create the materialized view definition but do not populate it with data.
BUILD IMMEDIATE	Create the materialized view and then populate it with data.

Used for Query Rewrite

Even though a materialized view is defined, it will not automatically be used by the query rewrite facility. The clause ENABLE QUERY REWRITE must be specified if the materialized view is to be considered available for rewriting queries.

If this clause is omitted or specified as DISABLE QUERY REWRITE when the materialized view is initially created, the materialized view can subsequently be enabled for query rewrite with the ALTER MATERIALIZED VIEW statement.

If you define a materialized view as BUILD DEFERRED, it is also not eligible for query rewrite until it is populated with data.

Query Rewrite Restrictions

Query rewrite is not possible with all materialized views. If query rewrite is not occurring when expected, check to see if your materialized view satisfies all of the following conditions.

Materialized View Restrictions

There cannot be non-repeatable expressions (ROWNUM, SYSDATE, non-repeatable PL/SQL functions, and so on) anywhere in the defining query.
There cannot be references to RAW or LONG RAW datatypes or object REFs.
The query must be a single-block query, that is, it cannot contain set functions (UNION, MINUS, and so on). However, a materialized view can have multiple query blocks (for example, inline views in the FROM clause and subselects in the WHERE or HAVING clauses).
If the materialized view was registered as PREBUILT, the precision of the columns must agree with the precision of the corresponding SELECT expressions unless overridden by WITH REDUCED PRECISION.

Query Rewrite Restrictions

If a query has both local and remote tables, only local tables will be considered for potential rewrite.
None of the detail tables can be owned by SYS, and the materialized view cannot be owned by SYS.

Non-SQL Text Rewrite Restrictions

SELECT and GROUP BY lists, if present, must be the same in the query and the materialized view and must contain straight columns, that is, no expressions are allowed in the columns.
Aggregate operators must occur only as the outermost part of the expression; that is, aggregates such as AVG(AVG(x)) or AVG(x)+AVG(x) are not allowed.
The WHERE clause must contain only inner or outer equijoins, and they can be connected only by ANDs. No ORs and no selections on single tables are allowed in the WHERE clause.
HAVING or CONNECT BY clauses are not allowed.

Refresh Options

When you define a materialized view, you can specify its two refresh options: how to refresh and what type of refresh. If unspecified, the defaults are assumed as ON DEMAND and FORCE.

The two refresh execution modes are: ON COMMIT and ON DEMAND. The method you select will affect the type of materialized view that can be defined.

Refresh Mode Description

ON COMMIT

Refresh occurs automatically when a transaction that modified one of the materialized view's fact tables commits. Can be used with materialized views on single table aggregates and materialized views containing joins only.

ON DEMAND

Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package (REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT).

Refresh Mode	Description
ON COMMIT	Refresh occurs automatically when a transaction that modified one of the materialized view's fact tables commits. Can be used with materialized views on single table aggregates and materialized views containing joins only.
ON DEMAND	Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package (REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT).

If you think the materialized view did not refresh, check the alert log or trace file.

If a materialized view fails during refresh at COMMIT time, the user has to explicitly invoke the refresh procedure using the DBMS_MVIEW package after addressing the errors specified in the trace files. Until this is done, the view will no longer be refreshed automatically at commit time.

You can specify how you want your materialized views to be refreshed from the detail tables by selecting one of four options: FORCE, COMPLETE, FAST, and NEVER.

Refresh Option Description

COMPLETE

Refreshes by recalculating the materialized view's defining query.

FAST

Refreshes by incrementally adding the new data that has been inserted into the tables. The new data is obtained from the direct path log or from the materialized view logs.

FORCE

Applies fast refresh if possible; otherwise, it applies COMPLETE refresh.

NEVER

Indicates that the materialized view will not be refreshed with the Oracle refresh mechanisms.

Refresh Option	Description
COMPLETE	Refreshes by recalculating the materialized view's defining query.
FAST	Refreshes by incrementally adding the new data that has been inserted into the tables. The new data is obtained from the direct path log or from the materialized view logs.
FORCE	Applies fast refresh if possible; otherwise, it applies COMPLETE refresh.
NEVER	Indicates that the materialized view will not be refreshed with the Oracle refresh mechanisms.

Whether the fast refresh option is available will depend upon the type of materialized view. Fast refresh is available for three general classes of materialized views: materialized views with joins only, materialized views with joins and aggregates, and materialized views with aggregates on a single table.

General Restrictions on Fast Refresh

The materialized view's defining query is restricted as follows:

The FROM list must contain base tables only (that is, no views).
It cannot contain references to non-repeating expressions like SYSDATE and ROWNUM.
It cannot contain references to RAW or LONG RAW data types.
It cannot contain HAVING or CONNECT BY clauses.
The WHERE clause can contain only joins and they must be equi-joins (inner or outer) and all join predicates must be connected with ANDs. No selection predicates on individual tables are allowed.
It cannot have subqueries, inline views, or set functions like UNION or MINUS.

Restrictions on Fast Refresh on Materialized Views with Joins Only

Defining queries for materialized views with joins only and no aggregates have these restrictions on fast refresh:

All restrictions from "General Restrictions on Fast Refresh".
They cannot have GROUP BY clauses or aggregates.
If the WHERE clause of the query contains outer joins, then unique constraints must exist on the join columns of the inner join table.
Rowids of all the tables in the FROM list must appear in the SELECT list of the query.
Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.
Materialized views from this category are FAST refreshable after DML or Direct Load to the base tables.

Restrictions on Fast Refresh on Materialized Views with Single-Table Aggregates

Defining queries for materialized views with single-table aggregates have these restrictions on fast refresh:

All restrictions from "General Restrictions on Fast Refresh".
They can only have a single table.
The SELECT list must contain all GROUP BY columns.
Expressions are allowed in the GROUP BY and SELECT clauses provided they are the same.
They cannot have a WHERE clause.
COUNT(*) must be present.
They cannot have a MIN or MAX function.
For a materialized view with an aggregate with a single table, a materialized view log must exist on the table and must contain all columns referenced in the materialized view. The log must have been created with the INCLUDING NEW VALUES clause.
If AVG(expr) or SUM(expr) is specified, you must have COUNT(expr).
If VARIANCE(expr) or STDDEV(expr) is specified, you must have COUNT(expr) and SUM(expr).

Restrictions on Fast Refresh on Materialized Views with Joins and Aggregates:

Defining queries for materialized views with joins and aggregates have these restrictions on fast refresh:

All restrictions from "General Restrictions on Fast Refresh".
The WHERE clause can contain inner equi-joins only (that is, no outer joins)
Materialized views from this category are FAST refreshable after Direct Load to the base tables; they are not FAST refreshable after conventional DML to the base tables.
Materialized views from this category can have only the ON DEMAND option (that is, no ON COMMIT refresh option is allowed).

ORDER BY

An ORDER BY clause is allowed in the CREATE MATERIALIZED VIEW statement. It is only used during the initial creation of the materialized view. It is not used during a full refresh or an incremental refresh.

To improve the performance of queries against large materialized views, store the rows in the materialized view in the order specified in the ORDER BY clause. This initial ordering provides physical clustering of the data. If indexes are built on the columns by which the materialized view is ordered, accessing the rows of the materialized view using the index will significantly reduce the time for disk I/O due to the physical clustering.

The ORDER BY clause is not considered part of the materialized view definition. As a result, there is no difference in the manner in which Oracle detects the various types of materialized views (for example, materialized join views with no aggregates). For the same reason, query rewrite is not affected by the ORDER BY clause. This feature is similar to the CREATE TABLE ... ORDER BY ... capability that exists in Oracle. For example:

CREATE MATERIALIZED VIEW sum_sales 
  REFRESH FAST ON DEMAND AS
  SELECT cityid, COUNT(*) count_all, 
  SUM(sales) sum_sales, COUNT(sales) cnt_sales
  FROM city_sales
  ORDER BY cityid;

In the above example, we would use the "ORDER BY cityid" clause only during the creation of the materialized view. The materialized view definition is not affected by the ORDER BY clause. The definition is:

SELECT cityid, COUNT(*) count_all, 
SUM(sales) sum_sales, COUNT(sales) cnt_sales
FROM city_sales

Using Oracle Enterprise Manager

A materialized view can also be created using Oracle Enterprise Manager by selecting the materialized view object type. There is no difference in the information required if this approach is used. However, there are three property sheets which must be completed and you need to ensure that the option "Enable Query Rewrite" on the General sheet is selected.

Nested Materialized Views

A nested materialized view is a materialized view whose definition is based on another materialized view. A nested materialized view may reference other relations in the database in addition to materialized views.

Why Use Nested Materialized Views?

In a data warehouse, you typically create many aggregate views on a single join (for example, rollups along different dimensions). Incrementally maintaining these distinct materialized aggregate views can take a long time because the underlying join has to be performed many times. By using nested materialized views, the join is performed just once (while maintaining the materialized view containing joins only) and incremental maintenance of single-table aggregate materialized views is very fast due to the self-maintenance refresh operations on this class of views. Using nested materialized views also overcomes the limitation posed by materialized aggregate views, where incremental maintenance can only be done with direct-load insert.

Rules for Using Nested Materialized Views

You should keep a couple of points in mind when deciding whether to use nested materialized views.

If you do not need the FAST REFRESH clause, then you can define a nested materialized view.
Materialized views with joins only and single-table aggregate materialized views can be REFRESH FAST and nested if all the materialized views that they depend on are either materialized join views or single-table aggregate materialized views.

Restrictions when Using Nested Materialized Views

Only nested materialized join views and nested single-table aggregate materialized views can use incremental refresh. If you want complete refresh for all of your materialized views, then you can still nest these materialized views.

Materialized join views and single-table aggregate materialized views can be incrementally refreshed in the presence of arbitrary DML. In addition, the ON COMMIT refresh mode can be used with these types of materialized views. To maximize performance with materialized join views and single-table aggregate materialized views, you should first combine the two together. That is, define a single-table aggregate materialized view on top of a materialized join view. Such a composition yields a materialized aggregate view with respect to the base tables. Thus, logically:

single-table aggregate materialized view (materialized join view (<tables>))

is equivalent to:

materialized view with joins and aggregates(<tables>)

Figure 8-3 Nested Materialized View Equivalents

Figure 8-3 is just one of many possible ways to nest materialized views, but it is likely to be the most frequent and practical. Cyclic dependencies (a materialized view that indirectly references itself) are caught at creation time and an error is generated. Some restrictions are placed on the way you can nest materialized views. Oracle allows nesting a materialized view only when all the immediate dependencies of the materialized view do not have any dependencies amongst themselves. Thus, in the dependency tree, a materialized view can never be a parent as well as a grandparent of an object. For example, Figure 8-4, shows an impermissible materialized view because it is both a parent and grandparent of the same object.

Figure 8-4 Nested Materialized View Restriction

Limitations of Nested Materialized Views

Nested materialized views incur the space overhead of materializing the join and having a materialized view log. This is in contrast to materialized aggregate views where the space requirements of the materialized join view and its log are not demanding, but have relatively long refresh times due to multiple computations of the same join.

Nested materialized views are incrementally refreshable under any type of DML while materialized aggregate views are incrementally refreshable under direct-load insert only.

Example of a Nested Materialized View

You can create a materialized join view or a single-table aggregate materialized view on a single-table on top of another materialized join view, single-table aggregate materialized view, complex materialized view (a materialized view Oracle cannot perform incremental refresh on) or base table. All the underlying objects (be they materialized views or tables) on which the materialized view is defined on must have a materialized view log. All the underlying objects are treated as if they were tables. All the existing options for materialized join views and single-table aggregate materialized views can be used. Thus, ON COMMIT refresh is supported for these types of nested materialized views.

The following presents a retail database with an example schema and some materialized views to illustrate how nested materialized views can be created.

STORE   (store_key, store_name, store_city, store_state, store_country)  
PRODUCT (prod_key, prod_name, prod_brand)   
TIME    (time_key, time_day, time_week, time_month)
FACT    (store_key, prod_key, time_key, dollar_sales)
  
/* create the materialized view logs */
CREATE MATERIALIZED VIEW LOG ON fact 
  WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON store 
  WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON time 
  WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON product 
  WITH ROWID;

/*create materialized join view join_fact_store_time as incrementally 
refreshable at COMMIT time */
CREATE MATERIALIZED VIEW join_fact_store_time 
REFRESH FAST ON COMMIT AS
SELECT s.store_key, s.store_name, f.dollar_sales, t.time_key, t.time_day,  
       f.prod_key, f.rowid frid, t.rowid trid, s.rowid srid    
FROM fact f, store s, time t
WHERE f.time_key = t.time_key AND
      f.store_key = s.store_key;

To create a nested materialized view on the table join_fact_store_time, you would have to create a materialized view log on the table. Because this will be a single-table aggregate materialized view on join_fact_store_time, you need to log all the necessary columns and use the INCLUDING NEW VALUES clause.

/* create materialized view log on join_fact_store_time */
CREATE MATERIALIZED VIEW log on join_fact_store_time 
  WITH rowid (store_name, time_day, dollar_sales)
  INCLUDING new values;

/* create the single-table aggregate materialized view sum_sales_store_time on 
join_fact_store_time as incrementally refreshable at COMMIT time. */
CREATE MATERIALIZED VIEW sum_sales_store_time 
  REFRESH FAST ON COMMIT 
  AS
  SELECT COUNT(*) cnt_all, SUM(dollar_sales) sum_sales, COUNT(dollar_sales)
         cnt_sales, store_name, time_day
  FROM join_fact_store_time
  GROUP BY store_name, time_day;

Note that the above single-table aggregate materialized view sum_sales_store_time is logically equivalent to a multi-table aggregate on the tables fact, time, and store whose definition is

SELECT COUNT(*) cnt_all, SUM(f.dollar_sales) sum_sales, 
       COUNT(f.dollar_sales) cnt_sales, s.store_name, t.time_day
FROM fact f, time t , store s
WHERE f.time_key = t.time_key AND 
      f.store_key = s.store_key 
GROUP BY store_name, time_day;

You can now define a materialized join view join_fact_store_time_prod as a join between join_fact_store_time and table product.

CREATE MATERIALIZED VIEW join_fact_store_time_prod 
  REFRESH FAST ON COMMIT 
  AS
  SELECT j.rowid jrid, p.rowid prid, j.store_name, j.prod_key, j.prod_name,
         j.dollar_sales
  FROM join_fact_store_time j, product p 
  WHERE j.prod_key = p.prod_key;

The above schema can be diagrammatically represented as in Figure 8-5.

Figure 8-5 Nested Materialized View Schema

Nesting Materialized Views with Joins and Aggregates

Materialized views with joins and aggregates can be nested if they are refreshed as COMPLETE REFRESH. Thus, a user can arbitrarily nest materialized views having joins and aggregates. No incremental maintenance is possible for these materialized views.

Note that the ON COMMIT refresh option is not available for complex materialized views. Because you have to invoke the refresh functions manually, ordering has to be taken into account. This is because the refresh for a materialized view that is built on other materialized views will use the current state of the other materialized views, whether they are fresh or not. You can find the dependent materialized views for a particular object using the PL/SQL function GET_MV_DEPENDENCIES() in the DBMS_MVIEW package.

Nested Materialized View Usage Guidelines

Here are some guidelines on how to use nested materialized views:

If incremental refresh is desired when a materialized view contains joins and aggregates and standard fast refresh cannot be used because DML is occurring on the tables, consider creating a single-table aggregate materialized view nested on a materialized join view.
If incremental refresh is desired, you should incrementally refresh all the materialized views along any chain. It makes little sense to define an incrementally refreshable materialized view on top of a materialized view that must be refreshed with a complete refresh.
When using materialized join views and single-table aggregate materialized views, you can define them to be ON COMMIT or ON DEMAND. The choice would depend on the application using the materialized views. If one expects the materialized views to always remain fresh, then all the materialized views should have the ON COMMIT refresh option. If the time window for refresh does not permit refreshing all the materialized views at commit time, then the appropriate materialized views could be created with (or altered to have) the ON DEMAND refresh option.

Registration of an Existing Materialized View

Some data warehouses have implemented materialized views in ordinary user tables. Although this solution provides the performance benefits of materialized views, it does not:

provide query rewrite to all SQL applications
enable materialized views defined in one application to be transparently accessed in another application
generally support fast parallel or fast incremental materialized view refresh

Because of these problems, and because existing materialized views may be extremely large and expensive to rebuild, you should register your existing materialized view tables with Oracle whenever possible. You can register a user-defined materialized view with the CREATE MATERIALIZED VIEW ... ON PREBUILT TABLE statement. Once registered, the materialized view can be used for query rewrites or maintained by one of the refresh methods, or both.

In some cases, user-defined materialized views are refreshed on a schedule that is longer than the update cycle; for example, a monthly materialized view may be updated only at the end of each month, and the materialized view values always refer to complete time periods. Reports written directly against these materialized views implicitly select only data that is not in the current (incomplete) time period. If a user-defined materialized view already contains a time dimension:

It should be registered and then incrementally refreshed each update cycle.
A view should be created that selects the complete time period of interest.

For example, if a materialized view was formerly refreshed monthly at the end of each month, then the view would contain the selection WHERE time.month < CURRENT_MONTH().
The reports should be modified to refer to the view instead of referring directly to the user-defined materialized view.

If the user-defined materialized view does not contain a time dimension, then:

A new materialized view should be created that does include the time dimension (if possible).
The view should aggregate over the time column in the new materialized view.

The contents of the table must reflect the materialization of the defining query at the time you register it as a materialized view, and each column in the defining query must correspond to a column in the table that has a matching datatype. However, you can specify WITH REDUCED PRECISION to allow the precision of columns in the defining query to be different from that of the table columns.

The table and the materialized view must have the same name, but the table retains its identity as a table and can contain columns that are not referenced in the defining query of the materialized view. These extra columns are known as unmanaged columns. If rows are inserted during a refresh operation, each unmanaged column of the row is set to its default value. Therefore, the unmanaged columns cannot have NOT NULL constraints unless they also have default values.

Unmanaged columns are not supported by single-table aggregate materialized views or materialized views containing joins only.

Materialized views based on prebuilt tables are eligible for selection by query rewrite provided the parameter QUERY_REWRITE_INTEGRITY is set to at least the level of TRUSTED. See Chapter 19, "Query Rewrite", for details about integrity levels.

When you drop a materialized view that was created on a prebuilt table, the table still exists--only the materialized view is dropped.

When a prebuilt table is registered as a materialized view and query rewrite is desired, the parameter QUERY_REWRITE_INTEGRITY must be set to at least STALE_TOLERATED because, when it is created, the materialized view is marked as unknown. Therefore, only stale integrity modes can be used.

CREATE TABLE sum_sales_tab
  PCTFREE 0  TABLESPACE mviews
   STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0)
    AS
    SELECT f.store_key
       SUM(dollar_sales) AS dollar_sales,
       SUM(unit_sales) AS unit_sales,
       SUM(dollar_cost) AS dollar_cost
         FROM fact f GROUP BY f.store_key;

CREATE MATERIALIZED VIEW sum_sales_tab
ON PREBUILT TABLE WITHOUT REDUCED PRECISION
ENABLE QUERY REWRITE
AS
SELECT f.store_key,
  SUM(dollar_sales) AS dollar_sales,
  SUM(unit_sales) AS unit_sales,
  SUM(dollar_cost) AS dollar_cost
  FROM fact f GROUP BY f.store_key;

This example illustrates the two steps required to register a user-defined table. First, the table is created, then the materialized view is defined using exactly the same name as the table. This materialized view sum_sales_tab is eligible for use in query rewrite.

Partitioning a Materialized View

Because of the large volume of data held in a data warehouse, partitioning is an extremely useful option that can be used by the database designer.

Partitioning the fact tables improves scalability, simplifies system administration, and makes it possible to define local indexes that can be efficiently rebuilt. See Chapter 5, "Parallelism and Partitioning", for further details about partitioning.

Partitioning a materialized view also has benefits for refresh, since the refresh procedure can use parallel DML to maintain the materialized view. To realize these benefits, the materialized view has to be defined as PARALLEL and parallel DML must be enabled in the session.

When the data warehouse or data mart contains a time dimension, it is often desirable to archive the oldest information, and then reuse the storage for new information, the rolling window scenario. If the fact tables or materialized views include a time dimension and are horizontally partitioned by the time attribute, then management of rolling materialized views can be reduced to a few fast partition maintenance operations provided that the unit of data that is rolled out equals, or is at least aligned with, the range partitions.

If you plan to have rolling materialized views in your warehouse, then you should determine how frequently you plan to perform partition maintenance operations, and you should plan to partition fact tables and materialized views to reduce the amount of system administration overhead required when old data is aged out.

With the introduction of new partitioning options in Oracle8i, you are not restricted to using range partitions. For example, a composite partition using both a time value and, say, a key value could result in an ideal partition solution for your data.

An ideal case for using partitions is when a materialized view contains a subset of the data. For example, this can be achieved by defining an expression of the form WHERE time_key < '1-OCT-1998' in the SELECT expression for the materialized view. However, if a WHERE clause of this type is included, then query rewrite will be restricted to the exact match case, which severely restricts when the materialized view is used. To overcome this problem, use a partitioned materialized view with no WHERE clause and then query rewrite will be able to use the materialized view and it will only search the appropriate partition, thus improving query performance.

There are two approaches to partitioning a materialized view:

Partitioning the Materialized View

Partitioning a materialized view involves defining the materialized view with the standard Oracle partitioning clauses as illustrated in the example below. This example creates a materialized view called part_sales_mv which uses three partitions, is, by default, fast refreshed, and is eligible for query rewrite.

CREATE MATERIALIZED VIEW part_sales_mv
  PARALLEL
  PARTITION by RANGE (time_key)
  (
    PARTITION time_key
      VALUES LESS THAN (TO_DATE('31-12-1997', 'DD-MM-YYYY'))
      PCTFREE 0 PCTUSED
      STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
      TABLESPACE sf1,
   PARTITION month2
     VALUES LESS THAN (TO_DATE('31-01-1998', 'DD-MM-YYYY'))
     PCTFREE 0 PCTUSED
     STORAGE INITIAL 64k NEXT 16k PCTINCREASE 0)
      TABLESPACE sf2,
 PARTITION month3
     VALUES LESS THAN (TO_DATE('31-01-1998', 'DD-MM-YYYY'))
     PCTFREE 0 PCTUSED
     STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
      TABLESPACE sf3)
BUILD DEFERRED
REFRESH FAST
ENABLE QUERY REWRITE
AS
SELECT f.store_key, f.time_key,
  SUM(f.dollar_sales) AS sum_dol_sales,
       SUM(f.unit_sales) AS sum_unit_sales
         FROM fact f GROUP BY f.time_key, f.store_key;

Partitioning a Prebuilt Table

Alternatively, a materialized view can be registered to a partitioned prebuilt table as illustrated in the following example:

CREATE TABLE part_fact_tab(
       time_key, store_key, sum_dollar_sales,
         sum_unit_sale)
  PARALLEL
  PARTITION by RANGE (time_key)
  (
    PARTITION month1
      VALUES LESS THAN (TO_DATE('31-12-1997', 'DD-MM-YYYY'))
      PCTFREE 0 PCTUSED 99
      STORAGE (INITITAL 64k NEXT 16k PCTINCREASE 0)
      TABLESPACE sf1,
   PARTITIION month2
     VALUES LESS THAN (TO_DATE('31-01-1998', 'DD-MM-YYYY'))
     PCTFREE 0 PCTUSED 99
     STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
      TABLESPACE sf2,
 PARTITION month3
     VALUES LESS THAN (TO_DATE('31-01-1998', DD-MM-YYYY'))
     PCTFREE 0 PCTUSED 99
     STORAGE INITIAL 64k NEXT 16k PCTINCREASE 0)
     TABLESPACE sf3)
AS
SELECT f.time_key, f.store_key,
  SUM(f.dollar_sales) AS sum_dollar_sales, 
  SUM(f.unit_sales)   AS sum_unit_sales
         FROM fact f GROUP BY f.time_key, f.store_key;

CREATE MATERIALIZED VIEW part_fact_tab
ON PREBUILT TABLE
ENABLE QUERY REWRITE
AS 
SELECT f.time_key,  f.store_key, 
  SUM(f.dollar_sales) AS sum_dollar_sales, 
  SUM(f.unit_sales)   AS sum_unit_sales 
        FROM fact f  GROUP BY  f.time_key , f.store_key;

In this example, the table part_fact_tab has been partitioned over three months and then the materialized view was registered to use the prebuilt table. This materialized view is eligible for query rewrite because the ENABLE QUERY REWRITE clause has been included.

Indexing Selection for Materialized Views

The two main operations on a materialized view are query execution and incremental refresh, and each operation has different performance requirements. Query execution may need to access any subset of the materialized view key columns, and may need to join and aggregate over a subset of those columns. Consequently, query execution usually performs best if there is a single-column bitmap index defined on each materialized view key column.

In the case of materialized views containing only joins using the fast refresh option, it is highly recommended that indexes be created on the columns that contain the rowids to improve the performance of the refresh operation.

If a materialized view using joins and aggregates is fast refreshable, then an index is automatically created and cannot be disabled.

See Chapter 18, "Tuning Parallel Execution", for further details.

Invalidating a Materialized View

Dependencies related to materialized views are automatically maintained to ensure correct operation. When a materialized view is created, the materialized view depends on the detail tables referenced in its definition. Any DDL operation, such as a DROP or ALTER, on any dependency in the materialized view will cause it to become invalid.

A materialized view is automatically revalidated when it is referenced. In many cases, the materialized view will be successfully and transparently revalidated. However, if a column has been dropped in a table referenced by a materialized view or the owner of the materialized view did not have one of the query rewrite privileges and that has now been granted to the owner, the command:

ALTER MATERIALIZED VIEW  mview_name ENABLE QUERY REWRITE

should be used to revalidate the materialized view. If there are any problems, an error will be returned.

The state of a materialized view can be checked by querying the tables USER_MVIEWS or ALL_MVIEWS. The column STALENESS will show one of the values FRESH, STALE, UNUSABLE, UNKNOWN, or UNDEFINED to indicate whether the materialized view can be used.

Security Issues

To create a materialized view, the privilege CREATE MATERIALIZED VIEW is required, and to create a materialized view that references a table in another schema, you must have SELECT privileges on that table. Moreover, if you enable query rewrite, you must have the QUERY REWRITE or GLOBAL QUERY REWRITE privilege to reference tables in your own schema. To enable query rewrite on a materialized view that references tables outside your schema, you must have the GLOBAL QUERY REWRITE privilege.

If you continue to get a privilege error while trying to create a materialized view and you believe that all the required privileges have been granted, then the problem is most likely due to a privilege not being granted explicitly and trying to inherit the privilege from a role instead. The owner of the materialized view must have explicitly been granted SELECT access to the referenced tables if they are in a different schema.

Guidelines for Using Materialized Views in a Data Warehouse

Determining what materialized views would be most beneficial for performance gains is aided by the analysis tools of the DBMS_OLAP package. Specifically, you can call the DBMS_OLAP.RECOMMEND_MV procedure to see a list of materialized views that Oracle recommends based on the statistics and the usage of the target database. See Chapter 15, "Summary Advisor", for further details.

If you are going to write your own materialized views without the aid of Oracle analysis tools, then use these guidelines to achieve maximum performance:

Instead of defining multiple materialized views on the same tables with the same GROUP BY columns but with different measures, define a single materialized view including all of the different measures.
If your materialized view includes the aggregated measure AVG(x), also include COUNT(x) to support incremental refresh. Similarly, if VARIANCE(x) or STDDEV(x) is present, then always include COUNT(x) and SUM(x) to support incremental refresh.

Altering a Materialized View

There are five amendments that can be made to a materialized view:

change its refresh option (FAST/FORCE/COMPLETE/NEVER)
change its refresh mode (ON COMMIT/ ON DEMAND)
recompile
enable/disable its use for query rewrite
consider fresh

All other changes are achieved by dropping and then recreating the materialized view.

The COMPILE clause of the ALTER MATERIALIZED VIEW statement can be used when the materialized view has been invalidated as described in "Invalidating a Materialized View". This compile process is quick, and allows the materialized view to be used by query rewrite again.

For further information about ALTER MATERIALIZED VIEW, see Oracle8i SQL Reference.

Dropping a Materialized View

Use the DROP MATERIALIZED VIEW statement to drop a materialized view. For example:

DROP MATERIALIZED VIEW sales_sum_mv;

This command drops the materialized view sales_sum_mv. If the materialized view was prebuilt on a table, then the table is not dropped but it can no longer be maintained with the refresh mechanism. Alternatively, you can drop a materialized view using Oracle Enterprise Manager.

Overview of Materialized View Management Tasks

The motivation for using materialized views is to improve performance, but the overhead associated with materialized view management can become a significant system management problem. Materialized view management activities include:

Identifying what materialized views to create initially
Indexing the materialized views
Ensuring that all materialized views and materialized view indexes are refreshed properly each time the database is updated
Checking which materialized views have been used
Determining how effective each materialized view has been on workload performance
Measuring the space being used by materialized views
Determining which new materialized views should be created
Determining which existing materialized views should be dropped
Archiving old detail and materialized view data that is no longer useful

After the initial effort of creating and populating the data warehouse or data mart, the major administration overhead is the update process, which involves the periodic extraction of incremental changes from the operational systems; transforming the data; verification that the incremental changes are correct, consistent, and complete; bulk-loading the data into the warehouse; and refreshing indexes and materialized views so that they are consistent with the detail data.

The update process must generally be performed within a limited period of time known as the update window. The update window depends on the update frequency (such as daily or weekly) and the nature of the business. For a daily update frequency, an update window of two to six hours might be typical.

The update window usually displays the time for the following activities:

Loading the detail data.
Updating or rebuilding the indexes on the detail data.
Performing quality assurance tests on the data.
Refreshing the materialized views.
Updating the indexes on the materialized views.

A popular and efficient way to load data into a warehouse or data mart is to use SQL*Loader with the DIRECT or PARALLEL option or to use another loader tool that uses the Oracle direct path API.

See Also:
See Oracle8i Utilities for the restrictions and considerations when using SQL*Loader with the DIRECT or PARALLEL keywords.

Loading strategies can be classified as one-phase or two-phase. In one-phase loading, data is loaded directly into the target table, quality assurance tests are performed, and errors are resolved by performing DML operations prior to refreshing materialized views. If a large number of deletions are possible, then storage utilization may be adversely affected, but temporary space requirements and load time are minimized. The DML that may be required after one-phase loading causes multi-table aggregate materialized views to become unusable in the safest rewrite integrity level.

In a two-phase loading process:

Data is first loaded into a temporary table in the warehouse.
Quality assurance procedures are applied to the data.
Referential integrity constraints on the target table are disabled, and the local index in the target partition is marked unusable.
The data is copied from the temporary area into the appropriate partition of the target table using INSERT AS SELECT with the PARALLEL or APPEND hint.
The temporary table is dropped.
The constraints are enabled, usually with the NOVALIDATE option.

Immediately after loading the detail data and updating the indexes on the detail data, the database can be opened for operation, if desired. Query rewrite can be disabled by default (with ALTER SYSTEM SET QUERY_REWRITE_ENABLED = FALSE) until all the materialized views are refreshed, but enabled at the session level for any users who do not require the materialized views to reflect the data from the latest load (with ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE). However, as long as QUERY_REWRITE_INTEGRITY = ENFORCED or TRUSTED, this is not necessary because the system ensures that only materialized views with updated data participate in a query rewrite.

Guideline 1:	Your dimensions should either be denormalized (each dimension contained in one table) or the joins between tables in a normalized or partially normalized dimension should guarantee that each child-side row joins with exactly one parent-side row. The benefits of maintaining this condition are described in "Creating a Dimension".
	If desired, this condition can be enforced by adding FOREIGN KEY and NOT NULL constraints on the child-side join key(s) and PRIMARY KEY constraints on the parent-side join key(s). If your materialized view contains only a single detail table, or if it performs no aggregation, a preferred alternative would be to use queries containing outer joins in place of inner joins. In this case, the Oracle optimizer can guarantee the integrity of the result without enforced referential integrity constraints.
Guideline 2:	If dimensions are denormalized or partially denormalized, hierarchical integrity must be maintained between the key columns of the dimension table. Each child key value must uniquely identify its parent key value, even if the dimension table is denormalized. Hierarchical integrity in a denormalized dimension can be verified by calling the VALIDATE_DIMENSION procedure of the DBMS_OLAP package.
Guideline 3:	Fact tables and dimension tables should similarly guarantee that each fact table row joins with exactly one dimension table row. This condition must be declared, and optionally enforced, by adding FOREIGN KEY and NOT NULL constraints on the fact key column(s) and PRIMARY KEY constraints on the dimension key column(s), or by using outer joins as described in Guideline 1. In a data warehouse, constraints are typically enabled with the NOVALIDATE and RELY options to avoid constraint enforcement performance overhead.
Guideline 4:	Incremental loads of your detail data should be done using the SQLLoader direct-path option, or any bulk loader utility that uses Oracle's direct path interface (including INSERT AS SELECT with the APPEND or PARALLEL hints). Fast refresh after conventional DML is not supported with views with aggregates and joins, but it is for single-table aggregate views. See Oracle8i SQL Reference* for further details.
Guideline 5:	Range-partition your tables by a monotonically increasing time column if possible (preferably of type DATE). Different materialized views have different requirements for speeding up refresh.
Guideline 6:	After each load and before refreshing your materialized view, use the VALIDATE_DIMENSION procedure of the DBMS_OLAP package to incrementally verify dimensional integrity.
Guideline 7:	Horizontally partition and index the materialized view as you have the fact tables. Include a local concatenated index on all the materialized view keys.

8 Materialized Views

Overview of Data Warehousing with Materialized Views

Materialized Views for Data Warehouses

Materialized Views for Distributed Computing

Materialized Views for Mobile Computing

The Need for Materialized Views

Figure 8-1 Transparent Query Rewrite

Components of Summary Management

Figure 8-2 Overview of Summary Management

Terminology

Schema Design Guidelines for Materialized Views

Types of Materialized Views

Materialized Views with Joins and Aggregates

Single-Table Aggregate Materialized Views

Table 8-1 Single-Table Aggregate Requirements

Materialized Views Containing Only Joins

Creating a Materialized View

Naming

Storage Characteristics

Build Methods

Used for Query Rewrite

Query Rewrite Restrictions

Materialized View Restrictions

Query Rewrite Restrictions

Non-SQL Text Rewrite Restrictions

Refresh Options

General Restrictions on Fast Refresh

Restrictions on Fast Refresh on Materialized Views with Joins Only

Restrictions on Fast Refresh on Materialized Views with Single-Table Aggregates

Restrictions on Fast Refresh on Materialized Views with Joins and Aggregates:

ORDER BY

Using Oracle Enterprise Manager

Nested Materialized Views

Why Use Nested Materialized Views?

Rules for Using Nested Materialized Views

Restrictions when Using Nested Materialized Views

Figure 8-3 Nested Materialized View Equivalents

Figure 8-4 Nested Materialized View Restriction

Limitations of Nested Materialized Views

Example of a Nested Materialized View

Figure 8-5 Nested Materialized View Schema

Nesting Materialized Views with Joins and Aggregates

Nested Materialized View Usage Guidelines

Registration of an Existing Materialized View

Partitioning a Materialized View

Partitioning the Materialized View

Partitioning a Prebuilt Table

Indexing Selection for Materialized Views

Invalidating a Materialized View

Security Issues

Guidelines for Using Materialized Views in a Data Warehouse

Altering a Materialized View

Dropping a Materialized View

Overview of Materialized View Management Tasks

8
Materialized Views