ALTER MATERIALIZED VIEW

Purpose

This statement is used to modify the properties of a materialized view.

Privilege requirements

To execute the ALTER MATERIALIZED VIEW statement, the current user must have the ALTER privilege on the target object. For more information about OceanBase Database privileges, see Privilege types in MySQL-compatible mode.

Syntax

ALTER MATERIALIZED VIEW [database.]view_name alter_mview_action_list;

alter_mview_action_list:
    alter_mview_action [, alter_mview_action ...]

alter_mview_action:
    parallel_clause
    | REFRESH [nested_refresh_option] [[START WITH expr] [NEXT expr]]

parallel_clause:
    NOPARALLEL
    | PARALLEL integer

nested_refresh_option:
    INDIVIDUAL
    | INCONSISTENT
    | CONSISTENT

Parameters

alter_mview_action

• parallel_clause: specifies the refresh parallelism of the materialized view. Valid values:

  • NOPARALLEL: default value. The parallelism is 1.
  • PARALLEL integer: specifies the parallelism. The value of integer must be an integer greater than or equal to 1.

  Here is an example:

  -- Create a base table.
  obclient> CREATE TABLE test_table (
      id INT PRIMARY KEY,
      name VARCHAR(50)
  );
  
  -- Create a materialized view.
  obclient> CREATE MATERIALIZED VIEW mv1 AS
      SELECT id, name FROM test_table;
  
  -- Set the refresh parallelism to 1 (default).
  obclient> ALTER MATERIALIZED VIEW mv1 NOPARALLEL;
  
  -- Set the refresh parallelism to 5.
  obclient> ALTER MATERIALIZED VIEW mv1 PARALLEL 5;
  

• REFRESH [nested_refresh_option] [[START WITH expr] [NEXT expr]]: specifies the refresh strategy of a nested materialized view or the time interval of the background refresh task of a materialized view.

  • nested_refresh_option: optional. Specifies the refresh strategy of a nested materialized view.

    • INDIVIDUAL: default value. Specifies independent refresh.
    • INCONSISTENT: specifies cascading inconsistent refresh.
    • CONSISTENT: specifies cascading consistent refresh.

    Here is an example:

    obclient> ALTER MATERIALIZED VIEW mv1 REFRESH INDIVIDUAL;
    

    obclient> ALTER MATERIALIZED VIEW mv1 REFRESH INCONSISTENT;
    

    obclient> ALTER MATERIALIZED VIEW mv1 REFRESH CONSISTENT;
    

  • [[START WITH expr] [NEXT expr]]: specifies the time interval of the background refresh task of a materialized view.

    • [START WITH expr]: optional. Specifies the start time expression of the refresh plan.

    • [NEXT expr]: optional. Specifies the next refresh time expression.

      Notice

      • If you use the NEXT clause, the time expression of the refresh plan must be set to a future time. Otherwise, an error will be returned.
      • If you want to schedule the refresh task of a materialized view periodically, define the NEXT clause to set the scheduling cycle.

    We recommend that you use sysdate() to specify the current time in the current time zone. Here are some examples of time expressions:

    START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY
    

    This clause indicates that the materialized view will be refreshed every 1 day starting from the current time (sysdate()).

    Here is an example:

    obclient> ALTER MATERIALIZED VIEW mv1
        REFRESH START WITH sysdate();
    

    Only the refresh interval is specified:

    obclient> ALTER MATERIALIZED VIEW mv1
        REFRESH NEXT sysdate() + INTERVAL 1 DAY;
    

    Both the start time and the refresh interval are specified:

    obclient> ALTER MATERIALIZED VIEW mv1
        REFRESH START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY;
    

References

• Overview of materialized views
• Create a materialized view
• Query a materialized view
• Refresh a materialized view