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