Page 177 - DCAP603_DATAWARE_HOUSING_AND_DATAMINING

Unit 9: Data Warehouse Refreshment – II

In this example, to perform a fast refresh, UNIQUE constraints should exist on c.cust_id and notes
t.time_id. You should also create indexes on the columns sales_rid, times_rid, and customers_rid,
as illustrated in the following. This will improve the refresh performance.
CREATE INDEX mv_ix_salesrid ON detail_sales_mv(“sales_rid”);
Alternatively, if the previous example did not 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(+);

9.2.3 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: Nested Materialized View
You can create a nested materialized view on materialized views that contain joins only or joins
and aggregates. All the underlying objects (materialized views or tables) on which the materialized
view is defined must have a materialized view log. All the underlying objects are treated as if
they were tables. In addition, you can use 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;

LoveLy professionaL university 171