CREATE MATERIALIZED VIEW name
storage_clause
[PARALLEL]
build_clause
refresh_clause refresh_type
ENABLE/DISABLE QUERY REWRITE
AS
SELECT_statement
Syntax of materialized view creation
Location 1
Required keywords.
Location 2
An optional keyword that specifies if parallelism is allowed for queries on this materialized view.
Location 3
A unique name for the materialized view.
Location 4
A clause that describes the storage characteristics of the view.
Location 5
A clause that describes how and how often a materialized view will be refreshed.
Location 6
Keywords that specify if a materialized view will be used for automatic query rewrites.
Location 7
A required keyword.
Location 8
The SELECT statement used to populate the materialized view.
Location 9
A clause that defines when a materialized view will be built.
Create materialized view Syntax
The basic syntax for creating a materialized view is shown in the following listing.
Following the command description, examples are given that illustrate the creation of local replicas of remote data.
create materialized view [user.]name
[ organization index iot_clause]
[ { { segment attributes clauses }
| cluster cluster (column [, column] ...) }
[ {partitioning clause | parallel clause | build clause } ]
| on prebuilt table [ {with | without} reduced precision ] ]
[ using index
[ { physical attributes clauses| tablespace clause }
[ physical attributes clauses| tablespace clause ]
| using no index ]
[ refresh clause ]
[ for update ] [{disable | enable} query rewrite]
as subquery;
The create materialized view command has four major sections. The first section is the
header, in which the materialized view is named (the first line in the listing):
create materialized view [user.]name
The materialized view will be created in your user account (schema) unless a different
username is specified in the header. In the second section, the storage parameters are set:
[ organization index iot_clause]
[ { { segment attributes clauses }
| cluster cluster (column [, column] ...) }
[ {partitioning clause | parallel clause | build clause } ]
| on prebuilt table [ {with | without} reduced precision ] ]
[ using index
[ { physical attributes clauses| tablespace clause }
[ physical attributes clauses| tablespace clause ]
| using no index ]
The storage parameters will be applied to a table that will be created in the local database. If the data has already been replicated to a local table, you can use the on prebuilt table clause to tell Oracle to use that table as a materialized view.