-- Chapter 4 Summary Management

-- 4.2 Creating a materialized view

-- Page 140
-- Materialized View with Joins and Aggregates Example

CREATE MATERIALIZED VIEW MONTHLY_SALES_MV
PCTFREE 0 TABLESPACE mview <- storage parameters
STORAGE (initial 64k
         next 64k pctincrease 0)
BUILD IMMEDIATE 
REFRESH FORCE 
ON DEMAND 
ENABLE QUERY REWRITE 
AS
SELECT t.month, t.year, p.product_id, <- what it contains
       SUM (f.purchase_price) as sum_of_sales,
       COUNT (f.purchase_price) as total_sales,
       COUNT(*) as cstar
FROM time t, product p, purchases f
WHERE t.time_key = f.purchase_date AND
      f.product_id = p.product_id
GROUP BY t.month, t.year, p.product_id;

-- Page 144
-- Materialized Join View Example

CREATE MATERIALIZED VIEW customer_purchases_mv
BUILD IMMEDIATE
REFRESH COMPLETE
AS
SELECT c.sex, c.occupation, f.purchase_price
FROM purchases f, customer c
WHERE f.customer_id = c.customer_id

-- Page 145
-- Materialized View on Prebuilt  Table

-- create a table
-- populate the table (for illustration, we use insert select)
-- you can use sqlldr or other methods also.
-- register the table as materialized view

CREATE TABLE monthly_customer_sales 
(
YEAR         NUMBER(4),
MONTH        NUMBER(2),
CUSTOMER_ID  VARCHAR2(10),
DOLLAR_SALES NUMBER
);

insert /*+append */ into monthly_customer_sales
SELECT t.year, t.month, c.customer_id,
      SUM(f.purchase_price) AS dollar_sales
FROM time t, purchases f, customer c
WHERE f.time_key = t.time_key AND
      f.customer_id = c.customer_id
GROUP BY t.year, t.month, c.customer_id;

commit;

CREATE MATERIALIZED VIEW monthly_customer_sales
ON PREBUILT TABLE
ENABLE QUERY REWRITE
AS
SELECT t.year, t.month, c.customer_id,
      SUM(f.purchase_price) AS dollar_sales
FROM time t, purchases f, customer c
WHERE f.time_key = t.time_key AND
      f.customer_id = c.customer_id
GROUP BY t.year, t.month, c.customer_id;

-- Page 146
-- Partitioned materialized view

CREATE MATERIALIZED VIEW MONTHLY_SALES_MV_PART
PARTITION by RANGE (time_key)
( partition purchases_jan2002
  values less than (TO_DATE('01-02-2002', 'DD-MM-YYYY'))
  pctfree 0 pctused 99
  storage (initial 64k next 64k pctincrease 0)
  tablespace purchases_jan2002 ,
partition purchases_feb2002
  values less than (TO_DATE('01-03-2002', 'DD-MM-YYYY'))
  pctfree 0 pctused 99
  storage (initial 64k next 64k pctincrease 0)
  tablespace purchases_feb2002 ,
partition purchases_mar2002
  values less than (TO_DATE('01-04-2002', 'DD-MM-YYYY'))
  pctfree 0 pctused 99
  storage (initial 64k next 64k pctincrease 0)
  tablespace purchases_mar2002
)
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT t.time_key, p.product_id, SUM (f.purchase_price) as
sum_of_sales
FROM time t, product p, purchases f
WHERE t.time_key = f.purchase_date AND
f.product_id = p.product_id
GROUP BY t.time_key, p.product_id;

-- Page 147
-- Index on Materialized View

CREATE INDEX easydw.products_by_month_concat_index
ON MONTHLY_SALES_MV (month, year, product_id)
pctfree 5
tablespace indx
storage (initial 64k next 64k pctincrease 0) ;

CREATE BITMAP INDEX easydw.total_products_by_month_index
ON monthly_sales_mv (month, year);

CREATE BITMAP INDEX easydw.total_products_by_id_index
ON monthly_sales_mv (product_id);

---------------------------------------------------------------------------
-- 4.3 Refresh

-- Page 153
-- Creating a fast refreshable materialized View

CREATE MATERIALIZED VIEW LOG on time
WITH ROWID, SEQUENCE (time_key, month, year)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG on purchases
WITH ROWID, SEQUENCE (purchase_date, product_id, purchase_price)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG on product
WITH ROWID, SEQUENCE (product_id)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW monthly_sales_mv
PCTFREE 0 TABLESPACE summary
STORAGE (initial 64k next 64k pctincrease 0)
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT t.month, t.year, p.product_id,
       SUM (ps.purchase_price) as sum_of_sales,
       COUNT (ps.purchase_price) as total_sales, COUNT(*)
FROM time t, product p, purchases ps
WHERE t.time_key = ps.purchase_date AND
      ps.product_id = p.product_id
GROUP BY t.month, t.year, p.product_id;

-- Page 154
-- Fast refreshable materialized join view 
-- Needs materialized view logs created in previous examples on purchases
-- and product (WITH ROWID clause)

CREATE MATERIALIZED VIEW LOG on customer WITH ROWID;

CREATE MATERIALIZED VIEW product_customer
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT c.rowid r1, c.sex, p.rowid r2, p.product_id,
      f.rowid r3, f.purchase_price
FROM purchases f, product p, customer c
WHERE f.customer_id = c.customer_id
  AND f.product_id = p.product_id;

-- Page 156
-- Materialized View enabled for PCT (partition key)
CREATE MATERIALIZED VIEW monthly_sales_partkey_mv
REFRESH FAST
AS
SELECT t.month, ps.time_key,
       SUM (ps.purchase_price) as sum_of_sales,
       COUNT (ps.purchase_price) as total_sales, COUNT(*)
FROM time t, purchases ps
WHERE t.time_key = ps.time_key
GROUP BY t.month, ps.time_key;

-- Materialized View enabled for PCT (partition marker)
CREATE MATERIALIZED VIEW monthly_sales_marker_mv
REFRESH FAST
AS
SELECT t.month, DBMS_MVIEW.PMARKER(ps.rowid) as pmark,
       SUM (ps.purchase_price) as sum_of_sales,
       COUNT (ps.purchase_price) as total_sales, COUNT(*)
FROM time t, purchases ps
WHERE t.time_key = ps.time_key
GROUP BY t.month, DBMS_MVIEW.PMARKER(ps.rowid);

------------------------------------------------------------------------------
-- 4.4 EXPLAIN MVIEW

-- Page 158-159:
-- EXPLAIN_MVIEW Example
-- use utlxmv.sql in rdbms/admin directory to create MV_CAPABILITIES_TABLE

ALTER MATERIALIZED VIEW LOG on TIME EXCLUDING NEW VALUES;
DROP MATERIALIZED VIEW LOG on PRODUCT;

BEGIN
dbms_mview.explain_mview (
'SELECT t.month, t.year, p.product_id,
        SUM (f.purchase_price) as sum_of_sales,
        COUNT (f.purchase_price) as total_sales,
        COUNT(*) as cstar
FROM time t, product p, purchases f
WHERE t.time_key = f.purchase_date AND
f.product_id = p.product_id
GROUP BY t.month, t.year, p.product_id');
END;
/

SELECT capability_name, possible, related_text table, msgtxt explanation
FROM MV_CAPABILITIES_TABLE;

-- Page 160
-- re-run after correcting the problem
CREATE MATERIALIZED VIEW LOG on product
WITH ROWID, SEQUENCE (product_id)
INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG on TIME including new values;

BEGIN
dbms_mview.explain_mview (
'SELECT t.month, t.year, p.product_id,
        SUM (f.purchase_price) as sum_of_sales,
        COUNT (f.purchase_price) as total_sales,
        COUNT(*) as cstar
FROM time t, product p, purchases f
WHERE t.time_key = f.purchase_date AND
      f.product_id = p.product_id
GROUP BY t.month, t.year, p.product_id');
END;
/

SELECT capability_name, possible, related_text table, msgtxt explanation
FROM MV_CAPABILITIES_TABLE
WHERE capability_name LIKE 'REFRESH%';

------------------------------------------------------------------------------
-- 4.5 Dimensions

-- Page 163
-- Geography Dimension
CREATE DIMENSION geography_dim
       LEVEL postal_code IS customer.postal_code
       LEVEL town IS customer.town
       LEVEL county IS customer.county
       LEVEL country IS customer.country
HIERARCHY loc_rollup (
          postal_code CHILD OF
          town CHILD OF
          county CHILD OF
          country
);

-- Page 165
-- Time Dimension (multiple hierarchies)
CREATE DIMENSION time_dim
       LEVEL time_key IS time.time_key
       LEVEL month IS time.month
       LEVEL quarter IS time.quarter
       LEVEL year IS time.year
       LEVEL week_number IS time.week_number
HIERARCHY calendar_rollup (
       time_key CHILD OF
       month CHILD OF
       year
)
HIERARCHY fiscal_rollup (
      time_key CHILD OF
      week_number CHILD OF
      quarter
);

-- Page 166
-- Product Dimension (attribute clause)

CREATE DIMENSION product_dim
      LEVEL product_id IS product.product_id
      LEVEL category IS product.category
HIERARCHY merchandise_rollup (
      product_id CHILD OF
      category
)
ATTRIBUTE product_id DETERMINES (product_name, manufacturer);

-- Page 167
-- Time Dimension using Normalized Tables
CREATE DIMENSION time_dim
      LEVEL time_key IS time.time_key
      LEVEL month IS month.month
      LEVEL quarter IS quarter.quarter
      LEVEL year IS year.year
      LEVEL week IS week.week_number
HIERARCHY calendar_rollup (
      time_key CHILD OF
      month CHILD OF
      year
JOIN KEY time.month REFERENCES month
JOIN KEY month.year REFERENCES year
)
HIERARCHY fiscal_rollup (
      time_key CHILD OF
      week CHILD OF
      quarter
JOIN KEY time.week REFERENCES week
JOIN KEY week.quarter REFERENCES quarter
);

-- Page 168-169
-- Validating a dimension

CREATE DIMENSION customer_dim
      LEVEL customer IS customer.customer_id
      LEVEL town IS customer.town
      LEVEL region IS customer.county
HIERARCHY customer_zone (
      customer CHILD OF
      town CHILD OF
      region
)
ATTRIBUTE town DETERMINES postal_code
ATTRIBUTE customer DETERMINES (sex, occupation);

SELECT distinct town, county, postal_code FROM customer;
INSERT INTO customer VALUES ('AB130000', 'London', 'London',
                            'W1 2QC', '7-JUL-74', 'UK', 'Doctor', 'F');
INSERT INTO customer VALUES ('AB130001', 'Liverpool', 'Hants',
                            'LV1 1QT','18-JUN-73', 'UK', 'Doctor', 'M');
COMMIT;

variable run_id number;
execute dbms_olap.create_id(:run_id);
execute dbms_olap.validate_dimension('CUSTOMER_DIM', 'EASYDW',
                                     FALSE, TRUE, :run_id);

-- find the violated relationships
SELECT distinct owner, table_name, dimension_name, relationship
FROM system.MVIEW_EXCEPTIONS
WHERE runid = :run_id;

-- find the offending rows
SELECT customer_id, town, county, postal_code FROM customer
WHERE rowid IN (select bad_rowid FROM system.mview_exceptions);


------------------------------------------------------------------------------
-- 4.6 Query Rewrite

-- NOTE: In the following examples we use EXPLAIN PLAN 

-- Page 170
-- set session parameters
-- enable query rewrite
ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;

-- enable cost-based optimizer
ALTER SESSION SET OPTIMIZER_MODE=”all_rows”;

-- materialized view

CREATE MATERIALIZED VIEW monthly_sales_mv
ENABLE QUERY REWRITE
AS
SELECT t.month, t.year, p.product_id,
       SUM (ps.purchase_price) as sum_of_sales,
       COUNT (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id
GROUP BY t.month, t.year, p.product_id;

-- Page 171
-- exact text match
EXPLAIN PLAN FOR
SELECT t.year, t.month, p.product_id,
       sum (ps.purchase_price) as sum_of_sales,
       count (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = f.time_key AND
      f.product_id = p.product_id
GROUP BY t.year, t.month, p.product_id;

@utlxpls;

-- Page 172
-- partial text match starting with FROM clause
EXPLAIN PLAN FOR
SELECT t.month, t.year, p.product_id, 
       AVG(ps.purchase_price) avg_of_sales 
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND 
      ps.product_id = p.product_id
GROUP BY t.year, t.month, p.product_id;

@utlxpls;

-- Page 173
-- aggregate rollup
EXPLAIN PLAN FOR
SELECT t.year, p.product_id,
       SUM (ps.purchase_price) as sum_of_sales,
       COUNT (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id
GROUP BY t.year, p.product_id;

@utlxpls;

-- Page 174
-- join-back to product table using primary key constraint
EXPLAIN PLAN FOR
SELECT t.year, t.month, p.product_name,
       SUM (ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id
GROUP BY t.year, t.month, p.product_name;

@utlxpls;

-- Page 175, 176
-- Filtered Data

CREATE MATERIALIZED VIEW elec_sales_mv
ENABLE QUERY REWRITE
AS
SELECT t.month, t.year, p.product_id,
       SUM(ps.purchase_price) as sum_of_sales,
       COUNT (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.purchase_date AND
      ps.product_id = p.product_id AND
      p.category = 'ELEC' AND t.year BETWEEN 1997 and 2002
GROUP BY t.month, t.year, p.product_id;

EXPLAIN PLAN FOR
SELECT t.month, p.product_id,
   SUM(ps.purchase_price) as sum_of_sales, 
   COUNT (ps.purchase_price) as total_sales
 FROM time t, product p, purchases ps
 WHERE t.time_key = ps.purchase_date AND 
   ps.product_id = p.product_id AND
   p.category = 'ELEC' AND t.year = 1999
 GROUP BY t.month, t.year, p.product_id;

@utlxpls;

EXPLAIN PLAN FOR
SELECT t.month, SUM(ps.purchase_price) as sum_of_sales, 
   COUNT (ps.purchase_price) as total_sales
 FROM time t, product p, purchases ps
 WHERE t.time_key = ps.purchase_date AND 
   ps.product_id = p.product_id AND p.category = 'Elec' AND 
   t.year = 2002 AND product_name  = 'Digital  Camera'
GROUP BY t.month;

@utlxpls;

-- Page 177:
-- aggregate computability

EXPLAIN PLAN FOR
SELECT t.year, p.product_id, AVG(ps.purchase_price) as ave_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND 
ps.product_id = p.product_id
GROUP BY  t.year, p.product_id;

@utlxpls;

-- Page 178-180
-- Rollup using Dimensions

CREATE DIMENSION time
       LEVEL time_key is time.time_key
       LEVEL month is time.month
       LEVEL quarter is time.quarter
       LEVEL year is time.year
       LEVEL week_number is time.week_number
HIERARCHY fiscal_rollup (
          time_key CHILD OF
          week_number CHILD OF
          quarter )
HIERARCHY calendar_rollup(
          time_key CHILD OF
          month CHILD OF
          year);

CREATE MATERIALIZED VIEW monthly_sales_mv
ENABLE QUERY REWRITE
AS
SELECT t.month, p.product_id,
       SUM(ps.purchase_price) as sum_of_sales,
       COUNT (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id
GROUP BY t.month, p.product_id;


-- rollup to higher LEVEL in the HIERARCHY
EXPLAIN PLAN FOR
SELECT t.year, p.product_id, 
       COUNT (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id
GROUP BY t.year, p.product_id;

@utlxpls;

-- rewriting with an attribute clause

CREATE MATERIALIZED VIEW cust_sales_mv
ENABLE QUERY REWRITE
AS
SELECT c.customer_id, SUM(ps.purchase_price) as sum_of_sales,
COUNT (ps.purchase_price) as total_sales
FROM customer c, purchases ps
WHERE c.customer_id = ps.customer_id
GROUP BY c.customer_id;

CREATE DIMENSION customer_dim
       LEVEL customer IS customer.customer_id
       LEVEL town IS customer.town
       LEVEL region IS customer.county
HIERARCHY customer_zone (
  customer CHILD OF
  town CHILD OF
  region )
ATTRIBUTE customer DETERMINES (sex, occupation);

-- rewrite using an attribute clause
EXPLAIN PLAN FOR
SELECT c.sex, c.occupation, SUM(ps.purchase_price) as sum_of_sales
FROM purchases ps, customer c
WHERE c.customer_id = ps.customer_id
GROUP BY c.sex, c.occupation;

@utlxpls;

-- Page 181:
-- rewrite using constraints

ALTER TABLE purchases ENABLE NOVALIDATE CONSTRAINT fk_customer_id;
ALTER TABLE purchases MODIFY CONSTRAINT fk_customer_id RELY;

EXPLAIN PLAN FOR
SELECT p.product_id, SUM(ps.purchase_price) as sum_of_sales
FROM product p, purchases ps
WHERE ps.product_id = p.product_id
GROUP BY p.product_id;

@utlxpls;

-- Page 183-185:
-- Rewrite Integrity Levels

INSERT INTO purchases VALUES ( 'SP1061','1-FEB-2002',
      'AB123456','1-FEB-2002', 0024,28.01, 4.50, 'Y');
INSERT INTO purchases VALUES ( 'SP1062','1-FEB-2002',
      'AB123457','1-FEB-2002', 1024,28.01, 4.50, 'Y');
INSERT INTO purchases VALUES ( 'SP1063','2-FEB-2002',
      'AB123457','2-FEB-2002', 0024,28.01, 4.50, 'N');
INSERT INTO purchases VALUES ( 'SP1064','2-FEB-2002',
      'AB123457','2-FEB-2002', 1024,28.01, 4.50, 'N');
COMMIT;


ALTER SESSION SET QUERY_REWRITE_INTEGRITY=ENFORCED;

SELECT t.month, p.product_id, 
   SUM(ps.purchase_price) as sum_of_sales, 
   COUNT(ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
  ps.product_id = p.product_id
GROUP BY t.month, p.product_id;


ALTER SESSION SET QUERY_REWRITE_INTEGRITY=STALE_TOLERATED;

SELECT t.month, p.product_id, 
   SUM(ps.purchase_price) as sum_of_sales, 
   COUNT(ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
  ps.product_id = p.product_id
GROUP BY t.month, p.product_id;

-- Page 187
-- Rewrite using HINTS

EXPLAIN PLAN FOR
SELECT t.year, p.product_id, SUM(ps.purchase_price) sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id
GROUP BY t.year, p.product_id;

@utlxpls;

EXPLAIN PLAN FOR
SELECT /*+ REWRITE(monthly_sales_mv) */ t.year, p.product_id, 
       SUM(ps.purchase_price) as sum_of_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id
GROUP BY t.year, p.product_id;

@utlxpls;

-- Page 188, 189:
-- PCT rewrite

CREATE MATERIALIZED VIEW product_category_sales_mv
ENABLE QUERY REWRITE
AS
SELECT ps.time_key, p.category, 
       SUM(ps.purchase_price) as sum_of_sales
FROM product p, purchases ps
WHERE ps.product_id = p.product_id
GROUP BY ps.time_key, p.category;

EXPLAIN PLAN FOR
SELECT ps.time_key, p.category, 
       SUM(ps.purchase_price) as sum_of_sales
 FROM  product p, purchases ps
 WHERE ps.product_id = p.product_id and 
       ps.time_key  BETWEEN TO_DATE('01-01-2002', 'DD-MM-YYYY') 
                        AND TO_DATE('31-03-2002', 'DD-MM-YYYY')
 GROUP BY ps.time_key, p.category;

@utlxpls;

ALTER TABLE purchases ADD PARTITION purchases_apr2002
            values less than (TO_DATE('01-05-2002', 'DD-MM-YYYY'));
INSERT INTO purchases VALUES ( 'SP1063','2-APR-2002',
    'AB123457','7-APR-2002', 0024,28.01, 4.50, 'N');
INSERT INTO purchases VALUES ( 'SP1064','2-APR-2002',
    'AB123457','8-APR-2002', 1024,28.01, 4.50, 'N');
COMMIT;

EXPLAIN PLAN FOR
SELECT ps.time_key, p.category, 
       SUM(ps.purchase_price) as sum_of_sales, 
FROM  product p, purchases ps
WHERE ps.product_id = p.product_id and 
      ps.time_key  BETWEEN TO_DATE('01-01-2002', 'DD-MM-YYYY') 
                       AND TO_DATE('30-04-2002', 'DD-MM-YYYY')
 GROUP BY ps.time_key, p.category;

@utlxpls;

-- Page 190-193:
-- Rewrite with GROUPING SETS

CREATE MATERIALIZED VIEW sales_mv
ENABLE QUERY REWRITE
AS
SELECT p.category, t.time_key, c.county, c.town,
       SUM(f.purchase_price) sales,
       GROUPING_ID(p.category, t.time_key, c.town, c.county) gid
FROM product p, purchases f, time t, customer c
WHERE p.product_id = f.product_id AND
      t.time_key = f.time_key AND
      c.customer_id = f.customer_id
GROUP BY GROUPING SETS ((p.category, t.time_key),
                        (p.category, t.time_key, c.town),
                        (t.time_key, c.county));

-- simple group by
EXPLAIN PLAN FOR
SELECT p.category, t.time_key, SUM(f.purchase_price) sales
FROM product p, purchases f, time t, customer c
WHERE p.product_id = f.product_id AND
      t.time_key = f.time_key AND
      c.customer_id = f.customer_id
GROUP BY p.category, t.time_key;

@utlxpls;

-- grouping set in query
EXPLAIN PLAN FOR
SELECT p.category,  t.time_key, c.county, 
       SUM(f.purchase_price) sales
FROM product p, purchases f,  time t, customer c 
WHERE p.product_id = f.product_id AND 
      t.time_key = f.time_key AND
      c.customer_id = f.customer_id 
GROUP BY GROUPING SETS ((p.category, t.time_key), 
                        (t.time_key, c.county)); 

@utlxpls;

-- rewrite using multiple materialized views
CREATE MATERIALIZED VIEW sales_mv2
ENABLE QUERY REWRITE
AS
SELECT p.category, c.county, t.year, SUM(f.purchase_price) sales
FROM product p, purchases f, time t, customer c
WHERE p.product_id = f.product_id AND
      t.time_key = f.time_key AND
      c.customer_id = f.customer_id
GROUP BY p.category, c.county, t.year;

EXPLAIN PLAN FOR
SELECT p.category,  t.time_key, c.county, t.year, c.town,
 SUM(f.purchase_price) sales
FROM product p, purchases f,  time t, customer c 
WHERE p.product_id = f.product_id AND 
      t.time_key = f.time_key AND
      c.customer_id = f.customer_id 
GROUP BY GROUPING SETS ((p.category, t.time_key),   
                        (t.time_key, c.town), 
                        (p.category, c.county, t.year)); 

@utlxpls;

-- Page 194-196
-- Explain Rewrite

-- example 1 : incorrect session prm

ALTER SESSION SET query_rewrite_enabled = FALSE;

BEGIN
dbms_mview.explain_rewrite('
SELECT t.month, t.year, p.product_id,
       SUM (ps.purchase_price) as sum_of_sales,
       COUNT (ps.purchase_price) as total_sales
FROM time t, product p, purchases ps
WHERE t.time_key = ps.time_key AND
      ps.product_id = p.product_id
GROUP BY t.month, t.year, p.product_id', 'MONTHLY_SALES_MV');
END;
/
SELECT mv_name, message FROM rewrite_table;

-- example 2(a) : missing RELY constraint

ALTER TABLE purchases MODIFY constraint fk_time rely;
BEGIN
dbms_mview.explain_rewrite('
SELECT p.product_id,
      SUM (ps.purchase_price) as sum_of_sales,
      COUNT(ps.purchase_price) as total_sales
FROM product p, purchases ps
WHERE p.product_id = ps.product_id
GROUP BY p.product_id', 'MONTHLY_SALES_MV');
END;
/
SELECT mv_name, message FROM rewrite_table;

-- example 2(b) : re-run after fixing RELY problem
ALTER TABLE purchases MODIFY constraint fk_time rely;

BEGIN
dbms_mview.explain_rewrite('
SELECT p.product_id,
      SUM (ps.purchase_price) as sum_of_sales,
      COUNT(ps.purchase_price) as total_sales
FROM product p, purchases ps
WHERE p.product_id = ps.product_id
GROUP BY p.product_id', 'MONTHLY_SALES_MV');
END;
/
SELECT mv_name, message FROM rewrite_table;

-- example 3: better mv found

CREATE MATERIALIZED VIEW PRODUCT_SALES_EXACT_MATCH
ENABLE QUERY REWRITE
AS
SELECT p.product_id,
       SUM(ps.purchase_price) as sum_of_sales,
       COUNT(ps.purchase_price) as total_sales
FROM product p, purchases ps
WHERE p.product_id = ps.product_id
GROUP BY p.product_id;

begin
dbms_mview.explain_rewrite('
SELECT p.product_id,
       SUM(ps.purchase_price) as sum_of_sales,
       COUNT(ps.purchase_price) as total_sales
FROM product p, purchases ps
WHERE p.product_id = ps.product_id
GROUP BY p.product_id', 'MONTHLY_SALES_MV');
end;
/

SELECT mv_name, message FROM rewrite_table;


---------------------------------------------------------------------------
-- 4.7 Summary Advisor

-- Example 1: Recommending Materialized Views

-- Step 1: Load Workload
-- several alternative workload sources may be used

variable workload_id number;

EXECUTE DBMS_OLAP.CREATE_ID(:workload_id);

-- Source 1: sql cache
EXECUTE DBMS_OLAP.LOAD_WORKLOAD_CACHE (:workload_id,
                                       DBMS_OLAP.WORKLOAD_NEW,
                                       DBMS_OLAP.FILTER_NONE,
                                      'Easydw', 1);

-- Source 2: hypothetical workload
EXECUTE DBMS_OLAP.RECOMMEND_MVIEW_STRATEGY(:run_id,
                                           DBMS_OLAP.WORKLOAD_NONE,
                                           DBMS_OLAP.FILTER_NONE,
                                           500000, 100, NULL, NULL);

-- Source 3: user defined workload
CREATE TABLE my_workload
(query varchar2(2000) not null,
 owner varchar2(30) not null,
 application varchar2(30),
 frequency number,
 lastuse number,
 priority number,
 responsetime number,
 resultsize number,
 sql_addr number,
 sql_hash number
);

variable workload_id number;
execute dbms_olap.create_id (:workload_id);
execute DBMS_OLAP.LOAD_WORKLOAD_USER (:workload_id,
                                      DBMS_OLAP.WORKLOAD_NEW,
                                      DBMS_OLAP.FILTER_NONE,
                                      'EASYDW', 'MY_WORKLOAD');



-- Step 2: Set Filters for recommendation process (Optional)
variable filter_id number;
EXECUTE DBMS_OLAP.CREATE_ID(:filter_id);
EXECUTE DBMS_OLAP.ADD_FILTER_ITEM(:filter_id, 'SCHEMA', 'EASYDW',
                                  NULL, NULL, NULL, NULL);
EXECUTE DBMS_OLAP.ADD_FILTER_ITEM (:filter_id, 'FREQUENCY',
                                  NULL, 10, NULL, NULL, NULL);

-- Step 3: generate recommendations
variable run_id number;
EXECUTE DBMS_OLAP.CREATE_ID(:run_id);
EXECUTE DBMS_OLAP.RECOMMEND_MVIEW_STRATEGY(:run_id, :workload_id,
                                           :filter_id, 500000, 100,
                                           NULL, NULL);

-- Step 4: grant access (change directory path as appropriate)
--         and generate report/script

EXECUTE DBMS_JAVA.GRANT_PERMISSION('EASYDW',
                                   'java.io.FilePermission',
                                   'h:\oracle\reports\*',
                                   'read, write');
EXECUTE DBMS_OLAP.GENERATE_MVIEW_REPORT
        ('h:\oracle\reports\report_rec.html', 
          :run_id, DBMS_OLAP.RPT_ALL);

EXECUTE DBMS_OLAP.GENERATE_MVIEW_SCRIPT('h:\oracle\reports\mview_rec.sql',
                                        :run_id, 'SUMMARY');


-- Example 2: evaluate usage

-- Follow steps 1,2 as in recommendation process.

-- Step 3: evaluate existing structures
EXECUTE DBMS_OLAP.EVALUATE_MVIEW_STRATEGY(:run_id, :workload_id,
                                          :filter_id);

-- see the usage statistics
SELECT runid, mview_owner, mview_name, storage, freq,
       cumulative_benefit as benefit 
FROM SYSTEM.MVIEW_EVALUATIONS;


-- Example 3: Estimate Summary Size
set serveroutput on

DECLARE
no_of_rows NUMBER;
mv_size NUMBER;
BEGIN
  no_of_rows :=0;
  mv_size :=0;
  DBMS_OLAP.estimate_summary_size ('purchases_by_county',
    'SELECT SUM(ps.purchase_price), ct.county
    FROM PURCHASES ps, CUSTOMER ct
    WHERE ps.customer_id = ct.customer_id
    GROUP BY ct.county' , no_of_rows, mv_size );
  DBMS_OUTPUT.put_line ( 'SELECT on Purchases GROUP by County ');
  DBMS_OUTPUT.put_line ( 'No of Rows: ' || no_of_rows );
  DBMS_OUTPUT.put_line ( 'Size of Summary (bytes): ' || mv_size);
  DBMS_OUTPUT.put_line ( '');
END;
/