CREATE MATERIALIZED VIEW LOG

Description

This statement is used to create a materialized view log.

A materialized view log (MLOG) records incremental updates to the base table (a regular table or a materialized view) to support the fast refresh feature of materialized views. An MLOG is a tracking table that monitors changes in the base table and applies these changes to the corresponding materialized view for fast refresh.

info

seekdb does not support specifying partitions for mlog at this time. The partitions of mlog are bound to those of the base table.

Privilege requirements

To create a materialized view log, you must have the CREATE TABLE privilege and the SELECT privilege on the base table. For more information about privileges in seekdb, see Privilege types.

Syntax

CREATE [OR REPLACE] MATERIALIZED VIEW LOG ON [database.] table
    [parallel_clause]
    [with_clause]
    [mv_log_purge_clause];

parallel_clause:
    NOPARALLEL
    | PARALLEL integer

with_clause:
    WITH [ {PRIMARY KEY | ROWID | SEQUENCE}
            [ { , PRIMARY KEY | , ROWID | , SEQUENCE }]... ]
        (column_name [, column_name]...)
        [new_values_clause]

new_values_clause:
    {INCLUDING | EXCLUDING} NEW VALUES

mv_log_purge_clause:
    PURGE {IMMEDIATE [ SYNCHRONOUS ]
        | START WITH datetime_expr [NEXT datetime_expr]
        | [START WITH datetime_expr] NEXT datetime_expr
        }

Parameters

Parameter	Description
OR REPLACE	Optional. Specifies OR REPLACE to indicate that if the corresponding mlog already exists, a new mlog will be created based on the definition, replacing the existing mlog. This process does not affect the normal read and write operations of the materialized view and base table.
database.	Optional. Specifies the database where the base table of the materialized view log is located. If you omit database., the base table is assumed to be in your own database.
table	Specifies the name of the base table corresponding to the materialized view log.
parallel_clause	Optional. Specifies the degree of parallelism (dop) for creating the materialized view table, which determines the level of parallel processing for the materialized view log. For more information, see parallel_clause below.
with_clause	Optional. Specifies the auxiliary columns included in the materialized view log. It indicates whether to record the primary key (PRIMARY KEY) and row identifier (ROWID) when the base table changes, and can also be used to add a sequence (SEQUENCE) to provide additional sorting information for the materialized view log. For more information, see with_clause below.
column_name	Optional. Specifies the name of the column whose value will be recorded in the materialized view log for all changed rows.
new_values_clause	Optional. Specifies whether to record both the old and new values in the materialized view log for update operations. For more information, see new_values_clause below.
mv_log_purge_clause	Optional. Specifies the time when data in the materialized view log will be purged. For more information, see mv_log_purge_clause below.

parallel_clause

• NOPARALLEL: The default configuration, with a parallelism level of 1.

• PARALLEL integer: Specifies the parallelism level, where integer is greater than or equal to 1.

with_clause

• PRIMARY KEY: Indicates that the materialized view log records the primary key columns of the base table. This option only takes effect for tables with a primary key. If not specified, the system automatically adds this attribute to the materialized view log, meaning the materialized view log will default to including the primary key columns.

• ROWID: Indicates that the materialized view log records the row identifiers (ROWID) of the base table. If not specified, the system automatically adds this attribute to the materialized view log, meaning the materialized view log will default to including the M_ROW$$ column.

• SEQUENCE: Indicates that the materialized view log records the sequence numbers (seq_no) for multi-row updates within a transaction. The system automatically adds this attribute to the materialized view log, meaning the materialized view log will default to including the SEQUENCE$$ column.

new_values_clause

• INCLUDING: The default setting, indicating that both old and new values can be saved in the materialized view log. If you want the materialized view to support fast refresh, you must specify INCLUDING NEW VALUES.

• EXCLUDING: Indicates that new values will not be recorded in the materialized view log. Do not use EXCLUDING NEW VALUES, as it will result in an error.

mv_log_purge_clause

tip

Data in the materialized view log will only be purged after the materialized view has been successfully refreshed.

• IMMEDIATE: Indicates that the materialized view log will be purged immediately after each refresh. The default value is SYNCHRONOUS.

  • SYNCHRONOUS: Optional. Indicates that the purge will be executed synchronously.

• START WITH datetime_expr [NEXT datetime_expr]:

  • START WITH datetime_expr: Specifies the initial purge time for the materialized view log.
  • [NEXT datetime_expr]: Optional. Specifies the next purge time for the materialized view log. Used to set the next purge time.

• [START WITH datetime_expr] NEXT datetime_expr: If you specify only the NEXT datetime_expr parameter and omit the START WITH datetime_expr parameter, the initial purge time for the materialized view log will be set to the value of the NEXT datetime_expr parameter.

tip

Make sure that both START WITH datetime_expr and NEXT datetime_expr are set to future dates; otherwise, an error will occur.

It is recommended to use sysdate() to represent the current time in the current 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 purged starting from the current time (sysdate()) every 1 day.

Examples

1. Create a table named test_tbl1.

   CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);

2. Create a materialized view log on the test_tbl1 table. Specify a parallelism level of 5 for parallel processing of the materialized view log, record changes to the col2 column, and record both the old and new values during changes. Configure the materialized view log to start purging expired records from the current date and every 1 day.

   CREATE MATERIALIZED VIEW LOG ON test_tbl1
       PARALLEL 5
       WITH SEQUENCE(col2) INCLUDING NEW VALUES
       PURGE START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY;

3. View the information about the materialized view log on the test_tbl1 table.

   DESC mlog$_test_tbl1;

   The returned result is as follows:

   +------------+-------------+------+------+---------+-------+
   | Field      | Type        | Null | Key  | Default | Extra |
   +------------+-------------+------+------+---------+-------+
   | col1       | int(11)     | NO   | PRI  | NULL    |       |
   | col2       | varchar(20) | YES  |      | NULL    |       |
   | SEQUENCE$$ | bigint(20)  | NO   | PRI  | NULL    |       |
   | DMLTYPE$$  | varchar(1)  | YES  |      | NULL    |       |
   | OLD_NEW$$  | varchar(1)  | YES  |      | NULL    |       |
   +------------+-------------+------+------+---------+-------+
   5 rows in set (0.003 sec)

4. Change the materialized view log on the test_tbl1 table to record changes to the col2 and col3 columns.

   CREATE OR REPLACE MATERIALIZED VIEW LOG ON test_tbl1
       PARALLEL 5
       WITH SEQUENCE(col2 ,col3) INCLUDING NEW VALUES
       PURGE START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY;

5. View the information about the materialized view log on the test_tbl1 table.

   DESC mlog$_test_tbl1;

   The returned result is as follows:

   +------------+-------------+------+------+---------+-------+
   | Field      | Type        | Null | Key  | Default | Extra |
   +------------+-------------+------+------+---------+-------+
   | col1       | int(11)     | NO   | PRI  | NULL    |       |
   | col2       | varchar(20) | YES  |      | NULL    |       |
   | col3       | int(11)     | YES  |      | NULL    |       |
   | SEQUENCE$$ | bigint(20)  | NO   | PRI  | NULL    |       |
   | DMLTYPE$$  | varchar(1)  | YES  |      | NULL    |       |
   | OLD_NEW$$  | varchar(1)  | YES  |      | NULL    |       |
   +------------+-------------+------+------+---------+-------+
   6 rows in set (0.003 sec)

References

Materialized view logs