Refresh materialized views

Updating data in the base table may result in inconsistencies between the materialized view and the base table. To maintain data consistency in the materialized view, OceanBase Database refreshes the materialized view as needed.

OceanBase Database supports both full and incremental refresh strategies for materialized views, as well as manual and automatic scheduling of refreshes.

Full refresh

OceanBase Database performs full refreshes using a shadow table. This process involves creating a hidden table, executing the refresh statement on the hidden table, and then swapping the original table with the hidden one. Therefore, full refresh operations require additional storage space and will completely rebuild indexes (if any exist).

Considerations for full refresh

• A full refresh can be a time-consuming process, especially when a large amount of data needs to be read and processed. Therefore, before performing a full refresh, you should always consider the time required for the full refresh.
• If the column types in the current base table match those in the materialized view, full refresh is supported. If not, full refresh is not supported.
• 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 for materialized views supports SQL statements in five scenarios: single-table non-aggregation, single-table aggregation, multi-table join, multi-table join with aggregation, and set queries (UNION ALL). Incremental refresh is not supported for SQL statements that do not fit into these five scenarios. For more information about the SQL statements that support incremental refreshes, see the description below.

Incremental refresh in single-table non-aggregation scenarios

Example of incremental refresh in single-table non-aggregation scenarios

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 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 in single-table aggregation scenarios

The basic requirements for incremental refresh in single-table aggregation scenarios are as follows:

• The table specified in the FROM clause must be a base table, not an inline view or a standard view.

  Note

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

• Only one table is allowed in the FROM clause.

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

• The view definition must not contain subqueries.

• Window functions are not supported.

• The view definition must not include ROLLUP, HAVING, DISTINCT, ORDER BY, LIMIT, or FETCH clause.

• If the query includes DISTINCT, the output columns in the incrementally refreshable materialized view must be unique. In this case, you can either prohibit the use of DISTINCT directly, or remove it.

• Statements without GROUP BY must be scalar aggregations.

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

  Aggregation function	Requirements for the SELECT clause
  COUNT( expr )	N/A
  SUM ( expr )	COUNT( expr ) or expr be not null
  AVG ( expr )	SUM ( expr ) and COUNT( expr )
  STDDEV ( expr )	SUM ( expr ),COUNT( expr ),SUM ( expr * expr )
  VARIANCE ( expr )	SUM ( expr ),COUNT( expr ),SUM ( expr * expr )
  Other aggregation functions that can be decomposed into SUM and COUNT (the calculation method may change, which may affect the precision)	SUM (col1) and COUNT(col1)
  MAX(expr)/MIN(expr) Note For OceanBase Database V4.3.5, the MAX and MIN aggregate functions support incremental refresh from V4.3.5 BP4.	COUNT(expr)

  1. The GROUP BY clause must be in the standard GROUP BY syntax, and does not support ROLLUP or HAVING.
  2. The SELECT clause must contain all GROUP BY columns.
  3. The aggregation functions do not contain the DISTINCT keyword, and the parameters are fundamental columns.
  4. The SELECT clause must contain the columns of the aggregation function and the dependency columns of the aggregation function, and the COUNT(*) column.For example, if the aggregation function SUM(expr) is used, the COUNT(*) column, the COUNT(expr) column, and the SUM(expr) column must be included.

Requirements for incremental refresh with MAX/MIN aggregate functions:

When the preceding requirements are met, the GROUP BY column must have an index with the GROUP BY column as the prefix in the base table of the materialized view.

Here is an example:

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 incremental refresh with aggregation.

   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);
   

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 that is refreshed incrementally.

   • Create a materialized view named mv1_test_tbl1. Specify the refresh method as incremental 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 count(*), count(col3), and sum(col3), grouped by the values of the col2 column.

     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. Specify the refresh method as incremental and allow manual refresh. The query part of the materialized view calculates the aggregate results of count(*), count(col3), and sum(col3) from the test_tbl1 table.

     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. Specify the refresh method as incremental and allow manual refresh. The query part of the materialized view calculates the results of count(col3) and sum(col3) from the test_tbl1 table.

     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. Specify the refresh method as incremental 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 results of count(*), count(col3), and sum(col3), grouped by the values of the col2 and col3 columns.

     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. Specify the refresh method as incremental 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 count(*), count(col3), sum(col3), and avg(col3), and also calculates some custom columns calcol1 and calcol2, grouped by the values of the col2 column.

     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. Specify the refresh method as incremental 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 count(*), count(col3), sum(col3), count(col3*col3), sum(col3*col3), and STDDEV(col3), grouped by the values of the col2 column.

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

        CREATE INDEX idx_test_tbl1 ON test_tbl1(col1, col2);
        

     2. Create a materialized view named mv7_test_tbl1. Specify the refresh method as incremental 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 aggregate results of count(*), the sum of the minimum value of col3 and the maximum value of col4 in each group, and groups the data 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; ```

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 cannot contain an inline view.

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

   Note

   • For OceanBase Database V4.3.5, incremental refresh of a materialized view with multiple tables joined supports outer joins (LEFT JOIN/RIGHT JOIN) starting from V4.3.5 BP3.
   • Outer joins are supported only if the join tree is a left-deep join tree in which INNER JOIN is performed before LEFT JOIN or RIGHT JOIN.

3. Materialized log (mlog) must be created for each table in the FROM clause, and the columns used in the materialized view must be present 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, or FETCH clause.

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

Example of incremental refresh of a materialized view with multiple tables joined

1. Create the 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 the t1 and t2 tables.

   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 the materialized view mv1_t1_t2 for incremental refresh of the join between the t1 and t2 tables.

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

   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 statements 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;
   

6. Run the following statements 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 aggregate views over multiple tables

The requirements for incremental refresh of aggregate views over multiple tables are as follows:

• The requirements for incremental refresh of aggregate views over multiple tables are the union of the requirements for incremental refresh of aggregate views over a single table and incremental refresh of joined views over multiple tables.

• Incremental refresh is supported for materialized views with outer joins. The limitations for outer joins in non-aggregated materialized views also apply to aggregated materialized views with outer joins. The limitations for inner joins in aggregated materialized views also apply to aggregated materialized views with outer joins. However, aggregated materialized views with outer joins do not support the MIN and MAX aggregate functions, and do not support real-time materialized views.

  Note

  For OceanBase Database V4.3.5, incremental refresh is supported for materialized views with outer joins starting with V4.3.5 CP5.

Examples of incremental refresh of aggregate views 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 on 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 the incremental refresh of the join and aggregate operations on 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 materialized views with set queries

Set materialized views support using UNION ALL set queries and allow incremental refresh. Incrementally refreshed set materialized views permit all types of incrementally refreshable materialized views in each set branch, except for outer join materialized views.

The basic requirements for incremental refresh with set queries are as follows:

• Materialized views with set queries do not support real-time materialized views.

• The top-level query must be UNION ALL; the view definition must not contain subqueries, nor ORDER BY/LIMIT/FETCH clauses.

• For each branch of UNION ALL, the output column types at the same projection position must be the same. It is not supported to forcibly convert column types to a consistent type using CAST in sub-branches.

  For example, for multi-table join materialized views, the primary keys of each joined table must appear in the SELECT statement. These output columns in the SELECT cannot have additional CAST operations added due to UNION ALL.

• For each branch of UNION ALL, if there are constants at the same projection position with the same type but different values, these constants can serve as identifier columns to distinguish each branch.

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

   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 the materialized view mv_ua_tbl1_tbl2 for incremental refresh of materialized views with 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 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 control the parallelism for materialized view refresh at the session level.

Set the mview_refresh_dop parameter

mview_refresh_dop is a system variable in OceanBase Database that specifies the default degree of parallelism (DOP) for materialized view refresh operations. By setting an appropriate value for this variable, 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 mview_refresh_dop system variable, see mview_refresh_dop.

Here are some examples:

• Set the DOP for the current session to 5.

  SET mview_refresh_dop = 5;
  

• Set the DOP for all sessions to 5.

  SET GLOBAL mview_refresh_dop = 5;
  

  Notice

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

View the parallelism information of a materialized view

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

  Note

  The DBA_MVIEWS view displays the parallelism of a materialized view that is specified for the materialized view.

  • When the value of the REFRESH_DOP column is not 0, the background refresh task of the materialized view uses the parallelism specified by REFRESH_DOP.
  • When the value of REFRESH_DOP is 0, the parallelism specified by the mview_refresh_dop parameter at the global level is used.

  Here is an example:

  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. Check the background refresh parallelism setting for 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 return result is as follows:

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

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

     ALTER MATERIALIZED VIEW mv0_t1 PARALLEL 8;
     

  4. Check the background refresh parallelism setting for the materialized view.

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

     The return 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 obtain the historical background refresh parallelism of a materialized view.

  Here is an example:

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

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

You can use the DBMS_MVIEW package to manually refresh a materialized view whose refresh mode is set to ON DEMAND. For materialized views defined for incremental refreshes, you can specify a complete refresh when you manually refresh them.

Use the REFRESH Statement to Refresh a Materialized View

DBMS_MVIEW.REFRESH (
    IN mv_name          VARCHAR(65535),              -- materialized view name.
    IN method           VARCHAR(65535) DEFAULT NULL, -- Refresh option
                                                     --   f fast refresh </think>                                                       --   f fast refresh
                                                     --   ? Forced refresh
                                                     --   C|c refresh all rows in table
                                                     --   A|a Refreshes continuously, which is equivalent to C.
    IN refresh_parallel INT DEFAULT 0);              -- Refresh parallelism level.

Example:

1. Insert data into the test_tbl1 table. The table contains three data records.

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

2. Run the following command to view 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 procedure.

   • Refresh the materialized view using the refresh option defined for the materialized view:

     CALL DBMS_MVIEW.REFRESH('mv1_test_tbl1');
     

   • Refresh the materialized view by specifying the refresh option:

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

4. Query the information about the materialized view mv1_test_tbl1 again.

   SELECT * FROM mv1_test_tbl1;
   

   The following command is returned:

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

Adjust the parallelism of manual refresh of materialized view

You can set the default parallelism for manual refreshes of materialized views by using the mview_refresh_dop system variable.

You can also explicitly specify refresh_parallel to set the refresh degree of parallelism when calling the DBMS_MVIEW.REFRESH function.

Example:

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

   SET mview_refresh_dop = 5;
   

2. Manually refresh the materialized view:

   • Set the refresh parallelism to 8 explicitly. This time, the refresh parallelism is set to 8.

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

   • The parallelism for this refresh is 5, based on the session variable value, as it was not explicitly specified.

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

Automatic refresh of materialized views

When you create a materialized view and 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 as the refresh parallelism.

   Automatic refresh operations are performed in internal sessions. To make the setting effective in the background session, 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 mview_refresh_dop variable is set to 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, 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) it directly depends on, its data consistency depends on the materialized views on which it is built. 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 be refreshed first to ensure they are up-to-date.

For example, in the following figure, 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 you refresh mv1, mv2, and mv3 in this order, the data of the 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 first and then mv2, the data of mv3 will not be up-to-date (it will lag behind mv2).

Starting from OceanBase Database V4.3.5 BP3, nested materialized views in OceanBase Database V4.3.5 support cascading refresh. Cascading refresh is divided into cascading inconsistent refresh and cascading consistent refresh.

• Cascading inconsistent refresh: This method refreshes all materialized views that the nested materialized view depends on from bottom to top. Each materialized view is refreshed without data consistency guarantees, and the data points read from the base tables are inconsistent. Cascading inconsistent refresh is suitable for batch synchronization scenarios, such as when a business party periodically synchronizes data from an upstream data source. After the data synchronization is complete, cascading inconsistent refresh can be used to ensure the eventual consistency of the materialized views.

• Cascading consistent refresh: This method 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 refresh is 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 completely refreshed, any dependent materialized views must be completely 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 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 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;
   

7. Query data from the mv1 materialized view.

   SELECT * FROM mv1;
   

   The return result is as follows:

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

8. Query data from the mv2 materialized view.

   SELECT * FROM mv2;
   

   The return result is as follows:

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

9. Insert one 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 data from the mv1 materialized view.

    SELECT * FROM mv1;
    

    The return 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 data from the mv2 materialized view.

    SELECT * FROM mv2;
    

    The return result is as follows:

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

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

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

    INSERT INTO tbl2 VALUES (3, 25);
    

15. Completely refresh the mv1 materialized view.

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

16. Query data from the mv1 materialized view.

    SELECT * FROM mv1;
    

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

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

    The return result is as follows:

    ERROR 9760 (HY000): cannot fast refresh materialized view
    

    Notice

    Since mv1 has been fully refreshed, an error is returned when you incrementally refresh mv2 directly. You must fully refresh mv2 first.

18. Query the data in the materialized view mv2 again.

    SELECT * FROM mv2;
    

    The return result is as follows:

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

19. Fully refresh the materialized view mv2.

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

20. Query the data in the materialized view mv2 again.

    SELECT * FROM mv2;
    

    The return result is as follows:

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

Real-time nested materialized view example

If a materialized view (nested materialized view) is a real-time materialized view, the incremental refresh of the lower-level materialized view must be performed to update the mlog. The query results of the real-time materialized view are obtained by merging the query results of the lower-level materialized view and itself, which are simulated by the mlog. Therefore, the incremental refresh of the lower-level materialized view must be performed to update the mlog, ensuring that the data of the real-time materialized view is the most recent.

Here is an example:

1. Create a table named tbl3.

   CREATE TABLE tbl3(col1 INT, col2 INT, col3 INT);
   

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

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

3. Create a materialized view named mv_tbl3_1 based on the tbl3 table.

   CREATE MATERIALIZED VIEW mv_tbl3_1
       ENABLE ON QUERY COMPUTATION
       AS SELECT col1, col2, col3, COUNT(*) AS cnt
           FROM tbl3
           GROUP BY col1, col2, col3;
   

4. Create a materialized view log on the mv_tbl3_1 materialized view.

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

5. Create a materialized view named mv_tbl3_2 based on the mv_tbl3_1 materialized view.

   CREATE MATERIALIZED VIEW mv_tbl3_2
       ENABLE ON QUERY COMPUTATION
       AS SELECT col1, col2, col3, COUNT(*) AS cnt
           FROM mv_tbl3_1
           GROUP BY col1, col2, col3;
   

6. Create a materialized view log on the mv_tbl3_2 materialized view.

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

7. Create a materialized view named mv_tbl3_3 based on the mv_tbl3_2 materialized view.

   CREATE MATERIALIZED VIEW mv_tbl3_3
       ENABLE ON QUERY COMPUTATION
       AS SELECT col1, col2, col3, COUNT(*) AS cnt
           FROM mv_tbl3_2
           GROUP BY col1, col2, col3;
   

8. Insert a row of data into the tbl3 table.

   INSERT INTO tbl3 VALUES(1, 1, 1);
   

9. Query the tbl3 table.

   SELECT * FROM tbl3;
   

   The returned result is as follows:

   +------+------+------+
   | col1 | col2 | col3 |
   +------+------+------+
   |    1 |    1 |    1 |
   +------+------+------+
   1 row in set
   

10. Query the mv_tbl3_1 materialized view.

    SELECT * FROM mv_tbl3_1;
    

    The returned result is as follows:

    +------+------+------+------+
    | col1 | col2 | col3 | cnt  |
    +------+------+------+------+
    |    1 |    1 |    1 |    1 |
    +------+------+------+------+
    1 row in set
    

11. Query the mv_tbl3_2 materialized view.

    SELECT * FROM mv_tbl3_2;
    

    The returned result is as follows:

    Empty set
    

12. Query the mv_tbl3_3 materialized view.

    SELECT * FROM mv_tbl3_3;
    

    The returned result is as follows:

    Empty set
    

13. Incrementally refresh the mv_tbl3_1 materialized view.

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

14. Query the mv_tbl3_2 materialized view again.

    SELECT * FROM mv_tbl3_2;
    

    The returned result is as follows:

    +------+------+------+------+
    | col1 | col2 | col3 | cnt  |
    +------+------+------+------+
    |    1 |    1 |    1 |    1 |
    +------+------+------+------+
    1 row in set
    

15. Query the mv_tbl3_3 materialized view again.

    SELECT * FROM mv_tbl3_3;
    

    The returned result is as follows:

    Empty set
    

16. Incrementally refresh the mv_tbl3_2 materialized view.

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

17. View the data in the materialized view mv_tbl3_3 again.

    SELECT * FROM mv_tbl3_3;
    

    The returned result is as follows:

    +------+------+------+------+
    | col1 | col2 | col3 | cnt  |
    +------+------+------+------+
    |    1 |    1 |    1 |    1 |
    +------+------+------+------+
    1 row in set
    

View refresh statistics

OceanBase Database can collect and store statistics about view refresh operations. You can query these statistics by using specific views. Current and historical view refresh statistics are stored in the database. By analyzing historical view refresh statistics, you can understand and analyze the performance of view refreshes in the database.

The statistics about view refreshes provide the following features:

• Reporting: provides an overview of current and historical statistics about view refreshes, including the actual time required for the refresh to execute, to track and monitor the refresh performance.

• Diagnostics: provides detailed current and historical statistics about view refreshes to analyze the performance of view refreshes. For example, if the refresh of a view takes a long time, the statistics can help you identify whether the performance degradation is caused by an increase in system load or an increase in 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 view refreshes, see DBMS_MVIEW_STATS overview.

View the refresh information of materialized views

References

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