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                                   |
  SET SECURE                               |
  UNSET SECURE                             |
  SET COMMENT = '<comment>'                |
  UNSET COMMENT                            |
  }

Parameters

name

Specifies the identifier of the materialized view to alter.

RENAME TO new_name

This option allows you to rename a materialized view.

The new identifier must be unique for the schema in which the view is created. 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 Requirements.

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 becomes out of date.

CLUSTER BY expr#

This command clusters the materialized view. Clustering re-orders the rows in the materialized view to increase performance for queries that filter based on the clustering key expressions.

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

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.

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 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.

SET ...

Specifies the property to set for the materialized view:

SECURE

This option turns the view into a secure view. For more information about secure views, see Working with Secure Views.

COMMENT = 'string_literal'

This option sets a comment for the materialized view. The comment has no effect on the behavior of the view, but can provide useful information to people who use or maintain the view.

UNSET ...

Specifies the property to unset for the materialized view:

SECURE

This option turns the view into a non-secure view.

COMMENT

This option removes the comment for the materialized 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;

Modify the view to be a secure view:

ALTER MATERIALIZED VIEW mv1 SET SECURE;

Add or replace the comment for a materialized view:

ALTER MATERIALIZED VIEW mv1 SET COMMENT = 'Sample view';