Refresh a materialized view

When the data in the base table is updated, the data in the materialized view may become inconsistent with that in the base table. 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. You can manually or automatically refresh a materialized view.

Full refresh

OceanBase Database performs a full refresh by using the remote refresh method. In this method, a hidden table is created. Then, a refresh statement is executed on the hidden table, and the original table and hidden table are switched. Therefore, a full refresh operation requires additional space and rebuilds indexes (if any).

Considerations for full refresh

• A full refresh may 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 it.
• A full refresh is allowed only if the column types of the base table match those of the corresponding columns of the materialized view. If they do not match, a full refresh is not allowed.
• If a materialized view is fully refreshed, any dependent materialized views (nested materialized views) must be fully refreshed before they are incrementally refreshed. Otherwise, an error will be returned.

Incremental refresh

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

Non-aggregate incremental refresh from a single table

Example

1. Create a table named tbl1.

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

2. Create a materialized view log on the tbl1 table.

   CREATE MATERIALIZED VIEW LOG ON tbl1
       WITH SEQUENCE (col2, col3, col4) INCLUDING NEW VALUES;
   

3. Create a materialized view named mv_tbl1 for incremental refresh 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 an 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

  Since nested materialized views do not support real-time materialized views, 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 clause, the output columns of the materialized view that can be incrementally updated must be unique. In this case, you can directly disable the DISTINCT clause or remove it.

• If the query does not contain the GROUP BY clause, the query must be a scalar aggregate.

• For materialized views with the GROUP BY clause, the supported aggregate functions are SUM and COUNT, and only simple columns can be used in the aggregate functions. The requirements for GROUP BY are as follows:

  Aggregate function	The SELECT clause must contain dependent columns
  COUNT( expr )	N/A
  SUM ( expr )	COUNT( expr ) or expr is not null
  AVG ( expr )	SUM ( expr ),COUNT( expr )
  STDDEV ( expr )	SUM ( expr ),COUNT( expr ),SUM ( expr * expr )
  VARIANCE ( expr )	SUM ( expr ),COUNT( expr ),SUM ( expr * expr )
  Other aggregate functions that can be decomposed into SUM and COUNT... (The calculation method changes, which may cause precision changes)	SUM (col1),COUNT(col1)
  MAX(expr)/MIN(expr)	COUNT(expr)

  1. The GROUP BY clause must be in the standard GROUP BY syntax and cannot contain ROLLUP or HAVING.
  2. The SELECT clause must contain all columns in the GROUP BY clause.
  3. The aggregate functions cannot contain the DISTINCT keyword.
  4. The SELECT clause must contain the dependent columns and the COUNT(*) column corresponding to the aggregate functions, in addition to the columns of the aggregate functions. For example, if you use the SUM(expr) aggregate function, you must also specify the COUNT(*) and COUNT(expr) columns.

Conditions for incremental refresh of a single-table aggregate view with MAX/MIN aggregate functions:

If the requirements for an incremental refresh of a single-table aggregate view are met, the following requirement must be met for a materialized view with the MAX or MIN aggregate function: an index with the GROUP BY columns as the prefix exists on the base table of the materialized view.

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 log on the test_tbl1 table.

   CREATE MATERIALIZED VIEW LOG ON test_tbl1
     WITH SEQUENCE (col2, col3, col4) INCLUDING NEW VALUES;
   

3. Create a materialized view with an incremental refresh method.

   • Create a materialized view named mv1_test_tbl1. Set the refresh method to incremental and allow manual refresh triggers as needed. The query part selects the col2 column from the test_tbl1 table and calculates the aggregate results of count(*), count(col3), and sum(col3), grouping by the values in the col2 column. The query is as follows: SELECT col2, count(*), count(col3), sum(col3) FROM test_tbl1 GROUP BY col2;.

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

   • Create a materialized view named mv2_test_tbl1. Set the refresh method to incremental and allow manual refresh triggers as needed. The query part calculates the aggregate results of count(*), count(col3), and sum(col3) from the test_tbl1 table. The query is as follows: SELECT count(*), count(col3), sum(col3) FROM test_tbl1;.

     CREATE MATERIALIZED VIEW mv2_test_tbl1
       REFRESH FAST ON DEMAND
       AS SELECT count(*) cnt, count(col3) cnt_col3, sum(col3) sum_col3
          FROM test_tbl1;
     

   • Create a materialized view named mv3_test_tbl1. Set the refresh method to incremental and allow manual refresh triggers as needed. The query part calculates the results of count(col3) and sum(col3) from the test_tbl1 table. The query is as follows: SELECT count(col3), sum(col3) FROM test_tbl1;.

     CREATE MATERIALIZED VIEW mv3_test_tbl1
       REFRESH FAST ON DEMAND
       AS SELECT count(col3) cnt_col3, sum(col3) sum_col3
          FROM test_tbl1;
     

   • Create a materialized view named mv4_test_tbl1. Set the refresh method to incremental and allow manual refresh triggers as needed. The query part selects the col2 and col3 columns from the test_tbl1 table and calculates the aggregate results of count(*), count(col3), and sum(col3), grouping by the values in the col2 and col3 columns. The query is as follows: SELECT col2, col3, count(*), count(col3), sum(col3) FROM test_tbl1 GROUP BY col2, col3;.

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

   • Create a materialized view named mv5_test_tbl1. Set the refresh method to incremental and allow manual refresh triggers as needed. The query part selects the col2 column from the test_tbl1 table and calculates the aggregate results of count(*), count(col3), sum(col3), and avg(col3), grouping by the values in the col2 column. Additionally, it calculates two custom columns, calcol1 and calcol2. The query is as follows: SELECT col2, count(*), count(col3), sum(col3), avg(col3), calcol1, calcol2 FROM test_tbl1 GROUP BY col2;.

     CREATE MATERIALIZED VIEW mv5_test_tbl1
       REFRESH FAST ON DEMAND
       AS SELECT col2, count(*) cnt, count(col3) cnt_col3, 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 mv6_test_tbl1. Set the refresh method to incremental and allow manual refresh triggers as needed. The query part selects the col2 column from the test_tbl1 table and calculates the aggregate results of count(*), count(col3), sum(col3), count(col3*col3), sum(col3*col3), and STDDEV(col3), grouping by the values in the col2 column. The query is as follows: SELECT col2, count(*), count(col3), sum(col3), count(col3*col3), sum(col3*col3), STDDEV(col3) FROM test_tbl1 GROUP BY col2;.

     CREATE MATERIALIZED VIEW mv6_test_tbl1
       REFRESH FAST ON DEMAND
       AS SELECT col2, count(*) cnt, count(col3) cnt_col3, 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 using the MAX and MIN aggregate functions.

     1. Create an index named idx_test_tbl1 on the test_tbl1 table based on the col1 and col2 columns. The query is as follows: CREATE INDEX idx_test_tbl1 ON test_tbl1 (col1, col2);.

        CREATE INDEX idx_test_tbl1 ON test_tbl1(col1, col2);
        

     2. Create a materialized view named mv7_test_tbl1. Set the refresh method to incremental and allow manual refresh triggers as needed. The query part selects the col1 and col2 columns from the test_tbl1 table and calculates the aggregate results of count(*), the sum of the minimum value of col3 and the maximum value of col4 in each group, grouping by the combination of col1 and col2.

        CREATE MATERIALIZED VIEW mv7_test_tbl1
            REFRESH FAST ON DEMAND
            AS SELECT
                col1,
                col2,
                count(*) cnt,
                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 log on the m_tbl1 table.

        obclient> CREATE MATERIALIZED VIEW LOG ON m_tbl1
            WITH PRIMARY KEY, ROWID, SEQUENCE (col2, col3, col4, col5, col6)
            INCLUDING NEW VALUES;
        

     3. Create an index named idx1_m_tbl1 on the m_tbl1 table based on the col3 and (DATE(col2)) columns.

        obclient> CREATE INDEX idx1_m_tbl1 ON m_tbl1(col3, (DATE(col2)));
        

     4. Create a materialized view named m_tbl1_mv1 for single-table aggregate incremental refresh.

        obclient> CREATE MATERIALIZED VIEW m_tbl1_mv1
            REFRESH FAST ON DEMAND
            AS SELECT
                col3,
                DATE(col2) gby_2,
                COUNT(*) cnt,
                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 the incremental refresh of a materialized view with multiple tables joined:

1. The FROM clause must specify a base table, not an inline view.

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 and RIGHT JOIN).
   • Outer joins are supported only for left-deep join trees in which INNER JOIN precedes LEFT JOIN.
   • 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 are not supported for real-time materialized views.

3. Materialized log (mlog) must be created for each table in the FROM clause, and the columns used in the view must exist in the mlogs.

4. The view definition must not contain subqueries.

5. The view definition must not contain the ROLLUP, HAVING, WINDOW FUNCTION, DISTINCT, ORDER BY, LIMIT, and FETCH clauses.

6. The view definition must not contain expressions that generate unstable output values, such as ROWNUM, RAND, and SYSDATE.

Example

1. Create base tables t1 and t2.

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

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

2. Create materialized view logs on tables t1 and t2.

   CREATE MATERIALIZED VIEW LOG ON t1 WITH PRIMARY KEY, ROWID, SEQUENCE (c2) INCLUDING NEW VALUES;
   

   CREATE MATERIALIZED VIEW LOG ON t2 WITH PRIMARY KEY, ROWID, SEQUENCE (c4) INCLUDING NEW VALUES;
   

3. Create an incremental refresh materialized view mv1_t1_t2 for the join of tables t1 and t2.

   CREATE MATERIALIZED VIEW mv1_t1_t2
     REFRESH FAST
     AS SELECT t1.c1 t1c1, t1.c2, t2.c1 t2c1, t2.c4
       FROM t1 JOIN t2 ON t1.c1=t2.c1;
   

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

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

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

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

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

   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. Run the following statement to create table t2 and index idx_t2_c3.

   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. Run the following statement to create materialized view logs on tables t1 and t2.

   CREATE MATERIALIZED VIEW LOG ON t1 WITH PRIMARY KEY, ROWID, SEQUENCE (c2, c3, c4) INCLUDING NEW VALUES;
   CREATE MATERIALIZED VIEW LOG ON t2 WITH PRIMARY KEY, ROWID, SEQUENCE (c2, c3, c4) INCLUDING NEW VALUES;
   

5. Run the following statement to create a 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;
   

6. Run the following statement to create indexes on 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 an aggregate view over multiple tables

The following requirements apply to incremental refresh of an aggregate view over multiple tables:

• The requirements apply to the union of the requirements for incremental refresh of a single-table aggregate view and incremental refresh of a joined view over multiple tables.
• You can incrementally refresh an aggregate view with outer joins. The restrictions on outer joins apply to non-aggregated outer join materialized views. The restrictions on the aggregate part apply to inner join aggregate materialized views. However, aggregate 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 an aggregate view over 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 materialized view logs for the tables t3 and t4.

   CREATE MATERIALIZED VIEW LOG ON t3 WITH PRIMARY KEY, ROWID, SEQUENCE(c2, c3, c4) INCLUDING NEW VALUES;
   

   CREATE MATERIALIZED VIEW LOG ON t4 WITH PRIMARY KEY, ROWID, SEQUENCE(c2, c3, c4) INCLUDING NEW VALUES;
   

3. Create the real-time materialized view mv1_t3_t4 for incremental refresh of the join and aggregate operation between the tables t3 and t4.

   CREATE MATERIALIZED VIEW mv1_t3_t4
     REFRESH FAST
     ENABLE ON QUERY COMPUTATION
     AS SELECT t3.c1,
         COUNT(*) cnt,
         COUNT(t4.c4) cnt_c4,
         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 set materialized view supports the UNION ALL clause and allows incremental refreshes. An incremental refresh of a set materialized view allows the use of all materialized views that support incremental refreshes except for outer join materialized views in the set branches.

The following requirements apply to incremental refreshes of set queries:

• A materialized view that contains a set query does not support real-time materialization.

• 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 UNION ALL must have the same data type. You cannot use the CAST clause to forcibly convert the data types of the columns in the subqueries to the same data type.

  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 converted to other data types by using the CAST clause because of the UNION ALL clause.

• The same data type or value of a constant exists at the same projection position in each branch of UNION ALL. The constants are used to identify the branches.

Example of incremental refresh of a set query

1. Create 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 materialized view logs on tables ua_tbl1 and ua_tbl2.

   CREATE MATERIALIZED VIEW LOG ON ua_tbl1
       WITH PRIMARY KEY, ROWID, SEQUENCE (col2, col3, col4) INCLUDING NEW VALUES;
   

   CREATE MATERIALIZED VIEW LOG ON ua_tbl2
       WITH PRIMARY KEY, ROWID, SEQUENCE (col2, col3, col4) INCLUDING NEW VALUES;
   

3. Create a materialized view mv_ua_tbl1_tbl2 that supports incremental refreshes of set queries.

   CREATE MATERIALIZED VIEW mv_ua_tbl1_tbl2
       REFRESH FAST ON DEMAND
       AS SELECT
           a.col1 as a_c1,
           b.col1 as b_c1,
           1 marker,
           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,
           2 marker,
           count(*) val
       FROM ua_tbl1
       GROUP BY col1, col2;
   

Parallelism control mechanism for materialized view refresh

OceanBase Database provides a parallelism control mechanism for materialized view refresh. If you explicitly specify the parallelism for a refresh operation, the specified value is used. If you do not explicitly specify the parallelism, you can configure the system variable mview_refresh_dop to use the value of the system variable in the current session for the refresh operation.

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 setting this variable appropriately, 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 not used. 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 global parallelism to 5.

  SET GLOBAL mview_refresh_dop = 5;
  

  Notice

  Setting a global-level variable does not take effect on the current session. You need to log in again to create a new session for the setting to take effect.

View the parallelism of a materialized view

• Query the DBA_MVIEWS view to view the background refresh parallelism of a materialized view.

  Note

  The DBA_MVIEWS view can only view the parallelism specified for a materialized view.

  • If the REFRESH_DOP field is not 0, the background refresh task of the materialized view will use the parallelism corresponding to REFRESH_DOP.
  • If REFRESH_DOP is 0, the global-level mview_refresh_dop value will be used.

  Here are some examples:

  1. Create a materialized view named mv0_t1.

     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 of the materialized view mv0_t1.

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

     The returned result is as follows:

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

  3. Change the parallelism of the materialized view mv0_t1 to 8.

     ALTER MATERIALIZED VIEW mv0_t1 PARALLEL 8;
     

  4. Query the background refresh parallelism of the materialized view.

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

     The returned result is as follows:

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

• Query the DBA_MVREF_RUN_STATS view to view the historical refresh parallelism of a materialized view.

  Here are some examples:

  SELECT REFRESH_ID, MVIEWS, PARALLELISM
  FROM oceanbase.DBA_MVREF_RUN_STATS
  WHERE MVIEWS = 'db_test.mv0_t1'
  ORDER BY REFRESH_ID;
  

  The returned result is as follows:

  +------------+----------------+-------------+
  | REFRESH_ID | MVIEWS         | PARALLELISM |
  +------------+----------------+-------------+
  |    4928139 | db_test.mv0_t1 |           5 |
  |    4928218 | db_test.mv0_t1 |           5 |
  |    4928310 | db_test.mv0_t1 |           8 |
  |    4928397 | db_test.mv0_t1 |           8 |
  |    4928485 | db_test.mv0_t1 |           8 |
  +------------+----------------+-------------+
  5 rows in set
  

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 package. For a materialized view defined for incremental refresh, you can specify a full refresh when you manually refresh it.

Use REFRESH to manually refresh a materialized view

DBMS_MVIEW.REFRESH (
    IN mv_name          VARCHAR(65535),              -- The name of the materialized view.
    IN method           VARCHAR(65535) DEFAULT NULL, -- The refresh option. Valid values: f, ?, C, c, A, and a. The default value is NULL. The meaning of each value is as follows:
                                                     -- f: perform a fast refresh.
                                                     -- ?: perform a forced refresh.
                                                     -- C or c: perform a complete refresh.
                                                     -- A or a: perform a complete refresh. This option is equivalent to C.
    IN refresh_parallel INT DEFAULT 0);              -- The refresh parallelism. The default value is 0.

Here is an example:

1. Insert three rows of data 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. Query the information about the materialized view mv1_test_tbl1.

   SELECT * FROM mv1_test_tbl1;
   

   The return result is as follows:

   Empty set
   

3. Manually refresh the materialized view mv1_test_tbl1 by using the DBMS_MVIEW.REFRESH function.

   • Use the refresh option specified for the materialized view to refresh the materialized view:

     CALL DBMS_MVIEW.REFRESH('mv1_test_tbl1');
     

   • Specify the refresh option to refresh the materialized view:

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

4. Query the information about the materialized view mv1_test_tbl1.

   SELECT * FROM mv1_test_tbl1;
   

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

For manually refreshing a materialized view, you can set the system variable mview_refresh_dop to specify the default refresh parallelism for the materialized view refresh operation.

You can also explicitly specify the refresh parallelism by setting the refresh_parallel parameter when you call the DBMS_MVIEW.REFRESH function.

Here is an example:

1. Set the parallelism of the current session to 5.

   SET mview_refresh_dop = 5;
   

2. Manually refresh the materialized view:

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

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

   • Do not explicitly set the refresh parallelism. The parallelism of the current refresh 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 creates a background automatic refresh task for the materialized view when the conditions are met.

Parallelism of automatic refresh of materialized views

When a materialized view is automatically refreshed in the background, you can specify the parallelism in the following two ways:

1. Specify the parallelism (Table DOP) when you create a materialized view.

   Here is an example:

   CREATE MATERIALIZED VIEW mv_t1
       PARALLEL 8
       REFRESH COMPLETE 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 to specify the refresh parallelism.

   Automatic refresh operations are performed in internal sessions. To enable the background session to take effect, you must set the global-level mview_refresh_dop variable.

   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 parallelism as 8 when you create a materialized view, the background refresh task uses 8 as the refresh parallelism.

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

      • If you do not specify the parallelism when you create a materialized view, the background refresh task uses the value of the mview_refresh_dop variable, 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 for nested materialized views are the same as those for non-nested materialized views, including full refresh and incremental refresh. Although refreshing a nested materialized view only requires its directly dependent user tables and materialized views (along with their mlogs), the data consistency of the nested materialized view depends on the data consistency of the other materialized views it is built upon. This means that to ensure the data of a nested materialized view remains up-to-date through refreshes, you must first refresh the other materialized views it depends on.

For example, consider the following scenario: materialized view mv1 is built on tables tbl1 and tbl2, materialized view mv2 is built on mv1 and tbl3, and materialized view mv3 is built on mv1 and mv2. If you refresh mv1, mv2, and mv3 in that order, the data of all nested materialized views will be consistent. However, if you refresh mv2 first and then mv1, the data of mv2 will not be up-to-date (it will lag behind mv1). Similarly, if you refresh mv3 before mv2, the data of mv3 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. Each refresh does not guarantee data consistency, and the data points read from the base tables may not be consistent. Cascading inconsistent refreshes are suitable for batch synchronization scenarios, such as when a business party periodically synchronizes data from the upstream source. After the data synchronization is completed, a cascading inconsistent refresh can ensure the eventual consistency of the materialized views.

• Cascading consistent refresh: Snapshot consistent cascading refresh. 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 also be fully refreshed before they can be incrementally refreshed. Otherwise, an error will occur.

Example:

1. Create the tbl1 table and insert a 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 a row of data.

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

   INSERT INTO tbl2 VALUES (1, 21);
   

3. Create materialized view logs on the tbl1 and tbl2 tables.

   CREATE MATERIALIZED VIEW LOG ON tbl1 WITH PRIMARY KEY (name) INCLUDING NEW VALUES;
   

   CREATE MATERIALIZED VIEW LOG ON tbl2 WITH PRIMARY KEY (age) INCLUDING NEW VALUES;
   

4. 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;
   

5. Create a materialized view log on the mv1 materialized view.

   CREATE MATERIALIZED VIEW LOG ON mv1 WITH PRIMARY KEY (name, age) INCLUDING NEW VALUES;
   

6. Create the nested 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;
   

7. 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
   

8. 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
   

9. Insert a row of data into the tbl1 and tbl2 tables.

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

   INSERT INTO tbl2 VALUES (2, 19);
   

10. Incrementally refresh the mv1 materialized view.

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

11. 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
    

12. Incrementally refresh the mv2 materialized view.

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

13. 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
    

14. Insert a row of data into the tbl1 and tbl2 tables.

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

    INSERT INTO tbl2 VALUES (3, 25);
    

15. Fully refresh the mv1 materialized view.

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

16. 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
    

17. 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

    After the mv1 materialized view is fully refreshed, an error will be returned when you incrementally refresh the mv2 materialized view. You must fully refresh the mv2 materialized view before you incrementally refresh it.

18. 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
    

19. Fully refresh the mv2 materialized view.

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

20. 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
    

Materialized view refresh statistics

OceanBase Database collects and stores statistics about materialized view refresh operations. You can query these statistics from specific views. The statistics about current and historical materialized view refresh operations are stored in the database. By analyzing the historical statistics, you can understand and analyze the performance of materialized view refresh operations in the database.

The statistics about materialized view refresh operations serve the following purposes:

• Reporting: Provides an overview of the current and historical statistics about view refresh operations, including the actual time required to execute the refresh. This helps you track and monitor the refresh performance.

• Diagnostics: By analyzing the detailed current and historical statistics, you can effectively analyze the performance of materialized view refresh operations. For example, if a materialized view refresh operation takes a long time, the statistics can help you identify whether the performance degradation is caused by an increase in system load or a large amount of data changes.

Collect statistics about materialized views

You can collect statistics about materialized views by using the analyze table statement or the call dbms_stats.gather_table_stats('database_name', 'table_name') procedure.

• For more information about how to collect statistics about tables and columns, see GATHER_TABLE_STATS.

• For more information about how to manage the collection and retention of statistics about materialized view refresh operations, see DBMS_MVIEW_STATS overview.

Display refresh information of materialized views

References

• Overview of materialized views
• Materialized view logs
• Create a materialized view
• Query rewrite for materialized views
• Query a materialized view
• Delete a materialized view
• Resource isolation for materialized views
• Diagnose exceptions of materialized views