Oracle SQL: the Essential Reference

CREATE/ALTER/DROP MATERIALIZED VIEW

CREATE MATERIALIZED VIEW [schema.]materialized_view_name [Physical_Attributes_Clause] [TABLESPACE tablespace] [STORAGE Storage_Clause] [REFRESH [FAST | COMPLETE | FORCE] [START WITH date] [NEXTREF date] AS materialized_view_query

Creates a materialized view (also called a snapshot), which is the result of a query run against one or more tables or views.

ALTER MATERIALIZED VIEW [schema.]materialized_view_name
  [Physical_Attributes_Clause]
  [STORAGE Storage_Clause]
  [REFRESH
     [FAST | COMPLETE | FORCE]
     [START WITH date]
     [NEXTREF date]

Changes the storage or automatic refresh characteristics of a materialized view (or snapshot).

DROP MATERIALIZED VIEW [schema.]materialized_view_name

Removes a materialized view (or snapshot) from the database.

Keywords

TABLESPACE

Specifies the name of the tablespace in which this materialized view will be created. The default tablespace for the schema owner is the default.

STORAGE

Specifies the physical storage characteristics. See Chapter 4 for detailed information.

REFRESH

Specifies the mode and times for automatic refreshes. FAST means use the materialized view log associated with the master table; COMPLETE means refresh by re-executing the materialized view's query; FORCE is the default, and means that Oracle will decide if a FAST refresh is possible and, if not, will do a COMPLETE refresh.

START WITH

Specifies a date for the next automatic refresh time using a standard Oracle date expression.

NEXTREF

Specifies ...