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 types in MySQL-compatible 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: Modifies the parallelism of the materialized view log table. Valid values:

  • NOPARALLEL: Parallelism is 1, which is the default setting.
  • PARALLEL integer: Specifies the parallelism. integer can be 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]]: Modifies the time interval for the background cleanup task of the materialized view log.

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

  Notice

  Make sure that the time expressions START WITH expr and NEXT expr are set to 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 indicates that the system will clear expired materialized view log records starting from the current time (sysdate()) and every 1 day.

  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: Modifies the inline storage length threshold for LOBs in the materialized view log. This parameter can only be increased.

  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