Refresh a materialized view

Data changes in a base table may result in data inconsistency with its materialized view. To maintain data consistency between base tables and materialized views, OceanBase Database supports refreshes of materialized views. When a materialized view is refreshed, all its indexes are automatically updated.

The refresh of a materialized view is performed in series with the help of object locks. Base tables are not locked during the refresh of a materialized view. This improves the concurrency performance.

Refresh mode

You can use the ON DEMAND clause to specify the manual on-demand refresh mode for a materialized view.

You can use the DBMS_MVIEW package to manually refresh a materialized view, or set periodical refresh of a materialized view by using the START WITH ... NEXT... statement.

For more information about the DBMS_MVIEW package, see Overview.

Refresh methods

Complete refresh

Refresh condition

To perform a complete refresh for a materialized view, the column types of the materialized view must match those of the base tables. If they do not match, you cannot perform a complete refresh.

Working mechanism

OceanBase Database first creates a hidden table, executes refresh statements on the hidden table, and then replaces the original table with the hidden table. Therefore, a complete refresh operation requires additional storage space. In addition, if the original table has indexes, all indexes are re-created.

Incremental refreshes

Limitations on incremental refreshes of an aggregate materialized view involving a single table

• The MAX and MIN aggregate functions are not supported for incremental refreshes.
• Incremental refreshes are not supported in complex scenarios involving inner join views, UNION operators, or subqueries.
• Expressions containing random output values, such as ROWNUM and RAND, are not supported. *ROLLUP and HAVING are not supported.
• The DISTINCT keyword can be disabled or removed. This is because the selected output columns are unique in a materialized view that supports an incremental refresh.
• ORDER BY is not supported.
• Window functions are not supported.
• Scalar aggregate functions without a GROUP BY clause are supported.
• Only aggregate functions, such as SUM and COUNT, are supported for the incremental refresh of a materialized view that involves a GROUP BY clause. The aggregate functions can use only simple columns and the output must contain the COUNT(*) and GROUP BY columns.

The requirements for GROUP BY are as follows:

1. The GROUP BY clause must be in standard GROUP BY syntax. ROLLUP and HAVING are not supported.
2. The SELECT clause must contain all GROUP BY columns.
3. The aggregate functions must not contain the DISTINCT keyword. The arguments of aggregate functions must be columns of the base table.
4. The SELECT clause must contain COUNT(*). The following table lists other supported aggregate functions. At present, the MIN and MAX functions are not supported.

Limitations on incremental refreshes of a materialized view involving a multi-table join

1. A table specified in the FROM clause must be a base table, and cannot be an inner join view, a normal view, or a materialized view.
2. The FROM clause contains at least two tables that are joined through an inner join.
3. A table specified in the FROM clause must have a primary key that is specified in the SELECT statement.
4. Materialized view logs are created on all tables specified in the FROM clause, and contain all columns used in the materialized view.
5. The definition of the materialized view does not contain subqueries.
6. The definition of a materialized view contains only select-project-join (SPJ) queries without the GROUP BY, ROLLUP, HAVING, WINDOW FUNCTION, DISTINCT, ORDER BY, LIMIT, or FETCH clause.
7. The definition of a materialized view does not contain expressions with random output values, such as ROWNUM, RAND, and SYSDATE.

Limitations on incremental refreshes of an aggregate materialized view involving multiple tables

The limitations on incremental refreshes of an aggregate materialized view involving multiple tables are the union set of the [Limitations on incremental refreshes of an aggregate materialized view involving a single table](#Limitations on incremental refreshes of an aggregate materialized view involving a single table) and [Limitations on incremental refreshes of a materialized view involving a multi-table join](#Limitations on incremental refreshes of a materialized view involving a multi-table join) sections.

Example of incremental refreshes of an aggregate materialized view involving 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) INCLUDING NEW VALUES;
   

3. Create a materialized view and specify the incremental refresh method.

   • Create a materialized view named mv1_test_tbl1. Specify incremental refresh as the refresh method, and specify that you can manually initiate a refresh as needed. In the query part, calculate the count(*), count(col3), and sum(col3) results of the col2 column in the test_tbl1 table. Then, group the data by the value 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 incremental refresh as the refresh method, and specify that you can manually initiate a refresh as needed. In the query part, calculate the count(*), count(col3), and sum(col3) results of 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 incremental refresh as the refresh method, and specify that you can manually initiate a refresh as needed. In the query part, calculate the count(col3) and sum(col3) results of 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 incremental refresh as the refresh method, and specify that you can manually initiate a refresh as needed. In the query part, calculate the count(*), count(col3), and sum(col3) results of the col2 and col3 columns in the test_tbl1 table. Then, group the data 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 incremental refresh as the refresh method, and specify that you can manually initiate a refresh as needed. In the query part, calculate the count(*), count(col3), sum(col3), and avg(col3) results of the col2 column, and the calcol1 and calcol2 results of some custom columns in the test_tbl1 table. Then, group the data by the value 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 incremental refresh as the refresh method, and specify that you can manually initiate a refresh as needed. In the query part, calculate the count(*), count(col3), sum(col3), count(col3*col3), sum(col3*col3) results of the col2 column in the test_tbl1 table. Then, group the data by the value 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;
     

Example of incremental refreshes of a materialized view involving a multi-table join

1. Create two base tables named t1 and t2 respectively.

   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 an incrementally refreshed materialized view named mv1_t1_t2 based on a join of 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 are some examples of creating indexes for a materialized view and its dependent base tables:

1. (Optional) Delete related test data.

   If the following database objects do not exist, skip this step.

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

2. Create a table named t1 and an index named idx_t1_c2 on this table.

   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. Create a table named t2 and an index named idx_t2_c3 on this table.

   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. Create a materialized view log on each of 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. Create a real-time materialized view named 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. Create an index for the primary key column of each base table of 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);
   

Example of incremental refreshes of an aggregate materialized view involving multiple tables

1. Create two base names named t3 and t4 respectively.

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

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

2. Create a materialized view log on each of the t3 and t4 tables.

   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 an incrementally refreshed materialized view named mv1_t3_t4 based on a join of the t3 and t4 tables.

   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;
   

Manually refresh a materialized view

If you set the refresh mode of a materialized view to ON DEMAND, you can use the DBMS_MVIEW package to manually refresh the materialized view.

Refresh a materialized view by using REFRESH

DBMS_MVIEW.REFRESH (
   list                   IN     VARCHAR2,                  -- The name of the materialized view. You can specify only one materialized view.
   method                 IN     VARCHAR2       : = NULL,    -- The refresh options. Valid values:
                                                            --   f: specifies to perform fast refreshes.
                                                            --   ?: specifies to perform forcible refreshes.
                                                            --   C|c: specifies to perform complete refreshes.
                                                            --   A|a: specifies to always perform refreshes, which is equivalent to C.
   ----------- The following parameters are provided for the compatibility with Oracle and do not take effect. ----------------
   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 : = 1);       -- The DOP of the materialized view refresh operation. This parameter is supported only in OceanBase Database.

Here is an example:

1. Insert three records 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 information about the mv1_test_tbl1 materialized view.

   SELECT * FROM mv1_test_tbl1;
   

   The return result is as follows:

   Empty set
   

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

   CALL DBMS_MVIEW.REFRESH('mv1_test_tbl1');
   

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

   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
   

Refresh DOP

You can use the refresh_parallel option to specify the degree of parallelism (DOP) of the refresh operation. This option is supported only for complete refreshes.

Here is an example:

Refresh the materialized view with a specified DOP.

CALL DBMS_MVIEW.REFRESH('mv3', refresh_parallel => 8);

Automatically refresh a materialized view

If you specify the START WITH datetime_expr and NEXT datetime_expr clauses when you create a materialized view, a background automatic refresh task is created for the materialized view.

Refresh DOP

You can specify the DOP of a background automatic refresh task in the following three ways:

1. Specify the refresh DOP in a hint.

   Here is an example:

   CREATE /*+ parallel(8) */ MATERIALIZED VIEW ...
   

2. Specify the refresh DOP in the session variable of a DDL operation.

   Here is an example:

   1. Enable the DOP of the DDL operation.

      ALTER SESSION ENABLE PARALLEL DDL;
      

   2. Set the DOP value of the DDL operation.

      ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
      

3. Specify the table-level DOP when you create the materialized view.

   Here is an example:

   CREATE MATERIALIZED VIEW xxx PARALLEL 8 ...
   

Statistics on materialized view refreshes

OceanBase Database can collect and store statistics on materialized view refreshes. You can query specific views to view the statistics. Statistics on both ongoing and history materialized view refresh operations are stored in the database. Statistics on history materialized view refreshes help you understand and analyze the performance of materialized view refreshes in the database.

Statistics on materialized view refreshes provide the following benefits:

• Reporting: The system provides statistics overviews of ongoing and history materialized view refresh operations, including the actual time required for executing the refresh. This helps you track and monitor the refresh performance.

• Diagnostics: You can use detailed statistics on ongoing and history materialized view refresh operations to effectively analyze the refresh performance. For example, if the refresh of a materialized view takes a long time, you can check the refresh statistics and identify whether the performance decrease is due to the increase in system workload or data changes.

Collect statistics on materialized views

OceanBase Database allows you to collect statistics on materialized views. You can use the analyze table or call dbms_stats.gather_table_stats('database_name', 'table_name') statement to collect statistics.

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

For more information about how to collect and retain refresh statistics on materialized views, see Overview.

Statistics on materialized view refreshes

References

• Overview
• Create a materialized view
• Query a materialized view
• Drop a materialized view