Refresh a materialized view

When the data in the base tables is updated, the data in the materialized view may become inconsistent with that in the base tables. To maintain the data in the materialized view, OceanBase Database refreshes the materialized view.

OceanBase Database supports full refreshes and incremental refreshes for materialized views, and supports manual and automatic refreshes.

Full refresh

OceanBase Database performs a full refresh by using remote refresh. Specifically, a hidden table is created, a refresh statement is executed on the hidden table, and the original table is then switched with the hidden table. Therefore, a full refresh operation requires additional space and will fully rebuild the indexes (if any).

Considerations

• A full refresh can be a very time-consuming process, especially when a large amount of data needs to be read and processed. Therefore, you should always consider the time required for a full refresh before you execute the operation.
• A full refresh is allowed only if the columns of the base table match the columns of the materialized view. If they do not match, a full refresh is not allowed.
• If a materialized view is fully refreshed, all dependent materialized views (nested materialized views) must be fully refreshed before any incremental refreshes can be performed. Otherwise, an error will be returned.

Incremental refresh

Currently, incremental refresh of materialized views supports SQL statements of the following types: non-aggregated single-table queries, aggregated single-table queries, multi-table joins, joined aggregated queries, and UNION ALL queries. For SQL statements that do not fall into these five categories, incremental refresh is not supported. For more information about the requirements for incremental refresh SQL statements, see the following description.

Incremental refresh of a non-aggregated table

Example of incremental refresh of a non-aggregated table

1. Create the tbl1 table.

   CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 INT, col3 INT, col4 INT);
   

2. Create an incremental refresh materialized view mv_tbl1 based on the tbl1 table.

   CREATE MATERIALIZED VIEW mv_tbl1
       REFRESH FAST ON DEMAND
       AS SELECT col1, col2
          FROM tbl1;
   

Incremental refresh of a single table aggregate view

The following requirements must be met for incremental refresh of a single table aggregate view:

• The FROM table must be a base table, not an inline view or a regular view.

  Note

  Nested materialized views do not support real-time materialized views. Therefore, when the FROM table is a materialized view, real-time materialized views cannot be used.

• Only one table exists in the FROM clause.

• An mlog is created on the FROM table, and all columns used in the view exist in the mlog.

• The view definition does not contain subqueries.

• Window functions are not supported.

• The view definition does not contain the ROLLUP, HAVING, DISTINCT, ORDER BY, LIMIT, or FETCH clause.

• If the query contains the DISTINCT keyword, the output columns of the materialized view that can be incrementally updated must be unique. In this case, you can directly disable the DISTINCT keyword or remove it.

• A statement without the GROUP BY clause must be a scalar aggregate.

• For a materialized view with the GROUP BY clause, only the SUM and COUNT aggregate functions are supported, and only simple columns can be used in the aggregate functions. The GROUP BY clause must meet the following requirements:

  1. The GROUP BY clause must be a standard GROUP BY clause and cannot contain the ROLLUP or HAVING clause.
  2. The SELECT clause must contain all columns in the GROUP BY clause.
  3. The aggregate function cannot contain the DISTINCT keyword.

• Materialized views that use the MIN or MAX function do not support real-time materialized views.

  Note

  In OceanBase Database, the parameters of the MIN and MAX aggregate functions can be non-basic columns, and the GROUP BY columns can be non-basic columns.

Example of incremental refresh of a single-table aggregate materialized view

1. Create a table named test_tbl1.

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

2. Create a materialized view that is refreshed incrementally.

   • Create a materialized view named mv1_test_tbl1. Specify the incremental refresh method for the materialized view and allow manual refresh. The query part of the materialized view selects the col2 column from the test_tbl1 table and calculates the aggregate result of sum(col3), grouped by the values of the col2 column. The materialized view is named mv1_test_tbl1.

     CREATE MATERIALIZED VIEW mv1_test_tbl1
       REFRESH FAST ON DEMAND
       AS SELECT col2, sum(col3) sum_col3
          FROM test_tbl1
          GROUP BY col2;
     

   • Create a materialized view named mv2_test_tbl1. Specify the incremental refresh method for the materialized view and allow manual refresh. The query part of the materialized view selects the col2 and col3 columns from the test_tbl1 table and calculates the aggregate result of sum(col3). The materialized view is named mv2_test_tbl1.

     CREATE MATERIALIZED VIEW mv2_test_tbl1
       REFRESH FAST ON DEMAND
       AS SELECT col2, col3, sum(col3) sum_col3
          FROM test_tbl1
          GROUP BY col2, col3;
     

   • Create a materialized view named mv3_test_tbl1. Specify the incremental refresh method for the materialized view and allow manual refresh. The query part of the materialized view selects the col2 column from the test_tbl1 table and calculates the aggregate results of sum(col3) and avg(col3), and also calculates some custom columns calcol1 and calcol2, grouped by the values of the col2 column. The materialized view is named mv3_test_tbl1.

     CREATE MATERIALIZED VIEW mv3_test_tbl1
       REFRESH FAST ON DEMAND
       AS SELECT col2, sum(col3) sum_col3, avg(col3) avg_col3, avg(col3) * sum(col3)/col2 calcol1, col2+sum(col3) calcol2
          FROM test_tbl1
          GROUP BY col2;
     

   • Create a materialized view named mv4_test_tbl1. Specify the incremental refresh method for the materialized view and allow manual refresh. The query part of the materialized view selects the col2 column from the test_tbl1 table and calculates the aggregate results of sum(col3), count(col3*col3), sum(col3*col3), and STDDEV(col3), grouped by the values of the col2 column. The materialized view is named mv4_test_tbl1.

     CREATE MATERIALIZED VIEW mv4_test_tbl1
       REFRESH FAST ON DEMAND
       AS SELECT col2, sum(col3) sum_col3, count(col3*col3) cnt_col3_2, sum(col3*col3) sum_col3_2, STDDEV(col3) stddev_col3
          FROM test_tbl1
          GROUP BY col2;
     

   • Create a materialized view named mv5_test_tbl1. Specify the incremental refresh method for the materialized view and allow manual refresh. The query part of the materialized view selects the col1 and col2 columns from the test_tbl1 table and calculates the sum of the minimum value of col3 and the maximum value of col4 in each group, grouped by the combination of col1 and col2. The materialized view is named mv5_test_tbl1.

     CREATE MATERIALIZED VIEW mv5_test_tbl1
         REFRESH FAST ON DEMAND
         AS SELECT
             col1,
             col2,
             MIN(col3) + MAX(col4) AS min_max_val
             FROM test_tbl1
             GROUP BY col1, col2;
     

   • Use non-basic columns in the GROUP BY clause.

     1. Create a table named m_tbl1.

        obclient> CREATE TABLE m_tbl1(col1 INT PRIMARY KEY, col2 DATETIME, col3 INT, col4 INT, col5 INT, col6 INT);
        

     2. Create a materialized view named m_tbl1 mv1 that is refreshed incrementally. The materialized view is named m_tbl1_mv1.

        obclient> CREATE MATERIALIZED VIEW m_tbl1_mv1
            REFRESH FAST ON DEMAND
            AS SELECT
                col3,
                DATE(col2) gby_2,
                MAX(col4) max_c4,
                MIN(col5 + col6) min_c5_c6
            FROM m_tbl1
            GROUP BY col3, DATE(col2);
        

Incremental refresh of a materialized view with multiple tables joined

The following requirements must be met for incremental refresh of a materialized view with multiple tables joined:

1. The FROM clause must specify base tables, not inline views.

2. The FROM clause must specify at least two tables.

   Notice

   • The incremental refresh feature of materialized views in OceanBase Database supports outer joins (LEFT JOIN/RIGHT JOIN).
   • Outer join limitations: The join tree must be a left-deep join tree where INNER JOIN is performed first and LEFT JOIN is performed later.
   • If the SELECT clause of the incremental refresh materialized view contains the LEFT JOIN operator, you cannot specify a primary key (PRIMARY KEY) or unique index (UNIQUE INDEX) when you create the materialized view. This is to avoid refresh exceptions caused by constraint conflicts.
   • Aggregated materialized views with outer joins do not support real-time materialized views.

3. The materialized view definition must not contain subqueries.

4. The materialized view definition must not contain the ROLLUP, HAVING, WINDOW FUNCTION, DISTINCT, ORDER BY, LIMIT, or FETCH clause.

5. The materialized view definition must not contain expressions that generate unstable output values, such as ROWNUM, RAND, or SYSDATE.

Example

1. Create the base tables tb1 and tb2.

   CREATE TABLE tb1(c1 INT PRIMARY KEY, c2 INT, c3 INT);
   

   CREATE TABLE tb2(c1 INT PRIMARY KEY, c4 INT, c5 INT);
   

2. Create the materialized view mv1_tb1_tb2 for the join of the tb1 and tb2 tables.

   CREATE MATERIALIZED VIEW mv1_tb1_tb2
     REFRESH FAST
     AS SELECT tb1.c1, tb1.c2, tb2.c4
       FROM tb1 JOIN tb2 ON tb1.c1=tb2.c1;
   

Here is an example of creating indexes for the materialized view and its base tables:

1. (Optional) Execute the following statement to delete the test data.

   You can skip this step if the following database objects do not exist.

   DROP TABLE IF EXISTS t1;
   DROP TABLE IF EXISTS t2;
   DROP MATERIALIZED VIEW rt_mv1;
   

2. Execute the following statement to create the t1 table and the idx_t1_c2 index.

   CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT, c2 INT, c3 INT, c4 INT, c5 INT);
   CREATE INDEX idx_t1_c2 ON t1(c2);
   

3. Execute the following statement to create the t2 table and the idx_t2_c3 index.

   CREATE TABLE t2(c1 INT PRIMARY KEY AUTO_INCREMENT, c2 INT, c3 INT, c4 INT, c5 INT);
   CREATE INDEX idx_t2_c3 ON t2(c3);
   

4. Execute the following statement to create the real-time materialized view rt_mv1.

   CREATE MATERIALIZED VIEW rt_mv1
     NEVER REFRESH
     ENABLE ON QUERY COMPUTATION
     DISABLE QUERY REWRITE
     AS SELECT t1.c1 AS t1_c1, t2.c1 AS t2_c1, t1.c2 AS t1_c2, t2.c2 AS t2_c2, t1.c3 AS t1_c3, t2.c3 AS t2_c3 
         FROM t1, t2
         WHERE t1.c2 = t2.c3;
   

5. Execute the following statement to create indexes for the primary key columns of the base tables in the materialized view.

   CREATE INDEX idx_mv_t1_c1 ON rt_mv1(t1_c1);
   CREATE INDEX idx_mv_t2_c1 ON rt_mv1(t2_c1);
   

Incremental refresh of aggregated views across multiple tables

The requirements for incremental refresh of aggregated views across multiple tables are as follows:

• The requirements for incremental refresh of aggregated views across multiple tables are the union of the requirements for incremental refresh of aggregated views on a single table and incremental refresh of joined views across multiple tables.
• Incremental refresh is supported for aggregated views with outer joins. The limitations for outer joins in aggregated views are the same as those for non-aggregated outer-joined materialized views. The limitations for the aggregated part are the same as those for inner-joined aggregated views. However, aggregated views with outer joins do not support the MIN and MAX aggregate functions, and do not support real-time materialized views.

Example of incremental refresh of aggregated views across multiple tables

1. Create the base tables t3 and t4.

   CREATE TABLE t3(c1 INT, c2 INT, c3 INT, c4 INT, PRIMARY KEY(c1));
   

   CREATE TABLE t4(c1 INT, c2 INT, c3 INT, c4 INT, PRIMARY KEY(c1));
   

2. Create a real-time materialized view mv1_t3_t4 for incremental refresh of the aggregated join between tables t3 and t4.

   CREATE MATERIALIZED VIEW mv1_t3_t4
     REFRESH FAST
     ENABLE ON QUERY COMPUTATION
     AS SELECT t3.c1,
         SUM(t4.c4) sum_c4,
         AVG(t4.c4) avg_c4
       FROM t3, t4
       WHERE t3.c2 = t4.c3
       GROUP BY t3.c1;
   

Incremental refresh of a set query

A materialized view that uses a UNION ALL set query supports incremental refresh. An incremental refresh of a set query allows you to use any materialized view that supports incremental refresh in the branches of the UNION ALL set query except for an excepted join materialized view.

The following conditions must be met for an incremental refresh of a set query:

• A materialized view that uses a set query does not support real-time materialized views.

• The top-level query is UNION ALL, and the view definition does not contain subqueries or clauses such as ORDER BY, LIMIT, or FETCH.

• The output columns at the same projection position in each branch of the UNION ALL set query must be of the same type. You cannot forcibly convert the column types of the subqueries to the same type by using the CAST function.

  For example, in a materialized view that joins multiple tables, the primary keys of the joined tables must appear in the SELECT clause. The SELECT output columns cannot be forcibly converted to the same type by using the CAST function due to the UNION ALL set query.

• The same type or value of a constant must exist at the same projection position in each branch of the UNION ALL set query. The constant is used to distinguish the branches.

Example

1. Create the base tables ua_tbl1 and ua_tbl2.

   CREATE TABLE ua_tbl1 (col1 INT PRIMARY KEY, col2 INT, col3 INT, col4 INT);
   

   CREATE TABLE ua_tbl2 (col1 INT PRIMARY KEY, col2 INT, col3 INT, col4 INT);
   

2. Create the materialized view mv_ua_tbl1_tbl2 that supports incremental refresh of a set query.

   CREATE MATERIALIZED VIEW mv_ua_tbl1_tbl2
       REFRESH FAST ON DEMAND
       AS SELECT
           a.col1 as a_c1,
           b.col1 as b_c1,
           a.col2 val
       FROM ua_tbl1 a
               INNER JOIN ua_tbl2 b
               ON a.col2 = b.col3
       UNION ALL
       SELECT
           col1 a_c1,
           col2 b_c1,
           count(*) val
       FROM ua_tbl1
       GROUP BY col1, col2;
   

Parallelism control mechanism for materialized view refresh

OceanBase Database supports controlling the parallelism of materialized view refresh operations:

• Refresh parallelism:

  • Explicitly specified refresh parallelism: If you specify the refresh parallelism using the REFRESH ... PARALLEL n clause when defining the materialized view, the specified value is used during the refresh operation.
  • Implicitly specified refresh parallelism: If you do not explicitly specify the refresh parallelism, the system uses the value of the mview_refresh_dop system variable set in the current session as the default parallelism to control the concurrency of the refresh operation.

View materialized view parallelism information

• Query the DBA_MVIEWS view for the background refresh parallelism set for a materialized view.

  Note

  The DBA_MVIEWS view displays only the parallelism explicitly specified for a materialized view.

  • If the REFRESH_DOP field is non-zero, the materialized view's background refresh task uses the parallelism corresponding to REFRESH_DOP.
  • If REFRESH_DOP is 0, the value of the globally set mview_refresh_dop is used.

• Query the DBA_MVREF_RUN_STATS view for historical materialized view refresh parallelisms.

Example:

1. Create the materialized view mv0_t1.

   obclient> CREATE MATERIALIZED VIEW mv0_t1
       REFRESH COMPLETE ON DEMAND
       START WITH sysdate()
           NEXT sysdate() + INTERVAL 10 SECOND
       AS
       SELECT c1, c2
       FROM t1;
       ```
   
   

2. Query the background refresh parallelism for the materialized view mv0_t1.

   obclient> SELECT OWNER, MVIEW_NAME,REFRESH_DOP
       FROM oceanbase.DBA_MVIEWS
       WHERE OWNER = 'db_test'
       AND MVIEW_NAME = 'mv0_t1'
   

   The result is as follows:

   +---------+------------+-------------+
   | OWNER   | MVIEW_NAME | REFRESH_DOP |
   +---------+------------+-------------+
   | db_test | mv0_t1     |           0 |
   +---------+------------+-------------+
   1 row in set
   

3. Change the refresh parallelism for the materialized view mv0_t1 to 8.

   obclient> ALTER MATERIALIZED VIEW mv0_t1 REFRESH PARALLEL 8;
   

4. Query the background refresh parallelism for the materialized view mv0_t1.

   obclient> SELECT OWNER, MVIEW_NAME,REFRESH_DOP
       FROM oceanbase.DBA_MVIEWS
       WHERE OWNER = 'db_test'
       AND MVIEW_NAME = 'mv0_t1'
   

   The result is as follows:

   +---------+------------+-------------+
   | OWNER   | MVIEW_NAME | REFRESH_DOP |
   +---------+------------+-------------+
   | db_test | mv0_t1     |           8 |
   +---------+------------+-------------+
   1 row in set
   

Set mview_refresh_dop

mview_refresh_dop is a system variable in OceanBase Database that controls the default parallelism for materialized view refresh operations. By appropriately setting its value, you can significantly improve the refresh efficiency and optimize the database performance.

When mview_refresh_dop is set to 0 or 1, parallel refresh is disabled. For more information about the system variable mview_refresh_dop, see mview_refresh_dop.

Here are some examples:

• Set the parallelism for the current session to 5.

  SET mview_refresh_dop = 5;
  

• Set the parallelism for all sessions to 5.

  SET GLOBAL mview_refresh_dop = 5;
  

  Notice

  Setting the value of a global variable does not take effect on the current session. You must log in again to create a new session for the setting to take effect.

Manually refresh a materialized view

If the refresh mode of a materialized view is ON DEMAND, you can manually refresh the materialized view by using the DBMS_MVIEW.REFRESH procedure. For a materialized view defined for incremental refresh, you can manually refresh it as a full refresh.

Syntax:

DBMS_MVIEW.REFRESH (
    mv_name                IN    VARCHAR(65535)
    [, method              IN    VARCHAR(65535) DEFAULT NULL]
    [, refresh_parallel    IN    INT DEFAULT 0]
    [, nested              IN    BOOLEAN DEFAULT FALSE]
    [, async               IN    BOOLEAN DEFAULT FALSE]

Parameters:

| Parameter | Description | |---------------------|-----------------------|| mv_name | The name of the materialized view to be refreshed. | | method | Defines the refresh strategy of the materialized view. You can specify the default strategy when creating the materialized view. Valid values: <ul><li>Forf: fast refresh. </li><li>?: force refresh. </li><li>Corc: complete refresh. </li><li>Aora: always refresh, equivalent toC. </li></ul>| | refresh_parallel | The concurrency of the refresh operation, that is, the number of threads that perform the refresh operation simultaneously. | | nested | Specifies the refresh mode for nested materialized views. Valid values: <ul><li>FALSE: the default value, which indicates to independently refresh nested materialized views. </li><li>TRUE: which indicates to cascade refresh nested materialized views. </li></ul>| | async | Specifies whether to perform asynchronous refresh. Valid values: <ul><li>FALSE: the default value, which indicates to return only after the refresh task is completed. </li><li>TRUE: which indicates to return immediately after creating the background refresh task by callingREFRESH`. |

For more information about DBMS_MVIEW.REFRESH, see DBMS_MVIEW.REFRESH.

Here is an example:

1. Insert three rows into the test_tbl1 table. The following sample code shows how to do this:

   INSERT INTO test_tbl1 VALUES (1, 1, 1, 1),(2, 2, 2, 2),(3, 3, 3, 3);
   

2. View the information about the materialized view mv1_test_tbl1. The following sample code shows how to do this:

   SELECT * FROM mv1_test_tbl1;
   

   The returned result is as follows:

   Empty set
   

3. Manually refresh the materialized view mv1_test_tbl1 by using the DBMS_MVIEW.REFRESH procedure. The following sample code shows how to do this:

   • Refresh the materialized view by using the refresh option specified for the materialized view:

     CALL DBMS_MVIEW.REFRESH('mv1_test_tbl1');
     

   • Refresh the materialized view by using the specified refresh option:

     CALL DBMS_MVIEW.REFRESH('mv1_test_tbl1', 'c');
     

4. View the information about the materialized view mv1_test_tbl1. The following sample code shows how to do this:

   SELECT * FROM mv1_test_tbl1;
   

   The returned result is as follows:

   +------+------+----------+----------+
   | col2 | cnt  | cnt_col3 | sum_col3 |
   +------+------+----------+----------+
   |    1 |    1 |        1 |        1 |
   |    2 |    1 |        1 |        2 |
   |    3 |    1 |        1 |        3 |
   +------+------+----------+----------+
   3 rows in set
   

Set the refresh parallelism for manually refreshing a materialized view

You can set the default refresh parallelism for manually refreshing a materialized view by setting the system variable mview_refresh_dop.

You can also explicitly set the refresh parallelism for the current refresh by specifying the refresh_parallel parameter when calling the DBMS_MVIEW.REFRESH function.

Here is an example:

1. Set the current session parallelism to 5. The following sample code shows how to do this:

   SET mview_refresh_dop = 5;
   

2. Manually refresh the materialized view:

   • Explicitly set the refresh parallelism to 8. The current refresh parallelism is 8.

     CALL DBMS_MVIEW.REFRESH('mv1', 'c', 8);
     

   • Do not explicitly set the refresh parallelism. The current refresh parallelism is 5, which is the value of the session variable.

     CALL DBMS_MVIEW.REFRESH('mv1', 'c');
     

Automatic refresh of materialized views

When you create a materialized view, if you specify the START WITH datetime_expr and NEXT datetime_expr clauses, the system automatically creates a background refresh task for the materialized view when the conditions are met.

Parallelism of automatic refresh of materialized views

You can specify the parallelism for background refresh of materialized views in the following two ways:

1. Specify the refresh parallelism when you create a materialized view.

   Here is an example:

   CREATE MATERIALIZED VIEW mv_t1
       REFRESH COMPLETE PARALLEL 8 ON DEMAND
           START WITH sysdate() NEXT sysdate() + INTERVAL 10 SECOND
       AS
       SELECT c1, c2
       FROM t1;
   

2. Set the global session variable mview_refresh_dop as the refresh parallelism.

   Automatic refresh operations are performed by internal sessions. To make the setting of the global session variable mview_refresh_dop effective for internal sessions, you must set it at the global level.

   Notice

   If you do not explicitly specify the parallelism when you create a materialized view and the value of the mview_refresh_dop variable is 0 or 1, the background refresh task does not enable parallel refresh.

   Here is an example:

   1. Set the global session parallelism to 5.

      SET GLOBAL mview_refresh_dop = 5;
      

   2. Create a materialized view:

      • If you specify the refresh parallelism as 8 when you create a materialized view, the background refresh task uses 8 as the refresh parallelism.

        CREATE MATERIALIZED VIEW mv1_t1
            REFRESH COMPLETE PARALLEL 8 ON DEMAND
                 START WITH sysdate() NEXT sysdate() + INTERVAL 10 SECOND
            AS
            SELECT c1, c2
            FROM t1;
        

      • If you do not specify the refresh parallelism when you create a materialized view, the background refresh task uses the value of the mview_refresh_dop variable, which is 5, as the refresh parallelism.

        CREATE MATERIALIZED VIEW mv2_t1
            REFRESH COMPLETE ON DEMAND
                 START WITH sysdate() NEXT sysdate() + INTERVAL 10 SECOND
            AS
            SELECT c1, c2
            FROM t1;
        

Refresh nested materialized views

Refresh rules for nested materialized views

The refresh methods supported by nested materialized views are the same as those supported by non-nested materialized views, including full refresh and incremental refresh. Although refreshing a nested materialized view only requires the user tables and materialized views (along with their mlogs) that it directly depends on, its data consistency relies on the materialized views it is based on. This means that to ensure the data of a nested materialized view remains up-to-date through refresh, the data of the materialized views it depends on must also be up-to-date, requiring them to be refreshed first.

For example, consider the following scenario: materialized view mv1 is built on tables tbl1 and tbl2, materialized view mv2 is built on materialized view mv1 and table tbl3, and materialized view mv3 is built on materialized views mv1 and mv2. If we refresh mv1, mv2, and mv3 in that order, we can ensure the overall data consistency of the nested materialized views. However, if we refresh mv2 first and then mv1, the data in mv2 will not be the latest (it will lag behind mv1). Similarly, if we refresh mv3 first and then mv2, the data in mv3 will not be the latest (it will lag behind mv2).

OceanBase Database supports cascading refreshes for nested materialized views. Cascading refreshes are divided into cascading inconsistent refreshes and cascading consistent refreshes:

• Cascading inconsistent refresh: Refreshes all materialized views that the nested materialized view depends on, starting from the bottom up. Each materialized view's refresh does not guarantee data consistency, and the data points read from the base tables are inconsistent. Cascading inconsistent refreshes are suitable for batch synchronization scenarios, such as when a business party synchronizes data from the upstream at regular intervals. After data synchronization is complete, a cascading inconsistent refresh can be performed to ensure the eventual consistency of the materialized views.

• Cascading consistent refresh: Snapshot-consistent cascading refresh, which ensures that after the entire cascading refresh is completed, the data points of all base tables that the upper-level materialized views depend on are consistent. Cascading consistent refreshes are suitable for real-time data synchronization scenarios. We ensure that after each cascading refresh, the data snapshots in the materialized views are at the same data point.

Nested materialized view refresh example

If a materialized view is fully refreshed, any dependent materialized views must be fully refreshed before they can be incrementally refreshed. Otherwise, an error will occur.

Here is an example:

1. Create the tbl1 table and insert one row of data.

   CREATE TABLE tbl1(id INT, name VARCHAR(30), PRIMARY KEY(id));
   

   INSERT INTO tbl1 VALUES (1, 'jack');
   

2. Create the tbl2 table and insert one row of data.

   CREATE TABLE tbl2(id INT, age INT, PRIMARY KEY(id));
   

   INSERT INTO tbl2 VALUES (1, 21);
   

3. Create the mv1 materialized view based on the tbl1 and tbl2 tables.

   CREATE MATERIALIZED VIEW mv1 (PRIMARY KEY (id1, id2))
       REFRESH FAST ON DEMAND
       AS SELECT tbl1.id id1, tbl2.id id2, tbl1.NAME, tbl2.AGE
           FROM tbl1, tbl2
           WHERE tbl1.id = tbl2.id;
   

4. Create a materialized view (mv2) based on the mv1 materialized view.

   CREATE MATERIALIZED VIEW mv2
       REFRESH FAST
       AS SELECT COUNT(*) cnt, COUNT(AGE) age_cnt, SUM(AGE) age_sum
           FROM mv1;
   

5. Query the data in the mv1 materialized view.

   SELECT * FROM mv1;
   

   The returned result is as follows:

   +------+------+------+------+
   | id1  | id2  | NAME | AGE  |
   +------+------+------+------+
   |    1 |    1 | jack |   21 |
   +------+------+------+------+
   1 row in set
   

6. Query the data in the mv2 materialized view.

   SELECT * FROM mv2;
   

   The returned result is as follows:

   +------+---------+---------+
   | cnt  | age_cnt | age_sum |
   +------+---------+---------+
   |    1 |       1 |      21 |
   +------+---------+---------+
   1 row in set
   

7. Insert one row of data into the tbl1 and tbl2 tables.

   INSERT INTO tbl1 VALUES (2, 'rose');
   

   INSERT INTO tbl2 VALUES (2, 19);
   

8. Incrementally refresh the mv1 materialized view.

   CALL dbms_mview.refresh('mv1', 'f');
   

9. Query the data in the mv1 materialized view.

   SELECT * FROM mv1;
   

   The returned result is as follows:

   +------+------+------+------+
   | id1  | id2  | NAME | AGE  |
   +------+------+------+------+
   |    1 |    1 | jack |   21 |
   |    2 |    2 | rose |   19 |
   +------+------+------+------+
   2 rows in set
   

10. Incrementally refresh the mv2 materialized view.

    CALL dbms_mview.refresh('mv2', 'f');
    

11. Query the data in the mv2 materialized view.

    SELECT * FROM mv2;
    

    The returned result is as follows:

    +------+---------+---------+
    | cnt  | age_cnt | age_sum |
    +------+---------+---------+
    |    2 |       2 |      40 |
    +------+---------+---------+
    1 row in set
    

12. Insert one row of data into the tbl1 and tbl2 tables.

    INSERT INTO tbl1 VALUES (3, 'mary');
    

    INSERT INTO tbl2 VALUES (3, 25);
    

13. Fully refresh the mv1 materialized view.

    CALL dbms_mview.refresh('mv1', 'c');
    

14. Query the data in the mv1 materialized view.

    SELECT * FROM mv1;
    

    The returned result is as follows:

    +------+------+------+------+
    | id1  | id2  | NAME | AGE  |
    +------+------+------+------+
    |    1 |    1 | jack |   21 |
    |    2 |    2 | rose |   19 |
    |    3 |    3 | mary |   25 |
    +------+------+------+------+
    3 rows in set
    

15. Incrementally refresh the mv2 materialized view.

    CALL dbms_mview.refresh('mv2', 'f');
    

    The returned result is as follows:

    ERROR 9760 (HY000): cannot fast refresh materialized view
    

    Notice

    Since mv1 has been fully refreshed, an error will occur if you directly incrementally refresh mv2. You must fully refresh mv2 first.

16. Query the data in the mv2 materialized view.

    SELECT * FROM mv2;
    

    The query result is as follows:

    +------+---------+---------+
    | cnt  | age_cnt | age_sum |
    +------+---------+---------+
    |    2 |       2 |      40 |
    +------+---------+---------+
    1 row in set
    

17. Fully refresh the mv2 materialized view.

    CALL dbms_mview.refresh('mv2', 'c')
    

18. Query the data in the mv2 materialized view.

    SELECT * FROM mv2;
    

    The returned result is as follows:

    +------+---------+---------+
    | cnt  | age_cnt | age_sum |
    +------+---------+---------+
    |    3 |       3 |      65 |
    +------+---------+---------+
    1 row in set
    

Monitoring information of materialized view refreshes

OceanBase Database supports monitoring materialized view refresh operations, providing the execution status and performance data of current and historical refresh tasks. This monitoring information helps evaluate refresh efficiency, identify potential performance bottlenecks, and provide a basis for subsequent tuning.

Monitoring features include:

• Reporting: Provides an overview of current and historical materialized view refresh operations, including key metrics such as the actual time spent on each refresh, to track and monitor refresh performance.
• Diagnostics: Detailed refresh records and performance data enable in-depth analysis of the causes of materialized view refresh delays or performance degradation. For example, it can determine whether the longer refresh time is due to increased system load or a larger amount of data changes.

Hierarchical diagnostic report (REFRESH_REPORT)

OceanBase Database supports the DBMS_MVIEW.REFRESH_REPORT subprogram, which generates a hierarchical diagnostic report for a single materialized view refresh task, helping users with performance tuning and troubleshooting.

The report is output in XML/JSON format. For more information about hierarchical diagnostic reports, see REFRESH_REPORT.

Example:

Retrieve the diagnostic report in XML format by using refresh_id.

obclient> SELECT DBMS_MVIEW.REFRESH_REPORT(refresh_id=> 100023);

Collect statistics

To improve query performance and optimize execution plans, OceanBase Database supports collecting statistics on the data distribution of tables and indexes. For materialized views, their data distribution can also be collected using a similar mechanism, helping the database better perform query optimization.

You can collect statistics for materialized views in the following ways:

• Use the ANALYZE TABLE statement. For more information, see ANALYZE.
• Call the DBMS_STATS.GATHER_TABLE_STATS('database_name', 'table_name') system procedure to collect statistics. For more information, see GATHER_TABLE_STATS.

Example:

Collect statistics for the mv2 materialized view.

obclient> ANALYZE TABLE mv2 UPDATE HISTOGRAM ON cnt,age_cnt,age_sum WITH 15 BUCKETS;

Materialized view-related views

References

• Overview of materialized views
• Materialized view logs
• Create materialized views
• Query rewrite for materialized views
• Query materialized views
• Delete materialized views