RelationalDBDesign RelationalDBDesign 


Performance Tuning   «Prev 

Syntax of Materialized View Creation

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.