Understanding the CREATE MATERIALIZED VIEW Command

In modern Oracle Database releases (for example, 19c and 23c), a materialized view is a database object that stores the result of a query on disk. The CREATE MATERIALIZED VIEW command lets you define:

• What the materialized view will be called and where it lives (schema, tablespace).
• How the underlying segment is stored and indexed.
• When and how the data is refreshed from the base tables.
• Whether the optimizer can use it for query rewrite.
• Which query (AS subquery) populates the materialized view.

This lesson focuses on the main clauses in the CREATE MATERIALIZED VIEW statement and how they work together so that you can read or write the syntax with confidence.

At a high level, the statement looks like this:

CREATE MATERIALIZED VIEW schema.mv_name
  -- Physical / storage options
  [TABLESPACE tablespace_name]
  [USING INDEX ...]
  [PARTITION BY ...]
  [PARALLEL n]

  -- Build and refresh behavior
  [BUILD {IMMEDIATE | DEFERRED}]
  [REFRESH {FAST | COMPLETE | FORCE | NEVER}
     [ON COMMIT | ON DEMAND]
     [START WITH date_expr]
     [NEXT date_expr]
     [WITH {PRIMARY KEY | ROWID}]]

  -- Query rewrite
  [ENABLE QUERY REWRITE | DISABLE QUERY REWRITE]

AS
  subquery;

Think of the statement in three layers:

1. Header and storage – names the materialized view and defines where and how it is stored.
2. Build and refresh behavior – controls when the view is populated and how it stays in sync with its base tables.
3. Query definition – the AS subquery that specifies which data is stored.

The header names the materialized view and (optionally) its schema:

CREATE MATERIALIZED VIEW [schema.]mv_name

• schema – optional. If omitted, the materialized view is created in the current user’s schema.
• mv_name – the name of the materialized view. This becomes both a schema object and a physical segment once built.

The next group of clauses controls where and how the materialized view’s data is stored:

[ TABLESPACE tablespace_name ]
[ USING INDEX (index_attributes...) | USING NO INDEX ]
[ { segment attributes clauses | CLUSTER cluster_name (column, ...) } ]
[ { partitioning clause | PARALLEL n | BUILD clause } ]
[ ON PREBUILT TABLE [{WITH | WITHOUT} REDUCED PRECISION] ]

Key concepts:

• TABLESPACE tablespace_name – places the materialized view segment in a specific tablespace.
• USING INDEX – defines storage and attributes for an index that supports the materialized view. You can also use USING NO INDEX if you will create indexes separately.
• Partitioning clause – lets you partition the materialized view for better manageability and performance on large data sets.
• PARALLEL n – specifies the degree of parallelism for operations on the materialized view (creation, refresh, and queries, depending on configuration).
• ON PREBUILT TABLE – tells Oracle to use an existing table as the storage for the materialized view instead of creating a new segment. This is useful when you already have a populated table and want to layer a materialized view definition on top of it.
• WITH / WITHOUT REDUCED PRECISION – controls whether numeric and datetime precision can be reduced when mapping from the base tables to the prebuilt table.

The build clause defines when the materialized view is initially populated:

BUILD IMMEDIATE
-- or
BUILD DEFERRED

• BUILD IMMEDIATE – the materialized view is populated as soon as the CREATE MATERIALIZED VIEW statement completes. This is the most common choice.
• BUILD DEFERRED – the structure is created, but data is not loaded until you perform a refresh. This is useful if you want to control exactly when the initial data load happens (for example, during a maintenance window).

The refresh clause is the heart of a materialized view’s behavior. It controls how changes from the base tables are propagated:

REFRESH {FAST | COMPLETE | FORCE | NEVER}
  [ON COMMIT | ON DEMAND]
  [START WITH date_expr]
  [NEXT date_expr]
  [WITH {PRIMARY KEY | ROWID}]

Refresh methods:

• COMPLETE – truncates and recomputes the materialized view by rerunning the defining query. Simple but potentially expensive on large data sets.
• FAST – applies only the changes since the last refresh, usually based on materialized view logs or partition-change tracking. Requires additional setup but is typically much faster.
• FORCE – attempts a FAST refresh; if that is not possible, falls back to COMPLETE. This is the default when no method is specified.
• NEVER – marks the materialized view as never refreshable. This is rare, but can be useful when you are using ON PREBUILT TABLE and want to control all data changes yourself.

Refresh timing:

• ON COMMIT – refreshes whenever a transaction commits a change to any of the base tables. This keeps the materialized view very current but can slow down commits on high-volume OLTP systems.
• ON DEMAND – requires an explicit call (for example, DBMS_MVIEW.REFRESH) or a scheduled job to refresh the materialized view. This is the default and is common for data warehouse workloads.
• START WITH / NEXT – define a schedule for automatic refresh (for example, every night at midnight) using date expressions.

Row identification:

• WITH PRIMARY KEY – associates rows in the materialized view with rows in the base tables using primary keys.
• WITH ROWID – uses rowids instead of primary keys to track changes.

The choice between PRIMARY KEY and ROWID can affect fast refresh eligibility and how flexible the base table definitions can be over time.

Query rewrite lets the optimizer transparently redirect user queries from base tables to a suitable materialized view:

ENABLE QUERY REWRITE
-- or
DISABLE QUERY REWRITE

• ENABLE QUERY REWRITE – allows the optimizer to consider this materialized view when rewriting queries. Additional privileges and database settings must be in place (for example, appropriate QUERY REWRITE privileges and initialization parameters).
• DISABLE QUERY REWRITE – prevents the materialized view from being used for query rewrite. This may be appropriate for some replication-only use cases.

Even when query rewrite is disabled, applications can still query the materialized view directly by name.

Finally, the AS clause defines the data to store:

AS
  SELECT ...
  FROM   ...
  WHERE  ...
  GROUP BY ...;

• This is the query whose result set is stored in the materialized view.
• The complexity of this query (joins, aggregations, analytic functions, subqueries) determines which refresh methods and query rewrite options are possible.
• For summary materialized views, the query typically includes GROUP BY to aggregate fact data by one or more dimensions.

Later lessons in this module will show concrete examples of summary and replication materialized views and how their defining queries tie back to these clauses.

The following table summarizes the main clauses in the CREATE MATERIALIZED VIEW statement:

Clause	Meaning
storage_clause	Specifies physical storage characteristics (tablespace, segment attributes, clustering) for the materialized view’s underlying table.
PARALLEL n	Defines a degree of parallelism for operations on the materialized view. Useful for large data sets and heavy refresh operations.
ON PREBUILT TABLE	Uses an existing table as the storage for the materialized view instead of creating a new segment, often in migration and consolidation scenarios.
build_clause	Controls when the materialized view is first populated: BUILD IMMEDIATE (at creation time) or BUILD DEFERRED (at first refresh).
refresh_clause	Describes how and how often the materialized view is refreshed from base tables, including: Method: FAST, COMPLETE, FORCE, or NEVER. Timing: ON COMMIT or ON DEMAND, plus optional START WITH and NEXT schedule expressions. Row identity: WITH PRIMARY KEY or WITH ROWID.
ENABLE / DISABLE QUERY REWRITE	Enables or disables the use of this materialized view in query rewrite. When enabled and properly configured, the optimizer may transparently redirect queries to the materialized view.
AS subquery	The SELECT statement that defines which data is stored in the materialized view (including joins, filters, and aggregations).

To create and use materialized views, you typically need:

• CREATE MATERIALIZED VIEW and CREATE TABLE (or CREATE ANY TABLE) privileges.
• Either UNLIMITED TABLESPACE or sufficient quota on the target tablespace.
• ON COMMIT REFRESH privilege if you specify REFRESH ... ON COMMIT on tables you do not own.
• Appropriate privileges on any remote tables accessed via database links.
• QUERY REWRITE (and possibly GLOBAL QUERY REWRITE) if you want the optimizer to use the materialized view for query rewrite.

In practice, DBAs often manage these privileges centrally to control who can create materialized views with performance or refresh impact.

To reinforce your understanding of the clauses and their meanings, you can complete the following exercise:
Create Materialized View Command – Exercise