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 object to be operated. For more information about the privileges in OceanBase Database, see Privilege classification in Oracle mode.

Syntax

ALTER MATERIALIZED VIEW [schema.]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.

  Example:

  obclient> ALTER MATERIALIZED VIEW mv1 PARALLEL 5;
  

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

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

    • INDIVIDUAL: default value. Independent refresh.
    • INCONSISTENT: cascading refresh without consistency.
    • CONSISTENT: cascading refresh with consistency.

    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 the 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 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 the materialized view periodically, define the NEXT clause to set the scheduling cycle.

    We recommend that you use current_date to indicate the current time in the current time zone. Examples of time expressions are as follows:

    • Refresh the materialized view every 10 seconds starting from the current time (current_date).

      START WITH current_date NEXT current_date + INTERVAL '10' SECOND
      

    • Refresh the materialized view every 10 hours starting from the current time (current_date).

      START WITH current_date NEXT current_date + INTERVAL '10' HOUR
      

    • Refresh the materialized view every 1 day starting from the current time (current_date).

      START WITH current_date NEXT current_date + 1
      

    Example:

    obclient> ALTER MATERIALIZED VIEW mv1
        REFRESH START WITH current_date;
    

    obclient> ALTER MATERIALIZED VIEW mv1
        REFRESH NEXT current_date + 1;
    

    obclient> ALTER MATERIALIZED VIEW mv1
        REFRESH START WITH current_date NEXT current_date + 1;
    

Examples

1. Create a table named test_tbl1.

   obclient> CREATE TABLE test_tbl1 (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(20), col3 NUMBER, col4 BLOB);
   

2. Create a materialized view named mv_test_tbl1.

   obclient> CREATE MATERIALIZED VIEW mv_test_tbl1
       AS SELECT col1, col2, col3
          FROM test_tbl1;
   

3. Set the parallelism of the materialized view mv_test_tbl1 to 5.

   obclient> ALTER MATERIALIZED VIEW mv_test_tbl1 PARALLEL 5;
   

4. Set the initial refresh time of the materialized view mv_test_tbl1 to the current date and refresh it every day.

   obclient> ALTER MATERIALIZED VIEW mv_test_tbl1
       REFRESH START WITH current_date NEXT current_date + 1;
   

References

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