---------------------------------------------------------------------------------------
7.2 Creating a Materialized View
---------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW MONTHLY_SALES_MV
PCTFREE 0 TABLESPACE summary           
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,
       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.time_key AND
      f.product_id = p.product_id
GROUP BY t.month, t.year, p.product_id;

---------------------------------------------------------------------------------------

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

---------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW customers_maxsales2003_mv
ENABLE QUERY REWRITE
AS
SELECT c.customer_id, SUM(f.purchase_price) AS dollar_sales
FROM purchases f, customer c
WHERE f.customer_id = c.customer_id
GROUP BY c.customer_id
HAVING SUM(f.purchase_price)
    IN (SELECT max(f.purchase_price) dollar_sales
        FROM purchases f, time t
        WHERE f.time_key = t.time_key
          AND t.year = 2003
        GROUP BY f.customer_id);

---------------------------------------------------------------------------------------
-- 7.2.2 Using Summary Management with Existing Summary Tables
---------------------------------------------------------------------------------------

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;

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;

---------------------------------------------------------------------------------------
-- 7.2.3 Partitioning the Materialized View
---------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW Q12003_SALES_MV
PARTITION by RANGE(time_key) 
(
    partition purchases_jan2003 
        values less than (TO_DATE('01-FEB-2003', 'DD-MON-YYYY'))
        tablespace purchases_jan2003,
    partition purchases_feb2003 
        values less than (TO_DATE('01-MAR-2003', 'DD-MON-YYYY'))
        tablespace purchases_feb2003,
    partition purchases_mar2003 
        values less than (TO_DATE('01-APR-2003', 'DD-MON-YYYY'))
        tablespace purchases_mar2003 
)
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.time_key AND 
      f.product_id = p.product_id AND
      t.time_key BETWEEN TO_DATE('01-JAN-2003', 'DD-MON-YYYY') AND
                         TO_DATE('31-MAR-2003', 'DD-MON-YYYY')
GROUP BY t.time_key, p.product_id;

---------------------------------------------------------------------------------------
-- 7.2.4 Indexing the 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);

---------------------------------------------------------------------------------------
-- 7.2.5 Security of Materialized Views
---------------------------------------------------------------------------------------

-- Add privileges 
GRANT select any table to easydw;
GRANT execute any procedure to easydw;

-- Add privileges for materialized views and query rewrite
GRANT create materialized view to easydw;
GRANT drop materialized view to easydw;
GRANT alter materialized view to easydw;
GRANT global query rewrite to easydw;

---------------------------------------------------------------------------------------
-- 7.3.1 Using the DBMS_MVIEW refresh procedures
---------------------------------------------------------------------------------------


VARIABLE failures number;
EXECUTE DBMS_MVIEW.REFRESH_ALL_MVIEWS(:failures); 

EXECUTE DBMS_MVIEW.REFRESH('MONTHLY_SALES_MV', 'C');

EXECUTE DBMS_MVIEW.REFRESH('MONTHLY_SALES_MV, Q12003_SALES_MV',
                           atomic_refresh=>TRUE);

EXECUTE DBMS_MVIEW.REFRESH_DEPENDENT(:failures, 'customer', '?', 
                                     refresh_after_errors=>true);)

---------------------------------------------------------------------------------------
-- 7.3.3.1 Fast Refresh using Materialized View Logs
---------------------------------------------------------------------------------------

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 (time_key, 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.time_key AND
       ps.product_id = p.product_id
 GROUP BY t.month, t.year, p.product_id;

---------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW LOG on customer WITH ROWID;

CREATE MATERIALIZED VIEW product_customer_mv
BUILD IMMEDIATE 
REFRESH FAST ON COMMIT 
AS
SELECT c.rowid r1, c.gender, 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;

---------------------------------------------------------------------------------------
-- 7.3.4 Partition Change Tracking
---------------------------------------------------------------------------------------

EXECUTE DBMS_MVIEW.REFRESH('monthly_sales_mv', 'P');

---------------------------------------------------------------------------------------
-- 7.3.4.1.1 Creating a Materialized View that supports PCT refresh
-- 
-- Join Dependency Expression
---------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW monthly_sales_mv
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.time_key AND
      ps.product_id = p.product_id
GROUP BY t.month, t.year, p.product_id;

CREATE MATERIALIZED VIEW regional_sales_mv
AS
SELECT c.region, 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.region;

---------------------------------------------------------------------------------------
-- Partition Key
---------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW regional_sales_partkey_mv
AS
SELECT c.region, ps.time_key,
       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.region, ps.time_key;

SELECT * FROM regional_sales_mv;

SELECT * FROM regional_sales_partkey_mv;

---------------------------------------------------------------------------------------
-- Partition Marker
---------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW regional_sales_marker_mv
REFRESH FORCE
AS
SELECT c.region, DBMS_MVIEW.PMARKER(ps.rowid) as pmark,
       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.region, DBMS_MVIEW.PMARKER(ps.rowid);

select * from regional_sales_marker_mv;

---------------------------------------------------------------------------------------
-- 7.3.5 Refresh Performance
---------------------------------------------------------------------------------------

SELECT what job_description, this_date, total_time
FROM dba_jobs 
WHERE what like '%REFRESH%';

---------------------------------------------------------------------------------------
-- 7.3.6 Nested Materialized Views
---------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW monthly_cust_sales_mv 
ENABLE QUERY REWRITE
AS 
SELECT t.year, t.quarter, 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.quarter, t.month, c.customer_id;

CREATE MATERIALIZED VIEW quarterly_sales_mv 
ENABLE QUERY REWRITE
AS 
SELECT m.year, m.quarter, SUM(m.dollar_sales) AS dollar_sales
FROM monthly_cust_sales_mv m
GROUP BY m.year, m.quarter;

---------------------------------------------------------------------------------------
7.3.6.1	Why use Nested Materialized Views? 
---------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW sales_mv1 
ENABLE QUERY REWRITE
AS 
SELECT c.customer_id, SUM(f.purchase_price) AS dollar_sales
FROM purchases f, customer c
WHERE f.customer_id = c.customer_id
GROUP BY c.customer_id
HAVING SUM(f.purchase_price)
    IN (SELECT max(f.purchase_price) dollar_sales
        FROM purchases f, time t
        WHERE f.time_key = t.time_key
          AND t.month_name = 'January' 
          AND t.year = 2003
        GROUP BY f.customer_id);

---------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW sales_submv1 
REFRESH FAST 
ENABLE QUERY REWRITE
AS 
SELECT c.customer_id, SUM(f.purchase_price) AS dollar_sales,
	COUNT(f.purchase_price) as cnt_sales, COUNT(*) cstar
FROM purchases f, customer c
WHERE f.customer_id = c.customer_id
GROUP BY c.customer_id;

---------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW sales_submv2 
REFRESH FAST 
ENABLE QUERY REWRITE
AS 
SELECT f.customer_id, max(f.purchase_price) max_sales
FROM purchases f, time t
WHERE f.time_key = t.time_key
  AND t.month_name = 'January' and t.year = 2003
GROUP BY f.customer_id;

---------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW sales_mv1 
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS 
SELECT customer_id, dollar_sales
FROM sales_submv1
WHERE dollar_sales IN (SELECT max_sales FROM sales_submv2);

---------------------------------------------------------------------------------------
-- 7.3.6.2 Refreshing Nested Materialized Views
---------------------------------------------------------------------------------------

execute dbms_mview.refresh('sales_mv1', nested=>TRUE);

execute dbms_mview.refresh_dependent(:failures,'time', nested=>TRUE);

---------------------------------------------------------------------------------------
-- 7.4	EXPLAIN_MVIEW Utility
---------------------------------------------------------------------------------------

-- Running EXPLAIN_MVIEW procedure
  
DROP MATERIALIZED VIEW LOG on PRODUCT;

ALTER MATERIALIZED VIEW LOG on TIME EXCLUDING 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.time_key AND
          f.product_id = p.product_id
    GROUP BY t.month, t.year, p.product_id');
END;
/

SELECT capability_name, possible p, related_text obj,    
       msgtxt  explanation
FROM MV_CAPABILITIES_TABLE;

---------------------------------------------------------------------------------------

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.time_key AND
          f.product_id = p.product_id
    GROUP BY t.month, t.year, p.product_id');
END;
/

SELECT capability_name, possible 
FROM MV_CAPABILITIES_TABLE 
WHERE capability_name LIKE 'REFRESH%';
			
EXECUTE dbms_mview.explain_mview('EASYDW.MONTHLY_SALES_MV'); 

---------------------------------------------------------------------------------------
-- 7.5	TUNE_MVIEW Utility
---------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW monthly_cat_sales_mv
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT distinct p.category, t.month 
FROM product p, purchases ps, time t
WHERE ps.product_id = p.product_id
  AND ps.time_key = t.time_key;

BEGIN
  dbms_mview.explain_mview (
   SELECT distinct p.category,  t.month 
   FROM product p, purchases ps, time t
   WHERE ps.product_id = p.product_id 
     AND ps.time_key = t.time_key;
   );
END;
/

SELECT capability_name, possible p,  msgtxt  explanation
FROM MV_CAPABILITIES_TABLE
WHERE CAPABILITY_NAME LIKE 'REFRESH%';

---------------------------------------------------------------------------------------

DECLARE
  taskname  varchar2(20);
BEGIN
  taskname := 'MY_TUNE_MVIEW_TASK';

 dbms_advisor.tune_mview(taskname,
   'CREATE MATERIALIZED VIEW monthly_sales_mv
    REFRESH FAST ON DEMAND
    ENABLE QUERY REWRITE
    AS
    SELECT distinct p.category, t.month 
    FROM product p, purchases ps, time t
    WHERE ps.product_id = p.product_id 
      AND ps.time_key = t.time_key'
 );
END;
/

-- change directory to an appropriate path
CREATE DIRECTORY TUNE_RESULTS AS '/oracle/scripts';

BEGIN
 DBMS_ADVISOR.CREATE_FILE
 (DBMS_ADVISOR.GET_TASK_SCRIPT('MY_TUNE_MVIEW_TASK'),
                               'TUNE_RESULTS',               
                               'mv_create.sql');
END;
/

BEGIN
 DBMS_ADVISOR.CREATE_FILE
 (DBMS_ADVISOR.GET_TASK_SCRIPT('MY_TUNE_MVIEW_TASK', 'UNDO'),
                               'TUNE_RESULTS',               
                               'mv_undo.sql');
END;
/

---------------------------------------------------------------------------------------