DROP MATERIALIZED VIEW sales_cube_mv;


CREATE MATERIALIZED VIEW sales_cube_mv
   ENABLE QUERY REWRITE
   AS
   SELECT calendar_year year, calendar_quarter_desc quarter, 
          calendar_month_desc month, cust_state_province state, 
          cust_city city,
          GROUPING_ID (calendar_year,calendar_quarter_desc,
                       calendar_month_desc,
                       cust_state_province,cust_city) gid,
          GROUPING(calendar_year) grp_y,
          GROUPING(calendar_quarter_desc) grp_q,
          GROUPING(calendar_month_desc) grp_m,
          GROUPING(cust_state_province) grp_s,
          GROUPING(cust_city) grp_c,
          SUM(amount_sold) sum_sales
   FROM sales s, times t, customers c
   WHERE s.time_id=t.time_id AND s.cust_id=c.cust_id
   GROUP BY GROUPING SETS ((calendar_year, cust_city),
                           (calendar_year, cust_city, 
                            cust_state_province),
                           (calendar_year, calendar_quarter_desc, 
                            calendar_month_desc,cust_city));

exec dbms_stats.gather_table_stats('SH','sales_cube_mv');

DROP MATERIALIZED VIEW sales_gby_mv;

CREATE MATERIALIZED VIEW sales_gby_mv
   ENABLE QUERY REWRITE
   AS
   SELECT   calendar_year year, cust_state_province state,
            SUM(amount_sold) sum_sales
   FROM     sales s, times t, customers c
   WHERE    s.time_id=t.time_id AND s.cust_id=c.cust_id
   GROUP BY (calendar_year, cust_state_province);

exec dbms_stats.gather_table_stats('SH','sales_gby_mv');