-- Oracle Materialized Views are used in large data warehouses to provide 
-- summarized data for BI reporting and other dependant applications.

CREATE MATERIALIZED VIEW product_sales_mv
PCTFREE 0
TABLESPACE ts_salesdw_mv
STORAGE (INITIAL 8k NEXT 8k PCTINCREASE 0)
BUILD IMMEDIATE
REFRESH FAST
ENABLE QUERY REWRITE
AS 
SELECT
 l.country_desc,
 p.product_name,
 TO_CHAR(s.transaction_date,'mm') transaction_month,
 TO_CHAR(s.transaction_date,'yyyy') transaction_year,
 SUM(s.sales_amount-s.sales_discount)  dollar_sales,
 SUM(s.sales_tax)  sales_tax,
 COUNT(*) AS cnt
FROM fact_sales_detail s,
     dim_products p,
     dim_locations l
WHERE s.product_id = p.product_id 
AND   s.country_id = l.country_id
GROUP BY
 l.country_desc,
 TO_CHAR(s.transaction_date,'mm'),
 TO_CHAR(s.transaction_date,'yyyy'),
 p.product_name;


-- Materialized View Analysis

SELECT
 mview_name,  rewrite_enabled, rewrite_capability, 
 refresh_mode, refresh_method, build_mode, compile_state,
 TO_CHAR(last_refresh_date,'mm/dd/yyyy hh24:mi:ss')  last_refresh,
 TO_CHAR(stale_since,'mm/dd/yyyy hh24:mi:ss')  stale_since
FROM user_mviews;

-- DBA mview analysis
SELECT
 owner, mview_name,  rewrite_enabled, rewrite_capability, 
 refresh_mode, refresh_method, build_mode, compile_state,
 TO_CHAR(last_refresh_date,'mm/dd/yyyy hh24:mi:ss')  last_refresh,
 TO_CHAR(stale_since,'mm/dd/yyyy hh24:mi:ss')  stale_since
FROM dba_mviews
WHERE staleness = 'NEEDS_COMPILE'
;

-- Mview Refresh Details
SELECT * FROM v$mvrefresh;

-- DBA mview analysis query
SELECT owner, mview_name, unusable, known_stale, invalid,
 TO_CHAR(last_refresh_date,'mm/dd/yyyy hh24:mi:ss') last_refresh
FROM dba_mview_analysis
WHERE invalid = 'Y'
;

-- Materialized View - Other Sytnax

CREATE MATERIALIZED VIEW mvw_name
REFRESH FORCE ON DEMAND
AS
SELECT
 col1, col2, . . . .   coln
FROM  
 some_table
;


-- Materialized View Refresh

ALTER MATERIALIZED VIEW mview_name REFRESH FAST;

ALTER MATERIALIZED VIEW mview_name REFRESH NEXT SYSDATE+7;

-- Refresh at 8:00 AM
ALTER MATERIALIZED VIEW mview_name REFRESH COMPLETE 
 START WITH TRUNC(SYSDATE+1) + 8/24
 NEXT SYSDATE+7;

ALTER MATERIALIZED VIEW mview_name CONSIDER FRESH;

ALTER MATERIALIZED VIEW mview_name ENABLE QUERY REWRITE;

ALTER MATERIALIZED VIEW mview_name COMPILE;

BEGIN  DBMS_MVIEW.refresh('owner.mview_name','C');
END;
/