CREATE TABLE dim_dates
(
   date_id      DATE,
   date_desc    VARCHAR2 (100),
   month_id     NUMBER,
   month_desc   VARCHAR2 (100),
   year_id      NUMBER,
   year_desc    VARCHAR2 (100),
   CONSTRAINT dim_dates_pk PRIMARY KEY (date_id)
);

CREATE TABLE fact_sales
(
   date_id      DATE,
   user_id      NUMBER,
   product_id   NUMBER,
   amount       NUMBER,
   CONSTRAINT fact_sales_r01 FOREIGN KEY
      (date_id)
       REFERENCES dim_dates (date_id)
);

INSERT INTO dim_dates
   SELECT dte,
          TO_CHAR (dte, 'DD MONTH YYYY'),
          TO_CHAR (dte, 'YYYYMM'),
          TO_CHAR (dte, 'MONTH YYYY'),
          TO_CHAR (dte, 'YYYY'),
          TO_CHAR (dte, 'YYYY')
     FROM (SELECT DISTINCT TRUNC (SYSDATE, 'YEAR') + MOD (ROWNUM, 1000) dte
             FROM all_objects);

INSERT INTO fact_sales
   SELECT TRUNC (SYSDATE, 'YEAR') + MOD (ROWNUM, 1000),
          MOD (ROWNUM, 100),
          MOD (ROWNUM, 10),
          ABS (DBMS_RANDOM.random) / 1000
     FROM all_objects;

BEGIN
   DBMS_STATS.gather_table_stats (USER, 'DIM_DATES', CASCADE => TRUE);
END;
/

BEGIN
   DBMS_STATS.gather_table_stats (USER, 'FACT_SALES', CASCADE => TRUE);
END;
/

CREATE MATERIALIZED VIEW agg_sales_monthly
    BUILD IMMEDIATE
    REFRESH ON DEMAND
    ENABLE QUERY REWRITE
AS
    SELECT d.month_id, f.product_id, SUM(f.amount)
      FROM fact_sales f, dim_dates d
     WHERE f.date_id = d.date_id
     GROUP BY d.month_id, f.product_id;

BEGIN
   DBMS_STATS.gather_table_stats (USER, 'AGG_SALES_MONTHLY', CASCADE => TRUE);
END;
/

EXPLAIN PLAN
   FOR
        SELECT d.month_id, SUM (f.amount)
          FROM fact_sales f, dim_dates d
         WHERE f.date_id = d.date_id
      GROUP BY d.month_id;

EXPLAIN PLAN
   FOR
        SELECT d.year_id, SUM (f.amount)
          FROM fact_sales f, dim_dates d
         WHERE f.date_id = d.date_id
      GROUP BY d.year_id;

CREATE DIMENSION dim_time
    LEVEL day        IS dim_dates.date_id
    LEVEL month     IS dim_dates.month_id
    LEVEL year        IS dim_dates.year_id
HIERARCHY time_rollup
    (day        CHILD OF
     month    CHILD OF
     year);

EXPLAIN PLAN
   FOR
        SELECT d.year_id, SUM (f.amount)
          FROM fact_sales f, dim_dates d
         WHERE f.date_id = d.date_id
      GROUP BY d.year_id;

ALTER SESSION SET query_rewrite_integrity = trusted;

EXPLAIN PLAN
   FOR
        SELECT d.year_id, SUM (f.amount)
          FROM fact_sales f, dim_dates d
         WHERE f.date_id = d.date_id
         GROUP BY d.year_id;