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.