Page 275 - DCAP603_DATAWARE_HOUSING_AND_DATAMINING

Unit 14: Quality Driven Data Warehouse Design

Materialized views and Models notes

Models, which provide array-based computations in SQL, can be used in materialized views.
Because the MODEL clause calculations can be expensive, you may want to use two separate
materialized views: one for the model calculations and one for the SELECT ... GROUP BY query.
For example, instead of using one, long materialized view, you could create the following
materialized views:
CREATE MATERIALIZED VIEW my_groupby_mv
REFRESH FAST

ENABLE QUERY REWRITE AS
SELECT country_name country, prod_name prod, calendar_year year,
SUM(amount_sold) sale, COUNT(amount_sold) cnt, COUNT(*) cntstr
FROM sales, times, customers, countries, products

WHERE sales.time_id = times.time_id AND
sales.prod_id = products.prod_id AND
sales.cust_id = customers.cust_id AND
customers.country_id = countries.country_id
GROUP BY country_name, prod_name, calendar_year;

CREATE MATERIALIZED VIEW my_model_mv
ENABLE QUERY REWRITE AS
SELECT country, prod, year, sale, cnt
FROM my_groupby_mv
MODEL PARTITION BY(country) DIMENSION BY(prod, year)

MEASURES(sale s) IGNORE NAV
(s[‘Shorts’, 2000] = 0.2 * AVG(s)[CURRENTV(), year BETWEEN 1996 AND 1999],
s[‘Kids Pajama’, 2000] = 0.5 * AVG(s)[CURRENTV(), year BETWEEN 1995 AND 1999],
s[‘Boys Pajama’, 2000] = 0.6 * AVG(s)[CURRENTV(), year BETWEEN 1994 AND 1999],
...

<hundreds of other update rules>);
By using two materialized views, you can incrementally maintain the materialized view my_
groupby_mv. The materialized view my_model_mv is on a much smaller data set because it is
built on my_groupby_mv and can be maintained by a complete refresh.
Materialized views with models can use complete refresh or PCT refresh only, and are available
for partial text query rewrite only.

LoveLy professionaL university 269