Help Center/ Data Warehouse Service / SQL Syntax Reference/ SQL Syntax Reference (9.1.1.x)/ DDL Syntax/ ALTER MATERIALIZED VIEW
Updated on 2025-10-14 GMT+08:00
View PDF
Share

ALTER MATERIALIZED VIEW

Description

ALTER MATERIALIZED VIEW modifies the properties of a materialized view. This syntax is supported only by clusters of version 8.2.1.220 or later.

Precautions

To use ALTER MATERIALIZED VIEW, ensure that enable_matview is set to on.

Syntax

  • Set whether to enable query rewrite for materialized views. 
    1
2
    		 
    ALTER MATERIALIZED VIEW [ IF EXISTS ] { materialized_view_name }
    [ ENABLE | DISABLE ] QUERY REWRITE;
  • Specify how a materialized view is refreshed. Currently, only the COMPLETE refresh mode is supported, which refresh full data in the materialized view. Execute the query statement defined in the materialized view to update the materialized view. 
    1
2
    		 
    ALTER MATERIALIZED VIEW [ IF EXISTS ] { materialized_view_name }
    REFRESH [ [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ] [ COMPLETE ] [ ON DEMAND ] [ [ START WITH (timestamptz) ] | [ EVERY (interval) ] ];
    • REFRESH ON DEMAND indicates that the data is manually refreshed as required.
    • START WITH specifies the first refresh time.
    • EVERY specifies the refresh interval. The value can be MONTH, DAY, HOUR, MINUTE, or SECOND.
  • Change how materialized views refresh asynchronously in the background. This change applies only to materialized views that refresh this way. This syntax is supported only by clusters of version 9.1.0.200 or later. For details, see REFRESH MATERIALIZED VIEW. 
    1
2
    		 
    ALTER MATERIALIZED VIEW [ IF EXISTS ] { materialized_view_name }
    REFRESH [DEFAULT] | [ RESTRICT ] | [ CASCADE FORWARD ] | [ CASCADE BACKWARD ] | [ CASCADE ALL ] ;
  • Change the owner of the materialized view. 
    1
2
    		 
    ALTER MATERIALIZED VIEW { materialized_view_name }
    OWNER TO new_owner;
  • Sett table properties of the materialized view. This syntax is supported only by clusters of version 9.1.0.200 and later. 
    1
2
3
    		 
    ALTER MATERIALIZED VIEW { materialized_view_name }
    SET ( {storage_parameter = value} [, ... ] )
    | RESET ( storage_parameter [, ... ] )
  • Rename a materialized view. materialized_view_name can specify a schema name, but new_materialized_view_name cannot specify a schema name. 
    1
2
    		 
    ALTER MATERIALIZED VIEW [ IF EXISTS ] materialized_view_name
    RENAME TO new_materialized_view_name;

Parameter Description

Table 1 ALTER MATERIALIZED VIEW parameters

Parameter

Description

Value Range

IF EXISTS

If the specified materialized view does not exist, a message instead of an error is returned.

-

materialized_view_name

Specifies the name of the materialized view to be modified.

Name of an existing materialized view.

ENABLE | DISABLE QUERY REWRITE

Specifies whether to enable query rewrite.

Disabled by default.

When ENABLE QUERY REWRITE is specified, you need to set the GUC parameter mv_rewrite_rule to enable query rewriting of materialized views.

REFRESH

Specify how materialized views are refreshed.

When the data in the base table changes, you need to refresh the materialized view (REFRESH MATERIALIZED VIEW) to update the data in the materialized view.

  • Refresh method: The options include DEFAULT (default refresh) and RESTRICT (forcible refresh). You can only specify the refresh method for materialized views that are asynchronously refreshed in the background.
  • Refresh direction: When using materialized views in a cascading nested manner, the refresh direction can be specified as FORWARD, BACKWARD, or ALL. You can only specify the refresh method for materialized views that are asynchronously refreshed in the background. For details, see REFRESH MATERIALIZED VIEW.
  • Currently, only the COMPLETE refresh mode is supported, which refresh full data in the materialized view. Execute the query statement defined in the materialized view to update the materialized view.
  • Refresh triggering mode.

    ON DEMAND: manual refresh on demand.

    START WITH (timestamptz) | EVERY (interval): scheduled refresh. START WITH specifies the first refresh time and EVERY specifies the refresh interval. The value can be MONTH, DAY, HOUR, MINUTE, or SECOND.

-

storage_parameter

Sett table properties of the materialized view.

Parameters such as mv_pck_column, bitmap_columns, enable_foreign_table_query_rewrite, excluded_inactive_tables, force_rewrite_timeout, and mv_analyze_mode can be set.

For more parameters that can be set, see Parameter Description.

new_owner

Specifies the owner of a materialized view.

Name of an existing user.

new_materialized_view_name

Specifies the name of the new materialized view.

A string, which must comply with the identifier naming conventions.

Examples

Enable query rewrite for a materialized view.

1
2
3
 
ALTER MATERIALIZED VIEW mv1 ENABLE QUERY REWRITE;
NOTICE:  REFRESH MATERIALIZED VIEW should be executed to enable query rewrite.
ALTER MATERIALIZED VIEW

Modify the table properties of a materialized view.

1
2
3
 
ALTER MATERIALIZED VIEW mv1 SET (force_rewrite_timeout=100);
ALTER MATERIALIZED VIEW mv1 SET (mv_pck_column='col1');
ALTER MATERIALIZED VIEW mv1 SET (enable_foreign_table_query_rewrite = true);

Change the refresh time of the materialized view.

1
 
ALTER MATERIALIZED VIEW mv1 REFRESH START WITH('2025-01-01 15:15:15'::timestamptz) EVERY (interval '60 s');

Modify the name of the materialized view.

1
 
ALTER MATERIALIZED VIEW my_schema.mv1 RENAME TO new_mv;

Helpful Links

CREATE MATERIALIZED VIEW, DROP MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW

Parent Topic: DDL Syntax