Oracle in a Nutshell

Name

CREATE/ALTER/DROP MATERIALIZED VIEW

Synopsis

CREATE Syntax:

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

ALTER syntax:

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

DROP Syntax:

DROP MATERIALIZED VIEW [schema.]materialized_view_name

Creates, modifies, or removes a materialized view (also called a snapshot), which is the result of a query run against one or more tables or views. The script dbmssnap.sql must be run by SYS before you attempt to create a materialized view. New with Oracle8i.

Keywords

materialized_view_name

Name of the materialized view. Because Oracle appends 7-character identifiers to the name when creating materialized view objects in the schema, you should limit the materialized_view_name to 27 characters or fewer.

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.

REFRESH

Specifies the mode and times for automatic refreshes.

FAST

Use the materialized view log associated with the master table.

COMPLETE

Refresh by reexecuting the materialized view’s query.

FORCE

Oracle will decide if a FAST refresh is possible and, if not, will do a COMPLETE refresh. FORCE is the ...