Oracle MATERIALIZED VIEW (Refresh)

Oracle MATERIALIZED VIEW (Refresh) is a SQL statement in the Oracle Specific category. Oracle materialized views with refresh options: COMPLETE (full rebuild), FAST (incremental), FORCE (best effort). The syntax is CREATE MATERIALIZED VIEW mv_name REFRESH COMPLETE ON DEMAND AS SELECT ...;. It returns materialized view created. A typical example: CREATE MATERIALIZED VIEW daily_sales_mv REFRESH COMPLETE ON DEMAND AS SELECT TRUNC(order_date) AS sale_day, SUM(amount) AS total FROM orders GROUP BY TRUNC(order_date); -- Manual refresh: EXEC DBMS_MVIEW.REFRESH('daily_sales_mv', 'C'); -- C = Complete, F = Fast, ? = Force -- Automatic refresh: CREATE MATERIALIZED VIEW sales_mv REFRESH START WITH SYSDATE NEXT SYSDATE + 1 AS SELECT ...; -- Fast refresh (needs materialized view log): CREATE MATERIALIZED VIEW LOG ON orders; CREATE MATERIALIZED… A close relative is SEQUENCE (Oracle), which oracle: generates sequential numbers. Independent of tables. Accessed via NEXTVAL and CURRVAL. A close relative is ROWNUM / FETCH FIRST, which oracle: ROWNUM assigns sequential numbers to rows. Used for limiting results (pre-12c).