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 privileges in OceanBase Database, see Privilege types in MySQL 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.
  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 of the refresh plan.
    • [NEXT expr]: optional. Specifies the next refresh time.

    Notice

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

    We recommend that you use sysdate() to represent the current time in the current time zone. The following example shows a time expression:

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

    This clause specifies that the materialized view is refreshed every day starting from the current time (sysdate()).

    Here is an example:

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

    Specify only the refresh interval:

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

    Specify both the start time and the refresh interval:

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

References

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