Materialized Views in Oracle

Materialized views are used to refresh the contents of a source table data in regular interval or on-demand. It's previously known as snapshot.

Basic Syntax

CREATE MATERIALIZED VIEW <view-name>

BUILD [IMMEDIATE | DEFERRED]

REFRESH [FAST | COMPLETE | FORCE ]

ON [COMMIT | DEMAND ]

[[ENABLE | DISABLE] QUERY REWRITE]

[ON PREBUILT TABLE]

AS

SELECT ...;

The BUILD clause options are given below. This option is used when you try to create a materialized view.

• IMMEDIATE - The materialized view is created immediately on CREATE MATERIALIZED view statement.
• DEFERRED - Materialized view is not populated while creating it, instead it will populate at the first requested refresh.

The REFRESH clause options are given below. This controls how the data in materialized views to be refreshed.

• FAST - Materialized view refreshed quickly using materialized view logs from base table. If materialized view logs not found then refresh will throw error.

SQL> CREATE MATERIALIZED VIEW APP_INSTANCE_MW

  2  BUILD IMMEDIATE

  3  REFRESH FAST

  4  AS SELECT APPLICATION_INSTANCE_ID FROM APP_INSTANCE;

AS SELECT APPLICATION_INSTANCE_ID FROM APP_INSTANCE

                                       *

ERROR at line 4:

ORA-23413: table "DEV_DB_4"."APP_INSTANCE" does not have a materialized view log

SQL> CREATE MATERIALIZED VIEW LOG ON APP_INSTANCE

  2  TABLESPACE NEON_1M

  3  WITH PRIMARY KEY

  4  INCLUDING NEW VALUES;

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW APP_INSTANCE_MW

  2  BUILD IMMEDIATE

  3  REFRESH FAST

  4  AS SELECT APPLICATION_INSTANCE_ID FROM APP_INSTANCE;

Materialized view created.

• COMPLETE - Materialized view segment is truncated and re-populated using base query. This refresh takes lot of time to complete. 
• FORCE - On force refresh, first it will attempt a FAST refresh using materialized view logs from source table. If it is not found then a COMPLETE refresh used.

Two types of refresh are available. 

• COMMIT - Refresh is triggered if one of underlying table of materialized view is committed with new changes.
• DEMAND - This can be triggered manually or using an automated materialized view refresher.

The ON PREBUILT TABLE clause used to create a materialized view using a pre-built table which have the same name as materialized view. I am creating a materialized view app_instance_mw here from a pre-built table app_instance_mw.

Materialized View Logs

Complete refresh requires lot of time to complete because it truncates the original segment and re-populates using underlying query. So it's best to generate materialized view logs from source table. This logs can be used to refresh materialized view very quickly. 

SQL> SELECT LOG_TABLE,
  2         MASTER,
  3         LAST_PURGE_DATE,
  4         B.BYTES
  5    FROM USER_MVIEW_LOGS A, USER_SEGMENTS B
  6   WHERE A.LOG_TABLE = B.SEGMENT_NAME;

LOG_TABLE            MASTER               LAST_PURG      BYTES
-------------------- -------------------- --------- ----------
MLOG$_APP_INSTANCE   APP_INSTANCE         09-FEB-16      65536