ALTER MATERIALIZED VIEW LOG

Purpose

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

Privileges

To execute the ALTER MATERIALIZED VIEW LOG statement, the current user must have the ALTER privilege on the base table (ordinary table or materialized view). For more information about OceanBase Database privileges, see Privilege types 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: the degree of parallelism for the materialized view log table. Valid values:

  • NOPARALLEL: the degree of parallelism is 1. This is the default value.
  • PARALLEL integer: the degree of parallelism, where integer is an integer greater than or equal to 1.

  Here is an example:

  ALTER MATERIALIZED VIEW LOG ON tbl1 PARALLEL 5;
  

• PURGE [[START WITH expr] [NEXT expr]]: the time cycle for scheduling background cleanup tasks for the materialized view log.

  • [START WITH expr]: optional. The time when the first cleanup task is scheduled.
  • [NEXT expr]: optional. The time when the next cleanup task is scheduled. It is used to specify the time when the next cleanup task is scheduled.

  Notice

  Make that both the START WITH expr and NEXT expr time expressions are set to future time points. Otherwise, an error will occur.

  We recommend that you use sysdate() to specify the current time in the local time zone. Here is an example of a time expression:

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

  The above clause indicates that expired materialized view log records will be cleaned up every day starting from the current time (sysdate()).

  Here is an example:

  ALTER MATERIALIZED VIEW LOG ON tbl1 PURGE START WITH sysdate();
  

  ALTER MATERIALIZED VIEW LOG ON tbl1 PURGE NEXT sysdate() + INTERVAL 1 DAY;
  

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

• LOB_INROW_THRESHOLD [=] integer: the length threshold for inline storage of LOBs in the materialized view log. The value can be increased but not decreased.

  Note

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

  Here is an example:

  ALTER MATERIALIZED VIEW LOG ON tbl1 LOB_INROW_THRESHOLD 10000;
  

References

Materialized view logs