Page 173 - DCAP603_DATAWARE_HOUSING_AND_DATAMINING

Unit 9: Data Warehouse Refreshment – II

notes

Note The techniques shown in this unit illustrate how to use materialized views in
data warehouses. Materialized views can also be used by Oracle Replication.

9.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. 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 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:

1. Materialized Views with Aggregates
2. Materialized Views Containing Only Joins
3. Nested Materialized Views

9.2.1 Materialized views with aggregates

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

LoveLy professionaL university 167