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 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 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 nested materialized view by using this statement. The refresh strategy of the nested materialized view is specified by using the nested_refresh_option option. The nested_refresh_option option is optional.

  • nested_refresh_option: specifies the refresh strategy of the nested materialized view. The following options are available.

    • INDIVIDUAL: specifies to independently refresh the nested materialized view. This is the default value.
    • INCONSISTENT: specifies to perform cascading inconsistent refreshes.
    • CONSISTENT: specifies to perform cascading consistent refreshes.

    Note

    For OceanBase Database V4.3.5, the nested materialized view refresh strategy can be modified 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 current_date to specify the current timezone time. An example of the time expression is 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 day starting from the current time (current_date).

    START WITH current_date NEXT current_date + 1
    

  Here are some examples:

  ALTER MATERIALIZED VIEW mv1
      REFRESH START WITH current_date;
  

  ALTER MATERIALIZED VIEW mv1
      REFRESH NEXT current_date + 1;
  

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

Examples

1. Create a table named test_tbl1.

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

2. Create a materialized view named mv_test_tbl1.

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

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

   ALTER MATERIALIZED VIEW mv_test_tbl1 PARALLEL 5;
   

4. Modify the refresh schedule of the materialized view mv_test_tbl1 to set the initial refresh time to the current date, and then refresh the materialized view every day thereafter.

   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