Skip to main content

ALTER MATERIALIZED VIEW

Description​

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 object to be modified. For more information about privileges in seekdb, see Privilege classification in seekdb.

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​

ParameterDescription
database.Optional. Specifies the database where the materialized view is located. If you omit database., the materialized view is assumed to be in the database to which the current session is connected.
view_nameSpecifies the name of the materialized view to be modified.
You can rename a materialized view by using the RENAME TABLE command.
alter_mview_action_listSpecifies the list of operations that can be performed to modify the materialized view. You can specify multiple operations, separated by commas (,). For more information, see alter_mview_action below.

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.

    Example:

    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 refresh cycle 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. Independent refresh.
      • INCONSISTENT: cascading non-consistent refresh.
      • CONSISTENT: cascading consistent refresh.

      Example:

      ALTER MATERIALIZED VIEW mv1 REFRESH INDIVIDUAL;
      ALTER MATERIALIZED VIEW mv1 REFRESH INCONSISTENT;
      ALTER MATERIALIZED VIEW mv1 REFRESH CONSISTENT;

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

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

      tip
      • If you use the NEXT clause, the refresh schedule 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 to be automatically executed periodically, define the NEXT clause to set the schedule 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

    The preceding clause indicates that the materialized view is refreshed every 1 day starting from the current time (sysdate()).

    Example:

    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​

Contents

Edit this page