CREATE TABLE sales (
   trans_date    DATE,
   cust_id       INT,
   sales_amount  NUMBER
); 

INSERT /*+ APPEND */ INTO sales

SELECT TRUNC(SYSDATE,'YYYY')+MOD(ROWNUM,366) trans_date,
       MOD(ROWNUM,100) cust_id,
       ABS(DBMS_RANDOM.RANDOM)/100 sales_amount
  FROM all_objects
/

COMMIT;

BEGIN
    FOR i IN 1 .. 4
    LOOP
        INSERT /*+ APPEND */ INTO sales
        SELECT trans_date, cust_id, 
          ABS(DBMS_RANDOM.RANDOM)/100 sales_amount
          FROM sales;
        COMMIT;
   END LOOP;
END;
/

CREATE TABLE time (
   day  PRIMARY KEY,
   mmyyyy,
   mon_yyyy,
   qtr_yyyy,
   yyyy
)
ORGANIZATION INDEX
AS
SELECT DISTINCT
   trans_date DAY,
   CAST (TO_CHAR(trans_date,'MMYYYY') AS NUMBER) MMYYYY,
   TO_CHAR(trans_date,'MON-YYYY') MON_YYYY,
   'Q' || CEIL(TO_CHAR(trans_date,'MM')/3) || ' FY'
       || TO_CHAR(trans_date,'YYYY') QTR_YYYY,
   CAST(TO_CHAR(trans_date, 'YYYY') AS NUMBER) YYYY
  FROM sales
/ 

ANALYZE TABLE SALES COMPUTE STATISTICS;

ANALYZE TABLE TIME COMPUTE STATISTICS; 

ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;

ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;

CREATE MATERIALIZED VIEW sales_mv
BUILD IMMEDIATE
REFRESH ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT sales.cust_id,
       SUM(sales.sales_amount) sales_amount,
       time.mmyyyy
  FROM sales, time
 WHERE sales.trans_date = time.day
 GROUP BY sales.cust_id, time.mmyyyy
/ 



CREATE DIMENSION time_dim
    LEVEL DAY      IS time.day
    LEVEL MMYYYY   IS time.mmyyyy
    LEVEL QTR_YYYY IS time.qtr_yyyy
    LEVEL YYYY     IS time.yyyy
HIERARCHY TIME_ROLLUP
(
 day CHILD OF
 mmyyyy CHILD OF
 qtr_yyyy CHILD OF
 yyyy
)
ATTRIBUTE mmyyyy
DETERMINES mon_yyyy; 

SELECT time.mmyyyy, SUM(sales_amount)
  FROM sales, time
 WHERE sales.trans_date = time.day
GROUP BY time.mmyyyy

SELECT time.qtr_yyyy, SUM(sales_amount)
 FROM sales, time
WHERE sales.trans_date = time.day
GROUP BY time.qtr_yyyy

QSM-01150: query did not rewrite

QSM-01082: Joining materialized view, SALES_MV, with table, TIME, not possible

QSM-01102: materialized view, SALES_MV, requires join back to table, TIME, on column, QTR_YYYY