Skip to main content

ALTER MATERIALIZED VIEW

Description​

This SQL statement can:

  • Alter the name of an asynchronous materialized view.

  • Alter the refresh strategy of an asynchronous materialized view.

  • Alter the status of an asynchronous materialized view to active or inactive.

  • Perform an atomic swap between two asynchronous materialized views.

  • Alter the properties of an asynchronous materialized view.

    You can use this SQL statement to alter the following properties:

    • partition_ttl_number
    • partition_refresh_number
    • resource_group
    • auto_refresh_partitions_limit
    • excluded_trigger_tables
    • mv_rewrite_staleness_second
    • unique_constraints
    • foreign_key_constraints
    • colocate_with
    • All session variable-related properties. For information on session variables, see System variables.
tip
  • This operation requires the ALTER privilege on the target materialized view. You can follow the instructions in GRANT to grant this privilege.
  • ALTER MATERIALIZED VIEW does not support directly modifying the query statement used to build the materialized view. You can build a new materialized view and swap it with the original one using ALTER MATERIALIZED VIEW SWAP WITH.

Syntax​

ALTER MATERIALIZED VIEW [db_name.]<mv_name> 
    { RENAME [db_name.]<new_mv_name> 
    | REFRESH <new_refresh_scheme_desc> 
    | ACTIVE | INACTIVE 
    | SWAP WITH [db_name.]<mv2_name>
    | SET ( "<key>" = "<value>"[,...]) }

Parameters​

ParameterRequiredDescription
mv_nameyesThe name of the materialized view to alter.
new_refresh_scheme_descnoNew refresh strategy, see SQL Reference - CREATE MATERIALIZED VIEW - Parameters for details.
new_mv_namenoNew name for the materialized view.
ACTIVEnoSet the status of the materialized view to active. StarRocks automatically sets a materialized view to inactive if any of its base tables is changed, for example, dropped and re-created, to prevent the situation that original metadata mismatches the changed base table. Inactive materialized views cannot be used for query acceleration or query rewrite. You can use this SQL to activate the materialized view after changing the base tables.
INACTIVEnoSet the status of the materialized view to inactive. An inactive asynchronous materialized view cannot be refreshed. But you can still query it as a table.
SWAP WITHnoPerform an atomic exchange with another asynchronous materialized view after necessary consistency checks.
keynoThe name of the property to alter, see SQL Reference - CREATE MATERIALIZED VIEW - Parameters for details.
NOTE
If you want to alter a session variable-related property of the materialized view, you must add a session. prefix to the property, for example, session.query_timeout. You do not need to specify the prefix for non-session properties, for example, mv_rewrite_staleness_second.
valuenoThe value of the property to alter.

Example​

Example 1: Alter the name of the materialized view.

ALTER MATERIALIZED VIEW lo_mv1 RENAME lo_mv1_new_name;

Example 2: Alter the refresh interval of the materialized view.

ALTER MATERIALIZED VIEW lo_mv2 REFRESH ASYNC EVERY(INTERVAL 1 DAY);

Example 3: Alter the timeout duration for the materialized view refresh tasks to 1 hour (default).

ALTER MATERIALIZED VIEW mv1 SET ("session.query_timeout" = "3600");

Example 4: Alter the materialized view's status to active.

ALTER MATERIALIZED VIEW order_mv ACTIVE;

Example 5: Perform an atomic exchange between materialized views order_mv and order_mv1.

ALTER MATERIALIZED VIEW order_mv SWAP WITH order_mv1;

Example 6: Enable profile for the materialized view refresh process. This feature is enabled by default.

ALTER MATERIALIZED VIEW mv1 SET ("session.enable_profile" = "true");

Example 7: Enable intermediate result spilling for the materialized view refresh process, and set the mode of spilling to force. Intermediate result spilling is enabled by default since v3.1.

-- Enable spilling during materialized view refresh.
ALTER MATERIALIZED VIEW mv1 SET ("session.enable_spill" = "true");
-- Set spill_mode to force (the default value is auto).
ALTER MATERIALIZED VIEW mv1 SET ("session.spill_mode" = "force");

Example 8: Alter the optimizer timeout duration for materialized view to 30 seconds (default since v3.3) if its query statement contains external tables or multiple joins.

ALTER MATERIALIZED VIEW mv1 SET ("session.new_planner_optimize_timeout" = "30000");

Example 9: Alter the query rewrite staleness time for the materialized view to 600 seconds.

ALTER MATERIALIZED VIEW mv1 SET ("mv_rewrite_staleness_second" = "600");