ALTER MATERIALIZED VIEW

Purpose

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

Privileges

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 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 degree of parallelism for refreshing the materialized view. Valid values:

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

  Here is an example:

  ALTER MATERIALIZED VIEW mv1 PARALLEL 5;
  

• REFRESH [nested_refresh_option] [[START WITH expr] [NEXT expr]]: specifies the time cycle for scheduling background refresh tasks of the materialized view. You can also modify the refresh strategy of the materialized view by using this statement. The refresh strategy of the materialized view is specified by using the nested_refresh_option parameter.

  • nested_refresh_option: optional. The refresh strategy of the materialized view.

    • INDIVIDUAL: the default value, indicating independent refresh.
    • INCONSISTENT: indicates cascaded, non-consistent refresh. *CONSISTENT: indicates cascaded, consistent refresh.

    Note

    For OceanBase Database V4.3.5, you can modify the refresh strategy of a nested materialized view starting from V4.3.5 BP3.

    Here are some examples:

    ALTER MATERIALIZED VIEW mv1 REFRESH INDIVIDUAL;
    

    ALTER MATERIALIZED VIEW mv1 REFRESH INCONSISTENT;
    

    ALTER MATERIALIZED VIEW mv1 REFRESH CONSISTENT;
    

  • [START WITH expr]: optional. The time expression when the refresh plan starts.

  • [NEXT expr]: optional. The time expression when the next refresh is scheduled.

    Notice

    • If the NEXT clause is used, the time expression for the refresh schedule must be set to a future time point; otherwise, an error will occur.
    • To enable periodic automatic scheduling of refresh tasks for the materialized view, define the NEXT clause to set the scheduling interval.

  We recommend that you use sysdate() to specify the current timezone time. An example of the time expression is as follows:

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

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

  Here are some examples:

  ALTER MATERIALIZED VIEW mv1
      REFRESH START WITH sysdate();
  

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

  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