Skip Headers

Oracle® ; Database Data Warehousing Guide
10g Release 1 (10.1)
Part Number B10736-01
Go to Documentation Home
Home		 Go to Boo k List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Feedback
Go to previous page
Previous		 Go to next page
Next
 View PDF

8 Basic Materialized Views

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

Overview of Data Warehousing with Mat erialized Views

Typically, data flows from one or more online transaction processing (OLTP) database into a data warehouse on a monthl y, weekly, or daily basis. The data is normally processed in a staging file before being added to the data warehouse. Data warehouses commonly range in size from tens of gigab ytes to a few terabytes. Usually, the vast majority of the data is stored in a few very large fact tables.

One technique emplo yed in data warehouses to improve performance is the creation of summaries. Summaries are special types of aggregate views that impro ve 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, you can create a table to contain the sums of sales by region and by product.

The summari es or aggregates that are referred to in this book and in literature on data warehousing are created in Oracle Database using a schem a object called a materialized view. Mater ialized views can perform a number of roles, such as improving query performance or providing replicated data.

In the past, or ganizations using summaries spent a significant amount of time and effort creating summaries manually, identifying which summaries to create, indexing the summaries, updating them, and advising their users on which ones to use. The introduction of summary management eased the workload of the database administrator and meant the user no longer needed to be aware of the summaries that had been defi ned. The database administrator creates one or more materialized views, which are the equivalent of a summary. The end user queries t he tables and views at the detail data level. The query rewrite mechanism in the Oracle server automatically rewrites the SQL query t o use the summary tables. This mechanism reduces response time for returning results from the query. Materialized views within the da ta warehouse are transparent to the end user or to the database application.

Although materialized views are usually accessed through the query rewrite mechanism, an end user or database application can construct queries that directly access the materialized views. However, serious consideration should be given to whether users should be allowed to do this because any change to the materia lized views will affect the queries that reference them.

Materialized Views for Data Warehouses

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

Materialized Views for Distributed Computing

In distributed environments, you can use materialized views to replicate data at distributed sites and to synchronize updates done at t hose sites with conflict resolution methods. The materialized views as replicas provide local access to data that otherwise would hav e to be accessed from remote sites. Materialized views are also useful in remote data marts. See Oracle Database Advanced Replication and Oracle Database Heterogeneous Connectivity Administrator's Guide for details on distributed and mobile co mputing.

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 and updates between clients and the central servers .

This chapter focuses on the use of materialized views in data warehouses. See Oracle Database Advanced Replication and Oracle Database Heterogeneous Connectivity Administrator's Guide for details on distributed and mobile computing.

The Need for Materialized Views

You can use materialized v iews to increase the speed of queries on very large databases. Queries to large databases often involve joins between tables, aggrega tions such as SUM, or both. These operations are expensive in terms of time and processing power. The type of materializ ed view you create determines how the materialized view is refreshed and used by query rewrite.

Materialized views improve que ry performance by precalculating expensive join and aggregation operations on the database prior to execution and storing the results in the database. The query optimizer automatically recognizes 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 go directly to the materialized view and n ot to the underlying detail tables. In general, rewriting queries to use materialized views rather than detail tables improves respon se. Figure 8-1 illustrates how query rewrite works.

Figure 8-1 Transparent Query Re write

Description of dwhsg027.gif follows
Description of the illustration dwhsg027.gif

When using query rewrite, create materialized views that satisfy the largest number of queries. For example, if you identify 20 qu eries that are commonly applied to the detail or fact tables, then you might be able to satisfy them with five or six well-written ma terialized views. A materialized view definition can include any number of aggregations (SUM, COUNT(x), COUNT(*), COUNT(DISTINCT x), AVG, VARIANCE, STDDEV, MIN, and MAX). It can also include any number of joins. If you are unsure of which materialized views to create, Oracle p rovides the SQLAccess Advisor, which is a set of advisory procedures in the DBMS_ADVISOR package to help in designing an d evaluating materialized views for query rewrite. See Chapter 17, " SQLAccess Advisor" for furthe r details.

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

Unlike indexes, materialized views can be accessed directly using a SELECT statement. However, it is recommended that you try to avoid writing SQL statements that directly reference the mate rialized view, because then it is difficult to change them without affecting the application. Instead, let query rewrite transparentl y rewrite your query to use the materialized view.

Note that the techniques shown in this chapter illustrate how to use materi alized views in data warehouses. Materialized views can also be used by Oracle Replication. See Oracle Database Advanced Replication for further information.

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

Data Warehousing Terminology

Some basic data warehousing terms are defined as follows:

    < li type="disc">

    Dimension tables describe the business entities of an ent erprise, represented as hierarchical, categorical information such as time, departments, locations, and products. Dimension tables ar e sometimes called lookup or reference tables.

    D imension tables usually change slowly over time and are not modified on a periodic schedule. They are used in long-running decision s upport queries to aggregate the data returned from the query into appropriate levels of the dimension hierarchy.

  • Hierarchies 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 be used to create materialized views. See Chapter 10, " Dimensions" for more information.

  • Fact tables describ e the business transactions of an enterprise.

    The vast majority of data in a data warehouse is stored in a few very large fact tables that are updated periodically with data from one or more operational OLTP databases.

    Fact tables include facts (also called measures) such as sales, units, and inventory.

    • A simple m easure is a numeric or character column of one table such as fact.sales.

    • A computed measu re is an expression involving measures of one table, for example, fact.revenues - fact.expenses.

      • < li type="circle">

      A multitable measure is a computed measure defined on multiple tables, for example, fact_a.revenues - fact_b.expenses.

    Fact tables also contain one or more foreign keys that organize the business transactions by the relevant business entities such as time, product, and market. In most cases, the se foreign keys are non-null, form a unique compound key of the fact table, and each foreign key joins with exactly one row of a dimension table.

  • A materialized view is a precomputed table comprising aggregated and joined data from fact and possibly from dimension tables. Among builders of data warehouses, a materialized view is also known as a summary.

    < /li>
< /a>

Materialized View Schema Design

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

A materialized view de finition includes any number of aggregates, as well as any number of joins. In several ways, a materialized view behaves like an inde x:

  • The purpose of a materialized view is to increase query execution performance.

  • The existence of a materialized view is transparent to SQL applications, so that a database administrator can create or drop m aterialized views at any time without affecting the validity of SQL applications.

  • A materialized view co nsumes storage space.

  • The contents of the materialized view must be updated when the underlying detail t ables are modified.

Schemas and Dimension Tables

In the case of normalized or partially normalized dimension tables (a dimension that is stored in more than one table), identify how these tables are joined. Note whether the joins between the dimens ion tables can guarantee that each child-side row joins with one and only one parent-side row. In the case of denormalized dimensions , determine whether the child-side columns uniquely determine the parent-side (or attribute) columns. These relationships can be enab led with constraints, using the NOVALIDATE and RELY options if the relationships represented by the constra ints are guaranteed by other means. Note that if the joins between fact and dimension tables do not support the parent-child relation ship described previously, you still gain significant performance advantages from defining the dimension with the CREATE DIMENSION statement. Another alternative, subject to some restrictions, is to use outer joins in the materialized view definition (that is, in the CREATE MATERIALIZED VIEW statement).

You must not create di mensions in any schema that does not satisfy these relationships. Incorrect results can be returned from queries otherwise.

Materialized View Schema Design Guidelines

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

Guidelines 1 an d 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.

Table 8-1 Schema Design Guidelines

Schema Guideline Description
Guideline 1

Dimensions

 Dimensions should either be deno rmalized (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 Dimensions".

You can enforce this condition by adding FOREIGN KE Y and NOT NULL constraints on the child-side join keys and PRIMARY KEY constraints on t he parent-side join keys.

Guideline 2 < p>Dimensions

 If dimensions are denormalized or partially denormalized, hierarchic al integrity must be maintained between the key columns of the dimension table. Each child key value must uniquely identify its paren t key value, even if the dimension table is denormalized. Hierarchical integrity in a denormalized dimension can be verified by calli ng the VALIDATE_DIMENSION procedure of the DBMS_DIMENSION package.
Guideline 3

Dimensions

 Fact and dimension tables should similarly guarantee that each fact table row joins with exactly one dimension table row. This condition m ust be declared, and optionally enforced, by adding FOREIGN KEY and NOT NULL cons traints on the fact key column(s) and PRIMARY KEY constraints on the dimension key column(s), or by using o uter joins. In a data warehouse, constraints are typically enabled with the NOVALIDATE and RELY clauses to avoid constraint enforcement performance overhead. See Oracle Da tabase SQL Reference for further details.
Guideline 4

Incremental Loads

 Incremental loads of your detail data sh ould be done using the SQL*Loader direct-path option, or any bulk loader utility that uses Oracle's direct-path interface. This inclu des INSERT ... AS SELECT with the APPEND or PARALLEL hints, where the hints cause the direct loader log to be used during the insert. See Oracle Database SQL Reference and "Types of Materialized Views" for more information.
Guideline 5

Partitions

 Range/composite partition your tables by a monotonically increasing time column if possible (preferably of type D ATE).
Guideline 6

Dimensions

< /td> 		After each load and before refreshing your materialized view, use the VALIDATE_ DIMENSION procedure of the DBMS_DIMENSION package to incrementally verify dimensional integrity.
Guideline 7

Time Dimensions

 If a time dimension appears in the materialized view as a time column, partition and index the materialized v iew in the same manner as you have the fact tables.

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

Loading Data into Data Warehouses

A popular and efficient way to load data into a data w arehouse or data mart is to use SQL*Loader with the DIRECT or PARALLEL option, DataPump, or to use another loader tool that uses the Oracle direct-path API. See Oracle Database 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, d ata is loaded directly into the target table, quality assurance tests are performed, and errors are resolved by performing DML operat ions prior to refreshing materialized views. If a large number of deletions are possible, then storage utilization can be adversely a ffected, but temporary space requirements and load time are minimized.

In a two-phase loading process:

  • Data is first loaded into a temporary table in the warehouse.

  • Quality assurance procedures are app lied to the data.

  • Referential integrity constraints on the target table are disabled, and the local inde x in the target partition is marked unusable.

  • The data is copied from the temporary area into the approp riate partition of the target table using INSERT AS SELECT with the PARALLEL or < code>APPEND hint. The temporary table is then dropped. Alternatively, if the target table is partitioned, you can create a new (empty) partition in the target table and use ALTER TABLE ... EXCHANGE PARTITION to incorporate the temporary table int o the target table. See Oracle Database SQL Reference f or more information.

  • 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 ope ration, if desired. You can disable query rewrite at the system level by issuing an ALTER SYSTEM SET< /code> QUERY_REWRITE_ENABLED = FALSE statement until all the materialized views are refreshed.

If QUERY_REWRITE_INTEGRITY is set to STALE_TOLERATED, access to the materialized view can be allowed at the sess ion level to any users who do not require the materialized views to reflect the data from the latest load by issuing an ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE statement. This scenario does not ap ply when QUERY_REWRITE_INTEGRITY is either ENFORCED or TRUSTED because the system ensures in t hese modes that only materialized views with updated data participate in a query rewrite.

Overview of Materiali zed View Management Tasks

The motivation for using materialized views is to improve performance, but the overhead asso ciated with materialized view management can become a significant system management problem. When reviewing or evaluating some of the necessary materialized view management activities, consider some of the following:

  • Identifying what mat erialized views to create initially.

  • Indexing the materialized views.

  • Ensur ing 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 materia lized view has been on workload performance.

  • Measuring the space being used by materialized views.

  • Determining which new materialized views should be created.

  • Determining which ex isting 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 admini stration overhead is the update process, which involves:

  • Periodic extraction of incremental changes from the operational systems.

  • Transforming the data.

  • Verifying that the increme ntal changes are correct, consistent, and complete.

  • Bulk-loading the data into the warehouse.

    • < li type="disc">

    Refreshing indexes and materialized views so that they are consistent with the detail data.

The u pdate process must generally be performed within a limited period of time known as the update window. The update window depends on the < a href="glossary.htm#i997216">update frequency (such as daily or weekly) and the nature of the business. For a d aily update frequency, an update window of two to six hours might be typical.

You need to know your update window for the foll owing activities:

  • Loading the detail data

  • Updating or rebuilding the indexe s on the detail data

  • Performing quality assurance tests on the data

  • Refresh ing the materialized views

  • Updating the indexes on the materialized views

Types of Materialized Views

The SELECT clause in the materialized view creation statement defines the data that the materialized view is to contain. Only a few restrictions limit what can 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. Besides tables, other elements such as views, inline views (s ubqueries in the FROM clause of a SELECT statement), subqueries, and materialized views can all be joined o r referenced in the SELECT clause. You cannot, however, define a materialized with a subquery in the select list of the defining query. You can, however, include subqueries elsewhere in the defining query, such as in the WHERE clause.

< p>The types of materialized views are:

Materialized Views with Aggregates

In data warehouses, materialized views normally contain aggreg ates as shown in Example 8-1. For fast refresh to be possible, the SELECT list must contain all of the GROUP BY columns (if present), and there must be a COUNT(*) and a COUNT(column) on any aggregated columns. Also, materialized view logs must be present on all tables referenced in the query that defines the materialized view. The valid aggregate functions are: SUM, COUNT(x), COUNT(*), AVG, VARIANCE, STDDEV, MIN, and MAX, and the expression to be aggregated can be any SQL value expression. See "Restrictions on Fast Refresh on Materialized Views with Aggregates".

Fast refresh for a materialized view containing joins and aggregates is possible after any type of DML to the base tables (direct load or conventional INSERT, UPDATE, or DELETE). It can be defined to be refreshed ON COMMIT or ON DEMAND. A REFRESH ON COMMIT materialized view will be refr eshed automatically when a transaction that does DML to one of the materialized view's detail tables commits. The time taken to compl ete the commit may be slightly longer than usual when this method is chosen. This is because the refresh operation is performed as pa rt of the commit process. Therefore, this method may not be suitable if many users are concurrently changing the tables upon which th e materialized view is based.

Here are some examples of materialized views with aggregates. Note that materialized view logs a re only created because this materialized view will be fast refreshed.

Example 8-1 Example 1: Crea ting a Materialized View

CREATE MATERIALIZED VIEW LOG ON products WITH SE
QUENCE, ROWID
(prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc, 
prod_category, prod_category_desc, prod_weigh
t_class, prod_unit_of_measure,
 prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price)
INCLUDING NEW VALUES;

CRE
ATE MATERIALIZED VIEW LOG ON sales
WITH SEQUENCE, ROWID
(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)

INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW product_sales_mv
PCTFREE 0  TABLESPACE demo
STORAGE (INITIAL 8k NEXT 8k PCTINCREASE
0)
BUILD IMMEDIATE
REFRESH FAST
ENABLE QUERY REWRITE
AS SELECT p.prod_name, SUM(s.amount_sold) AS dollar_sales,
COUNT(*) AS cnt, COUN
T(s.amount_sold) AS cnt_amt
FROM sales s, products p
WHERE s.prod_id = p.prod_id GROUP BY p.prod_name;

This example creat es a materialized view product_sales_mv that computes total number and value of sales for a product. It is derived by jo ining the tables sales and products on the column prod_id. 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 defaul t refresh method is FAST, which is allowed because the appropriate materialized view logs have been created on tables product and sales.

< /a>

Example 8-2 Example 2: C reating a Materialized View

CREATE MATERIALIZED VIEW product_sales_mv
PCT
FREE 0 TABLESPACE demo
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0)
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS

SELECT p.prod_name, SUM(s.amount_sold) AS dollar_sales
FROM sales s, products p WHERE s.prod_id = p.prod_id
GROUP BY p.prod_name;

<
/pre>


This example creates a materialized view product_sales_mv that computes the sum of sales by prod_name. It is derived by joining the tables sales and products on the column prod_id. The materi
alized view does not initially contain any data, because the build method is DEFERRED. A complete refresh is required fo
r the first refresh of a build deferred materialized view. When it is refreshed and once populated, this materialized view can be use
d by query rewrite.

Example 8-3 Example 3: Creating a Materialized View

CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID
(prod_id,
 cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW sum_sales
PARALL
EL
BUILD IMMEDIATE  
REFRESH FAST ON COMMIT AS  
SELECT s.prod_id, s.time_id, COUNT(*) AS count_grp,
   SUM(s.amount_sold) AS sum_dol
lar_sales,
   COUNT(s.amount_sold) AS count_dollar_sales,
   SUM(s.quantity_sold) AS sum_quantity_sales,
   COUNT(s.quantity_sold) AS
 count_quantity_sales
FROM sales s
GROUP BY s.prod_id, s.time_id;

This example creates a materialized view that contains aggregates on a single table. Because the materialized view log has been created with all referenced columns in the materialized view 's defining query, the materialized view is fast refreshable. If DML is applied against the sales table, then the change s will be reflected in the materialized view when the commit is issued.

Requirements for Using Materialized V iews with Aggregates

Table 8-2 illustrates the aggregate requirements for materialized views.< /p>

Table 8-2 Requirements for Materialized Views with Aggregates

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

Note that COUNT(*) must always be present t o guarantee all types of fast refresh. Otherwise, you may be limited to fast refresh after inserts only. Oracle recommends that you i nclude the optional aggregates in column Z in the materialized view in order to obtain the most efficient and accurate f ast refresh of the aggregates.

Materialize d Views Containing Only Joins

Some materialized views contain only joins and no aggregates, such as in Example 8-4, where a materialized view is created that joins the sales t able to the times and customers tables. The advantage of creating this type of materialized view is that ex pensive joins will be precalculated.

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

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

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

    < li type="disc">

    A materialized view log must be present for each detail table and the ROWID column must be present in each materia lized view log.

  • The rowids of all the detail tables must appear in the SELECT list of the m aterialized view query definition.

  • If there are no outer joins, you may have arbitrary selections and jo ins in the WHERE clause. However, if there are outer joins, the WHERE clause cannot have any selections. Fu rther, if there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.

  • 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 table 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 these restrictions are not met, you can create the materialized view as REFRESH FORCE to take advantage of fast refresh when it is possibl e. If one of the tables did not meet all of the criteria, but the other tables did, the materialized view would still be fast refresh able with respect to the other tables for which all the criteria are met.

< /a>

Materialized Join Views FROM Clause Considerations

If the materialized view contains only joins, the ROWID columns for each table (and each instance of a table that occurs multiple tim es in the FROM list) must be present in the SELECT list of the materialized view.

If the materialize d view has remote tables in the FROM clause, all tables in the FROM clause must be located on that same sit e. Further, ON COMMIT refresh is not supported for materialized view with remote tables. Materialized view logs must be present on the remote site for each detail table of the materialized view and ROWID columns must be present in the SELECT list of the materialized view.

To improve refresh performance, you should create indexes on the ma terialized view's columns that store the rowids of the fact table.

Example 8-4 Materialized View Containin g Only Joins

CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;
CREATE MATERIALIZED
 VIEW LOG ON times WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;
CREATE MATERIALIZED VIEW detail_sales_mv 
PARALL
EL BUILD IMMEDIATE
REFRESH FAST AS
SELECT s.rowid "sales_rid", t.rowid "times_rid", c.rowid "customers_rid",
       c.cust_id, c.cust
_last_name, s.amount_sold, s.quantity_sold, s.time_id
FROM sales s, times t, customers c 
WHERE  s.cust_id = c.cust_id(+) AND s.time_
id = t.time_id(+);

In this example, to perform a fast refresh, UNIQUE constraints should exist on c.cu st_id and t.time_id. You should also create indexes on the columns sales_rid, times_rid , and customers_rid, as illustrated in the following. This will improve the refresh performance.

CREATE INDEX mv_ix_salesrid ON detail_sales_mv("sales_rid");

Alternatively, if the previous example did not inclu de the columns times_rid and customers_rid, and if the refresh method was REFRESH FORCE< /code>, then this materialized view would be fast refreshable only if the sales table was updated but not if the tables times or customers were updated.

CREATE MATERIALIZED VIEW detail_sales_mv 
PARALLEL
BUILD
IMMEDIATE
REFRESH FORCE AS
SELECT s.rowid "sales_rid", c.cust_id, c.cust_last_name, s.amount_sold,
   s.quantity_sold, s.time_id
FROM
 sales s, times t, customers c 
WHERE s.cust_id = c.cust_id(+) AND s.time_id = t.time_id(+);
< /div>

Nested Materialized Views

A nested materialized view is a materialized view whose defi nition is based on another materialized view. A nested materialized view can reference other relations in the database in addition to referencing 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 aggre gate views can take a long time, because the underlying join has to be performed many times.

Using nested materialized views, you can create multiple single-table materialized views based on a joins-only materialized view and the join is performed just once. In addition, optimizations can be performed for this class of single-table aggregate materialized view and thus refresh is very effic ient.

Exam ple 8-5 Nested Materialized View

You can create a nested materialized view on materialized views that con tain joins only or joins and aggregates. All the underlying objects (materialized views or tables) on which the materialized view is defined must have a materialized view log. All the underlying objects are treated as if they were tables. In addition, you can use al l the existing options for materialized views.

Using the tables and their columns from the sh sample schema, the following materialized views illustrate how nested materialized views can be created.

CREATE MATERIALIZ
ED VIEW LOG ON sales WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON times WITH ROW
ID;

/*create materialized view join_sales_cust_time as fast refreshable at
   COMMIT time */
CREATE MATERIALIZED VIEW join_sales_cus
t_time 
REFRESH FAST ON COMMIT AS
SELECT c.cust_id, c.cust_last_name, s.amount_sold, t.time_id,
       t.day_number_in_week, s.rowid
srid, t.rowid trid, c.rowid crid 
FROM sales s, customers c, times t
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id;

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

/* create materialized view log on join_sales_cust_time */
CREATE MATERIALIZED VIEW LOG ON join_sales_cust_time 
WITH
ROWID (cust_last_name, day_number_in_week, amount_sold)
INCLUDING NEW VALUES;

/* create the single-table aggregate materialized view
 sum_sales_cust_time on
   join_sales_cust_time as fast refreshable at COMMIT time */
CREATE MATERIALIZED VIEW sum_sales_cust_time 
R
EFRESH FAST ON COMMIT AS
SELECT COUNT(*) cnt_all, SUM(amount_sold) sum_sales, COUNT(amount_sold)
       cnt_sales, cust_last_name, da
y_number_in_week
FROM join_sales_cust_time
GROUP BY cust_last_name, day_number_in_week;

Nesting Materialized Views with Joins and Aggregates

Some types of nested materialized views cannot be fast refreshed. Use EXPLAIN_MVIEW to identify those types of materialized views. Yo u can refresh a tree of nested materialized views in the appropriate dependency order by specifying the nested = TRUE pa rameter with the DBMS_MVIEW. REFRESH parameter. For example, if you call DBMS_MVIEW.REFRESH ('SUM_SALES_CUST_TIME' , nested => TRUE), the REFRESH procedure will first refresh the join_sales_cust_time materialized view, and then refresh the sum_sales_cust_time materialized view.

< a id="sthref452" name="sthref452">

Nested Materialized View Usage Guidelines

You should keep the following in mind when deciding whether to use nested materialized views:

  • If you want to use fast refresh, you should fast refresh all the materialized views along any chain.

  • If you want the highest level materialized view to be fresh with respect to the detail tables, you need to ensure that a ll materialized views in a tree are refreshed in the correct dependency order before refreshing the highest-level. You can automatica lly refesh intermediate materialized views in a nested hierarchy using the nested = TRUE parameter, as described in "Nesting Materialized Views with Joins and Aggregates". If you do not specify nested = TRUE and the materialzied views under the highest-level materialized view are stale, refreshing only the highest-level will succeed, but makes it fresh only with respect to its underlying materialized view, not the detail tables at the base of the tree.

  • When refreshing materialized views, you need to ensure that all materialized views in a tree are refreshed. If you only refresh t he highest-level materialized view, the materialized views under it will be stale and you must explicitly refresh them. If you use th e REFRESH procedure with the nested parameter value set to TRUE, only specified materialized v iews and their child materialized views in the tree are refreshed, and not their top-level materialized views. Use the REFRESH_ DEPENDENT procedure with the nested parameter value set to TRUE if you want to ensure that all materialized views in a tree are refreshed.

  • Freshness of a materialized view is calculated relative to the objects directl y referenced by the materialized view. When a materialized view references another materialized view, the freshness of the topmost ma terialized view is calculated relative to changes in the materialized view it directly references, not relative to changes in the tab les referenced by the materialized view it references.

Restrictions When Using Nested Materialized Vi ews

You cannot create both a materialize d view and a prebuilt materialized view on the same table. For example, If you have a table costs with a materialized vi ew cost_mv based on it, you cannot then create a prebuilt materialized view on table costs. The result woul d make cost_mv a nested materialized view and this method of conversion is not supported.

Creating Materialized Views

A materialized view can be created with the CREATE MATERIALIZED VIEW statement or using Enterprise Manager. Example 8-6 creates the materialized view cust_sa les_mv.

Example 8-6 Creating a Materialized View

CREATE MATERIALIZED VIEW cust_sales_mv
PCTFREE 0 TABLESPACE demo
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0)
PARALLEL
BUILD IMMEDIA
TE
REFRESH COMPLETE
ENABLE QUERY REWRITE AS
SELECT  c.cust_last_name, SUM(amount_sold) AS sum_amount_sold
FROM customers c, sales s W
HERE s.cust_id = c.cust_id
GROUP BY c.cust_last_name;

It is not uncommon in a data warehouse to have already created summ ary or aggregation tables, and you might not wish to repeat this work by building a new materialized view. In this case, the table th at already exists in the database can be registered as a prebuilt materialized view. This technique is described in "Registering Existing Materialized Views".

< p>Once you have selected the materialized views you want to create, follow these steps for each materialized view.

  1. Design the materialized view. Existing user-defined materialized views do not requir e this step. If the materialized view contains many rows, then, if appropriate, the materialized view should be partitioned (if possi ble) and should match the partitioning of the largest or most frequently updated detail or fact table (if possible). Refresh performa nce benefits from partitioning, because it can take advantage of parallel DML capabilities and possible PCT-based refresh.

    2. < li>

    Use the CREATE MATERIALIZED VIEW statement to create and, optionally, populate the mate rialized view. If a user-defined materialized view already exists, then use the ON PREBUILT TABLE 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 later. A BUILD DEFERRED materialized view is disabled for use by query rewri te until the first COMPLETE REFRESH, after which it will be automatically enabled, provided the ENABL E QUERY REWRITE clause has been specified.


    See Also:

    Oracle Database SQL Reference< /em> for descriptions of the SQL statements CREATE MATERIALIZED VIEW, ALTER MATERIALIZED VIEW, and DROP MATERIALIZED VIEW
    < br />

Creating Materialized Views with Column Alias Lists

Currently, when a mater ialized view is created, if its defining query contains same-name columns in the SELECT list, the name conflicts need to be resolved by specifying unique aliases for those columns. Otherwise, the CREATE MATERIALIZED VIEW< /code> statement will fail with the error messages of columns ambiguously defined. However, the standard method of attaching aliases in the SELECT clause for name resolution restricts the use of the full text match query rewrite and it will occur only w hen the text of the materialized view's defining query and the text of user input query are identical. Thus, if the user specifies se lect aliases in the materialized view's defining query while there is no alias in the query, the full text match comparison will fail . This is particularly a problem for queries from Discoverer, which makes extensive use of column aliases.

The following is an example of the problem. sales_mv is created with column aliases in the SELECT clause but the input query < code>Q1 does not have the aliases. The full text match rewrite will fail. The materialized view is as follows:

CREATE MATERIALIZED VIEW sales_mv
ENABLE QUERY REWRITE AS
SELECT s.time_id sales_tid, c.time_id costs_tid
FROM sales s
, products p, costs c
WHERE s.prod_id = p.prod_id AND c.prod_id = p.prod_id AND
      p.prod_name IN (SELECT prod_name FROM products)
;

Input query statement Q1 is as follows:

SELECT s.time_id, c.time_id
FROM sal
es s, products p, costs c
WHERE s.prod_id = p.prod_id AND c.prod_id = p.prod_id AND
      p.prod_name IN (SELECT prod_name FROM produ
cts);

Even though the materialized view's defining query is almost identical and logically equivalent to the user's input query, query rewrite does not happen because of the failure of full text match that is the only rewrite possibility for some queries (for example, a subquery in the WHERE clause).

You can add a column alias list to a CREATE MA TERIALIZED VIEW statement. The column alias list explicitly resolves any column name conflict without attaching a liases in the SELECT clause of the materialized view. The syntax of the materialized view column alias list is illustrat ed in the following example:

CREATE MATERIALIZED VIEW sales_mv (sales_tid, costs_tid)
ENABLE QUERY REWR
ITE AS
SELECT s.time_id, c.time_id
FROM sales s, products p, costs c
WHERE s.prod_id = p.prod_id AND c.prod_id = p.prod_id AND
p.prod_name IN (SELECT prod_name FROM products);

In this example, the defining query of sales_mv now matches exactly with the user query Q1, so full text match rewrite will take place.

Note that when aliases are specified in both the SELECT clause and the new alias list clause, the alias list clause supersedes the ones in the SELECT< /code> clause.

Naming Materialized Views

The name of a ma terialized view must conform to standard Oracle naming conventions. However, if the materialized view is based on a user-defined preb uilt 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 might consider extending this naming scheme to the materialized views so that they are easily identifiab le. For example, instead of naming the materialized view sum_of_sales, it could be called sum_of_sales_mv t o denote that this is a materialized view and not a table or view.

Stora ge And Table Compression

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 yo u do not know how much space the materialized view will require, then the DBMS_MVIEW.ESTIMATE_SIZE package can estimate the number of bytes required to store this uncompressed materialized view. This information can then assist the design team in determ ining the tablespace in which the materialized view should reside.

You should use table compression with highly redundant data , such as tables with many foreign keys. This is particularly useful for materialized views created with the ROLLUP clau se. Table compression reduces disk use and memory use (specifically, the buffer cache), often leading to a better scaleup for read-on ly operations. Table compression can also speed up query execution at the expense of update cost.


See Also:

Oracle Database SQL Reference for a complete description of STORAGE semantics, Oracle Database Performance Tuning Guide , and Chapter 5, " Parallelism and Partitioning in Data Warehouses" for table compression examples

Build Methods

Two build methods are available for creating the materialized v iew, as shown in Table 8-3. If you select BUILD IMMEDIATE, the materialized view de finition 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, thi s build process can take a considerable amount of time.

An alternative approach is to use the BUILD DEFERRE D 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 15, " Maintaining the Data Warehouse".

Table 8-3 Build Methods


Enabling Query Rewrite

Before creating a materialized v iew, you can verify what types of query rewrite are possible by calling the procedure DBMS_MVIEW.EXPLAIN_MVIEW, or use < code>DBMS_ADVISOR.TUNE_MVIEW to optimize the materialized view so that a many types of query rewrite are possible. Once the ma terialized view has been created, you can use DBMS_MVIEW.EXPLAIN_REWRITE to find out if (or why not) it will rewrite a s pecific query.

Even though a materialized view is defined, it will not automatically be used by the query rewrite facility. Ev en though query rewrite is enabled by default, you also must specify the ENABLE QUERY REWRITE clause 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 created, the materialized view can subseq uently be enabled for query rewrite with the ALTER MATERIALIZED VIEW statement.

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

Query Rewrite Restrictions

Query rewrite is not possible with al l materialized views. If query rewrite is not occurring when expected, DBMS_MVIEW.EXPLAIN_REWRITE can help provide reaso ns why a specific query is not eligible for rewrite. If this shows that not all types of query rewrite are possible, use the procedur e DBMS_ADVISOR.TUNE_MVIEW to see if the materialized view can be defined differently so that query rewrite is possible. Also, check to see if your materialized view satisfies all of the following conditions.

Materialized View Restrictions

You should keep in mind the following restrictions:

  • The defining query of the materialized view cannot contain any non-repeatable expressions (ROWNUM, SYSDATE, non-repeatable PL/ SQL functions, and so on).

  • The query cannot contain any references to RAW or LONG RAW datatypes or object REFs.

  • If the materialized view was registered as < code>PREBUILT, the precision of the columns must agree with the precision of the corresponding SELECT expressions unless overridden by the WITH REDUCED PRECISION clause.

Gener al Query Rewrite Restrictions

You should keep in mind the following restrictions:

  • If a query has both local and remote tables, only local tables will be considered for potential rewrite.

  • Neither the detail tables nor the materialized view can be ow ned by SYS.

  • If a column or expression is present in the GROUP BY clause of the materialized view, it must also be present in the SELECT list.

  • Aggregate func tions 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.

  • CONNECT BY clauses are not allowed .

Refresh Options

When you define a materialized view, you can specify three refresh options: how to refresh, what type of refresh, and can trusted co nstraints be used. If unspecified, the defaults are assumed as ON DEMAND, FORCE, and ENF ORCED constraints respectively.

The two refresh execution modes are ON COMMIT and ON DEMAND. Depending on the materialized view you create, some of the options may not be available. Table 8-4 describes the refresh modes.

Table 8-4 Refresh Modes

Build Method Description
BUILD I MMEDIATE Create the materialized view and then populate it with data.
BUILD DEFERRED Create the materialized view definition but do not populate it with data.
Refresh Mode Description
ON COMMIT Refresh occurs automatically when a transaction that modified one of the materialized view's detail tables commits. This can be specified as long as the materialized view is fast refreshable (in other words, not complex). The ON COMMIT privilege is necessary to use this mode
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)

When a materialized view is maintained using the ON COMMIT method, the time required to comple te the commit may be slightly longer than usual. This is because the refresh operation is performed as part of the commit process. Th erefore this method may not be suitable if many users are concurrently changing the tables upon which the materialized view is based.

If you anticipate performing insert, update or delete operations on tables referenced by a materialized view concurrently wit h the refresh of that materialized view, and that materialized view includes joins and aggregation, Oracle recommends you use O N COMMIT fast refresh rather than ON DEMAND fast refresh.

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

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

< p>You can specify how you want your materialized views to be refreshed from the detail tables by selecting one of four options: COMPLETE, FAST, FORCE, and NEVER. Table 8-5 describes the refresh options.

Table 8-5 Refresh Options

Refresh Option Description
COMPLETE Refreshes by recalculating the materialized view's defining query.
FAST Applies incremental changes to refresh the materialized view using the information logged in the materialized vi ew logs, or from a SQL*Loader direct-path or a partition maintenance operation.
FORCE Applies FAST refresh if possible; otherwise, it applies COMPLETE refresh.
NEVER Indicates that the materialized view will not be refreshed with refresh mechanisms.

Whether the fast refresh option is available depends upon the type o f materialized view. You can call the procedure DBMS_MVIEW.EXPLAIN_MVIEW to determine whether fast refresh is possible.< /p>

You can also specify if it is acceptable to use trusted constraints and REWRITE_INTEGRITY = TRUSTED during refres h. Any nonvalidated RELY constraint is a trusted constraint. For example, nonvalidated foreign key/primary key relations hips, functional dependencies defined in dimensions or a materialized view in the UNKNOWN state. If query rewrite is ena bled during refresh, these can improve the performance of refresh by enabling more performant query rewrites. Any materialized view t hat can uses TRUSTED constraints for refresh is left in a state of trusted freshness (the UNKNOWN state) af ter refresh.

This is reflected in the column STALENESS in the view USER_MVIEWS. The column UNK NOWN_TRUSTED_FD in the same view is also set to Y, which means yes.

You can define this property of the ma terialized either during create time by specifying REFRESH USING TRUSTED [ENFORCED] CONSTRAINTS or by using ALTER MATERIALIZED VIEW DDL.

Table 8-6 Constraints

Constraints to Use Description 
TRUSTED CONSTRAINTS
Refresh can use trusted constraints and REWR ITE_INTEGRITY = TRUSTED during refresh.This allows use of non-validated RELY constraints and rewrite against materialized views in UNKNOWN or FRESH state during refresh.
ENFORCED CONSTRAINTS
Refresh can use validated constraints and REFRESH_INTEGRITY=ENFORCED during refresh. This allows use of only validated, enforced constraints and rewrite against materialized views in FRESH state during refresh.

General Restrictions on Fast Refresh

The defining query of the materialized vi ew is restricted as follows:

  • The materialized view must not contain references to non-repeating expressi ons like SYSDATE and ROWNUM.

  • The materialized view must not contain references to RAW or LONG RAW data types.

Restrictions on Fast Refresh on Materialized Views with Joins Only

Defining queries for materialized views wi th joins only and no aggregates have the following restrictions on fast refresh:

  • All restrictions from < a href="#i1007007">"General Restrictions on Fast Refresh".

  • They cannot have GROUP BY clauses or aggregates.

  • If the WHERE clause of the query contains outer joins, th en unique constraints must exist on the join columns of the inner join table.

  • If there are no outer join s, you can have arbitrary selections and joins in the WHERE clause. However, if there are outer joins, the WHERE clause cannot have any selections. Furthermore, if there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.

  • Rowids of all the tables in the FROM l ist must appear in the SELECT list of the query.

  • Materialized view logs must exist with row ids for all the base tables in the FROM list of the query.

Restrictions on Fast Refresh on Materialized Views with Aggregates

Defining queries for materialized views w ith aggregates or joins have the following restrictions on fast refresh:

Fast refresh is supported for both ON COMM IT and ON DEMAND materialized views, however the following restrictions apply:

Restrictions on Fast Refresh on Materialized Views with UNION ALL

Materialized views with th e UNION ALL set operator support the REFRESH FAST option if the following conditi ons are satisfied:

  • The defining query must have the UNION ALL operator at the top level.

    The UNION ALL operator cannot be embedded inside a subquery, with one exception: The UNION ALL can be in a subquery in the FROM clause provided the defining query is of the form SELECT * FROM (view or subquery with UNION ALL) as in the following example:

    CREATE VIEW view_with_unionall_mv AS
(SELECT c.rowid crid, c.cust_id, 2 umarker
 FROM customers c WHERE c.cust_last_name = 'S
mith'
 UNION ALL
 SELECT c.rowid crid, c.cust_id, 3 umarker
 FROM customers c WHERE c.cust_last_name = 'Jones');

CREATE MATERIALIZED
 VIEW unionall_inside_view_mv
REFRESH FAST ON DEMAND AS
SELECT * FROM view_with_unionall;

    Note that the view view_w ith_unionall_mv satisfies all requirements for fast refresh.

  • Each query block in the UNION< /code> ALL query must satisfy the requirements of a fast refreshable materialized view with aggregates or a fast refresh able materialized view with joins.

    The appropriate materialized view logs must be created on the tables as required for the co rresponding type of fast refreshable materialized view.

    Note that the Oracle Database also allows the special case of a single table materialized view with joins only provided the ROWID column has been included in the SELECT list and in the materialized view log. This is shown in the defining query of the view view_with_unionall_mv.

  • The SELECT list of each query must include a maintenance column, called a UNION ALL marker. The UNION ALL column must have a distinct constant numeric or string value in each UNION ALL branch. Further, the marker column must appear in the same ordinal position in the SELECT list of e ach query block.

  • Some features such as outer joins, insert-only aggregate materialized view queries and remote tables are not supported for materialized views with UNION ALL.

  • PCT-bas ed refresh is not supported for UNION ALL materialized views.

  • The compatibilit y initialization parameter must be set to 9.2.0 or higher to create a fast refreshable materialized view with UNION ALL.

Achieving Refresh Goals

In addition to the EXPLAIN_MVIEW pr ocedure, which is discussed throughout this chapter, you can use the DBMS_ADVISOR.TUNE_MVIEW procedure to optimize a CREATE MATERIALIZED VIEW statement to achieve REFRESH FAST and EN ABLE QUERY REWRITE goals. The procedure is described in "Tuning Mater ialized Views for Fast Refresh and Query Rewrite".

Refreshing Nested Materialized Views

A ne sted materialized view is considered to be fresh as long as its data is synchronized with the data in its detail tables, even if some of its detail tables could be stale materialized views.

You can refresh nested materialized views in two ways: DBMS_MVI EW.REFRESH with the nested flag set to TRUE and REFRESH_DEPENDENT with the nested flag set to TRUE on the base tables. If you use DBMS_MVIEW.REFRESH, the entire materialized view ch ain is refreshed from the top down. With DBMS_MVIEW.REFRESH_DEPENDENT, the entire chain is refreshed from the bottom up.

Example 8 -7 Example of Refreshing a Nested Materialized View

The following statement shows an example of refreshin g a nested materialized view:

DBMS_MVIEW.REFRESH('SALES_MV,COST_MV', nested => TRUE);

Th is statement will first refresh all child materialized views of sales_mv and cost_mv based on the dependenc y analysis and then refresh the two specified materialized views.

You can query the STALE_SINCE column in the *_MVIEWS views to find out when a materialized view became stale.

ORDER BY Clause

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

To improve the performance of q ueries 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 often reduces the time for disk I/O due to the physical clustering.

The ORDER BY clause is not considered part of the materialized view defini tion. As a result, there is no difference in the manner in which Oracle Database 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 < code>BY clause. This feature is similar to the CREATE TABLE ... ORDER BY c apability.

Materialized View Logs

Materialized view logs are required if you want to use fast refresh, with the exception of PCT refresh,where there a few situations where they are not necessary. As a general rule, though, you should create materialized view logs if you want to use f ast refresh. Materialized view logs are defined using a CREATE MATERIALIZED VIEW LOG statement on the base table that is to be changed. They are not created on the materialized view. For fast refresh of materialize d views, the definition of the materialized view logs must normally specify the ROWID clause. In addition, for aggregate materialized views, it must also contain every column in the table referenced in the materialized view, the INCLUDING < code>NEW VALUES clause and the SEQUENCE clause.

An example of a materialized view log is show n as follows where one is created on the table sales.

CREATE MATERIALIZED VIEW LOG ON sale
s WITH ROWID
(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)
INCLUDING NEW VALUES;

Alternat ively, a materialized view log can be amended to include the rowid, as in the following:

ALTER MATERIAL
IZED VIEW LOG ON sales ADD ROWID
(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)
INCLUDING NEW VALUES;

Oracle recommends that the keyword SEQUENCE be included in your materialized view log statement unless you a re sure that you will never perform a mixed DML operation (a combination of INSERT, UPDATE, or DELETE operations on multiple tables). The SEQUENCE column is required in the materialized view log to support fast ref resh with a combination of INSERT, UPDATE, or DELETE statements on multiple tables. You can, h owever, add the SEQUENCE number to the materialized view log after it has been created.

The boundary of a mixed D ML operation is determined by whether the materialized view is ON COMMIT or ON DEMAND.

  • For ON COMMIT, the mixed DML statements occur within the same transactio n because the refresh of the materialized view will occur upon commit of this transaction.

  • For ON< /code> DEMAND, the mixed DML statements occur between refreshes. The following example of a materialized view log illust rates where one is created on the table sales that includes the SEQUENCE keyword:

    CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID
(prod_id, cust_id, time_id, channel_id, promo_id, 
 quantity_sold, am
ount_sold) INCLUDING NEW VALUES;

Using the FORCE Option with Materialized View Logs

If you specify FORCE and any items specified with the ADD clause have already been specified for the materialized view log, Oracle does not return an error, but silently ignores the existing elements and adds to the materialized view log any items that do not already exist in the log. For example, if you used a filter column such as cust_id and this column already existed, Oracle Datab ase ignores the redundancy and does not return an error.

Using Oracle Enterprise Manager

A materialized view can also be created using Enterprise Manager by selecting the materialized view object typ e. There is no difference in the information required if this approach is used. See Oracle Enterprise Manager Advanced Configuration for further information.

< div class="sect2">

Using Materi alized Views with NLS Parameters

When using certain materialized views, you must ensure that your NLS parameters are t he same as when you created the materialized view. Materialized views with this restriction are as follows:

  • Expressions that may return different values, depending on NLS parameter settings. For example, (date > "01/02/03") or (rate & lt;= "2.150") are NLS parameter dependent expressions.

  • Equijoins where one side of the join is character data. The result of this equijoin depends on collation and this can change on a session basis, giving an incorrect result in the cas e of query rewrite or an inconsistent materialized view after a refresh operation.

  • Expressions that gene rate internal conversion to character data in the SELECT list of a materialized view, or inside an aggregate of a materi alized aggregate view. This restriction does not apply to expressions that involve only numeric data, for example, a+b w here a and b are numeric fields.

Adding Comments to Materialized Views

You can add a comment to a materialized view. For example, the following statement adds a comment to data dictionary views for the existing materialized view:

COMMENT ON MATERIALIZED VIEW sales_mv IS 'sales materialized view'
;

To view the comment after the preceding statement execution, the user can query the catalog views, {USER, DBA} ALL_MVIEW_COMMENTS. For example:

SELECT MVIEW_NAME, COMMENTS
FROM USER_M
VIEW_COMMENTS WHERE MVIEW_NAME = 'SALES_MV';

The output will resemble the following:

MVIEW_
NAME           COMMENTS
 
-----------      -----------------------
SALES_MV         sales materialized view

Note: If the compatibility is set to 10.0.1 or higher, COMMENT ON TABLE will not be allowed for the materialized view container tab le. The following error message will be thrown if it is issued.

ORA-12098: cannot comment on the materi
alized view.

In the case of a prebuilt table, if it has an existing comment, the comment will be inherited by the materia lized view after it has been created. The existing comment will be prefixed with '(from table)'. For example, table sales_summary was created to contain sales summary information. An existing comment 'Sales summary data' was as sociated with the table. A materialized view of the same name is created to use the prebuilt table as its container table. After the materialized view creation, the comment becomes '(from table) Sales summary data'.

However, if the prebuilt table , sales_summary, does not have any comment, the following comment is added: 'Sales summary data'. Then, if we drop the materialized view, the comment will be passed to the prebuilt table with the comment: '(from materialized view) Sal es summary data'.

Registering Exist ing Materialized Views

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 a pplication to be transparently accessed in another application.

  • Generally support fast parallel or fast materialized view refresh.

Because of these limitations, and because existing materialized views can be extremely la rge and expensive to rebuild, you should register your existing materialized view tables whenever possible. You can register a user-d efined materialized view with the CREATE MATERIALIZED VIEW ... ON PREBUILT< /code> TABLE statement. Once registered, the materialized view can be used for query rewrites or maintained by one of th e refresh methods, or both.

The contents of the table must reflect the materialization of the defining query at the time you r egister 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 i n the defining query to be different from that of the table columns.

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

Materialized views based on prebuilt tables are eligible for selection by query rewrit e provided the parameter QUERY_REWRITE_INTEGRITY is set to STALE_TOLERATED or TRUSTED. See Chapter 18, " Query Rewrite" for details about integrity levels.

When you drop a materialized view that was created on a prebuilt table, the table still e xists—only the materialized view is dropped.

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

CREATE TABLE sum_sales
_tab
PCTFREE 0  TABLESPACE demo
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0) AS
SELECT s.prod_id, SUM(amount_sold) AS dollar_sales,
      SUM(quantity_sold) AS unit_sales
FROM sales s GROUP BY s.prod_id;

CREATE MATERIALIZED VIEW sum_sales_tab
ON PREBUILT TABLE WIT
HOUT REDUCED PRECISION
ENABLE QUERY REWRITE AS
SELECT s.prod_id, SUM(amount_sold) AS dollar_sales,
    SUM(quantity_sold) AS unit_sal
es
FROM sales s GROUP BY s.prod_id;

You could have compressed this table to save space. See "Storage And Table Compression" for details regarding table compression.

In some cases, user-defined materialized views are refresh ed on a schedule that is longer than the update cycle. For example, a monthly materialized view might be updated only at the end of e ach month, and the materialized view values always refer to complete time periods. Reports written directly against these materialize d 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 fast refreshed each update cycle.

    • < li type="disc">

    You can create a view that selects the complete time period of interest.

  • 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:

  • Create a new materialized view t hat does include the time dimension (if possible).

  • The view should aggregate over the time column in the new materialized view.

Choosing Indexes for Materialized View s

The two most common operations on a materialized view are query execution and fast refresh, and each operation has d ifferent performance requirements. Query execution might need to access any subset of the materialized view key columns, and might ne ed to join and aggregate over a subset of those columns. Consequently, query execution usually performs best if a single-column bitma p index is defined on each materialized view key column.

In the case of materialized views containing only joins using fast re fresh, Oracle recommends that indexes be created on the columns that contain the rowids to improve the performance of the refresh ope ration.

If a materialized view using aggregates is fast refreshable, then an index appropriate for the fast refresh procedure is created unless USING NO INDEX is specified in the CREATE MATERIALIZED VIEW statement.

See Chapter 17, " SQLAccess Advisor" for information on u sing the SQLAccess Advisor to determine what indexes are appropriate for your materialized view.

Dropping Materialized Views

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

DROP MATERIALIZED VIEW sales_sum_mv;

This statement drops the ma terialized view sales_sum_mv. If the materialized view was prebuilt on a table, then the table is not dropped, but it ca n no longer be maintained with the refresh mechanism or used by query rewrite. Alternatively, you can drop a materialized view using Oracle Enterprise Manager.

Analyzing Materialized View Capabilities

You can use the DBMS_MVIEW.EXPLAIN_MVIEW procedure to learn what is possible with a materialized view or potential materialized view. In particular, this procedure enables you to determine:

  • If a mater ialized view is fast refreshable

  • What types of query rewrite you can perform with this materialized view

  • Whether PCT refresh is possible

Using this procedure is straightforward. You simply ca ll DBMS_MVIEW.EXPLAIN_MVIEW, passing in as a single parameter the schema and materialized view name for an existing mate rialized view. Alternatively, you can specify the SELECT string for a potential materialized view or the complete CREATE MATERIALIZED VIEW statement. The materialized view or potential materialized view is then ana lyzed and the results are written into either a table called MV_CAPABILITIES_TABLE, which is the default, or to an array called MSG_ARRAY.

Note that you must run the utlxmv.sql script prior to calling EXPLAIN_MVIEW except when you are placing the results in MSG_ARRAY. The script is found in the admin directory. I t is to create the MV_CAPABILITIES_TABLE in the current schema. An explanation of the various capabilities is in Table 8-7, and all the possible messages are listed in Table 8-8.

Using the DBMS_MVIEW.EXPLAIN_MVIE W Procedure

The EXPLAIN_MVIEW procedure has the following paramete rs:

  • stmt_id

    An optional parameter. A client-supplied unique identifier to associate output rows with specific invocations of EXPLAIN_MVIEW.

  • mv

    The name of an existing materialized view or the query definition or the entire CREATE MATERIALIZED VIEW s tatement of a potential materialized view you want to analyze.

  • msg-array

    The PL/SQL varray that receives the output.

EXPLAIN_MVIEW analyzes the specified materialized view in terms of its refresh and rewrite capabilities and inserts its results (in the form of multiple rows) into MV_CAPABILITIES_TABLE or < code>MSG_ARRAY.


See Also:

PL/SQL Packages and Types Referenc e for further information about the DBMS_MVIEW package

DBMS_MVIEW.EXPLAIN_MVIEW Declarations

The following PL/SQL declarations that are made for you in the D BMS_MVIEW package show the order and datatypes of these parameters for explaining an existing materialized view and a potentia l materialized view with output to a table and to a VARRAY.

Explain an existing or potential materialized view with output to MV_CAPABILITIES_TABLE:

DBMS_MVIEW.EXPLAIN_MVIEW (mv           IN VARCHAR2,
           stmt_id IN VARCHAR2:= NULL);

Explain an existing or potential materialized view with output to a VARRAY:

< pre xml:space="preserve">DBMS_MVIEW.EXPLAIN_MVIEW (mv IN VARCHAR2, msg_array OUT SYS.ExplainMVAr rayType);

Using MV_CAPABILITIES_TABLE

One of the simplest ways to use DBMS_MVIEW.EXPLA IN_MVIEW is with the MV_CAPABILITIES_TABLE, which has the following struct ure:

CREATE TABLE MV_CAPABILITIES_TABLE 
(STMT_ID           VARCHAR(30),   -- client-supplied unique st
atement identifier
 MV                VARCHAR(30),   -- NULL for SELECT based EXPLAIN_MVIEW
 CAPABILITY_NAME   VARCHAR(30),   -- A de
scriptive name of particular 
                                  -- capabilities, such as REWRITE.
                                  -
- See Table 8-7
 POSSIBLE          CHARACTER(1),  -- Y = capability is possible
     -- N = capability is not possible
 RELATED_TEXT      VARCHAR(2000), -- owner.table.column, and so on related to
                  -- this message
 RELATED_NUM       NUMBER,        -- When there is a numeric value 
   -- associated with a row, it goes here.
 MSGNO             INTEGER,       -- When available, message # explaining
                  -- why disabled or more details when
                                  -- enabled.
 MSGTXT            VARCHAR(2000)
, -- Text associated with MSGNO
 SEQ               NUMBER);       -- Useful in ORDER BY clause when 
  -- selecting from this table.

You can use the utlxmv.sql script found in the admin directory to create MV_CAPABILITIES_TABLE.

Example 8-8 DBMS_MVIEW.EXPLAIN_MVIEW

First, create the materialized v iew. Alternatively, you can use EXPLAIN_MVIEW on a potential materialized view using its SELECT statement o r the complete CREATE MATERIALIZED VIEW statement.

CREATE MATERI
ALIZED VIEW cal_month_sales_mv
BUILD IMMEDIATE
REFRESH FORCE
ENABLE QUERY REWRITE AS
SELECT t.calendar_month_desc,  SUM(s.amount_sold
) AS dollars
FROM sales s,  times t WHERE s.time_id = t.time_id
GROUP BY t.calendar_month_desc;

Then, you invoke EX PLAIN_MVIEW with the materialized view to explain. You need to use the SEQ column in an ORDER BY clause so the rows will display in a logical order. If a capability is not possible, N will appear in the P column and an explanation in the MSGTXT column. If a capability is not possible for more than one reason, a r ow is displayed for each reason.

EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('SH.CAL_MONTH_SALES_MV');

SELECT ca
pability_name,  possible, SUBSTR(related_text,1,8)
  AS rel_text, SUBSTR(msgtxt,1,60) AS msgtxt
FROM MV_CAPABILITIES_TABLE
ORDER BY s
eq;



CAPABILITY_NAME                 P    REL_TEXT     MSGTXT
---------------                 -
 --------     ------
PCT                             N
REFRESH_COMPLETE                Y
REFRESH_FAST                    N
REWRITE
                      Y 
PCT_TABLE                       N    SALES        no partition key or PMARKER in select list  
PCT_TABLE
                   N    TIMES        relation is not a partitioned table 
REFRESH_FAST_AFTER_INSERT       N    SH.TIMES     mv log mu
st have new values  
REFRESH_FAST_AFTER_INSERT       N    SH.TIMES     mv log must have ROWID 
REFRESH_FAST_AFTER_INSERT       N    S
H.TIMES     mv log does not have all necessary columns  
REFRESH_FAST_AFTER_INSERT       N    SH.SALES     mv log must have new value
s  
REFRESH_FAST_AFTER_INSERT       N    SH.SALES     mv log must have ROWID  
REFRESH_FAST_AFTER_INSERT       N    SH.SALES     mv l
og does not have all necessary columns 
REFRESH_FAST_AFTER_ONETAB_DML   N    DOLLARS      SUM(expr) without COUNT(expr) 
REFRESH_FAST
_AFTER_ONETAB_DML   N                 see the reason why
                                                  REFRESH_FAST_AFTER_INSERT
is disabled
REFRESH_FAST_AFTER_ONETAB_DML   N                 COUNT(*) is not present in the select list 
REFRESH_FAST_AFTER_ONETAB_D
ML   N                 SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML      N                 see the reason why 
                                        REFRESH_FAST_AFTER_ONETAB_DML is disabled 
REFRESH_FAST_AFTER_ANY_DML      N    SH.TIMES
mv log must have sequence
REFRESH_FAST_AFTER_ANY_DML      N    SH.SALES     mv log must have sequence
REFRESH_PCT
 N                 PCT is not possible on any of the detail
                                                  tables in the materiali
zed view
REWRITE_FULL_TEXT_MATCH         Y      
REWRITE_PARTIAL_TEXT_MATCH      Y  
REWRITE_GENERAL                 Y   
REWRITE_PCT
                     N                 PCT is not possible on any detail tables

MV_CAPABILITIES_TABLE.CAPABILITY_NAME Details

Table 8-7 lists explanations for values i n the CAPABILITY_NAME column.

Table 8-7 CAPABILITY_NAME Column Details

If this capability is possible, all query rewrite capabilities are possible, including ge neral query rewrite and full and partial text match query rewrite. If this capability is not possible, at least general query rewrite is not possible.
CAPABILITY_NAME Description
PCT If this capability is possible, Partition Change Tracking (PCT) is possible on at least one detail relation. If th is capability is not possible, PCT is not possible with any detail relation referenced by the materialized view.
REFRESH_COMPLETE If this capability is possible, complete refresh of the materialized view is possible.
REFRESH_FAST If this capability is possible, fast refresh is possible at least under certain circumstances.
REWRITE If this capability is possible, at least full text match query rewrite is possible. If this capability is not possible, no form of query rewrite is possible.
PCT_TABLE If this capability is possible, it is possible with respect to a particular part itioned table in the top level FROM list. When possible, PCT applies to the partitioned table named in the RELATED _TEXT column.

PCT is needed to support fast fresh after partition maintenance operations on the table named in the R ELATED_TEXT column.

PCT may also support fast refresh with regard to updates to the table named in the RELATED_TE XT column when fast refresh from a materialized view log is not possible.

PCT is also needed to support query rewrite i n the presence of partial staleness of the materialized view with regard to the table named in the RELATED_TEXT column.< /p>

When disabled, PCT does not apply to the table named in the RELATED_TEXT column. In this case, fast refresh is no t possible after partition maintenance operations on the table named in the RELATED_TEXT column. In addition, PCT-based refresh of updates to the table named in the RELATED_TEXT column is not possible. Finally, query rewrite cannot be suppo rted in the presence of partial staleness of the materialized view with regard to the table named in the RELATED_TEXT co lumn.

PCT_TABLE_REWRITE If this capability is possible, it is possible with respect to a particular partitio ned table in the top level FROM list. When possible, PCT applies to the partitioned table named in the RELATED_TEX T column.

This capability is needed to support query rewrite against this materialized view in partial stale state with re gard to the table named in the RELATED_TEXT column.

When disabled, query rewrite cannot be supported if this mate rialized view is in partial stale state with regard to the table named in the RELATED_TEXT column.

REFRESH_FAST_AFTER_INSERT If this capability is possible, fast refresh from a materialized view log is possible at least in the c ase where the updates are restricted to INSERT operations; complete refresh is also possible. If this capability is not possible, no form of fast refresh from a materialized view log is possible.
REFRESH_FAST_AFTER_ONETAB_DML If thi s capability is possible, fast refresh from a materialized view log is possible regardless of the type of update operation, provided all update operations are performed on a single table. If this capability is not possible, fast refresh from a materialized view log may not be possible when the update operations are performed on multiple tables.
REFRESH_FAST_AFTER_ANY_DML If this capability is possible, fast refresh from a materialized view log is possible regardless of the type of update operation or the number of tables updated. If this capability is not possible, fast refresh from a materialized view log may not be possible when the update operations (other than INSERT) affect multiple tables.
REFRESH_FAST_PCT If this capabilit y is possible, fast refresh using PCT is possible. Generally, this means that refresh is possible after partition maintenance operati ons on those detail tables where PCT is indicated as possible.
REWRITE_FULL_TEXT_MATCH If this capability is p ossible, full text match query rewrite is possible. If this capability is not possible, full text match query rewrite is not possible .
REWRITE_PARTIAL_TEXT_MATCH If this capability is possible, at least full and partial text match query rewrit e are possible. If this capability is not possible, at least partial text match query rewrite and general query rewrite are not possi ble.
REWRITE_GENERAL
REWRITE_PCT If this capability is possible, query rewrite can use a partially stale material ized view even in QUERY_REWRITE_INTEGRITY = ENFORCED or TRUSTED modes. When this capability is not possible, query rewrite can use a partially stale materialized view only in QUERY_REWRITE_INTEGRITY = STALE_T OLERATED mode.

MV_CAPABILITIES_TABLE Column Details

Table 8-8 lists the semantics for RELATED_TEXT and RELATED_N UM columns.

Table 8-8 MV_CAPABILITIES_TABLE Column Details

MSGNO MSGTXT RELATED_NUM RELATED_TEXT
NULL NULL
For PCT capabi lity only: [owner.]name of the table upon which PCT is enabled< /td>
2066 This statement resulted in an Oracle error Oracle error number that oc curred
2067 No partition key or PMARKER or join depende nt expression in select list in select list
[owner.]name of relation for which PCT is not supported
2068 Relation is not partitioned
[owner.]name of relation for which PC T is not supported
2069 PCT not supported with multicolumn partition key [owner.]name< /em> of relation for which PCT is not supported
2070 PCT not supported with this type of partitioning
[owner.] name of relation for which PCT is not supported
2071 Internal error: undefined PCT failure code< /td> The unrecognized numeric PCT failure code [owner.]name of relation for which PCT is not supporte d
2072 Requirements not satisfied for fast refresh of nested mv

2077 Mv log is newer than last full refresh
[owner.]< /code>table_name of table upon which the mv log is needed
2078 Mv log must have new values
[owner.]table_name of table upon which the mv log is needed
2079 Mv log must have ROWID
[owner.]table_name of table upon which the mv log is needed
2080 Mv log must have primary key
[owner.]table_name of table upon which the mv log is nee ded
2081 Mv log does not have all necessary columns
[owner.]table_name of tab le upon which the mv log is needed
2082 Problem with mv log
[owner.]table_name of table upon which the mv log is needed
2099 Mv references a remote table or view in the FROM list Offset from the SELECT keyword to the table or view in question [owner.]name of the table or view in question
2126 Multiple master sites
Name of the first different node, or NULL if the first different node is local
2129 Join or filter condition(s) are complex
[owner.]name of the table involved with the join or filter condition (or NULL when not available)
2130 Expression not supported for fast refresh Offset from the SELECT keyword to the expression in question The alias name in the select list of the expression in question
2150 Select lists mu st be identical across the UNION operator Offset from the SELEC T keyword to the first different select item in the SELECT list The alias name of the first different select item in the SELECT list
2182 PCT is enabled through a join depe ndency
[owner.]name of relation for which PCT_TABLE_REWRITE is not enabled
2183 Expression to enable PCT not in PARTITION BY of analytic function or spreadsheet The unrecognized numeric PCT failure code [owner.]name of relation for which PCT is not enabled
2184 Expression to enable PCT cannot be rolled up
[owner.]name of relation for which PCT is not enabled
2185 No partition key or PMARKER in the SELECT list
[owner.]< /code>name of relation for which PCT_TABLE_REWRITE is not enabled
2186 GROUP OUTER JOIN is present

2187 materialized view on external table


G o to previous page
Previous		 Go to next page
Next
< /td> 		Oracle
Copyright © 2001, 2003 Oracle Corporation
All R ights Reserved.
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Feedback