Page 174 - DCAP603_DATAWARE_HOUSING_AND_DATAMINING

Data Warehousing and Data Mining

notes 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 product and sales.

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

168 LoveLy professionaL university