Automatic management of materialized view logs

For OceanBase Database V4.3.5, starting from V4.3.5 BP4, the automatic management feature for materialized view logs (mlog) is supported.

The automatic management of materialized view logs includes two aspects:

1. When creating a materialized view, OceanBase analyzes the dependencies of the materialized view on the base table and automatically creates the required mlog.
2. The system periodically prunes mlogs in the background, deleting mlogs that are no longer needed and streamlining the set of columns in mlogs, thereby reducing the maintenance cost of mlogs.

Limitations

When you create a materialized view, if you explicitly declare it as an incremental refresh materialized view (by specifying REFRESH FAST) or a real-time materialized view (by specifying ENABLE ON QUERY COMPUTATION), the system automatically creates an mlog or updates the mlog definition.

Considerations

• When OceanBase Database updates the mlog definition, it actually creates a new mlog table and replaces the original mlog. During this replacement process, an incremental refresh is required for the incremental refresh materialized views associated with the base table to flush the incremental data from the original mlog into the materialized views. This ensures the safe replacement of mlog. Therefore, if a base table is associated with many materialized views and has a large amount of incremental data, updating the mlog definition can be a time-consuming operation. It is important to be aware of this in advance.

• We recommend that you do not set the mlog_trim_interval interval to be too small. Otherwise, mlog may be mistakenly trimmed, and you may need to recreate or update mlog when creating the materialized view.

Related configurations

OceanBase Database provides two parameters to control the behavior of mlog auto-maintenance:

• enable_mlog_auto_maintenance: specifies whether to enable the materialized view log auto-maintenance feature.
• mlog_trim_interval: specifies the interval for scheduling the automatic mlog trimming task in the background.

Example

Enable mlog auto management.

obclient> ALTER SYSTEM SET enable_mlog_auto_maintenance = True;

Example 1: Automatically create an mlog

1. Create the test_tbl1 table.

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

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

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

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

   obclient> DESC mlog$_test_tbl1;
   

   The return 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 mlog definition

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.

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

3. View the information about the materialized view logs on the test_tbl2 table.

   obclient> DESC mlog$_test_tbl2;
   

   The return 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 modifies the existing mlog definition by adding the col3 column to the mlog table.

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

5. View the information about the materialized view logs on the test_tbl2 table again. You can observe that the mlog table definition for the test_tbl2 table contains the col2 and col3 columns.

   obclient> DESC mlog$_test_tbl2;
   

   The return 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: Automatic mlog pruning

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.

   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 modifies the existing mlog definition to add the col3 column to the mlog table.

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

4. View the information about the materialized view logs on the test_tbl3 table. You can observe that the mlog table definition for the test_tbl3 table contains the col2 and col3 columns.

   obclient> DESC mlog$_test_tbl3;
   

   The return 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. Modify the mlog pruning interval to observe the pruning result faster.

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

6. Drop the materialized view mv1_test_tbl3.

   obclient> DROP MATERIALIZED VIEW mv1_test_tbl3;
   

7. Wait for 5s and view the information about the materialized view logs on the test_tbl3 table. You can observe that the mlog table definition for the test_tbl3 table contains only the col3 column.

   obclient> DESC mlog$_test_tbl3;
   

   The return 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 materialized view mv2_test_tbl3.

   obclient> DROP MATERIALIZED VIEW mv2_test_tbl3;
   

9. Wait for 5s and view the information about the materialized view logs on the test_tbl3 table. You can observe that the mlog table for the test_tbl3 table no longer exists.

   obclient> DESC mlog$_test_tbl3;
   

   The return result is as follows:

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

10. Restore the mlog pruning interval.

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

References

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