Help Center/ GaussDB(DWS)/ SQL Syntax Reference/ SQL Syntax Reference (9.1.0.x)/ DDL Syntax/ ALTER MATERIALIZED VIEW
Updated on 2025-07-22 GMT+08:00
View PDF
Share

ALTER MATERIALIZED VIEW

Function

Modifies the properties of a materialized view.

Precautions

Before running 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 materialized views are refreshed. Currently, only COMPLETE is available, which rebuilds the entire materialized view from scratch, re-executing the defining query. 
    1
2
    		 
    ALTER MATERIALIZED VIEW [ IF EXISTS ] { materialized_view_name }
    REFRESH  [ 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 the owner of the materialized view. 
    1
2
    		 
    ALTER MATERIALIZED VIEW { materialized_view_name }
    OWNER TO new_owner;
  • Allow for setting table properties of materialized views. This syntax is supported only by clusters of 9.1.0.200 and later versions. 
    1
2
3
    		 
    ALTER MATERIALIZED VIEW { materialized_view_name }
    SET ( {storage_parameter = value} [, ... ] )
    | RESET ( storage_parameter [, ... ] )
  • Rename a view. 
    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.

When ENABLE QUERY REWRITE is specified to enable query rewrite for materialized views, the GUC parameter mv_rewrite_rule must be specified.

Disabled by default.

REFRESH

Specifies how materialized views are refreshed.

When the data in base tables changes, you need to run REFRESH MATERIALIZED VIEW to update the data in the materialized views.

  • Currently, only COMPLETE is available, which rebuilds the entire materialized view from scratch, re-executing the defining query.
  • How to trigger a refresh:

    ON DEMAND: manual refresh on demand.

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

-

storage_parameter

Allows for setting table properties of materialized views.

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 details, see Parameter Description.

new_owner

Specifies the owner of a materialized view.

Name of an existing user.

new_materialized_view_name

Indicates the name of the new materialized view.

A string compliant with the identifier naming rules.

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');

Helpful Links

CREATE MATERIALIZED VIEW, DROP MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW

Parent Topic: DDL Syntax