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 refresh and incremental refresh for materialized views, and supports manual and automatic refresh scheduling.

Full refresh

OceanBase Database performs a full refresh by using remote refresh. Specifically, it creates a hidden table, executes a refresh statement on the hidden table, and then switches the original table with the hidden table. Therefore, a full refresh requires additional space and will fully rebuild 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 performing it.
• A full refresh is allowed only if the column types of the base table match those of the materialized view. Otherwise, a full refresh cannot be performed.
• If a materialized view is fully refreshed, all dependent materialized views (nested materialized views) must be fully refreshed before they can be incrementally refreshed. Otherwise, an error will be returned.

Incremental refresh

Currently, incremental refresh is supported for materialized views that use the following types of SQL statements: non-aggregated single-table statements, aggregated single-table statements, multi-table join statements, aggregated multi-table join statements, and UNION ALL statements. For other types of SQL statements, incremental refresh is not supported. For more information about the requirements for incremental refresh, see the following sections.

Non-aggregated incremental refresh for a single table

Example of non-aggregated incremental refresh for a single table

1. Create a table named tbl1.

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

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;
   

Single-table incremental refresh for aggregate views

The following conditions must be met for a single-table incremental refresh for aggregate views:

• The FROM table must be a base table, not an inline view or a standard 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 exists 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 from the query.

• Statements without the GROUP BY clause must be scalar aggregations (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 the dependent column.
  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 split into SUM and COUNT... (The calculation method changes, which may affect the precision.)	SUM (col1),COUNT(col1)
  MAX(expr)/MIN(expr) Note For V4.3.5, support for MAX and MIN aggregate incremental refresh is available starting from V4.3.5 BP4.	COUNT(expr)

  1. The GROUP BY clause must use standard GROUP BY syntax and cannot contain ROLLUP or HAVING clauses.
  2. The SELECT clause must contain all the GROUP BY columns.
  3. The DISTINCT keyword cannot be used in aggregate functions.
  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, the SELECT clause must also contain the COUNT(*) and COUNT(expr) columns.

Conditions for incremental refresh of MAX/MIN aggregate views:

In addition to the above conditions, the following conditions must be met when you use the MAX and MIN aggregate functions:

1. The GROUP BY columns and the MAX and MIN parameters are base table columns.
2. An index exists that starts with the GROUP BY columns.

Example of incremental refresh of a single table

1. Create a table named test_tbl1.

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

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 that the materialized view is refreshed incrementally and that you can manually trigger a refresh. The query part of the materialized view selects the col2 column from the test_tbl1 table and calculates the aggregated 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 that the materialized view is refreshed incrementally and that you can manually trigger a refresh. The query part of the materialized view calculates the aggregated 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 that the materialized view is refreshed incrementally and that you can manually trigger a 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 that the materialized view is refreshed incrementally and that you can manually trigger a refresh. The query part of the materialized view selects the col2 and col3 columns from the test_tbl1 table and calculates the aggregated 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 that the materialized view is refreshed incrementally and that you can manually trigger a refresh. The query part of the materialized view selects the col2 column from the test_tbl1 table and calculates the aggregated 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 that the materialized view is refreshed incrementally and that you can manually trigger a refresh. The query part of the materialized view selects the col2 column from the test_tbl1 table and calculates the aggregated 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 that the materialized view is refreshed incrementally and that you can manually trigger a refresh. The query part of the materialized view selects the col1 and col2 columns from the test_tbl1 table and calculates the aggregated 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 materialized views with multiple tables joined

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

1. The FROM table cannot be an inline view.

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

   Note

   In OceanBase Database V4.3.5 BP3 and later versions, the incremental refresh feature for materialized views now supports multi-table outer joins (LEFT JOIN/RIGHT JOIN).

   Restrictions on using outer joins: The join tree must be a left-deep join tree where INNER JOIN comes first and LEFT JOIN comes after.

   Note that if the SELECT clause of an incrementally refreshed materialized view contains a LEFT JOIN operator, specifying a primary key (PRIMARY KEY) or a unique index (UNIQUE INDEX) during creation is prohibited to avoid refresh exceptions caused by constraint conflicts.

3. Materialized log (mlog) must be created for each table in the FROM clause, and all 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 following clauses: ROLLUP, HAVING, WINDOW FUNCTION, DISTINCT, ORDER BY, LIMIT, and FETCH.

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

Example of incremental refresh of materialized views with multiple tables joined

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

   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 t1;
   DROP MATERIALIZED VIEW LOG ON t2;
   DROP TABLE t2;
   DROP MATERIALIZED VIEW rt_mv1;
   

2. Run the following statements to create tables t1 and index idx_t1_c2.

   CREATE TABLE t1(c1 INT GENERATED BY DEFAULT AS IDENTITY, c2 INT, c3 INT, c4 INT, c5 INT, PRIMARY KEY(c1));
   CREATE INDEX idx_t1_c2 ON t1(c2);
   

3. Run the following statements to create tables t2 and index idx_t2_c3.

   CREATE TABLE t2(c1 INT GENERATED BY DEFAULT AS IDENTITY, c2 INT, c3 INT, c4 INT, c5 INT, PRIMARY KEY(c1));
   CREATE INDEX idx_t2_c3 ON t2(c3);
   

4. Run the following statements to create materialized view logs on 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 statements 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 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 materialized views across multiple tables

The basic requirements for incremental refresh of aggregate materialized views across multiple tables are as follows:

• The basic requirements for incremental refresh of aggregate materialized views across multiple tables are the union of those for incremental refresh of aggregate materialized views on a single table and incremental refresh of materialized views with joins.

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

  Note

  For V4.3.5, incremental refresh of aggregate materialized views with outer joins is supported starting from V4.3.5 BP5.

Example of incremental refresh of aggregate materialized views across multiple tables

1. Create 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 a 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

A materialized view with set queries supports the use of UNION ALL in the materialized view and allows for incremental refreshes. During an incremental refresh, the materialized view can include all types of materialized views that support incremental refreshes, except for excepted join materialized views.

The following are the basic requirements for incremental refreshes of materialized views with set queries:

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

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

• The output column types at the same projection position in each branch of UNION ALL must be the same. You cannot forcibly convert the column types of the subbranches to a consistent type by using CAST.

  For example, in a materialized view that joins multiple tables, the primary keys of each table must appear in the SELECT clause. These SELECT output columns cannot be converted to additional CAST columns due to UNION ALL.

• If the same type or value of constants exist at the same projection position in each branch of UNION ALL, the constants are used as identifier columns to distinguish the branches.

Example of incremental refresh of materialized views with set queries

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 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 the materialized view mv_ua_tbl1_tbl2 that supports incremental refreshes of set queries. The materialized view contains a UNION ALL clause.

   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 this 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 appropriately setting its value, you can significantly improve refresh efficiency and optimize database performance.

When mview_refresh_dop is set to 0 or 1, parallel refresh is not used. For more information about the mview_refresh_dop system variable, 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 a global-level 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

• Use the DBA_MVIEWS view to query the parallelism of the background refresh of a materialized view. The following example shows how to query the parallelism of the background refresh of a materialized view.

  Note

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

  • If the value of the REFRESH_DOP field is not 0, the background refresh task of the materialized view uses the parallelism specified by REFRESH_DOP.
  • If the value of REFRESH_DOP is 0, the global mview_refresh_dop parameter 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 current_date
             NEXT current_date + INTERVAL '10' SECOND
         AS SELECT c1, c2
             FROM t1;
     

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

     SELECT OWNER, MVIEW_NAME,REFRESH_DOP
     FROM SYS.DBA_MVIEWS
     WHERE OWNER = 'SYS'
     AND MVIEW_NAME = 'MV0_T1';
     

     The return result is as follows:

     +-------+------------+-------------+
     | OWNER | MVIEW_NAME | REFRESH_DOP |
     +-------+------------+-------------+
     | SYS   | MV0_T1     |           0 |
     +-------+------------+-------------+
     1 row in set
     

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

     ALTER MATERIALIZED VIEW mv0_t1 PARALLEL 8;
     

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

     SELECT OWNER, MVIEW_NAME,REFRESH_DOP
     FROM SYS.DBA_MVIEWS
     WHERE OWNER = 'SYS'
     AND MVIEW_NAME = 'MV0_T1';
     

     The return result is as follows:

     +-------+------------+-------------+
     | OWNER | MVIEW_NAME | REFRESH_DOP |
     +-------+------------+-------------+
     | SYS   | MV0_T1     |           8 |
     +-------+------------+-------------+
     1 row in set
     

• Use the DBA_MVREF_RUN_STATS view to query the historical parallelism of a materialized view. The following example shows how to query the historical parallelism of a materialized view.

  Here is an example:

  SELECT REFRESH_ID, MVIEWS, PARALLELISM
  FROM SYS.DBA_MVREF_RUN_STATS
  WHERE MVIEWS = 'SYS.MV0_T1'
  ORDER BY REFRESH_ID;
  

  The return result is as follows:

  +------------+------------+-------------+
  | REFRESH_ID | MVIEWS     | PARALLELISM |
  +------------+------------+-------------+
  |    6752103 | SYS.MV0_T1 |           5 |
  |    6752733 | SYS.MV0_T1 |           5 |
  |    6753371 | SYS.MV0_T1 |           5 |
  |    6753985 | SYS.MV0_T1 |           8 |
  |    6754618 | SYS.MV0_T1 |           8 |
  |    6755249 | SYS.MV0_T1 |           8 |
  +------------+------------+-------------+
  6 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 during manual refresh.

Refresh a materialized view by using the REFRESH function

DBMS_MVIEW.REFRESH (
   list                   IN     VARCHAR2,                  -- The name of the materialized view. Multiple materialized views are not supported.
   method                 IN     VARCHAR2       := NULL,    -- The refresh option. Valid values: f, ?, C, c, and A. The default value is NULL. The meaning of each value is as follows:
                                                            --   f: fast refresh
                                                            --   ?: force refresh
                                                            --   C|c: complete refresh
                                                            --   A|a: always refresh, which is equivalent to C
   ----------- The following parameters are not supported and are provided only for Oracle compatibility. ----------------
   rollback_seg           IN     VARCHAR2       := NULL,
   push_deferred_rpc      IN     BOOLEAN        := true,
   refresh_after_errors   IN     BOOLEAN        := false,
   purge_option           IN     BINARY_INTEGER := 1,
   parallelism            IN     BINARY_INTEGER := 0,
   heap_size              IN     BINARY_INTEGER := 0,
   atomic_refresh         IN     BOOLEAN        := true, 
   nested                 IN     BOOLEAN        := false,
   out_of_place           IN     BOOLEAN        := false,
   skip_ext_data          IN     BOOLEAN        := false,
   ---------------------------------------------------------
   refresh_parallel       IN     BINARY_INTEGER := 0);       -- The refresh parallelism of the materialized view. This parameter is unique to OceanBase Database.

Here is an example:

1. Insert three rows of data into the test_tbl1 table.

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

2. Query the mv1_test_tbl1 materialized view.

   SELECT * FROM mv1_test_tbl1;
   

   The returned result is as follows:

   Empty set
   

3. Manually refresh the mv1_test_tbl1 materialized view.

   • 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 specifying the refresh option:

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

4. Query the mv1_test_tbl1 materialized view again.

   SELECT * FROM mv1_test_tbl1;
   

   The returned result is as follows:

   +------+------+----------+----------+
   | COL2 | CNT  | CNT_COL3 | SUM_COL3 |
   +------+------+----------+----------+
   |    3 |    1 |        1 |        3 |
   |    2 |    1 |        1 |        2 |
   |    1 |    1 |        1 |        1 |
   +------+------+----------+----------+
   3 rows in set
   

Set the refresh parallelism of a materialized view

For a materialized view that is manually refreshed, you can set the default refresh parallelism by using the mview_refresh_dop system variable.

You can also explicitly set the refresh parallelism for the current refresh by specifying 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 refresh parallelism for this refresh is 8.

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

   • Do not explicitly specify the refresh parallelism. The 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 and 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 of the automatic refresh of materialized views 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 current_date
               NEXT current_date + INTERVAL '10' SECOND
           AS SELECT c1, c2
              FROM t1;
   

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

   The automatic refresh operation is performed by an internal session. To take effect on 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 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 current_date
                     NEXT current_date + 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 current_date
                     NEXT current_date + INTERVAL '10' SECOND
            AS SELECT c1, c2
            FROM t1;
        

Refresh a nested materialized view

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 the user tables and materialized views (along with their mlogs) it directly depends on, the data consistency of the nested materialized view depends on the materialized views it is built upon. This means that to keep the data of a nested materialized view up to date through refreshes, you must first ensure that the data of the materialized views it depends on is up to date, and refresh those materialized views first.

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

For OceanBase Database V4.3.5, cascading refresh is supported for nested materialized views starting from V4.3.5 BP3. 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, 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 refresh is suitable for batch synchronization scenarios, such as when business parties synchronize data from upstream at regular intervals. After data synchronization is completed, cascading inconsistent refresh can be used to maintain the eventual consistency of materialized views.

• Cascading consistent refresh: This method ensures that, after the entire cascading refresh is completed, the data points read from 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 fully refreshed, any dependent materialized views must 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 VARCHAR2(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 a materialized view (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 return 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 return 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 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 the data in 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 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 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. Run the following command in the MySQL client:

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

    The return result is as follows:

    OBE-12052: cannot fast refresh materialized view
    at package body oceanbase.DBMS_MVIEW.DO_REFRESH , line : 54, col : 1
    at oceanbase.DBMS_MVIEW.REFRESH , line : 72, col : 1
    

    Notice

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

18. Query the data in the materialized view mv2 again. Run the following command in the MySQL client:

    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. Run the following command in the MySQL client:

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

20. Query the data in the materialized view mv2 again. Run the following command in the MySQL client:

    SELECT * FROM mv2;
    

    The return result is as follows:

    +------+---------+---------+
    | CNT  | AGE_CNT | AGE_SUM |
    +------+---------+---------+
    |    3 |       3 |      65 |
    +------+---------+---------+
    1 row in set
    

Example of a real-time nested materialized view

If a materialized view (nested materialized view) is a real-time materialized view, you must incrementally refresh the lower-level materialized view to update the materialized log (mlog). The query results of a real-time materialized view are obtained by simulating the mlog and merging the query results of the lower-level materialized view and itself. Therefore, you must incrementally refresh the lower-level materialized view to update the mlog to ensure that the data of the real-time materialized view is up-to-date.

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
       REFRESH COMPLETE ON DEMAND
       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
       REFRESH COMPLETE ON DEMAND
       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 real-time materialized view named mv_tbl3_3 based on the mv_tbl3_2 materialized view.

   CREATE MATERIALIZED VIEW mv_tbl3_3
       REFRESH COMPLETE ON DEMAND
       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. Refresh the incremental refresh materialized view mv_tbl3_2.

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

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

    SELECT * FROM mv_tbl3_3;
    

    The return result is as follows:

    +------+------+------+------+
    | COL1 | COL2 | COL3 | CNT  |
    +------+------+------+------+
    |    1 |    1 |    1 |    1 |
    +------+------+------+------+
    1 row in set
    

Materialized view refresh statistics

OceanBase Database can collect and save statistics about materialized view refresh operations. You can query the statistics by using specific views. The statistics of current and historical materialized view refresh operations are stored in the database. By analyzing the historical statistics of materialized view refresh operations, you can understand and analyze the performance of materialized view refresh operations in the database.

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

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

• Diagnosing: 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 increased system load or an increased amount of data changes.

Collect statistics of materialized views

You can collect statistics of 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 of tables and columns, see GATHER_TABLE_STATS.

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

View refresh information

References

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