Refresh materialized views

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

Full refresh

OceanBase Database performs a full refresh by using a remote refresh method. 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 rebuild indexes (if any) in full.

Considerations

• A full refresh can be a very time-consuming process, especially when a large amount of data needs to be read and processed. Therefore, you should always consider the time required for a full refresh before you perform one.
• If the columns of the base table match the column types of the corresponding columns in the materialized view, a full refresh can be performed. Otherwise, a full refresh cannot be performed.
• If a materialized view is fully refreshed, any 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 of materialized views supports SQL statements for non-aggregated single-table queries, aggregated single-table queries, multi-table joins, multi-table join aggregations, and UNION ALL queries. SQL statements that do not fall into these five categories are not supported for incremental refresh. For more information about the requirements for incremental refresh SQL statements, see the following description.

Incremental refresh for a non-aggregate table

Example of incremental refresh for a non-aggregate table

1. Create table tbl1.

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

2. Create a materialized view log on tbl1.

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

3. Create an incremental refresh materialized view named mv_tbl1 based on tbl1.

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

Single-table incremental refresh of aggregated views

The following requirements must be met for a single-table incremental refresh of an aggregated view:

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

  Note

  Nested materialized views do not support real-time materialized views. Therefore, if 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 the 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 clauses.

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

• Statements without the GROUP BY clause must be scalar aggregations (Scalar Aggregate).

• For materialized views in the GROUP BY scenario, 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 may change, which may affect precision)	SUM (col1),COUNT(col1)
  MAX(expr)/MIN(expr)	COUNT(expr)

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

Conditions for incremental refresh of aggregated views with MIN/MAX functions:

When the requirements for a single-table incremental refresh of an aggregated view are met, the requirements for using the MAX and MIN aggregate functions are as follows: an index exists on the base table of the materialized view, and the prefix of the index is the GROUP BY column.

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

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. Set the refresh method to incremental and allow manual refresh. The query part 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. The query part 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. The query part calculates the aggregate results of count(*), count(col3), and sum(col3) from the test_tbl1 table. The query part 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. The query part calculates the results of count(col3) and sum(col3) from the test_tbl1 table. The query part 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. 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), grouped by the values of the col2 and col3 columns. The query part 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. 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), grouped by the values of the col2 column. Additionally, the query part calculates some custom columns calcol1 and calcol2. The query part 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. 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), grouped by the values of the col2 column. The query part 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. The query part is as follows: SELECT col1, col2, count(*), SUM(col3), MIN(col3), MAX(col4) FROM test_tbl1 GROUP BY col1, col2;.

     1. Create an index named idx_test_tbl1 on the col1 and col2 columns of the test_tbl1 table. The index creation statement 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. 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, grouped by the combination of col1 and col2. The query part is as follows: SELECT col1, col2, count(*), SUM(MIN(col3) + MAX(col4)) FROM test_tbl1 GROUP BY col1, 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 a non-basic column in the GROUP BY clause. The query part is as follows: SELECT col1, col2, count(*), SUM(MIN(col3) + MAX(col4)) FROM test_tbl1 GROUP BY col1, col2;.

     1. Create a table named m_tbl1. The table creation statement is as follows: CREATE TABLE m_tbl1 (col1 INT, col2 DATE, col3 INT);.

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

     2. Create a materialized view log on the m_tbl1 table. The materialized view log creation statement is as follows: CREATE MATERIALIZED VIEW LOG ON m_tbl1;.

        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 col3 and (DATE(col2)) columns of the m_tbl1 table. The index creation statement is as follows: CREATE INDEX idx1_m_tbl1 ON m_tbl1 (col3, DATE(col2));.

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

     4. Create a single-table aggregate materialized view named m_tbl1_mv1. The materialized view creation statement is as follows: CREATE MATERIALIZED VIEW m_tbl1_mv1 REFRESH INCREMENTAL ON DEMAND AS SELECT col1, col2, count(*), SUM(MIN(col3) + MAX(col4)) FROM m_tbl1 GROUP BY col1, col2;.

        obclient> CREATE MATERIALIZED VIEW m_tbl1_mv1
            REFRESH FAST ON DEMAND
            AS SELECT
                col3,
                ROUND(col2) gby_2,
                COUNT(*) cnt,
                MAX(col4) max_c4,
                MIN(col5 + col6) min_c5_c6
            FROM m_tbl1
            GROUP BY col3, ROUND(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 must be a base table and cannot be an inline view.

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

   Notice

   • Materialized views with incremental refresh support outer joins (LEFT JOIN/RIGHT JOIN) in OceanBase Database.
   • Outer joins are supported only when the join tree is a left-deep join tree with INNER JOIN preceding LEFT JOIN.
   • If the SELECT clause of the materialized view to be incrementally refreshed contains LEFT JOIN, 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 FROM table, and the columns used in the view must exist in the mlogs.

4. The view definition cannot contain subqueries.

5. The view definition cannot contain the ROLLUP, HAVING, WINDOW FUNCTION, DISTINCT, ORDER BY, LIMIT, or FETCH clause.

6. The view definition cannot contain expressions that generate unstable output values, such as ROWNUM, RAND, or 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 a 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) Execute 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 t1;
   DROP MATERIALIZED VIEW LOG ON t2;
   DROP TABLE t2;
   DROP MATERIALIZED VIEW rt_mv1;
   

2. Execute the following statement to create table 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. Execute the following statement to create table 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. Execute 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. Execute 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. Execute 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 aggregate views over multiple tables

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

• The basic requirements for incremental refresh of aggregate views over multiple tables are the union of the basic requirements for incremental refresh of aggregate views over a single table and incremental refresh of joined views over multiple tables.
• Supports incremental refresh of aggregate views with outer joins. The restrictions on outer joins are the same as those for non-aggregated outer-joined materialized views. The restrictions on the aggregate part are the same as those for inner-joined aggregate 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 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 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 aggregate join of 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 materialized view that uses a UNION ALL set query supports incremental refresh. An incremental refresh of a materialized view allows you to use all incremental refreshable materialized views in the set query except for the except join materialized view.

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

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

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

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

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

• The same type or value of a constant exists at the same projection position in each branch of the UNION ALL query. The constant is used to identify the 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 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 refresh of a set query.

   CREATE MATERIALIZED VIEW mv_ua_tbl1_tbl2
       REFRESH FAST ON DEMAND
       AS SELECT
           a.col1 as a_c1,
           b.col1 as b_c1,
           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 set the parallelism for the current session.

Set mview_refresh_dop

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

When mview_refresh_dop is set to 0 or 1, parallel refresh is 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 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 can be used to view the parallelism of a materialized view.

  • When 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.
  • When the value of REFRESH_DOP is 0, the value of the global-level mview_refresh_dop variable 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 DBA_MVIEWS view to obtain the background refresh parallelism of the mv0_t1 materialized view.

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

     The returned result is as follows:

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

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

     ALTER MATERIALIZED VIEW mv0_t1 PARALLEL 8;
     

  4. Query the DBA_MVIEWS view to obtain the background refresh parallelism of the mv0_t1 materialized view.

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

     The returned result is as follows:

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

• Query the DBA_MVREF_RUN_STATS view to obtain the historical refresh 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 returned 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

When the refresh mode of a materialized view is ON DEMAND, you can use the DBMS_MVIEW package to manually refresh the materialized view. For materialized views defined for incremental refresh, you can specify a full refresh during manual refresh.

Use REFRESH to refresh a materialized view

DBMS_MVIEW.REFRESH (
   list                   IN     VARCHAR2,                  -- The name of the materialized view. This parameter does not support multiple materialized views.
   method                 IN     VARCHAR2       := NULL,    -- The refresh method. Valid values: f, ?, C, c, A, and a. For more information, see the description of the parameter. The default value is NULL. If you do not specify this parameter, the refresh method specified for the materialized view is used. The following table describes the valid values. For more information about the other parameters, see the Oracle documentation. The parameters are retained for compatibility with Oracle databases.
                                                            --   f: fast refresh
                                                            --   ?: force refresh
                                                            --   C|c: complete refresh
                                                            --   A|a: always refresh, which is equivalent to C
   -----------  The following parameters are retained for compatibility with Oracle databases. ----------------
   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 parallelism of the materialized view refresh. This parameter is specific to OceanBase Database.

Here is an example:

1. Insert three rows into the test_tbl1 table.

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

2. View the information of 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 method specified for the materialized view to refresh the materialized view:

     CALL DBMS_MVIEW.REFRESH('mv1_test_tbl1');
     

   • Specify the refresh method to refresh the materialized view:

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

4. View the information of the materialized view mv1_test_tbl1.

   SELECT * FROM mv1_test_tbl1;
   

   The return 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 parallelism of a manual refresh of a materialized view

You can set the system variable mview_refresh_dop to specify the default parallelism for the refresh of a materialized view.

You can also explicitly specify the refresh_parallel parameter in the DBMS_MVIEW.REFRESH function to specify the parallelism for the current refresh.

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 specify the refresh parallelism as 8. The parallelism for the current refresh is 8.

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

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

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

Automatically refresh 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 specified conditions are met.

Parallelism for automatically refreshing materialized views

You can specify the parallelism for automatically refreshing materialized views in the following two ways:

1. Specify the parallelism (Table DOP) when you create the 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.

   Automatic refresh operations are performed using internal sessions. To enable parallel refresh for background sessions, you must set the global-level mview_refresh_dop variable.

   Notice

   If you do not explicitly specify the parallelism when creating the materialized view and the mview_refresh_dop variable is set to 0 or 1, parallel refresh is not enabled for the background refresh task.

   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 creating the materialized view, the background refresh task will use 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 creating the materialized view, the background refresh task will use 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 nested materialized views

Refresh rules of nested materialized views

The refresh methods supported by nested materialized views are the same as those supported by non-nested materialized views, which include full refresh and incremental refresh. Although refreshing a nested materialized view only requires the user tables and materialized views (and their mlogs) that it directly depends on, its data consistency depends on the materialized views on which it is built. This means that to keep the data of a nested materialized view up to date through refresh, you must first ensure that the data of the materialized views it depends on is up to date, and refresh them first.

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 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 up to date (it will lag behind the data 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 up to date (it will lag behind the data of materialized view mv2).

OceanBase Database supports cascading refresh for nested materialized views. Cascading refresh is divided into cascading non-consistent refresh and cascading consistent refresh:

• Cascading non-consistent refresh: 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 read from the base tables it depends on is inconsistent. Cascading non-consistent refresh is suitable for batch synchronization, such as when a business party synchronizes data from an upstream source at regular intervals. After data synchronization is completed, cascading non-consistent refresh can be used to ensure the eventual consistency of materialized views.

• Cascading consistent refresh: Snapshot-consistent cascading refresh, which ensures that after the entire cascading refresh is completed, the data points of all base tables on which the upper-level materialized views depend 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 refreshed in full, then any dependent materialized views must also be refreshed in full before they can be refreshed incrementally. Otherwise, an error will occur.

Here is an 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. View 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. View 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. View 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. View 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. View 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:

    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 occur if you directly incrementally refresh mv2. You must fully refresh mv2 first.

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

Collect and store statistics for materialized view refreshes

OceanBase Database can collect and store statistics for materialized view refreshes. These statistics can be queried through specific views. Current and historical statistics for materialized view refreshes are stored in the database. By analyzing historical statistics for materialized view refreshes, you can understand and analyze the performance of materialized view refreshes in the database.

The statistics for materialized view refreshes serve the following purposes:

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

• Diagnostics: Enables effective analysis of materialized view refresh performance based on detailed current and historical statistics. For example, if a materialized view refresh takes a long time, the statistics can help identify whether the performance degradation is due to increased system load or a larger volume of data changes.

Collect statistics for materialized views

Statistics for materialized views can be collected using the analyze table statement or the call dbms_stats.gather_table_stats('database_name', 'table_name') procedure.

• For more information about collecting statistics for tables and columns, see GATHER_TABLE_STATS.

• For more information about managing the collection and retention of statistics for materialized view refreshes, see DBMS_MVIEW_STATS overview.

Show materialized 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