Understanding the syntax of creating a materialized view
Objective
Describe the parameters and what they mean in the CREATE MATERIALIZED VIEW command.
Materialized View Syntax Creation
Understanding the Syntax of Creating Materialized View
You create a materialized view by using Data Definition Language (DDL), a form of SQL that is used for defining all types of database objects.
Materialized View Syntax
The basic syntax for creating a materialized view is shown in the following tooltip:
CREATE MATERIALIZED VIEW name
storage_clause
[PARALLEL]
build_clause
refresh_clause refresh_type
ENABLE/DISABLE QUERY REWRITE
AS
SELECT_statement
Required keywords
An optional keyword that specifies if parallelism is allowed for queries on this materialized view.
A unique name for the materialized view
A clause that describes the storage characteristics of the view.
A clause that describes how and how often a materialized view will be refreshed.
Keywords that specify if a materialized view will be used for automatic query rewrites.
A required keyword.
The SELECT statement used to populate the materialized view.
A clause that defines when a materialized view will be built.
Syntax Materialized View Creation
The following table explains the meaning of the different clauses used in the CREATE MATERIALIZED VIEW statement:
Clause
Meaning
storage_clause
Specifies the storage characteristics of the materialized view, which can be the same as the characteristics of a normal view or table.
build_clause
Specifies if the materialized view is to be built as soon as the CREATE statement is committed with the value of
BUILD IMMEDIATE, or if the building of the view is deferred until a later time when it is specifically invoked with the value of BUILD DEFERRED.
refresh_clause / refresh_type
Specifies how and how often the materialized view is refreshed from the base tables. refresh_clause indicates when a
materialized view should be refreshed. The acceptable values for this part of the clause are ON COMMIT, which
forces a refresh each time a change to one of the tables is committed, or ON DEMAND, which will update the view
only when specifically requested and is the default. refresh_clause also includes a refresh type. A materialized view can be refreshed in four basic ways:
COMPLETE, which will cause all the values for the view to be refreshed; FAST, which refreshes a view with
incremental changes since the last refresh; FORCE, which will try to perform a FAST refresh and, if that
is not possible, will do a COMPLETE refresh; and NEVER, which will prevent a materialized view from ever
being refreshed.
ENABLE/DISABLE QUERY REWRITE
Specifies whether to enable or disable automatic query rewrites for queries that could be fulfilled with this view. Query rewrites are discussed in detail later in this module.
DISABLEQUERY REWRITE is the default for this choice.
Required System Privileges
To create a materialized view, you must have the privileges needed to create the underlying objects it will use. You must have the CREATE MATERIALIZED VIEW privilege, as well as the CREATE TABLE or CREATE ANY TABLE system privilege. In addition, you must have either the UNLIMITED TABLESPACE system privilege or a sufficient specified space quota in a local tablespace. To create a refresh-on-commit materialized view, you must also have the ON COMMIT REFRESH system privilege on any tables you do not own, or the ON COMMIT REFRESH system privilege. Materialized views of remote tables require queries of remote tables; therefore, you must have privileges to use a database link that accesses the remote database. The link you use can be either public or private. If the database link is private, you need to have the CREATE DATABASE LINK system privilege to create the database link.
If you are creating materialized views to take advantage of the query rewrite feature (in which the optimizer dynamically chooses to select data from the materialized view instead of the underlying table), you must have the QUERY REWRITE privilege. If the tables are in another user's schema,
you must have the GLOBAL QUERY REWRITE privilege. If the materialized view is created with
on commit refresh specified, you must have the ON COMMIT REFRESH system privilege or the
ON COMMIT REFRESH object privilege on each table outside your schema.
Note: As of Oracle 11g, queries that reference remote tables can support query rewrite.