Specifies the storage characteristics of the materialized view, which can be the same as the characteristics of a normal view or table.
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
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.