Categories:
Table, View, & Sequence DDL

ALTER MATERIALIZED VIEW

Alters a materialized view in the current/specified schema. Supported actions include:

  • Renaming the materialized view.
  • Suspending and resuming use and maintenance of the materialized view.
  • Clustering the materialized view.
  • Suspending and resuming reclustering of the materialized view.
  • Dropping clustering of the materialized view.

For more details, see Working with Materialized Views.

See also:
CREATE MATERIALIZED VIEW , DROP MATERIALIZED VIEW , SHOW MATERIALIZED VIEWS

Syntax

ALTER MATERIALIZED VIEW <name>
  {
  RENAME TO <new_name>                     |
  CLUSTER BY ( <expr1> [, <expr2> ... ] )  |
  DROP CLUSTERING KEY                      |
  SUSPEND RECLUSTER                        |
  RESUME RECLUSTER                         |
  SUSPEND                                  |
  RESUME
  }

Parameters

name

Specifies the identifier of the materialized view to alter.

If the RENAME clause is specified, you must include a new identifier for the view. The new identifier must be unique for the schema in which the view is created.

The identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive. For more details, see Identifier Syntax.

new_name

Specifies the new identifier of the materialized view.

The new identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (e.g. "My object"). Identifiers enclosed in double quotes are also case-sensitive. For more details, see Identifier Syntax.

expr#
Specifies an expression on which to cluster the materialized view. Typically, each expression is the name of a column in the materialized view.

Usage Notes

  • RENAME TO.

    This option allows you to rename a materialized view. Note that renaming a materialized view does not update references to that view. For example, if you create a view named “V1” on top of a materialized view, and then you rename the materialized view, the definition of view “V1” will become out of date.

  • CLUSTER BY.

    This command allows you to cluster the materialized view. Clustering re-orders the rows in the materialized view to increase performance for queries that filter based on the cluster key expressions. For more information about clustering materialized views, see: Materialized Views and Clustering. For more information about clustering in general, see: What is Data Clustering?.

  • DROP CLUSTERING KEY.

    This command drops the clustering of the materialized view.

  • SUSPEND RECLUSTER.

    The SUSPEND RECLUSTER option suspends re-clustering of the materialized view. For more information about clustering materialized views, see Materialized Views and Clustering.

  • RESUME RECLUSTER.

    The RESUME RECLUSTER option resumes reclustering of the materialized view.

  • SUSPEND.

    The SUSPEND option suspends the maintenance (updates) and use of the materialized view. While the view is suspended, updates to the base table are not propagated to the materialized view. The materialized view itself is also inaccessible; if you attempt to use it, you will get an error message similar to:

    SQL compilation error: Failure during expansion of view ‘MV1’: SQL compilation error: Materialized View MV1 is invalid.

    If you suspend a clustered materialized view, suspending the view implicitly suspends reclustering of that view.

  • RESUME.

    The RESUME option allows you to resume using the materialized view. It also resumes maintenance of the materialized view. If the view is clustered, it also implicitly resumes reclustering of that view.

Examples

Rename a materialized view:

ALTER MATERIALIZED VIEW table1_MV RENAME TO my_mv;

Cluster a materialized view:

ALTER MATERIALIZED VIEW my_mv CLUSTER BY(i);

Suspend clustering of a materialized view, but not use of the view:

ALTER MATERIALIZED VIEW my_mv SUSPEND RECLUSTER;

Resume clustering of a materialized view:

ALTER MATERIALIZED VIEW my_mv RESUME RECLUSTER;

Suspend all use and automatic maintenance of the specified materialized view:

ALTER MATERIALIZED VIEW my_mv SUSPEND;

Resume all use and automatic maintenance of the specified materialized view:

ALTER MATERIALIZED VIEW my_mv RESUME;

Stop clustering a materialized view:

ALTER MATERIALIZED VIEW my_mv DROP CLUSTERING KEY;