5 Basic Materialized Views
This chapter describes the use of materialized views. It contains the following topics:
5.1 Overview of Data Warehousing with Materialized Views
Typically, data flows from one or more online transaction processing (OLTP) database into a data warehouse on a monthly, 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 hundreds of gigabytes to petabytes. Usually, the vast majority of the data is stored in a few very large fact tables.
One technique employed in data warehouses to improve performance is the creation of summaries. Summaries are special types of aggregate views that improve 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 summary table to contain the sums of sales by region and by product.
The summaries or aggregates that are referred to in this book and in literature on data warehousing are created in Oracle Database using a schema object called a materialized view. Materialized views can perform a number of roles, such as improving query performance or providing replicated data.
The database administrator creates one or more materialized views, which are the equivalent of a summary. The end user queries the tables and views at the detail data level. The query rewrite mechanism in the Oracle server automatically rewrites the SQL query to use the summary tables. This mechanism reduces response time for returning results from the query. Materialized views within the data 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 materialized views affects the queries that reference them.
This section contains the following topics:
5.1.1 About Materialized Views for Data Warehouses
In data warehouses, you can use 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 aggregations. A materialized view eliminates the overhead associated with expensive joins and aggregations for a large or important class of queries.
5.1.2 About 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 those sites with conflict resolution methods. These replica materialized views provide local access to data that otherwise would have to be accessed from remote sites. Materialized views are also useful in remote data marts.
5.1.3 About 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.
5.1.4 The Need for Materialized Views
You can use materialized views to increase the speed of queries on very large databases. Queries to large databases often involve joins between tables, aggregations such as SUM
, or both. These operations are expensive in terms of time and processing power. The type of materialized view you create determines how the materialized view is refreshed and used by query rewrite.
Materialized views improve query 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 not to the underlying detail tables. In general, rewriting queries to use materialized views rather than detail tables improves response time. Figure 5-1 illustrates how query rewrite works.
When using query rewrite, create materialized views that satisfy the largest number of queries. For example, if you identify 20 queries that are commonly applied to the detail or fact tables, then you might be able to satisfy them with five or six well-written materialized views. A materialized view definition can include any number of aggregations (AVG
, BIT_AND_AGG
, BIT_OR_AGG
, BIT_XOR_AGG
, COUNT(x)
, COUNT(*)
, COUNT(DISTINCT x)
, KURTOSIS_POP
, KURTOSIS_SAMP
, MAX
, MIN
, SKEWNESS_POP
, SKEWNESS_SAMP
, STDDEV
, SUM
, and VARIANCE
). It can also include any number of joins. If you are unsure of which materialized views to create, Oracle Database provides the SQL Access Advisor, which is a set of advisory procedures in the DBMS_ADVISOR
package to help in designing and evaluating materialized views for query rewrite.
If a materialized view is to be used by query rewrite, it must be stored in the same database as the detail tables on which it depends. A materialized view can be partitioned, and you can define a materialized view on a partitioned table. You can also 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 materialized view, because then it is difficult to change them without affecting the application. Instead, let query rewrite transparently rewrite your query to use the materialized view.
Note that the techniques shown in this chapter illustrate how to use materialized views in data warehouses. Materialized views can also be used by Oracle Replication.
5.1.5 Components of Summary Management
Summary management consists of:
-
Mechanisms to define materialized views and dimensions.
-
A refresh mechanism to ensure that all materialized views contain the latest data.
-
A query rewrite capability to transparently rewrite a query to use a materialized view.
-
The SQL Access Advisor, which recommends materialized views, partitions, and indexes to create.
-
The
TUNE_MVIEW
package, which shows you how to make your materialized view fast refreshable and use general query rewrite.
The use of summary management features imposes no schema restrictions, and can enable some existing DSS database applications to improve performance without the need to redesign the database or the application.
Figure 5-2 illustrates the use of summary management in the warehousing cycle. After the data has been transformed, staged, and loaded into the detail data in the warehouse, you can invoke the summary management process. First, use the SQL Access Advisor to plan how you will use materialized views. Then, create materialized views and design how queries will be rewritten. If you are having problems trying to get your materialized views to work then use TUNE_MVIEW
to obtain an optimized materialized view.
Understanding the summary management process during the earliest stages of data warehouse design can yield large dividends later in the form of higher performance, lower summary administration costs, and reduced storage requirements.
5.1.6 Data Warehousing Terminology
Some basic data warehousing terms are defined as follows:
-
Dimension tables describe the business entities of an enterprise, represented as hierarchical, categorical information such as time, departments, locations, and products. Dimension tables are sometimes called lookup or reference tables.
Dimension tables usually change slowly over time and are not modified on a periodic schedule. They are used in long-running decision support 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 Dimensions for more information.
-
Fact tables describe 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 measure is a numeric or character column of one table such as
fact.sales
. -
A computed measure is an expression involving measures of one table, for example,
fact.revenues
-fact.expenses
. -
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, these 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.
5.1.7 About 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 guidelines. However, you realize significantly greater query execution performance and materialized view refresh performance benefits and you require fewer materialized views if your schema design complies with these guidelines.
A materialized view definition includes any number of aggregates, as well as any number of joins. In several ways, a materialized view behaves like an index:
-
The purpose of a materialized view is to increase query execution performance.
-
The existence of a materialized view is transparent to SQL applications, so that a database administrator can create or drop materialized views at any time without affecting the validity of SQL applications.
-
A materialized view consumes storage space.
-
The contents of the materialized view must be updated when the underlying detail tables are modified.
This section contains the following topics:
5.1.7.1 Schemas and Dimension Tables
In the case of normalized or partially normalized dimension tables (a dimension that is stored in multiple tables), identify how these tables are joined. Note whether the joins between the dimension 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 enabled with constraints, using the NOVALIDATE
and RELY
options if the relationships represented by the constraints are guaranteed by other means. Note that if the joins between fact and dimension tables do not support the parent-child relationship 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 dimensions in any schema that does not satisfy these relationships. Incorrect results can be returned from queries otherwise.
5.1.7.2 Guidelines for Materialized View Schema Design
Before starting to define and use the various components of summary management, you should review your schema design to abide by the following guidelines wherever possible. Guidelines 1 and 2 are more important than guideline 3. If your schema design does not follow guidelines 1 and 2, it does not then matter whether it follows guideline 3. Guidelines 1, 2, and 3 affect both query rewrite performance and materialized view refresh performance.
Dimensions Guideline 1
Dimensions should either be denormalized (each dimension contained in one table) or the joins between tables in a normalized or partially normalized dimension should guarantee that each child-side row joins with exactly one parent-side row.
You can enforce this condition by adding FOREIGN
KEY
and NOT NULL
constraints on the child-side join keys and PRIMARY
KEY
constraints on the parent-side join keys.
Dimensions Guideline 2
If dimensions are denormalized or partially denormalized, hierarchical integrity must be maintained between the key columns of the dimension table. Each child key value must uniquely identify its parent key value, even if the dimension table is denormalized. Hierarchical integrity in a denormalized dimension can be verified by calling the VALIDATE_DIMENSION
procedure of the DBMS_DIMENSION
package.
Dimensions Guideline 3
Fact and dimension tables should similarly guarantee that each fact table row joins with exactly one dimension table row. This condition must be declared, and optionally enforced, by adding FOREIGN
KEY
and NOT
NULL
constraints on the fact key column(s) and PRIMARY
KEY
constraints on the dimension key column(s), or by using outer joins. In a data warehouse, constraints are typically enabled with the NOVALIDATE
and RELY
clauses to avoid constraint enforcement performance overhead.
Dimensions Guideline 4
After each load and before refreshing your materialized view, use the VALIDATE_DIMENSION
procedure of the DBMS_DIMENSION
package to incrementally verify dimensional integrity.
Incremental Loads Guideline
Incremental loads of your detail data should be done using the SQL*Loader direct-path option, or any bulk loader utility that uses Oracle's direct-path interface. This includes INSERT
... AS SELECT
with the APPEND
or PARALLEL
hints, where the hints cause the direct loader log to be used during the insert.
Partitions Guideline
Range/composite partition your tables by a monotonically increasing time column if possible (preferably of type DATE
).
Time Dimensions Guideline
If a time dimension appears in the materialized view as a time column, partition and index the materialized view in the same manner as you have the fact tables.
If you 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 existing 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.
See Also:
-
"Creating Dimensions" for details on the benefits of maintaining a child-side row join with a parent-side row
5.1.8 About Loading Data into Data Warehouses
A popular and efficient way to load data into a data warehouse or data mart is to use a
CREATE TABLE AS SELECT
or INSERT AS SELECT
statement, loading external data using external tables. This allows massively parallel,
complex data loading using the power of SQL inside the database, avoiding any
unnecessary staging.
Alternative ways are to load data using SQL*Loader with the
DIRECT
or PARALLEL
option, Data Pump, or to use
another loader tool that uses the Oracle direct-path API.
Loading strategies can be classified as one-phase or two-phase. In one-phase loading, data is loaded directly into the target table, quality assurance tests are performed, and errors are resolved by performing DML operations prior to refreshing materialized views. If a large number of deletions are possible, then storage utilization can be adversely affected, 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 applied to the data.
-
Referential integrity constraints on the target table are disabled, and the local index in the target partition is marked unusable.
-
The data is copied from the temporary area into the appropriate partition of the target table using
INSERT
AS
SELECT
with thePARALLEL
orAPPEND
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 useALTER TABLE ... EXCHANGE PARTITION
to incorporate the temporary table into the target table. See Oracle Database SQL Language Reference for 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 operation, if desired. You can disable query
rewrite at the system level by issuing an ALTER
SYSTEM
SET
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 session 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 apply when QUERY_REWRITE_INTEGRITY
is either
ENFORCED
or TRUSTED
because the system ensures in
these modes that only materialized views with updated data participate in a query
rewrite.
See Also:
Oracle Database Utilities for the restrictions and
considerations when using SQL*Loader with the DIRECT
or
PARALLEL
keywords
5.1.9 Overview of Materialized View Management Tasks
The motivation for using materialized views is to improve performance, but the overhead associated with materialized view management can become a significant system management problem. When reviewing or evaluating some of the necessary materialized view management activities, consider some of the following:
-
Identifying what materialized views to create initially.
-
Indexing the materialized views.
-
Ensuring that all materialized views and materialized view indexes are refreshed properly each time the database is updated.
-
Checking which materialized views have been used.
-
Determining how effective each materialized view has been on workload performance.
-
Measuring the space being used by materialized views.
-
Determining which new materialized views should be created.
-
Determining which existing materialized views should be dropped.
-
Archiving old detail and materialized view data that is no longer useful.
After the initial effort of creating and populating the data warehouse or data mart, the major administration overhead is the update process, which involves:
-
Periodic extraction of incremental changes from the operational systems.
-
Transforming the data.
-
Verifying that the incremental changes are correct, consistent, and complete.
-
Bulk-loading the data into the warehouse.
-
Refreshing indexes and materialized views so that they are consistent with the detail data.
The update process must generally be performed within a limited period of time known as the update window. The update window depends on the update frequency (such as daily or weekly) and the nature of the business. For a daily update frequency, an update window of two to six hours might be typical.
You need to know your update window for the following activities:
-
Loading the detail data
-
Updating or rebuilding the indexes on the detail data
-
Performing quality assurance tests on the data
-
Refreshing the materialized views
-
Updating the indexes on the materialized views
5.2 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. Besides tables, other elements such as views, inline views (subqueries in the FROM
clause of a SELECT
statement), subqueries, and materialized views can all be joined or referenced in the SELECT
clause. You cannot, however, define a materialized view 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.
The types of materialized views are:
5.2.1 About Materialized Views with Aggregates
In data warehouses, materialized views
normally contain aggregates as shown in Example 5-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:
AVG
, BIT_AND_AGG
, BIT_OR_AGG
,
BIT_XOR_AGG
, COUNT(x)
, COUNT(*)
,
COUNT(x)
, KURTOSIS_POP
, KURTOSIS_SAMP
,
MAX
, MIN
, SKEWNESS_POP
,
SKEWNESS_SAMP
, STDDEV
, SUM
, and
VARIANCE
, 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 is refreshed automatically when a transaction that does DML to one of the materialized view's detail tables commits. The time taken to complete the commit may be slightly longer than usual when this method is chosen. This is because the refresh operation is performed as part of the commit process. Therefore, this method may not be suitable if many users are concurrently changing the tables upon which the materialized view is based.
Here are some examples of materialized views with aggregates. Note that materialized view logs are only created because this materialized view is fast refreshed.
Example 5-1 Creating a Materialized View (Total Number and Value of Sales)
CREATE MATERIALIZED VIEW LOG ON products WITH SEQUENCE, ROWID (prod_id, prod_name, prod_desc, prod_subcategory, prod_subcat_desc, prod_category, prod_cat_desc, prod_weight_class, prod_unit_of_measure, prod_pack_size, supplier_id, prod_status, prod_list_price, prod_min_price) INCLUDING NEW VALUES; 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 product_sales_mv BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE AS SELECT p.prod_name, SUM(s.amount_sold) AS dollar_sales, COUNT(*) AS cnt, COUNT(s.amount_sold) AS cnt_amt FROM sales s join products p ON (s.prod_id = p.prod_id) GROUP BY p.prod_name;
Note:
As of Oracle Database 23ai, materialized views support full rewrite capabilities for SQL statements using ANSI join syntax and for materialized view definitions using ANSI join syntax. You can use either ANSI or Oracle syntax in your MV definition and your SQL statements. Any SQL statement will be rewritten as much as possible, independent of the chosen join syntax in either the MV or the SQL statement. In the above example, the join in theCREATE MATERIALIZED VIEW
statement is expressed in ANSI syntax:
FROM sales s join products p
ON (s.prod_id = p.prod_id) GROUP BY p.prod_name
Oracle
syntax is also valid:
FROM sales s, products p
WHERE s.prod_id = p.prod_id GROUP BY p.prod_name
This example creates a materialized view product_sales_mv
that computes total number and value of sales for a product. It is derived by joining 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 default refresh method is FAST
, which is allowed because the appropriate materialized view logs have been created on tables products
and sales
.
You can achieve better fast refresh performance for local materialized views if you use a materialized view log that contains a WITH
COMMIT
SCN
clause. An example is the following:
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID(prod_id, cust_id, time_id), COMMIT SCN INCLUDING NEW VALUES;
Example 5-2 Creating a Materialized View (Computed Sum of Sales)
CREATE MATERIALIZED VIEW product_sales_mv_agg 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;
Using ANSI join syntax to illustrate the same example, the equivalent materialized view would look like this:
CREATE MATERIALIZED VIEW product_sales_mv_agg
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT p.prod_name, SUM(s.amount_sold) AS dollar_sales
FROM sales s join products p on (s.prod_id = p.prod_id)
GROUP BY p.prod_name;
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 materialized view does not initially contain any data, because the build method is DEFERRED
. A complete refresh is required for the first refresh of a build deferred materialized view. When it is refreshed and once populated, this materialized view can be used by query rewrite.
Example 5-3 Creating a Materialized View (Aggregates on a Single Table)
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 PARALLEL BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT s.prod_id, s.time_id, COUNT(*) AS count_grp, SUM(s.amount_sold) AS sum_dollar_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 changes are reflected in the materialized view when the commit is issued.
See Also:
Oracle Database SQL
Language Reference for syntax of the CREATE
MATERIALIZED
VIEW
and CREATE
MATERIALIZED
VIEW
LOG
statements
5.2.1.1 Requirements for Using Materialized Views with Aggregates
Table 5-1 illustrates the aggregate requirements for materialized views. If aggregate X
is present, aggregate Y
is required and aggregate Z
is optional.
Table 5-1 Requirements for Materialized Views with Aggregates
X | Y | Z |
---|---|---|
|
- |
- |
|
- |
- |
|
- |
- |
|
- |
- |
|
- |
- |
|
- |
- |
|
|
- |
|
- |
- |
|
|
|
|
|
|
|
|
|
|
|
SUM(expr^2) COUNT(expr^2) SUM(expr^3) COUNT(expr^3) |
|
|
SUM(expr^2) COUNT(expr^2) |
Note that COUNT(*)
must always be present to guarantee all types of fast refresh. Otherwise, you may be limited to fast refresh after inserts only. Oracle recommends that you include the optional aggregates in column Z
in the materialized view in order to obtain the most efficient and accurate fast refresh of the aggregates.
5.2.2 About Materialized Views Containing Only Joins
Some materialized views contain only joins and no aggregates , such as in Materialized Join Views FROM Clause Considerations, where a materialized view is created that joins the sales
table to the times
and customers
tables. The advantage of creating this type of materialized view is that expensive joins are precalculated.
Note:
As of Oracle Database 23ai, ANSI and Oracle join syntax are interchangeable. Both are fully supported.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 materialized view containing only joins can be defined to be refreshed ON COMMIT
or ON
DEMAND
. If it is ON
COMMIT
, the refresh is performed at commit time of the transaction that does DML on the materialized view's detail table.
If you specify REFRESH
FAST
, Oracle Database performs further verification of the query definition to ensure that fast refresh can be performed if any of the detail tables change. These additional checks are:
-
A materialized view log must be present for each detail table unless the table supports partition change tracking (PCT). Also, when a materialized view log is required, the
ROWID
column must be present in each materialized view log. -
The rowids of all the detail tables must appear in the
SELECT
list of the materialized view query definition.
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 possible. If one of the tables did not meet all of the criteria, but the other tables did, the materialized view would still be fast refreshable with respect to the other tables for which all the criteria are met.
To achieve an optimally efficient refresh, you should ensure that the defining query does not use an outer join that behaves like an inner join. If the defining query contains such a join, consider rewriting the defining query to contain an inner join.
See Also:
-
"Restrictions on Fast Refresh on Materialized Views with Joins Only" for more information regarding the conditions that cause refresh performance to degrade.
-
"About Partition Change Tracking (PCT) Refresh for Materialized Views"
5.2.2.1 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 times in the FROM
list) must be present in the SELECT
list of the materialized view.
If the materialized view has remote tables in the FROM
clause, all tables in the FROM
clause must be located on that same site in order to perform incremental (fast) refresh for the materialized view. Further, ON
COMMIT
refresh is not supported for materialized view with remote tables. Except for SCN-based materialized view logs, 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, as shown in the following example.
Example 5-4 Materialized View Containing 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 PARALLEL 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(+);
Alternatively, if the previous example did not include the columns
times_rid
and customers_rid
, and if the refresh
method was REFRESH
FORCE
, 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. Also note that this version uses ANSI join
syntax. Both ANSI join and Oracle join syntax are equally supported.
CREATE MATERIALIZED VIEW detail_sales_mv2 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 RIGHT OUTER JOIN times t ON t.time_id = s.time_id RIGHT OUTER JOIN customers c ON c.cust_id = s.cust_id;
5.2.3 About Nested Materialized Views
A nested materialized view is a materialized view whose definition is based on another materialized view. A nested materialized view can reference other relations in the database in addition to referencing materialized views.
This section contains the following topics:
5.2.3.1 Why Use Nested Materialized Views?
In a data warehouse, you typically create many aggregate views on a single join (for example, rollups along different dimensions). Incrementally maintaining these distinct materialized aggregate views can take a long time, because the underlying join has to be performed many times.
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 efficient.
Example 5-5 Nested Materialized View
You can create a nested materialized view on materialized views, but all parent and base materialized views must contain joins or aggregates. If the defining queries for a materialized view do not contain joins or aggregates, it cannot be nested. 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 all 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 MATERIALIZED VIEW LOG ON sales WITH ROWID; CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID; CREATE MATERIALIZED VIEW LOG ON times WITH ROWID; /*create materialized view join_sales_cust_time as fast refreshable at COMMIT time */ CREATE MATERIALIZED VIEW join_sales_cust_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 materialized view log on the table. Because this will be a single-table aggregate materialized view on join_sales_cust_time
, you must 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 REFRESH FAST ON COMMIT AS SELECT COUNT(*) cnt_all, SUM(amount_sold) sum_sales, COUNT(amount_sold) cnt_sales, cust_last_name, day_number_in_week FROM join_sales_cust_time GROUP BY cust_last_name, day_number_in_week;
5.2.3.2 About 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. You can refresh a tree of nested materialized views in the appropriate dependency order by specifying the nested = TRUE
parameter 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.
5.2.3.3 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 must ensure that all materialized views in a tree are refreshed in the correct dependency order before refreshing the highest-level. You can automatically refresh intermediate materialized views in a nested hierarchy using the
nested = TRUE
parameter, as described in "About Nesting Materialized Views with Joins and Aggregates". If you do not specifynested = TRUE
and the materialized 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 must ensure that all materialized views in a tree are refreshed. If you only refresh the highest-level materialized view, the materialized views under it will be stale and you must explicitly refresh them. If you use the
REFRESH
procedure with thenested
parameter value set toTRUE
, only specified materialized views and their child materialized views in the tree are refreshed, and not their top-level materialized views. Use theREFRESH_DEPENDENT
procedure with the nested parameter value set toTRUE
if you want to ensure that all materialized views in a tree are refreshed. -
If complete refresh is the only refresh option supported for a particular nested materialized view, then a complete refresh is performed even when a fast refresh is specified.
-
Freshness of a materialized view is calculated relative to the objects directly referenced by the materialized view. When a materialized view references another materialized view, the freshness of the topmost materialized view is calculated relative to changes in the materialized view it directly references, not relative to changes in the tables referenced by the materialized view it references.
5.2.3.4 Restrictions When Using Nested Materialized Views
You cannot create both a materialized view and a prebuilt materialized view on the same table. For example, If you have a table costs
with a materialized view cost_mv
based on it, you cannot then create a prebuilt materialized view on table costs
. The result would make cost_mv
a nested materialized view and this method of conversion is not supported.
5.3 Creating Materialized Views
A materialized view can be created with the CREATE
MATERIALIZED
VIEW
statement.
It is not uncommon in a data warehouse to have already created summary or aggregation tables, and you might not wish to repeat this work by building a new materialized view. In this case, the table that already exists in the database can be registered as a prebuilt materialized view. This technique is described in "Registering Existing Materialized Views".
Once you have selected the materialized views you want to create, follow these steps for each materialized view.
-
Design the materialized view. Existing user-defined materialized views do not require this step.
If the materialized view contains many rows, then, if appropriate, the materialized view should be partitioned (if possible) and should match the partitioning of the largest or most frequently updated detail or fact table (if possible). Refresh performance benefits from partitioning, because it can take advantage of parallel DML capabilities and possible PCT-based refresh.
-
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
ON
PREBUILT
TABLE
clause in theCREATE
MATERIALIZED
VIEW
statement. Otherwise, use theBUILD
IMMEDIATE
clause to populate the materialized view immediately, or theBUILD
DEFERRED
clause to populate the materialized view later. ABUILD
DEFERRED
materialized view is disabled for use by query rewrite until the firstCOMPLETE
REFRESH
, after which it is automatically enabled, provided theENABLE
QUERY
REWRITE
clause has been specified.
Example 5-6 Creating a Materialized View
This example illustrates creating a materialized view called cust_sales_mv
.
CREATE MATERIALIZED VIEW cust_sales_mv PARALLEL BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITE AS SELECT c.cust_last_name, SUM(amount_sold) AS sum_amount_sold FROM customers c, sales s WHERE s.cust_id = c.cust_id GROUP BY c.cust_last_name;
Note:
Both Oracle join syntax (shown above) and ANSI join syntax are supported. Below is the same example rewritten to use ANSI join syntax.CREATE MATERIALIZED VIEW cust_sales_mv PARALLEL BUILD IMMEDIATE REFRESH COMPLETE ENABLE QUERY REWRITE AS SELECT c.cust_last_name, SUM(amount_sold) AS sum_amount_sold FROM customers c JOIN sales s ON s.cust_id = c.cust_id GROUP BY c.cust_last_name;
Example 5-7 Creating a Materialized View with JSON Columns
This example creates a materialized view based on a table
purchase_order
that contains a column of data type
JSON
.
CREATE MATERIALIZED VIEW po_mv
BUILD IMMEDIATE
REFRESH FAST ON STATEMENT WITH ROWID
AS
SELECT o.rowid AS id, v.*
FROM purchase_order o,
JSON_TABLE(o.c FORMAT json, '$' error on error null on empty
COLUMNS
(
poNum varchar2(10) PATH '$.poNum',
poDate varchar2(12) PATH '$.poDate',
NESTED PATH '$.items[*]'
COLUMNS
(
item_seq for ordinality,
itemName varchar2(10) PATH '$.itemName',
itemPrice number PATH '$.itemPrice',
itemQuantity varchar2(10) PATH '$.itemQuantity'
)
)
) v;
See Also:
Oracle Database SQL
Language Referencefor descriptions of the SQL
statements CREATE
MATERIALIZED
VIEW
, ALTER
MATERIALIZED
VIEW
, and DROP
MATERIALIZED
VIEW
5.3.1 Creating Materialized Views with Column Alias Lists
Currently, when a materialized 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
statement fails 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 when the text of the materialized view's defining query and the text of user input query are identical. Thus, if the user specifies select aliases in the materialized view's defining query while there is no alias in the query, the full text match comparison fails. 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 Q1
does not have the aliases. The full text match rewrite fails. 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, c1.time_id FROM sales s, products p, costs c1 WHERE s.prod_id = p.prod_id AND c1.prod_id = p.prod_id AND p.prod_name IN (SELECT prod_name FROM products);
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
MATERIALIZED
VIEW
statement. The column alias list explicitly resolves any column name conflict without attaching aliases in the SELECT
clause of the materialized view. The syntax of the materialized view column alias list is illustrated in the following example:
CREATE MATERIALIZED VIEW sales_mv (sales_tid, costs_tid) ENABLE QUERY REWRITE 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 takes 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
clause.
5.3.2 Creating Materialized Views Based on Hybird Partitioned Tables
Use the CREATE MATERIALIZED VIEW
statement to create a materialized view that is based on a hybrid partitioned table.
In a hybrid partitioned table, some partitions are stored in database segments, whereas other partitions are stored externally. If a materialized view that is based on a hybrid partitioned table includes the partition key or partition marker in its SELECT
statement, it meets the requirements for PCT refresh.
To create a materialized view based on a hybrid partitioned table:
5.3.3 About Materialized Views Names
The name of a materialized view must conform to standard Oracle naming conventions. However, if the materialized view is based on a user-defined prebuilt table, then the name of the materialized view must exactly match that table name.
If you already have a naming convention for tables and indexes, you might 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.
5.3.4 About Storage And Table Compression for Materialized Views
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 requires, then the DBMS_MVIEW.ESTIMATE_MVIEW_SIZE
package can estimate the number of bytes required to store this uncompressed materialized view. This information can then assist the design team in determining 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
clause. Table compression reduces disk use and memory use (specifically, the buffer cache), often leading to a better scaleup for read-only operations. Table compression can also speed up query execution at the expense of update cost.
See Also:
-
Oracle Database VLDB and Partitioning Guide for more information about table compression
-
Oracle Database Administrator’s Guide for more information about table compression
-
Oracle Database SQL Language Reference for a complete description of
STORAGE
semantics
5.3.5 About Build Methods for Materialized Views
Two build methods are available for creating the materialized view, as shown in Table 5-2. If you select BUILD
IMMEDIATE
, the materialized view definition is added to the schema objects in the data dictionary, and then the fact or detail tables are scanned according to the SELECT
expression and the results are stored in the materialized view. Depending on the size of the tables to be scanned, this build process can take a considerable amount of time.
An alternative approach is to use the BUILD
DEFERRED
clause, which creates the materialized view without data, thereby enabling it to be populated at a later date using the DBMS_MVIEW.REFRESH
package.
See Also:
Table 5-2 Build Methods
Build Method | Description |
---|---|
|
Create the materialized view and then populate it with data. |
|
Create the materialized view definition but do not populate it with data. |
5.3.6 About Enabling Query Rewrite for Materialized Views
Before creating a materialized view, you can verify what types of query rewrite are possible by calling the procedure DBMS_MVIEW.EXPLAIN_MVIEW
, or use DBMS_ADVISOR.TUNE_MVIEW
to optimize the materialized view so that many types of query rewrite are possible. Once the materialized view has been created, you can use DBMS_MVIEW.EXPLAIN_REWRITE
to find out if (or why not) it will rewrite a specific query.
Even though a materialized view is defined, it will not automatically be used by the query rewrite facility. Even 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 subsequently 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 with data through a complete refresh.
5.3.7 About Query Rewrite Restrictions
Query rewrite is not possible with all materialized views. If query rewrite is not occurring when expected, DBMS_MVIEW.EXPLAIN_REWRITE
can help provide reasons why a specific query is not eligible for rewrite. If this shows that not all types of query rewrite are possible, use the procedure 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:
5.3.7.1 About Materialized View Restrictions for Query Rewrite
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
LONG
orLONG
RAW
data types or objectREF
s. -
If the materialized view was registered as
PREBUILT
, the precision of the columns must agree with the precision of the correspondingSELECT
expressions unless overridden by theWITH
REDUCED
PRECISION
clause. -
The defining query cannot contain any references to objects or
XMLTYPE
s. -
A materialized view is a noneditioned object and cannot depend on editioned objects unless it mentions an evaluation edition in which names of editioned objects are to be resolved.
-
A materialized view may only be eligible for query rewrite in a specific range of editions. The
query_rewrite_clause
in theCREATE
orALTER
MATERIALIZED
VIEW
statement lets you specify the range of editions in which a materialized view is eligible for query rewrite.
5.3.7.2 General Query Rewrite Restrictions
You should keep in mind the following restrictions:
-
A query can reference both local and remote tables. Such a query can be rewritten as long as an eligible materialized view referencing the same tables is available locally.
-
Neither the detail tables nor the materialized view can be owned by
SYS
. -
If a column or expression is present in the
GROUP
BY
clause of the materialized view, it must also be present in theSELECT
list. -
Aggregate functions must occur only as the outermost part of the expression. That is, aggregates such as
AVG(AVG(x))
orAVG(x)
+AVG(x)
are not allowed. -
CONNECT
BY
clauses are not allowed.
5.3.8 About Refresh Options for Materialized Views
When you define a materialized view, you can specify three refresh options: how to refresh, what type of refresh, and can trusted constraints be used. If unspecified, the defaults are assumed as ON
DEMAND
, FORCE
, and ENFORCED
constraints respectively.
Note:
As of Oracle Database 23ai, refresh support for JSON table materialized views includes the ability to fast refresh more types of materialized views of JSON tables, as well as Query Rewrite support for these materialized views. Performance for JSON table materialized views is improved through fast refresh of both single-table and multi-table MJVs and MAVs (Materialized Aggregate Views), as well as fast refresh of sub-query materialized views that generates JSON data. In previous releases, materialized view support on JSON data is limited to MJVs (Materialized View Join Views) on a single table only. In addition, Query Rewrite support for JSON table materialized views as of Oracle Database 23ai provides query performance that is generally an order of magnitude faster than in previous releases.
5.3.8.1 About Refresh Modes for Materialized Views
The refresh execution modes are ON COMMIT
, ON DEMAND
, and ON STATEMENT
. Depending on the materialized view you create, some options may not be available. Table 5-3 describes the refresh modes.
Table 5-3 Refresh Modes
Refresh Mode | Description |
---|---|
|
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 |
|
Refresh occurs when a user manually executes one of the available refresh procedures contained in the |
|
Refresh occurs automatically, without the need to commit the transaction, when a DML operation is performed on any of the materialized view’s base tables. This method does not require the creation of materialized view logs on materialized view’s base tables. This mode can be used as long as the materialized view is fast refreshable. |
When using the ON STATEMENT
or ON COMMIT
method, the time to complete a DML or commit may be slightly longer than usual. This is because the refresh operation is performed as part of the DML (for ON STATEMENT
refresh) or as part of the commit (for ON COMMIT
refresh). Therefore, these methods 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 with the refresh of that materialized view, and that materialized view includes joins and aggregation, Oracle recommends you use ON COMMIT
fast refresh rather than ON DEMAND
fast refresh.
If you think the materialized view did not refresh, check the alert log or trace file.
If a materialized view fails during refresh at DML or commit time, you must explicitly invoke the refresh procedure using the DBMS_MVIEW
package after addressing the errors specified in the trace files. Until this is done, the materialized view will no longer be refreshed automatically at commit time.
5.3.8.2 About Types of Materialized View Refresh
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 5-4 describes the refresh options.
Table 5-4 Refresh Options
Refresh Option | Description |
---|---|
|
Refreshes by recalculating the materialized view's defining query. |
|
Applies incremental changes to refresh the materialized view using the information logged in the materialized view logs, or from a SQL*Loader direct-path or a partition maintenance operation. |
|
Applies |
|
Indicates that the materialized view will not be refreshed with refresh mechanisms. |
Whether the fast refresh option is available depends upon the type of materialized view. You can call the procedure DBMS_MVIEW.EXPLAIN_MVIEW
to determine whether fast refresh is possible.
5.3.8.3 About Using Trusted Constraints and Materialized View Refresh
You can also specify if it is acceptable to use trusted constraints and QUERY_REWRITE_INTEGRITY = TRUSTED
during refresh. Any nonvalidated RELY
constraint is a trusted constraint. For example, nonvalidated foreign key/primary key relationships, functional dependencies defined in dimensions or a materialized view in the UNKNOWN
state. If query rewrite is enabled during refresh, these can improve the performance of refresh by enabling more performant query rewrites. Any materialized view that can use TRUSTED
constraints for refresh is left in a state of trusted freshness (the UNKNOWN
state) after refresh.
This is reflected in the column STALENESS
in the view USER_MVIEWS
. The column UNKNOWN_TRUSTED_FD
in the same view is also set to Y
, which means yes.
You can define this property of the materialized view either during create time by specifying REFRESH USING TRUSTED [ENFORCED] CONSTRAINTS
or by using ALTER MATERIALIZED VIEW
DDL.
Table 5-5 Constraints
Constraints to Use | Description |
---|---|
TRUSTED CONSTRAINTS |
Refresh can use trusted constraints and The |
ENFORCED CONSTRAINTS |
Refresh can use validated constraints and |
The fast refresh of a materialized view is optimized using the available primary and foreign key constraints on the join columns. This foreign key/primary key optimization can significantly improve refresh performance. For example, for a materialized view that contains a join between a fact table and a dimension table, if only new rows were inserted into the dimension table with no change to the fact table since the last refresh, then there will be nothing to refresh for this materialized view. The reason is that, because of the primary key constraint on the join column(s) of the dimension table and foreign key constraint on the join column(s) of the fact table, the new rows inserted into the dimension table will not join with any fact table rows, thus there is nothing to refresh. Another example of this refresh optimization is when both the fact and dimension tables have inserts since the last refresh. In this case, Oracle Database will only perform a join of delta fact table with the dimension table. Without the foreign key/primary key optimization, two joins during the refresh would be required, a join of delta fact with the dimension table, plus a join of delta dimension with an image of the fact table from before the inserts.
Note that this optimized fast refresh using primary and foreign key constraints on the join columns is available with and without constraint enforcement. In the first case, primary and foreign key constraints are enforced by the Oracle Database. This, however, incurs the cost of constraint maintenance. In the second case, the application guarantees primary and foreign key relationships so the constraints are declared RELY NOVALIDATE
and the materialized view is defined with the REFRESH FAST USING TRUSTED CONSTRAINTS
option.
5.3.8.4 General Restrictions on Fast Refresh
The defining query of the materialized view is restricted as follows:
-
The materialized view must not contain references to non-repeating expressions like
SYSDATE
andROWNUM
. -
The materialized view must not contain references to
RAW
orLONG
RAW
data types. -
It cannot contain a
SELECT
list subquery. -
It cannot contain analytic functions (for example,
RANK
) in theSELECT
clause. -
It cannot reference a table on which an
XMLIndex
index is defined. -
It cannot contain a
MODEL
clause. -
It cannot contain a
HAVING
clause with a subquery. -
It cannot contain nested queries that have
ANY
,ALL
, orNOT
EXISTS
. -
It cannot contain a
[START WITH …] CONNECT BY
clause. -
It cannot contain multiple detail tables at different sites.
-
ON
COMMIT
materialized views cannot have remote detail tables. -
Nested materialized views must have a join or aggregate.
-
Materialized join views and materialized aggregate views with a
GROUP
BY
clause cannot select from an index-organized table. -
It cannot be based on a remote view. Only complete refresh and force refresh is supported for materialized views based on remote views.
If fast refresh is required, then create the materialized view based on the remote table on which the remote view is based.
5.3.8.5 Restrictions on Fast Refresh on Materialized Views with Joins Only
Defining queries for materialized views with joins only and no aggregates have the following restrictions on fast refresh:
-
All restrictions from "General Restrictions on Fast Refresh".
-
They cannot have
GROUP
BY
clauses or aggregates. -
Rowids of all the tables in the
FROM
list must appear in theSELECT
list of the query. -
Materialized view logs must exist with rowids for all the base tables in the
FROM
list of the query. -
You cannot create a fast refreshable materialized view from multiple tables with simple joins that include an object type column in the
SELECT
statement.
Also, the refresh method you choose will not be optimally efficient if:
-
The defining query uses an outer join that behaves like an inner join. If the defining query contains such a join, consider rewriting the defining query to contain an inner join.
-
The
SELECT
list of the materialized view contains expressions on columns from multiple tables.
5.3.8.6 Restrictions on Fast Refresh on Materialized Views with Aggregates
Defining queries for materialized views with aggregates or joins have the following restrictions on fast refresh:
-
All restrictions from "General Restrictions on Fast Refresh".
Fast refresh is supported for both ON
COMMIT
and ON
DEMAND
materialized views, however the following restrictions apply:
-
All tables in the materialized view must have materialized view logs, and the materialized view logs must:
-
Contain all columns from the table referenced in the materialized view.
-
Specify with
ROWID
andINCLUDING
NEW
VALUES
. -
Specify the
SEQUENCE
clause if the table is expected to have a mix of inserts/direct-loads, deletes, and updates.
-
-
Only
AVG
,BIT_AND_AGG
,BIT_OR_AGG
,BIT_XOR_AGG
,COUNT
,KURTOSIS_POP
,KURTOSIS_SAMP
,MIN
,MAX
,SKEWNESS_POP
,SKEWNESS_SAMP
,STDDEV
,SUM
, andVARIANCE
are supported for fast refresh. -
You must specify
COUNT(*)
. -
Aggregate functions must occur only as the outermost part of the expression. That is, aggregates such as
AVG(AVG(x))
orAVG(x)
+AVG(x)
are not allowed. -
For each aggregate such as
AVG(expr)
, the correspondingCOUNT(expr)
must be present. Oracle recommends that you specifySUM(expr)
. -
If you specify
VARIANCE(expr)
orSTDDEV(expr)
, you must also specifyCOUNT(expr)
andSUM(expr)
. Oracle recommends that you specifySUM(expr *expr)
. -
If you specify
KURTOSIS_POP
,KURTOSIS_SAMP
,SKEWNESS_POP
, orSKEWNESS_SAMP
, you must also specifyCOUNT(expr)
andSUM(expr)
. ForSKEWNESS_POP
, andSKEWNESS_SAMP
, you must also specifyVARIANCE(expr)
andCOUNT(*)
. -
The
SELECT
column in the defining query cannot be a complex expression with columns from multiple base tables. A possible workaround to this is to use a nested materialized view. -
The
SELECT
list must contain allGROUP
BY
columns. -
The materialized view is not based on one or more remote tables.
-
If you use a
CHAR
data type in the filter columns of a materialized view log, the character sets of the primary site and the materialized view must be the same. -
If the materialized view has one of the following, then fast refresh is supported only on conventional DML inserts and direct loads.
-
Materialized views with
MIN
orMAX
aggregates -
Materialized views which have
SUM(expr)
but noCOUNT(expr)
-
Materialized views without
COUNT(*)
Such a materialized view is called an insert-only materialized view.
-
-
A materialized view with
MAX
orMIN
is fast refreshable after delete or mixed DML statements if it does not have aWHERE
clause.The max/min fast refresh after delete or mixed DML does not have the same behavior as the insert-only case. It deletes and recomputes the max/min values for the affected groups. You need to be aware of its performance impact.
-
Materialized views with named views or subqueries in the
FROM
clause can be fast refreshed provided the views can be completely merged. For information on which views will merge, see Oracle Database SQL Language Reference. -
If there are no outer joins, you may have arbitrary selections and joins in the
WHERE
clause. -
Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified. Also, unique constraints must exist on the join columns of the inner join table. If there are outer joins, all the joins must be connected by
AND
s and must use the equality (=
) operator. -
For materialized views with
CUBE
,ROLLUP
, grouping sets, or concatenation of them, the following restrictions apply:-
The
SELECT
list should contain grouping distinguisher that can either be aGROUPING_ID
function on allGROUP
BY
expressions orGROUPING
functions one for eachGROUP
BY
expression. For example, if theGROUP
BY
clause of the materialized view is "GROUP
BY
CUBE(a, b)
", then theSELECT
list should contain either "GROUPING_ID(a, b)
" or "GROUPING(a)
AND
GROUPING(b)
" for the materialized view to be fast refreshable. -
GROUP
BY
should not result in any duplicate groupings. For example, "GROUP BY a, ROLLUP(a, b)
" is not fast refreshable because it results in duplicate groupings "(a), (a, b), AND (a)
".
-
5.3.8.7 Restrictions on Fast Refresh on Materialized Views with UNION ALL
Materialized views with the UNION
ALL
set operator support the REFRESH
FAST
option if the following conditions 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: TheUNION
ALL
can be in a subquery in theFROM
clause provided the defining query is of the formSELECT * FROM
(view or subquery withUNION
ALL
) as in the following example:CREATE VIEW view_with_unionall AS (SELECT c.rowid crid, c.cust_id, 2 umarker FROM customers c WHERE c.cust_last_name = 'Smith' 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_with_unionall
satisfies the requirements for fast refresh. -
Each query block in the
UNION
ALL
query must satisfy the requirements of a fast refreshable materialized view with aggregates or a fast refreshable materialized view with joins.The appropriate materialized view logs must be created on the tables as required for the corresponding 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 theSELECT
list and in the materialized view log. This is shown in the defining query of the viewview_with_unionall
. -
The
SELECT
list of each query must include aUNION
ALL
marker, and theUNION
ALL
column must have a distinct constant numeric or string value in eachUNION
ALL
branch. Further, the marker column must appear in the same ordinal position in theSELECT
list of each query block. See "UNION ALL Marker and Query Rewrite" for more information regardingUNION
ALL
markers. -
Some features such as outer joins, insert-only aggregate materialized view queries and remote tables are not supported for materialized views with
UNION
ALL
. Note, however, that materialized views used in replication, which do not contain joins or aggregates, can be fast refreshed whenUNION
ALL
or remote tables are used. -
The compatibility initialization parameter must be set to 9.2.0 or higher to create a fast refreshable materialized view with
UNION
ALL
.
5.3.8.8 About Achieving Refresh Goals
In addition to the EXPLAIN_MVIEW
procedure, 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 ENABLE
QUERY
REWRITE
goals.
5.3.8.8.1 Refreshing Materialized Views on Prebuilt Tables
For materialized views created with the prebuilt option, the index I_snap$
is not created by default. This index helps fast refresh performance, and a description of how to create this index is illustrated in "Choosing Indexes for Materialized Views".
5.3.8.9 Refreshing Nested Materialized Views
A nested 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_MVIEW.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 chain is refreshed and the coverage starting from the specified materialized view in top-down fashion. That is, the specified materialized view and all its child materialized views in the dependency hierarchy are refreshed in order. With DBMS_MVIEW.REFRESH_DEPENDENT
, the entire chain is refreshed from the bottom up. That is, all the parent materialized views in the dependency hierarchy starting from the specified table are refreshed in order.
Example 5-8 Example of Refreshing a Nested Materialized View
The following statement shows an example of refreshing a nested materialized view:
DBMS_MVIEW.REFRESH('SALES_MV,COST_MV', nested => TRUE);
This statement will first refresh all child materialized views of sales_mv
and cost_mv
based on the dependency 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.
5.3.9 ORDER BY Clause in Materialized Views
An ORDER
BY
clause is allowed in the CREATE
MATERIALIZED
VIEW
statement. It is used only during the initial creation of the materialized view. It is not used during a full refresh or a fast refresh.
To improve the performance of queries against large materialized views, store the rows in the materialized view in the order specified in the ORDER
BY
clause. This initial ordering provides physical clustering of the data. If indexes are built on the columns by which the materialized view is ordered, accessing the rows of the materialized view using the index 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 definition. 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
BY
clause. This feature is similar to the CREATE
TABLE
... ORDER
BY
capability.
5.3.10 Using Oracle Enterprise Manager to Create Materialized Views
A materialized view can also be created using Enterprise Manager by selecting the materialized view object type. There is no difference in the information required if this approach is used.
5.3.11 Using Materialized Views with NLS Parameters
When using certain materialized views, you must ensure that your NLS parameters are the 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 <= "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 case of query rewrite or an inconsistent materialized view after a refresh operation.
-
Expressions that generate internal conversion to character data in the
SELECT
list of a materialized view, or inside an aggregate of a materialized aggregate view. This restriction does not apply to expressions that involve only numeric data, for example,a+b
wherea
andb
are numeric fields.
5.3.12 Adding Comments to Materialized Views
You can add comments to materialized views.
Example: Adding Comments to a Materialized View
The following statement adds a comment to data dictionary views for an existing materialized view:
COMMENT ON MATERIALIZED VIEW sales_mv IS 'sales materialized view';
To view the comment after the preceding statement execution, you can query the catalog views, {USER
, DBA}
ALL_MVIEW_COMMENTS
. For example, consider the following example:
SELECT MVIEW_NAME, COMMENTS FROM USER_MVIEW_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 table. The following error message will be thrown if it is issued.
ORA-12098: cannot comment on the materialized view.
In the case of a prebuilt table, if it has an existing comment, the comment will be inherited by the materialized 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 associated 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 you drop the materialized view, the comment will be passed to the prebuilt table with the comment: '(from materialized view) Sales summary data'
.
5.4 Creating Materialized View Logs
Materialized view logs are required if you want to use fast refresh, with the exception of partition change tracking refresh. That is, if a detail table supports partition change tracking for a materialized view, the materialized view log on that detail table is not required in order to do fast refresh on that materialized view. As a general rule, though, you should create materialized view logs if you want to use fast 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 unless there is another materialized view on top of that materialized view, which is the case with nested materialized views. For fast refresh of materialized 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
NEW
VALUES
clause and the SEQUENCE
clause. You can typically achieve better fast refresh performance of local materialized views containing aggregates or joins by using a WITH
COMMIT
SCN
clause.
An example of a materialized view log is shown as follows where one is created on the table sales
:
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) INCLUDING NEW VALUES;
Alternatively, you could create a commit SCN-based materialized view log as follows:
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold), COMMIT SCN INCLUDING NEW VALUES;
Oracle recommends that the keyword SEQUENCE
be included in your materialized view log statement unless you are 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 refresh with a combination of INSERT
, UPDATE
, or DELETE
statements on multiple tables. You can, however, add the SEQUENCE
number to the materialized view log after it has been created.
The boundary of a mixed DML 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 transaction because the refresh of the materialized view will occur upon commit of this transaction. -
For
ON
DEMAND
, the mixed DML statements occur between refreshes. The following example of a materialized view log illustrates where one is created on the tablesales
that includes theSEQUENCE
keyword: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;
This section contains the following topics:
5.4.1 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 Database ignores the redundancy and does not return an error.
5.4.2 Purging Materialized View Logs
Purging materialized view logs can be done during the materialized view refresh process or deferred until later, thus improving refresh performance time. You can choose different options for when the purge will occur, using a PURGE
clause, as in the following:
CREATE MATERIALIZED VIEW LOG ON sales PURGE START WITH sysdate NEXT sysdate+1 WITH ROWID (prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold) INCLUDING NEW VALUES;
You can also query USER_MVIEW_LOGS
for purge information, as in the following:
SELECT PURGE_DEFERRED, PURGE_INTERVAL, LAST_PURGE_DATE, LAST_PURGE_STATUS FROM USER_MVIEW_LOGS WHERE LOG_OWNER "SH" AND PRIMARY = 'SALES';
In addition to setting the purge when creating a materialized view log, you can also modify an existing materialized view log by issuing a statement resembling the following:
ALTER MATERIALIZED VIEW LOG ON sales PURGE IMMEDIATE;
See Also:
Oracle Database SQL Language Reference for more information regarding materialized view log syntax
5.5 Creating Materialized Views Based on Approximate Queries
A materialized view based on approximate queries uses SQL functions that return approximate functions in its defining query.
You can compute summary and aggregate approximations and store these results in materialized views for further analysis or querying. The summary approximation, which computes approximate aggregates for all dimensions within a group of rows, can be used to perform detailed aggregation. You can further aggregate the summary data to obtain aggregate approximations that can be used for high-level analysis so that the Oracle Database does not scan the base tables again to compute higher-level aggregates. Oracle Database does not scan the base tables again to compute higher-level aggregates. It just uses the existing aggregated results to compute the higher-level aggregates. For example, you can create a summary approximation that stores the approximate number of products sold within each state and within each country. This aggregate approximation is then used to return the approximate distinct number of products within each country.
To create a materialized view containing SQL functions that return approximate results:
Example 5-9 Creating a Materialized View Based on Approximate Queries
The following example creates a materialized view that stores the approximate number of distinct products that are sold on each day.
CREATE MATERIALIZED VIEW approx_count_distinct_pdt_mv
ENABLE QUERY REWRITE AS
SELECT t.calendar_year, t.calendar_month_number, t.day_number_in_month, approx_count_distinct(prod_id) daily_detail
FROM sales s, times t
WHERE s.time_id = t.time_id
GROUP BY t.calendar_year, t.calendar_month_number, t.day_number_in_month;
5.6 Creating a Materialized View Containing Bitmap-based COUNT(DISTINCT) Functions
Materialized views based on COUNT(DISTINCT)
functions can provide enhanced performance by using bitmap-based operations on integer columns.
Starting with Oracle Database Release 19c, you can create materialized views based on SQL aggregate functions that use bitmap representation to express the computation of COUNT(DISTINCT)
operations. These functions include BITMAP_BUCKET_NUMBER
, BITMAP_BIT_POSITION
and BITMAP_CONSTRUCT_AGG
.
To create a materialized view based on bitmaps:
5.7 Registering Existing 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 application 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 large and expensive to rebuild, you should register your existing materialized view tables 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.
The contents of the table must reflect the materialization of the defining query at the time you register it as a materialized view, and each column in the defining query must correspond to a column in the table that has a matching data type. However, you can specify WITH
REDUCED
PRECISION
to allow the precision of columns in the defining query to be different from that of the table columns.
The table and the materialized view must have the same name, but the table retains its identity as a table and can contain columns that are not referenced in the defining query of the materialized view. These extra columns are known as unmanaged columns. If rows are inserted during a refresh operation, each unmanaged column of the row is set to its default value. Therefore, the unmanaged columns cannot have NOT
NULL
constraints unless they also have default values.
Materialized views based on prebuilt tables are eligible for selection by query rewrite provided the parameter QUERY_REWRITE_INTEGRITY
is set to STALE_TOLERATED
or TRUSTED
.
See Also:
Basic Query Rewrite for Materialized Views 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.
The following example illustrates the two steps required to register a user-defined table. First, the table is created, then the materialized view is defined using exactly the same name as the table. This materialized view sum_sales_tab_mv
is eligible for use in query rewrite.
CREATE TABLE sum_sales_tab PCTFREE 0 TABLESPACE demo STORAGE (INITIAL 8M) 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_mv ON PREBUILT TABLE WITHOUT REDUCED PRECISION ENABLE QUERY REWRITE 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;
You could have compressed this table to save space.
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 might 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 fast refreshed each update cycle.
-
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 you should create a new materialized view that does include the time dimension (if possible). Also, in this case, the view should aggregate over the time column in the new materialized view.
5.8 Choosing Indexes for Materialized Views
The two most common operations on a materialized view are query execution and fast refresh, and each operation has different performance requirements. Query execution might need to access any subset of the materialized view key columns, and might need to join and aggregate over a subset of those columns. Consequently, query execution usually performs best if a single-column bitmap index is defined on each materialized view key column.
In the case of materialized views containing only joins using fast refresh, Oracle recommends that indexes be created on the columns that contain the rowids to improve the performance of the refresh operation.
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.
If the materialized view is partitioned, then, after doing a partition maintenance operation on the materialized view, the indexes become unusable, and they need to be rebuilt for fast refresh to work.
If you create a materialized view with the prebuilt option, the I_snap$
index is not automatically created. This index significantly improves fast refresh performance, and you can create it manually by issuing a statement such as the following:
CREATE UNIQUE INDEX <OWNER>."I_SNAP$_<MVIEW_NAME>" ON <OWNER>.<MVIEW_NAME> (SYS_OP_MAP_NONNULL("LOG_DATE")) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE FAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE <TABLESPACE_NAME>;
See Also:
Oracle Database SQL Tuning Guide for information on using the SQL Access Advisor to determine what indexes are appropriate for your materialized view
5.9 Dropping Materialized Views
Use the DROP
MATERIALIZED
VIEW
statement to drop a materialized view. For example, consider the following statement:
DROP MATERIALIZED VIEW sales_sum_mv;
This statement 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 or used by query rewrite. Alternatively, you can drop a materialized view using Oracle Enterprise Manager.
5.10 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 materialized view is fast refreshable
-
What types of query rewrite you can perform with this materialized view
-
Whether partition change tracking refresh is possible
Using this procedure is straightforward and described in "Using the DBMS_MVIEW.EXPLAIN_MVIEW Procedure". You simply call DBMS_MVIEW.EXPLAIN_MVIEW
, passing in as a single parameter the schema and materialized view name for an existing materialized 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 analyzed 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. It is to create the MV_CAPABILITIES_TABLE
in the current schema. An explanation of the various capabilities is in Table 5-6, and all the possible messages are listed in Table 5-7.
5.10.1 Using the DBMS_MVIEW.EXPLAIN_MVIEW Procedure
-
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
statement 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 MSG_ARRAY
.
See Also:
Oracle Database PL/SQL Packages and Types Reference for further information about the DBMS_MVIEW
package
This section contains the following topics:
5.10.1.1 DBMS_MVIEW.EXPLAIN_MVIEW Declarations
The following PL/SQL declarations that are made for you in the DBMS_MVIEW
package show the order and data types of these parameters for explaining an existing materialized view and a potential 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
:
DBMS_MVIEW.EXPLAIN_MVIEW (mv IN VARCHAR2, msg_array OUT SYS.ExplainMVArrayType);
5.10.1.2 Using MV_CAPABILITIES_TABLE
One of the simplest ways to use DBMS_MVIEW.EXPLAIN_MVIEW
is with the MV_CAPABILITIES_TABLE
, which has the following structure:
CREATE TABLE MV_CAPABILITIES_TABLE (STATEMENT_ID VARCHAR(30), -- Client-supplied unique statement identifier MVOWNER VARCHAR(30), -- NULL for SELECT based EXPLAIN_MVIEW MVNAME VARCHAR(30), -- NULL for SELECT based EXPLAIN_MVIEW CAPABILITY_NAME VARCHAR(30), -- A descriptive name of the particular -- capability: -- REWRITE -- Can do at least full text match -- rewrite -- REWRITE_PARTIAL_TEXT_MATCH -- Can do at least full and partial -- text match rewrite -- REWRITE_GENERAL -- Can do all forms of rewrite -- REFRESH -- Can do at least complete refresh -- REFRESH_FROM_LOG_AFTER_INSERT -- Can do fast refresh from an mv log -- or change capture table at least -- when update operations are -- restricted to INSERT -- REFRESH_FROM_LOG_AFTER_ANY -- can do fast refresh from an mv log -- or change capture table after any -- combination of updates -- PCT -- Can do Enhanced Update Tracking on -- the table named in the RELATED_NAME -- column. EUT is needed for fast -- refresh after partitioned -- maintenance operations on the table -- named in the RELATED_NAME column -- and to do non-stale tolerated -- rewrite when the mv is partially -- stale with respect to the table -- named in the RELATED_NAME column. -- EUT can also sometimes enable fast -- refresh of updates to the table -- named in the RELATED_NAME column -- when fast refresh from an mv log -- or change capture table is not -- possible. -- See Table 5-6 POSSIBLE CHARACTER(1), -- T = capability is possible -- F = capability is not possible RELATED_TEXT VARCHAR(2000), -- Owner.table.column, alias name, and so on -- related to this message. The specific -- meaning of this column depends on the -- NSGNO column. See the documentation for -- DBMS_MVIEW.EXPLAIN_MVIEW() for details. RELATED_NUM NUMBER, -- When there is a numeric value -- associated with a row, it goes here. MSGNO INTEGER, -- When available, QSM 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
.
See Also:
-
Refreshing Materialized Views for further details about partition change tracking
-
Advanced Query Rewrite for Materialized Views for further details about partition change tracking
Example 5-10 DBMS_MVIEW.EXPLAIN_MVIEW
First, create the materialized view. Alternatively, you can use EXPLAIN_MVIEW
on a potential materialized view using its SELECT
statement or the complete CREATE
MATERIALIZED
VIEW
statement.
CREATE MATERIALIZED 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 EXPLAIN_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 multiple reasons, a row is displayed for each reason.
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('SH.CAL_MONTH_SALES_MV'); SELECT capability_name, possible, SUBSTR(related_text,1,8) AS rel_text, SUBSTR(msgtxt,1,60) AS msgtxt FROM MV_CAPABILITIES_TABLE ORDER BY seq;
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 must have new values REFRESH_FAST_AFTER_INSERT N SH.TIMES mv log must have ROWID REFRESH_FAST_AFTER_INSERT N SH.TIMES mv log does not have all necessary columns REFRESH_FAST_AFTER_INSERT N SH.SALES mv log must have new values REFRESH_FAST_AFTER_INSERT N SH.SALES mv log must have ROWID REFRESH_FAST_AFTER_INSERT N SH.SALES mv log 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_DML 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 materialized 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
5.10.1.3 MV_CAPABILITIES_TABLE.CAPABILITY_NAME Details
Table 5-6 lists explanations for values in the CAPABILITY_NAME
column.
Table 5-6 CAPABILITY_NAME Column Details
CAPABILITY_NAME | Description |
---|---|
|
If this capability is possible, partition change tracking is possible on at least one detail relation. If this capability is not possible, partition change tracking is not possible with any detail relation referenced by the materialized view. |
|
If this capability is possible, complete refresh of the materialized view is possible. |
|
If this capability is possible, fast refresh is possible at least under certain circumstances. |
|
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. |
|
If this capability is possible, it is possible with respect to a particular partitioned table in the top level PCT is needed to support fast refresh after partition maintenance operations on the table named in the PCT may also support fast refresh with regard to updates to the table named in the PCT is also needed to support query rewrite in the presence of partial staleness of the materialized view with regard to the table named in the When disabled, PCT does not apply to the table named in the |
|
If this capability is possible, it is possible with respect to a particular partitioned table in the top level This capability is needed to support query rewrite against this materialized view in partial stale state with regard to the table named in the When disabled, query rewrite cannot be supported if this materialized view is in partial stale state with regard to the table named in the |
|
If this capability is possible, fast refresh from a materialized view log is possible at least in the case where the updates are restricted to |
|
If this 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. |
|
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 |
|
If this capability is possible, fast refresh using PCT is possible. Generally, this means that refresh is possible after partition maintenance operations on those detail tables where PCT is indicated as possible. |
|
If this capability is possible, full text match query rewrite is possible. If this capability is not possible, full text match query rewrite is not possible. |
|
If this capability is possible, at least full and partial text match query rewrite are possible. If this capability is not possible, at least partial text match query rewrite and general query rewrite are not possible. |
|
If this capability is possible, all query rewrite capabilities are possible, including general query rewrite and full and partial text match query rewrite. If this capability is not possible, at least general query rewrite is not possible. |
|
If this capability is possible, query rewrite can use a partially stale materialized view even in |
5.10.1.4 MV_CAPABILITIES_TABLE Column Details
Table 5-7 lists the semantics for RELATED_TEXT
and RELATED_NUM
columns.
Table 5-7 MV_CAPABILITIES_TABLE Column Details
MSGNO | MSGTXT | RELATED_NUM | RELATED_TEXT |
---|---|---|---|
|
|
For PCT capability only: |
|
2066 |
This statement resulted in an Oracle error |
Oracle error number that occurred |
|
2067 |
No partition key or |
|
|
2068 |
Relation is not partitioned |
|
|
2069 |
PCT not supported with multicolumn partition key |
|
|
2070 |
PCT not supported with this type of partitioning |
|
|
2071 |
Internal error: undefined PCT failure code |
The unrecognized numeric PCT failure code |
|
2072 |
Requirements not satisfied for fast refresh of nested materialized view |
||
2077 |
Materialized view log is newer than last full refresh |
|
|
2078 |
Materialized view log must have new values |
|
|
2079 |
Materialized view log must have |
|
|
2080 |
Materialized view log must have primary key |
|
|
2081 |
Materialized view log does not have all necessary columns |
|
|
2082 |
Problem with materialized view log |
|
|
2099 |
Materialized view references a remote table or view in the |
Offset from the |
|
2126 |
Multiple primary sites |
Name of the first different node, or |
|
2129 |
Join or filter condition(s) are complex |
|
|
2130 |
Expression not supported for fast refresh |
Offset from the |
The alias name in the |
2150 |
|
Offset from the |
The alias name of the first different select item in the |
2182 |
PCT is enabled through a join dependency |
|
|
2183 |
Expression to enable PCT not in |
The unrecognized numeric PCT failure code |
|
2184 |
Expression to enable PCT cannot be rolled up |
|
|
2185 |
No partition key or |
|
|
2186 |
|
||
2187 |
Materialized view on external table |