Types of Materialized Views

The SELECT clause in the materialized view creation statement defines the data that
the materialized view is to contain. Only a few restrictions limit what can be specified. Any number of tables can be joined together. However, they cannot be remote tables if you wish to take advantage of query rewrite. Besides tables, other elements such as views, inline views (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:
■ Materialized Views with Aggregates
■ Materialized Views Containing Only Joins
■ Nested Materialized Views


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

Fast refresh for a materialized view containing joins and aggregates is possible after any type of DML to the base tables (direct load or conventional INSERT, UPDATE, or DELETE). It can be defined to be refreshed ON COMMIT or ON DEMAND. A REFRESH ON
COMMIT materialized view will be 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 will be fast refreshed.


Example 8–1 Example 1: Creating a Materialized View
CREATE MATERIALIZED VIEW LOG ON products WITH SEQUENCE, ROWID
(prod_id, prod_name, prod_desc, prod_subcategory, prod_subcategory_desc,
prod_category, prod_category_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
PCTFREE 0 TABLESPACE demo
STORAGE (INITIAL 8k NEXT 8k PCTINCREASE 0)
BUILD IMMEDIATE
REFRESH FAST
ENABLE QUERY REWRITE
AS SELECT p.prod_name, SUM(s.amount_sold) AS dollar_sales,
COUNT(*) AS cnt, COUNT(s.amount_sold) AS cnt_amt
FROM sales s, products p
WHERE s.prod_id = p.prod_id GROUP BY p.prod_name;

This example 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.


Example 8–2 Example 2: Creating a Materialized View

CREATE MATERIALIZED VIEW product_sales_mv
PCTFREE 0 TABLESPACE demo
STORAGE (INITIAL 16k NEXT 16k PCTINCREASE 0)
BUILD DEFERRED
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT p.prod_name, SUM(s.amount_sold) AS dollar_sales
FROM sales s, products p WHERE s.prod_id = p.prod_id
GROUP BY p.prod_name;

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 8–3 Example 3: Creating a Materialized View

CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID
(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW sum_sales
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 will be reflected in the materialized view when the commit is issued


Materialized Views Containing Only Joins
Some materialized views contain only joins and no aggregates, 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 will be precalculated.

Fast refresh for a materialized view containing only joins is possible after any type of DML to the base tables (direct-path or conventional INSERT, UPDATE, or DELETE).
A materialized view containing only joins can be defined to be refreshed ON COMMIT
or ON DEMAND. If it is ON COMMIT, the refresh is performed at commit time of the
transaction that does DML on the materialized view's detail table.
If you specify REFRESH FAST, Oracle performs further verification of the query
definition to ensure that fast refresh can be performed if any of the detail tables
change. These additional checks are:

■ A materialized view log must be present for each detail table unless the table
supports 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.

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. Further, ON COMMIT refresh is not supported
for materialized view with remote tables. Materialized view logs must be present on
the remote site for each detail table of the materialized view and ROWID columns must
be present in the SELECT list of the materialized view, as shown in the following
example.


Example 8–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.

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


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.

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 8–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 need to
log all the necessary columns and use the INCLUDING NEW VALUES clause.

/* create materialized view log on join_sales_cust_time */
CREATE MATERIALIZED VIEW LOG ON join_sales_cust_time
WITH ROWID (cust_last_name, day_number_in_week, amount_sold)
INCLUDING NEW VALUES;
/* create the single-table aggregate materialized view sum_sales_cust_time
on join_sales_cust_time as fast refreshable at COMMIT time */

CREATE MATERIALIZED VIEW sum_sales_cust_time
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



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


Nested Materialized View Usage Guidelines
You should keep the following in mind when deciding whether to use nested
materialized views:

■ If you want to use fast refresh, you should fast refresh all the materialized views
along any chain.
■ If you want the highest level materialized view to be fresh with respect to the
detail tables, you need to ensure that 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 "Nesting Materialized Views with
Joins and Aggregates" on page 8-13. If you do not specify nested = 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 need to 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 the nested parameter value set to
TRUE, only specified materialized views and their child materialized views in the
tree are refreshed, and not their top-level materialized views. Use the REFRESH_
DEPENDENT procedure with the nested parameter value set to TRUE if you want to
ensure that all materialized views in a tree are refreshed.
■ Freshness of a materialized view is calculated relative to the objects 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.

Posted in: Materialized Views