ALTER MATERIALIZED VIEW LOG

Purpose

This statement is used to modify the properties of a materialized view log (mlog).

Privilege requirements

To execute the ALTER MATERIALIZED VIEW LOG statement, the current user must have the ALTER privilege on the base table. For more information about OceanBase Database privileges, see Privilege classification in MySQL mode.

Syntax

ALTER MATERIALIZED VIEW LOG ON [database.]table_name alter_mlog_action_list;

alter_mview_action_list:
    alter_mlog_action [, alter_mlog_action ...]

alter_mlog_action:
    parallel_clause
    | PURGE [[START WITH expr] [NEXT expr]]
    | LOB_INROW_THRESHOLD [=] integer

parallel_clause:
    NOPARALLEL
    | PARALLEL integer

Parameters

alter_mlog_action

• parallel_clause: specifies the parallelism of the materialized view log table. Valid values:

  • NOPARALLEL: parallelism is 1, which is the default.
  • PARALLEL integer: specifies the parallelism. integer is an integer greater than or equal to 1.

  Here is an example:

  -- First, create a base table.
  obclient> CREATE TABLE mvlog (
      id INT PRIMARY KEY,
      name VARCHAR(50)
  );
  
  -- Create a materialized view log.
  obclient> CREATE MATERIALIZED VIEW LOG ON mvlog;
  
  -- Set the parallelism to 5.
  obclient> ALTER MATERIALIZED VIEW LOG ON mvlog PARALLEL 5;
  
  -- Set the parallelism to 1 (default).
  obclient> ALTER MATERIALIZED VIEW LOG ON mvlog NOPARALLEL;
  

• PURGE [[START WITH expr] [NEXT expr]]: specifies the time interval for the background cleanup task of the materialized view log.

  • [START WITH expr]: optional. Specifies the initial cleanup time of the materialized view log.
  • [NEXT expr]: optional. Specifies the next cleanup time of the materialized view log.

  Notice

  Make sure that START WITH expr and NEXT expr specify future time points. Otherwise, an error will occur.

  We recommend that you use sysdate() to represent the current time in the current time zone. Here are some examples of time expressions:

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

  The preceding clause specifies that the expired records in the materialized view log will be cleaned up every 1 day starting from the current time (sysdate()).

  Here is an example:

  obclient> ALTER MATERIALIZED VIEW LOG ON mvlog PURGE START WITH sysdate();
  

  obclient> ALTER MATERIALIZED VIEW LOG ON mvlog PURGE NEXT sysdate() + INTERVAL 1 DAY;
  

  obclient> ALTER MATERIALIZED VIEW LOG ON mvlog PURGE START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY;
  

• LOB_INROW_THRESHOLD [=] integer: specifies the inline storage length threshold for LOBs in the materialized view log. You can only increase the threshold.

  Note

  The default value of LOB_INROW_THRESHOLD is 8 KB. The default value of LOB_INROW_THRESHOLD for mlogs is not controlled by the system variable ob_default_lob_inrow_threshold.

  Here is an example:

  obclient> ALTER MATERIALIZED VIEW LOG ON mvlog LOB_INROW_THRESHOLD 10000;
  

References

Materialized view logs