ALTER MATERIALIZED VIEW LOG

Description

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 (either a regular table or a materialized view). For more information about the privileges in seekdb, see Privilege classification.

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

Parameter	Description
database.	Optional. Specifies the database where the materialized view is located. If you omit database., the base table is assumed to be in the database to which the current session is connected.
table_name	Specifies the name of the base table corresponding to the materialized view log.
alter_mlog_action_list	A list of operations that can be performed to modify the materialized view log. Multiple operations can be specified, separated by commas (,). For more information, see alter_mlog_action below.

alter_mlog_action

• parallel_clause: Used to modify 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 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]]: Used to modify 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.
  tip

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

  Here are some examples:

  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: Used to modify the inline storage length threshold for LOBs in the materialized view log. Only increasing the threshold is supported.

  info

  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 log