--------------------------------------------------------------------------------------
-- 9.1 Setting up Query Rewrite
--------------------------------------------------------------------------------------

-- enable query rewrite
ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;

--------------------------------------------------------------------------------------
-- 9.2 Types of Query Rewrite
--------------------------------------------------------------------------------------

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

--------------------------------------------------------------------------------------
-- 9.2.1 SQL Text Match
--------------------------------------------------------------------------------------

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

@?/rdbms/admin/utlxpls;

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

-- partial text match
EXPLAIN PLAN FOR
SELECT t.month, p.product_id,  t.year,
       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;

@?/rdbms/admin/utlxpls;

--------------------------------------------------------------------------------------
-- 9.2.2 Aggregate Rollup 
--------------------------------------------------------------------------------------

-- rollup over month column
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;

@?/rdbms/admin/utlxpls;

--------------------------------------------------------------------------------------
-- 9.2.3 Join-back 
--------------------------------------------------------------------------------------

-- 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;

@?/rdbms/admin/utlxpls;

--------------------------------------------------------------------------------------
-- 9.2.4 Computing other Aggregates in the Query
--------------------------------------------------------------------------------------

-- 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;

@?/rdbms/admin/utlxpls;

--------------------------------------------------------------------------------------
-- 9.2.5 Filtered Data
--------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW sales_elec_1_6_2003_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 AND
       p.category = 'ELEC' AND
       t.month >= 200301 AND t.month <= 200306
 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.time_key AND 
       ps.product_id = p.product_id AND
       p.category = 'ELEC' AND t.month = 200305
 GROUP BY t.month, t.year, p.product_id;

@?/rdbms/admin/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.time_key AND
       ps.product_id = p.product_id AND p.category = 'ELEC' AND
       t.month = 200301 AND product_name  = 'Digital  Camera'
GROUP BY t.month;

@?/rdbms/admin/utlxpls;

--------------------------------------------------------------------------------------
-- 9.2.6 Rewrite Using Materialized Views with No Aggregation
--------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW customer_purchases_mv
ENABLE QUERY REWRITE
AS
SELECT c.gender, c.occupation, f.purchase_price  
FROM purchases f, customer c
WHERE f.customer_id = c.customer_id; 

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

EXPLAIN PLAN FOR 
SELECT c.gender, f.purchase_price  
FROM purchases f, customer c
WHERE f.customer_id = c.customer_id 
   AND c.occupation = 'Doctor';

@?/rdbms/admin/utlxpls;

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

EXPLAIN PLAN FOR 
SELECT c.occupation, SUM(f.purchase_price)  
FROM purchases f, customer c
WHERE f.customer_id = c.customer_id 
   AND c.gender = 'F'
GROUP BY c.occupation;

@?/rdbms/admin/utlxpls;

--------------------------------------------------------------------------------------
-- 9.2.7 Rewrite 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;

--------------------------------------------------------------------------------------
-- Using the ATTRIBUTE clause 
--------------------------------------------------------------------------------------

CREATE DIMENSION customer_dim
  LEVEL customer IS customer.customer_id
  LEVEL city     IS customer.city
  LEVEL state    IS customer.state
HIERARCHY  customer_zone
  ( customer CHILD OF
    city     CHILD OF
    state ) 
ATTRIBUTE customer DETERMINES (customer.gender, 
                               customer.occupation);
--------------------------------------------------------------------------------------

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;
--------------------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT c.gender, 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.gender, c.occupation;

@?/rdbms/admin/utlxpls;

--------------------------------------------------------------------------------------
-- 9.2.8 Rewrite using Constraints 
--------------------------------------------------------------------------------------

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;

ALTER TABLE purchases ENABLE NOVALIDATE CONSTRAINT fk_customer_id;

ALTER TABLE purchases MODIFY CONSTRAINT fk_customer_id RELY;

--------------------------------------------------------------------------------------
-- 9.3 Query Rewrite Integrity Modes
--------------------------------------------------------------------------------------

INSERT INTO product VALUES ('SP1300', 'XYZ', 'ELEC', '75.0',
                            '100.0', 15, 4.50, 'ABC', 'UVW');
COMMIT;

INSERT INTO purchases VALUES ('SP1300','1-FEB-2003', 
            'AB123456','1-FEB-2003', 28.01, 4.50, 'Y');

INSERT INTO purchases VALUES ('SP1300','2-FEB-2003', 
            'AB123457','1-FEB-2003', 28.01, 4.50, 'Y');

COMMIT;

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

SELECT staleness FROM user_mviews 
WHERE mview_name = 'MONTHLY_SALES_MV';

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
HAVING p.product_id = 'SP1300';

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

ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;

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
HAVING p.product_id = 'SP1300';               


--------------------------------------------------------------------------------------
-- 9.4 Query Rewrite and Partition Change Tracking
-- 
-- 9.4.1 Query Rewrite using PCT with Partition Key
--------------------------------------------------------------------------------------

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;

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

SELECT staleness FROM user_mviews 
WHERE mview_name = 'PRODUCT_CATEGORY_SALES_MV';

ALTER TABLE purchases ADD PARTITION purchases_jan2005
            values less than (TO_DATE('01-02-2005', 'DD-MM-YYYY'));

INSERT INTO purchases VALUES ( 'SP1063','2-JAN-2005', 
            'AB123457','7-JAN-2005', 28.01, 4.50, 'N');

INSERT INTO purchases VALUES ( 'SP1064','2-JAN-2005', 
            'AB123457','8-JAN-2005', 28.01, 4.50, 'N');

COMMIT;

SELECT staleness FROM user_mviews 
WHERE mview_name = 'PRODUCT_CATEGORY_SALES_MV';

--------------------------------------------------------------------------------------
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-10-2004', 'DD-MM-YYYY')
                        AND TO_DATE('31-12-2004', 'DD-MM-YYYY')
 GROUP BY ps.time_key, p.category;

@?/rdbms/admin/utlxpls;

--------------------------------------------------------------------------------------
-- 9.4.2 Query Rewrite using PCT with Partition Marker
--------------------------------------------------------------------------------------

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


EXPLAIN PLAN FOR
SELECT 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  >= TO_DATE('01-10-2004', 'DD-MM-YYYY') 
  AND ps.time_key  < TO_DATE('01-01-2005', 'DD-MM-YYYY')
GROUP BY p.category;

--------------------------------------------------------------------------------------
-- 9.5 Troubleshooting Query Rewrite with EXPLAIN_REWRITE
--------------------------------------------------------------------------------------

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;

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

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

SELECT mv_name, message FROM rewrite_table;

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

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, time t
 WHERE p.product_id = ps.product_id
   AND t.time_key = ps.time_key
 GROUP BY p.product_id', 'MONTHLY_SALES_MV');
END;
/ 

SELECT mv_name, message FROM rewrite_table;

--------------------------------------------------------------------------------------
-- 9.6 Advanced Query Rewrite Techniques
-- 
-- 9.6.1 Optimizer Hints for Query Rewrite
--------------------------------------------------------------------------------------

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;

@?/rdbms/admin/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;

@?/rdbms/admin/utlxpls;

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

SELECT /*+ REWRITE_OR_ERROR */ t.time_key,  
       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.time_key, p.product_id;

--------------------------------------------------------------------------------------
-- 9.6.2 Query Rewrite and Bind Variables
--------------------------------------------------------------------------------------

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

@?/rdbms/admin/utlxpls;

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

EXPLAIN PLAN FOR
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 AND
       p.category = :1 AND
       t.month >= 200301 and t.month <= 200306
GROUP BY t.month, t.year, p.product_id;

@?/rdbms/admin/utlxpls;

--------------------------------------------------------------------------------------
-- 9.6.3 Query Rewrite with Complex SQL Constructs
-- 
-- Set Operators
--------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW muscelec_mv
ENABLE QUERY REWRITE
AS
SELECT 'M' um, p.product_id, p.manufacturer, 
        SUM(ps.purchase_price)
FROM purchases ps, product p
WHERE ps.product_id = p.product_id AND p.category = 'MUSC'
GROUP BY p.product_id, p.manufacturer
UNION ALL
SELECT 'E' um, p.product_id, p.manufacturer,
       SUM(ps.purchase_price)
FROM purchases ps, product p
WHERE ps.product_id = p.product_id AND p.category = 'ELEC'
GROUP BY p.product_id, p.manufacturer;

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

EXPLAIN PLAN FOR 
SELECT p.product_id, SUM(ps.purchase_price)
FROM purchases ps, product p
WHERE ps.product_id = p.product_id AND p.category = 'ELEC'
GROUP BY p.product_id
UNION ALL
SELECT  p.product_id, SUM(ps.purchase_price)
FROM purchases ps, product p
WHERE ps.product_id = p.product_id AND p.category = 'MUSC' AND
      p.manufacturer = 'ABC'
GROUP BY p.product_id;

@?/rdbms/admin/utlxpls;

--------------------------------------------------------------------------------------
-- Sub-queries in the FROM clause
--------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW prodcat_sales_mv
 ENABLE QUERY REWRITE
 AS
 SELECT v.category, SUM(ps.purchase_price) as sum_of_sales
 FROM  (SELECT * FROM product p 
 WHERE p.manufacturer = 'ABC') v, purchases ps 
 WHERE ps.product_id = v.product_id
 GROUP BY v.category;

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

EXPLAIN PLAN FOR
SELECT v.category, SUM(ps.purchase_price) as sum_of_sales
 FROM  (SELECT * FROM product p 
 WHERE p.manufacturer = 'ABC') v, purchases ps 
 WHERE ps.product_id = v.product_id and v.category = 'ELEC'
 GROUP BY v.category;

@?/rdbms/admin/utlxpls;

--------------------------------------------------------------------------------------
-- Multiple occurrences of a table in the FROM clause
--------------------------------------------------------------------------------------

EXPLAIN PLAN FOR 
SELECT ot.month, SUM(ps.purchase_price) as sum_of_sales
FROM  purchases ps, time ot, time st
WHERE ps.time_key = ot.time_key AND ps.ship_date = st.time_key
  AND st.week_number – ot.week_number <= 1
GROUP BY ot.month; 

@?/rdbms/admin/utlxpls;

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

CREATE MATERIALIZED VIEW sameweek_sales_mv
ENABLE QUERY REWRITE
AS
SELECT od.month ord_mon, sd.month ship_mon, 
       od.week_number ord_week, sd.week_number ship_week,
       SUM(ps.purchase_price) as sum_of_sales
FROM  purchases ps, time od, time sd
WHERE ps.time_key = od.time_key AND ps.ship_date = sd.time_key
GROUP BY od.month, sd.month, sd.week_number, od.week_number;

--------------------------------------------------------------------------------------
-- 9.6.3.4 Grouping Sets
--------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW sales_mv
ENABLE QUERY REWRITE
AS
SELECT p.category,  t.time_key, c.country, c.state,
       SUM(f.purchase_price) sales,
       GROUPING_ID(p.category, t.time_key, c.country, c.state) 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.state),
                        (t.time_key, c.country));

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

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;

@?/rdbms/admin/utlxpls;

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

EXPLAIN PLAN FOR
SELECT p.category,  t.time_key, c.country, t.year, c.state,
 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.state),
                        (p.category, c.country, t.year));

@?/rdbms/admin/utlxpls;

--------------------------------------------------------------------------------------
---  Analytic Functions
--------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW rank_mv
ENABLE QUERY REWRITE
AS
SELECT p.product_id p_id, SUM(f.purchase_price) as sales,
       RANK() over (ORDER BY SUM(f.purchase_price)) as rank
FROM purchases f, product p
WHERE f.product_id = p.product_id
GROUP BY p.product_id;

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

EXPLAIN PLAN FOR
SELECT * FROM
(SELECT p.product_id p_id,
       RANK() over (ORDER BY SUM(f.purchase_price)) as rank
 FROM purchases f, product p
 WHERE f.product_id = p.product_id
 GROUP BY p.product_id)
WHERE rank < 10;

@?/rdbms/admin/utlxpls;

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

EXPLAIN PLAN FOR
SELECT p.product_id p_id,
       DENSE_RANK() over (ORDER BY SUM(f.purchase_price)) as drank,
       RANK() over (ORDER BY SUM(f.purchase_price) DESC) as rev_rank
FROM purchases f, product p
WHERE f.product_id = p.product_id
GROUP BY p.product_id;

@?/rdbms/admin/utlxpls;

--------------------------------------------------------------------------------------
-- 9.6.4 Query Rewrite using Nested Materialized Views
--------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW YEARLY_PROD_SALES_MV
ENABLE QUERY REWRITE
AS
SELECT m.product_id, SUM(m.sum_of_sales) as yearly_sales
FROM monthly_sales_mv m
GROUP BY m.product_id;

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

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

@?/rdbms/admin/utlxpls;

--------------------------------------------------------------------------------------
-- 9.6.5 Rewrite Equivalences
--------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW SALES_FORECAST_MV
ENABLE QUERY REWRITE
AS
SELECT t.month, t.year, 
       SalesForecast(ps.purchase_price)  sales_forecast
FROM time t, purchases ps
WHERE t.time_key = ps.time_key
GROUP BY t.month, t.year;

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

BEGIN
SYS.DBMS_ADVANCED_REWRITE.DECLARE_REWRITE_EQUIVALENCE (
   'SALES_FORECAST_ROLLUP',
   'SELECT t.year, SALESFORECAST(ps.purchase_price) sales_forecast
    FROM time t, purchases ps
    WHERE t.time_key = ps.time_key
    GROUP BY t.year', 
   'SELECT year, SUM(sales_forecast) yearly_forecast
    FROM sales_forecast_mv
    GROUP BY year' 
);
END;
/

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

EXPLAIN PLAN FOR
SELECT t.year, SALESFORECAST(ps.purchase_price)  sales_forecast
FROM time t, purchases ps
WHERE t.time_key = ps.time_key
GROUP BY t.year;

@?/rdbms/admin/utlxpls;

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

GRANT EXECUTE ON DBMS_ADVANCED_REWRITE TO <user>;

EXECUTE DBMS_ADVANCED_REWRITE.ALTER_REWRITE_EQUIVALENCE
            ('SALES_FORECAST_ROLLUP', mode=>'disabled');

--------------------------------------------------------------------------------------
-- 9.6.6 Using Query Rewrite during Refresh                                            
--------------------------------------------------------------------------------------

CREATE MATERIALIZED VIEW product_category_sales_mv
REFRESH FORCE
USING TRUSTED CONSTRAINTS             
 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;

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