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 classification in Oracle-compatible 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.

  Here is an example:

  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. Independent refresh.
    • INCONSISTENT: cascade refresh without consistency.
    • CONSISTENT: cascade refresh with consistency.

    Here are some examples:

    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 be automatically executed periodically, define the NEXT clause to specify the scheduling interval.

    We recommend that you use current_date to specify the current time in the current time zone. Here are some examples of the time expression:

    • 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
      

    Here are some examples:

    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 refresh 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 refresh plan of the materialized view mv_test_tbl1 to the current date, and set the refresh interval to 1 day.

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

References

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