Automatic management of materialized view logs

OceanBase Database supports automatic management of materialized view logs (MLOGs). This feature includes the following two aspects:

1. When a materialized view is created, the system analyzes the dependencies of the materialized view on the base table and automatically creates the required MLOGs.
2. In the background, the system periodically prunes MLOGs by removing those that are no longer referenced and by reducing the number of columns in the MLOGs, thereby reducing the maintenance cost of MLOGs.

Limitations

When you create a materialized view, an mlog table is automatically created or the definition of an existing mlog table is automatically updated only if the materialized view is an incrementally refreshed materialized view (that is, you specify REFRESH FAST) or a real-time materialized view (that is, you specify ENABLE ON QUERY COMPUTATION).

Considerations

• When you update the definition of an mlog table, OceanBase Database creates a new mlog table and replaces the original one. During the replacement, the incremental data in the original mlog table is refreshed into the incremental refresh materialized views associated with the mlog table. Therefore, when a base table is associated with a large number of materialized views and contains a large amount of incremental data, updating the mlog table definition may be a time-consuming operation.

• We recommend that you do not set the mlog_trim_interval parameter to a very small value. Otherwise, the mlog table may be incorrectly trimmed, and you may need to recreate or update the mlog table when you create a materialized view.

Related parameters

OceanBase Database provides two parameters to control the automatic behavior of mlog tables:

• enable_mlog_auto_maintenance: specifies whether to enable the automatic management feature of mlog tables.
• mlog_trim_interval: specifies the scheduling cycle of the background automatic trimming task of mlog tables.

Examples

Enable the automatic management of mlogs.

obclient> ALTER SYSTEM SET enable_mlog_auto_maintenance = True;

Example 1: Automatically create an mlog

1. Create a table named test_tbl1.

   obclient> CREATE TABLE test_tbl1(col1 INT, col2 INT, col3 INT);
   

2. Create an incremental refresh materialized view named mv_test_tbl1 on the test_tbl1 table. OceanBase Database automatically creates an mlog for the test_tbl1 table on the required col2 column.

   obclient> CREATE MATERIALIZED VIEW mv_test_tbl1
       REFRESH FAST
       AS SELECT
              col2,
              count(*) cnt
          FROM test_tbl1
          GROUP BY col2;
   

3. Query the information of the materialized view log on the test_tbl1 table. The query result is as follows:

   obclient> DESC mlog$_test_tbl1;
   

   The query result is as follows:

   +------------+-----------------+------+------+---------+-------+
   | FIELD      | TYPE            | NULL | KEY  | DEFAULT | EXTRA |
   +------------+-----------------+------+------+---------+-------+
   | COL2       | NUMBER(38)      | YES  | NULL | NULL    | NULL  |
   | SEQUENCE$$ | BIGINT(20)      | NO   | PRI  | NULL    | NULL  |
   | DMLTYPE$$  | VARCHAR2(1 )    | YES  | NULL | NULL    | NULL  |
   | OLD_NEW$$  | VARCHAR2(1 )    | YES  | NULL | NULL    | NULL  |
   | M_ROW$$    | BIGINT UNSIGNED | NO   | PRI  | NULL    | NULL  |
   +------------+-----------------+------+------+---------+-------+
   5 rows in set
   

Example 2: Automatically update the definition of an mlog

1. Create a table named test_tbl2.

   obclient> CREATE TABLE test_tbl2(col1 INT, col2 INT, col3 INT);
   

2. Create an incremental refresh materialized view named mv1_test_tbl2 on the test_tbl2 table. OceanBase Database automatically creates an mlog for the test_tbl2 table on the required col2 column. The query result is as follows:

   obclient> CREATE MATERIALIZED VIEW mv1_test_tbl2
       REFRESH FAST
       AS SELECT
              col2,
              count(*) cnt
          FROM test_tbl2
          GROUP BY col2;
   

3. Query the information of the materialized view log on the test_tbl2 table. The query result is as follows:

   obclient> DESC mlog$_test_tbl2;
   

   The query result is as follows:

   +------------+-----------------+------+------+---------+-------+
   | FIELD      | TYPE            | NULL | KEY  | DEFAULT | EXTRA |
   +------------+-----------------+------+------+---------+-------+
   | COL2       | NUMBER(38)      | YES  | NULL | NULL    | NULL  |
   | SEQUENCE$$ | BIGINT(20)      | NO   | PRI  | NULL    | NULL  |
   | DMLTYPE$$  | VARCHAR2(1 )    | YES  | NULL | NULL    | NULL  |
   | OLD_NEW$$  | VARCHAR2(1 )    | YES  | NULL | NULL    | NULL  |
   | M_ROW$$    | BIGINT UNSIGNED | NO   | PRI  | NULL    | NULL  |
   +------------+-----------------+------+------+---------+-------+
   5 rows in set
   

4. Create an incremental refresh materialized view named mv2_test_tbl2 on the test_tbl2 table. OceanBase Database detects that the current mlog table contains only the col2 column and updates the definition of the existing mlog to add the col3 column to the mlog table. The query result is as follows:

   obclient> CREATE MATERIALIZED VIEW mv2_test_tbl2
       REFRESH FAST
       AS SELECT
           col3,
           count(*) cnt
       FROM test_tbl2
       GROUP BY col3;
   

5. Query the information of the materialized view log on the test_tbl2 table. The query result is as follows:

   obclient> DESC mlog$_test_tbl2;
   

   The query result is as follows:

   +------------+-----------------+------+------+---------+-------+
   | FIELD      | TYPE            | NULL | KEY  | DEFAULT | EXTRA |
   +------------+-----------------+------+------+---------+-------+
   | COL2       | NUMBER(38)      | YES  | NULL | NULL    | NULL  |
   | COL3       | NUMBER(38)      | YES  | NULL | NULL    | NULL  |
   | SEQUENCE$$ | BIGINT(20)      | NO   | PRI  | NULL    | NULL  |
   | DMLTYPE$$  | VARCHAR2(1 )    | YES  | NULL | NULL    | NULL  |
   | OLD_NEW$$  | VARCHAR2(1 )    | YES  | NULL | NULL    | NULL  |
   | M_ROW$$    | BIGINT UNSIGNED | NO   | PRI  | NULL    | NULL  |
   +------------+-----------------+------+------+---------+-------+
   6 rows in set
   

Example 3: Automatically prune an mlog

1. Create a table named test_tbl3.

   obclient> CREATE TABLE test_tbl3(col1 INT, col2 INT, col3 INT);
   

2. Create an incremental refresh materialized view named mv1_test_tbl3 on the test_tbl3 table. OceanBase Database automatically creates an mlog for the test_tbl3 table on the required col2 column. The query result is as follows:

   obclient> CREATE MATERIALIZED VIEW mv1_test_tbl3
       REFRESH FAST
       AS SELECT
              col2,
              count(*) cnt
          FROM test_tbl3
          GROUP BY col2;
   

3. Create an incremental refresh materialized view named mv2_test_tbl3 on the test_tbl3 table. OceanBase Database detects that the current mlog table contains only the col2 column and updates the definition of the existing mlog to add the col3 column to the mlog table. The query result is as follows:

   obclient> CREATE MATERIALIZED VIEW mv2_test_tbl3
       REFRESH FAST
       AS SELECT
           col3,
           count(*) cnt
       FROM test_tbl3
       GROUP BY col3;
   

4. Query the information of the materialized view log on the test_tbl3 table. The query result is as follows:

   obclient> DESC mlog$_test_tbl3;
   

   The query result is as follows:

   +------------+-----------------+------+------+---------+-------+
   | FIELD      | TYPE            | NULL | KEY  | DEFAULT | EXTRA |
   +------------+-----------------+------+------+---------+-------+
   | COL2       | NUMBER(38)      | YES  | NULL | NULL    | NULL  |
   | COL3       | NUMBER(38)      | YES  | NULL | NULL    | NULL  |
   | SEQUENCE$$ | BIGINT(20)      | NO   | PRI  | NULL    | NULL  |
   | DMLTYPE$$  | VARCHAR2(1 )    | YES  | NULL | NULL    | NULL  |
   | OLD_NEW$$  | VARCHAR2(1 )    | YES  | NULL | NULL    | NULL  |
   | M_ROW$$    | BIGINT UNSIGNED | NO   | PRI  | NULL    | NULL  |
   +------------+-----------------+------+------+---------+-------+
   6 rows in set
   

5. Change the mlog pruning interval to observe the pruning result more quickly. The query result is as follows:

   obclient> ALTER SYSTEM SET mlog_trim_interval = '5s';
   

6. Drop the mv1_test_tbl3 materialized view. The query result is as follows:

   obclient> DROP MATERIALIZED VIEW mv1_test_tbl3;
   

7. Wait for 5 seconds and query the information of the materialized view log on the test_tbl3 table. The query result is as follows:

   obclient> DESC mlog$_test_tbl3;
   

   The query result is as follows:

   +------------+-----------------+------+------+---------+-------+
   | FIELD      | TYPE            | NULL | KEY  | DEFAULT | EXTRA |
   +------------+-----------------+------+------+---------+-------+
   | COL3       | NUMBER(38)      | YES  | NULL | NULL    | NULL  |
   | SEQUENCE$$ | BIGINT(20)      | NO   | PRI  | NULL    | NULL  |
   | DMLTYPE$$  | VARCHAR2(1 )    | YES  | NULL | NULL    | NULL  |
   | OLD_NEW$$  | VARCHAR2(1 )    | YES  | NULL | NULL    | NULL  |
   | M_ROW$$    | BIGINT UNSIGNED | NO   | PRI  | NULL    | NULL  |
   +------------+-----------------+------+------+---------+-------+
   5 rows in set
   

8. Drop the mv2_test_tbl3 materialized view. The query result is as follows:

   obclient> DROP MATERIALIZED VIEW mv2_test_tbl3;
   

9. Wait for 5 seconds and query the information of the materialized view log on the test_tbl3 table. The query result is as follows:

   obclient> DESC mlog$_test_tbl3;
   

   The query result is as follows:

   OBE-04043: object TEST_USER001.MLOG$_TEST_TBL3 does not exist
   

10. Restore the mlog pruning interval. The query result is as follows:

    obclient> ALTER SYSTEM SET mlog_trim_interval = '1d';
    

References

• Overview of materialized views
• Materialized view logs
• Create a materialized view
• Drop a materialized view