29
Materialized Views

The materialized views introduced in Oracle8i are generic objects that are used to summarize, precompute, replicate, and distribute data. They are suitable in various computing environments such as data warehousing, decision support, and distributed, or mobile computing.

Several new functional areas have been developed to offer comprehensive and robust support for the management and use of materialized views in different computing environments. The new functionality includes transparent query rewrite, object dependency management, staleness tracking of materialized data, new refresh methods such as transactionally consistent refresh on commit, and highly efficient incremental fast refresh using direct path and DML logs.

This chapter contains:

The Need for Materialized Views

Materialized views are used in 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 it 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 command. 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 these results in the database. The query optimizer can make use of 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 or views. Rewriting queries to use materialized views rather than detail relations results in a significant performance gain.

Figure 29-1 Transparent Query Rewrite

Thus, when using query rewrite, you want to create materialized views that satisfy the largest number of queries. For example, if you identify twenty 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. In case 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 rewrites.

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 rewrites, 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 and, depending on the types of refresh that are 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.

Creating a Materialized View

To create a materialized view, use the CREATE MATERIALIZED VIEW command. 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 DEFERRED
  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). The materialized view should be horizontally 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 horizontal partitions because it can take advantage of the parallel 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 option in the CREATE MATERIALIZED VIEW statement. Otherwise, use the BUILD IMMEDIATE option to populate the materialized view immediately, or the BUILD DEFERRED option 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, and DROP MATERIALIZED VIEW.

Naming

The name given to 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 "Summary Advisor", can provide an estimate on the bytes required to store this materialized view. This information can then assist the design team in determining into which tablespace 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 table below. 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 as per 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 "Warehouse Refresh".

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

When a materialized view is defined, it will not automatically be used by the query rewrite facility. Therefore, 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.

Query Rewrite Restrictions

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

Materialized View Restrictions

There cannot be non-repeatable expressions anywhere in the defining query (ROWNUM, SYSDATE, non-repeatable PL/SQL functions, and so on).
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 (e.g., 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 columns must agree with the precision of the corresponding SELECT expressions unless overridden by WITH REDUCED PRECISION.

Query Rewrite Restrictions

Detail tables must be local--only local detail tables or views can be accessed in the query or used in the definition of the materialized view.
None of the detail tables can be owned by SYS, and the materialized view cannot be owned by SYS.

Non-SQL Text Rewrite Restrictions

The FROM list cannot contain multiple occurrences of the same table or view.
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, which can be connected by ANDs. That is, no ORs and no selections on single tables are allowed in the WHERE clause.
HAVING or CONNECT BY clauses are not allowed.

Refresh Options

If you are going to refresh your materialized views from the detail or fact tables, then you must add a REFRESH clause to the CREATE MATERIALIZED VIEW statement. When defining the refresh clause, two elements need to be specified: what type of refresh should occur and how to execute the refresh.

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

Refresh Mode Description

ON COMMIT

Refresh occurs automatically on the next COMMIT performed at the master table. 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 on the next `COMMIT` performed at the master table. 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 the materialized view is being refreshed using the ON COMMIT method, then, following refresh operations, the alert log and trace file should be consulted to check that no errors have occurred.

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.

Selecting the ON DEMAND execution mode means that you can take advantage of the materialized view warehouse refresh facility, which provides a quick and efficient mechanism for refreshing your materialized views, either in their entirety or only with the additions to the detail data.

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 when ATOMIC REFRESH=TRUE and COMPLETE is the same as FORCE if ATOMIC REFRESH=FALSE.

FAST

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

FORCE

First determines if fast refresh is possible and applies it if it is; otherwise, it applies COMPLETE refresh.

NEVER

Suppresses refresh of the materialized view.

Refresh Option	Description
`COMPLETE`	Refreshes by recalculating the materialized view's defining query when ATOMIC REFRESH=TRUE and COMPLETE is the same as FORCE if ATOMIC REFRESH=FALSE.
`FAST`	Refreshes by incrementally adding the new data that has been inserted into the tables using direct path or from the materialized view log.
`FORCE`	First determines if fast refresh is possible and applies it if it is; otherwise, it applies `COMPLETE` refresh.
`NEVER`	Suppresses refresh of the materialized view.

Whether the fast refresh option is available will depend upon the type of materialized view that has been created. The table below summarizes under what conditions fast refresh is possible for the different types of materialized views. Creation of the materialized view will fail and an error will be reported if these conditions are not met.

Table 29-1 Requirements for Fast Refresh of Materialized Views

	When the Materialized View has:
	Only Joins	Joins and Aggregates	Aggregate on a Single Table
Detail tables only	X	X	X
Single table only	-	-	X
Table Appears only once in the FROM list	X	X	X
No non-repeating expressions like SYSDATE and ROWNUM	X	X	X
No references to RAW or LONG RAW	X	X	X
No GROUP BY	X	-	-
Rowids of all the detail tables must appear in the SELECT list of the query	X	-	-
Expressions are allowed in the GROUP BY and SELECT clauses provided they are the same	-	X	X
Aggregates allowed but cannot be nested	-	X	X
AVG with COUNT	-	X	X
SUM with COUNT	-	-	X
VARIANCE with COUNT and SUM	-	X	X
STDDEV with COUNT and SUM	-	X	X
WHERE clause contains join predicates which can be ANDed bit not ORed.	X	X	-
No WHERE clause	-	-	X
No HAVING or CONNECT BY	X	X	X
No subqueries, inline views, or set functions like UNION or MINUS	X	X	X
COUNT(*) must be present	-	-	X
No MIN and MAX allowed	-	-	X
If outer joins, then unique constraints must exist on the join columns of the inner join table	X	-	-
Materialized View logs must exist and contain all columns referenced in the materialized view and have been created with the LOG NEW VALUES clause	-	-	X
Materialized view logs must exist with rowids of all the detail tables	X	-	-
Non-aggregate expression in SELECT and GROUP BY must be straight columns	-	-	X
DML to detail table	X	-	X
Direct path data load	X	X	X
ON COMMIT	X	-	X
ON DEMAND	X	X	X

Defining the Data for the Materialized View

The SELECT clause in the materialized view defines the data that it is to contain and there are only a few restrictions on what may be specified. Any number of tables may be joined together, however, they cannot be remote tables if you wish to take advantage of query rewrite or the warehouse refresh facility. It is not only tables that may be joined or referenced in the SELECT clause, because views, inline views, subqueries and materialized views are all permissible.

Materialized Views with Joins and Aggregates

In data warehouses, materialized views would normally contain one of the aggregates shown in the table below. To get warehouse incremental refresh, 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.

Here are some examples of the type of materialized view which 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;

The statement above 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. 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_avgcnt_mv
  PCTFREE 0 TABLESPACE mviews
  STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0)
  BUILD IMMEDIATE
  REFRESH COMPLETE
  ENABLE QUERY REWRITE
  AS
  SELECT store_name,
     AVG(unit_sales) AS avgcnt_unit_sales,
     COUNT(DISTINCT(f.time_key)) AS count_days
      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_avgcnt_mv that computes the average 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. Note that the ON DEMAND clause has been omitted from this materialized view definition because it is optional; because it is the default, the materialized view will not be refreshed until a manual request is made.

Create Materialized View: Example 3

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 column 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 refresh method is FAST, which is allowed because the COUNT and SUM aggregates have been included to support fast refresh of the STDDEV aggregate.

Create Materialized View: Example 4

CREATE MATERIALIZED VIEW store_var_mv 
 PCTFREE 0  TABLESPACE mviews
   STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0)
  PARALLEL
  BUILD DEFERRED
  REFRESH FORCE 
    AS
    SELECT s.store_key, store_name,
           VARIANCE(unit_sales) AS var_unit_sales 
     FROM fact f, store s, time t
     WHERE s.store_key = f.store_key  AND
           f.time_key = t.time_key 
        GROUP BY s.store_key, t.time_key, store_name;

The statement above creates a materialized view store_stdcnt_mv that computes the variance 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 not populated with data immediately and the materialized view is not available for use by query rewrite because the ENABLE QUERY REWRITE clause has not been specified. The refresh method is FORCE, which means that the most suitable refresh method will be selected.

Single Table Aggregate Materialized Views

A materialized view which contains one or more aggregates (SUM, AVG, VARIANCE, STDDEV, COUNT) and a GROUP BY 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 which includes the INCLUDING NEW VALUES option, and contains all columns referenced in the materialized view query definition.

In this release, it is assumed that the materialized view and all the base tables the materialized view is dependent upon must belong to the same schema.

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. Whenever DML is applied against the fact table, when the commit is issued, the changes will be reflected in the materialized view.

Table 29-2 illustrates the aggregate requirements for a single table aggregate materialized view.

Table 29-2 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)`
`VAR(expr)`	`COUNT(expr)`	`SUM(expr * expr)`

Note that COUNT(*) must always be present.

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 to the store table. The advantage of creating this type of materialized view is that expensive joins have been precalculated.

If you specify REFRESH FAST, Oracle performs further verification of the query definition to ensure that fast refresh can always 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 be 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 must be created as REFRESH FORCE. 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, but only for the other tables for which all the criteria are met.

In this release, it is assumed that the materialized view and all the base tables the materialized view is dependent upon must belong to the same schema.

In a data warehouse star schema, if space is at a premium, you can include the rowid of the fact table only as 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.

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 detail table.

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.

To speed up refresh, it is recommended that the user create indices 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 the example shown above, 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 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 if the fact table changed but not if the tables time or store changed.

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(+);

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, does not enable materialized views defined in one application to be transparently accessed in another application, and does not 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 the Oracle server 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 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 (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 31, "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, the parameter QUERY_REWRITE_INTEGRITY must be set to at least STALE_TOLERATED because, when it is created, the materialized view is marked as stale, 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 must be 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

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

Horizontally partitioning the fact tables by a time attribute improves scalability, simplifies system administration, and makes it possible to define local indexes that can be efficiently rebuilt. SQL*Loader can be directed to load a single partition of a table. In this case, only the corresponding local index partitions are rebuilt. Global indexes must be fully rebuilt after a direct load, which can be very costly when loading a relatively small number of rows into a large table. For this reason, it is strongly recommended that all fact table indexes should be defined as local indexes. For example, this can be accomplished by having a bitmap index on each key column (bitmap indexes are always local) and a single multikey index that includes all the key columns, with the partitioning attribute as the leading column of the multikey index.

Partitioning a materialized view also has benefits as far as refresh is concerned, 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. 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 horizontal 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 horizontally 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 store_key value could result in an ideal partition solution for your data.

For further details about partitioning, see Oracle8i Concepts.

An ideal case for using partitions is when a materialized view contains a subset of the data, which is obtained by defining an expression of the form WHERE time_key < '1-OCT-1998' in the SELECT expression for the materialized view. 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 the Prebuilt Table

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 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 below.

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 fast incremental refresh, and each operation has different performance requirements. Fast incremental refresh needs to perform an exact match on the materialized view keys, and performs best when there is a concatenated index that includes all of the materialized view keys. Query execution, on the other hand, 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.

One option for indexing the materialized view is to define a unique, local index that contains all of the materialized view keys, and a single-column bitmap index on each materialized view key, if storage space and refresh time permit.

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

Invalidating a Materialized View

Dependencies related to materialized views are automatically maintained to ensure correct operation. At DDL time, a materialized view depends on the detail tables referenced in its definition.

A shared cursor depends on all objects referenced in the cursor. If a cursor is rewritten, the cursor depends on the materialized view selected by query rewrite and the dimensions of the tables of the cursor if they are being used by query rewrite. Any operation that would invalidate these dimensions or the materialized view would invalidate the cursor.

Therefore, any DDL operation, such as a DROP or ALTER, on any dependency in the materialized view will cause it to become invalid.

The state of a materialized view can be checked by querying the table USER_MVIEW_ANALYSIS or ALL_MVIEW_ANALYSIS. The column UNUSABLE takes a value of Y or N and advises whether the materialized view may be used. The column KNOWN_STALE also takes a value of Y or N and advises whether a materialized view is known to be stale and finally column INVALID will be set to Y if the materialized view is invalid and N if it is not.

A materialized view is automatically revalidated whenever it is referenced. However, if a column has been dropped in a table referenced by a materialized view or the owner of the materialized view didn't have one of the query rewrite privileges and that has now been granted to them, the command

ALTER MATERIALIZED VIEW  mview_name ENABLE QUERY REWRITE

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

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, the privilege CREATE ANY MATERIALIZED VIEW is needed.

If the materialized view is to be used by query rewrite, then the privilege QUERY REWRITE should be granted, or if the materialized view references tables not in your schema, then GLOBAL QUERY REWRITE must be granted.

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 it has been inherited from a role instead. The owner of the materialized view must have explicitly been granted SELECT access to the referenced tables.

Guidelines for using Materialized Views in a Data Warehouse

Determining what materialized views would be most beneficial, in terms of 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. Note that this package currently only recommends materialized views having aggregates on multiple tables.

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 only three amendments that can be made to a materialized view:

change its refresh method
recompile
enable/disable its use for query rewrite

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

The COMPILE option 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, which means that the materialized view can be used by query rewrite.

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

Dropping a Materialized View

Use the DROP MATERIALIZED VIEW command 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.

29 Materialized Views

The Need for Materialized Views

Figure 29-1 Transparent Query Rewrite

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

Table 29-1 Requirements for Fast Refresh of Materialized Views

Defining the Data for the Materialized View

Materialized Views with Joins and Aggregates

Single Table Aggregate Materialized Views

Table 29-2 Single Table Aggregate Requirements

Materialized Views Containing Only Joins

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

29
Materialized Views