Syntax for Creating Materialized View(Example)

Lesson 3	Understanding the syntax of creating a materialized view
Objective	Describe the parameters and what they mean in the CREATE MATERIALIZED VIEW command.

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. `DISABLE` `QUERY 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.

Create Materialized View Command

Click the link below to match clauses with their purposes.
Create Materialized View Command

In the next lesson, you will learn how to create a materialized view for summary data.