Create a materialized view log

Description

This topic describes how to create a materialized view log by using SQL statements.

A materialized view log (mlog) records the incremental data updates of a user table, that is the base table of the materialized view, to support quick refresh of the materialized view. An mlog is a record table that tracks changes to the base table and applies these changes to the corresponding materialized view to achieve quick refresh of the materialized view.

Required privileges

You need the CREATE TABLE privilege and the SELECT privilege on the base table to create a materialized view log. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode.

Syntax

The syntax for creating a materialized view log is as follows:

CREATE MATERIALIZED VIEW LOG ON [database.] table_name [parallel_clause] [with_clause] [mv_log_purge_clause];

For more information about the parameters in the syntax, see CREATE MATERIALIZED VIEW LOG.

Example

1. Create a table named test_tbl1.

   CREATE TABLE test_tbl1 (id INT PRIMARY KEY, name VARCHAR(20), age INT);
   

2. Create a materialized view log on the test_tbl1 table. Set the degree of parallelism (DOP) of the materialized view log to 5. Specify to record data changes to the name and age columns in the base table to the materialized view, including the old and new values before and after the change. Specify to clear expired log data once a day, starting from the current day.

   CREATE MATERIALIZED VIEW LOG ON test_tbl1 
     PARALLEL 5 
     WITH SEQUENCE(name ,age) INCLUDING NEW VALUES
     PURGE START WITH sysdate() NEXT sysdate() + interval 1 day;
   

3. Query the information of the materialized view log on the test_tbl1 table.

   DESC mlog$_test_tbl1;
   

   The return result is as follows:

   +------------+-------------+------+------+---------+-------+
   | FIELD      | TYPE        | NULL | KEY  | DEFAULT | EXTRA |
   +------------+-------------+------+------+---------+-------+
   | SEQUENCE$$ | bigint      | NO   | PRI  | NULL    |       |
   | id         | int(11)     | NO   |      | NULL    |       |
   | name       | varchar(20) | YES  |      | NULL    |       |
   | age        | int(11)     | YES  |      | NULL    |       |
   | DMLTYPE$$  | varchar(1)  | YES  |      | NULL    |       |
   | OLD_NEW$$  | varchar(1)  | YES  |      | NULL    |       |
   +------------+-------------+------+------+---------+-------+
   6 rows in set
   

References

• Overview

• Drop a materialized view log

• DBA_MVIEW_LOGS