Refresh a materialized view

Data changes in the base table may result in data inconsistency with its materialized view. To maintain data consistency between base tables and materialized views, OceanBase Database supports refresh 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. The base table is 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.

Refresh methods

Full refresh

Prerequisite

To perform a full refresh of a materialized view, the column types of the materialized view must match those of the base table.

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 full refresh operation requires additional storage space. In addition, if the original table has indexes, all indexes are re-created.

Incremental refresh

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

• An incremental refresh does not support the MAX or MIN aggregate function.
• An incremental refresh does not support complex scenarios, such as those involving inner join views, UNION operators, and subqueries.
• An incremental refresh does not support expressions with random outputs, such as ROWNUM and RAND.
• An incremental refresh does not support ROLLUP or HAVING.
• DISTINCT can be disabled or removed. This is because the selected output columns are unique in a materialized view that supports the incremental refresh.
• An incremental refresh does not support ORDER BY.
• An incremental refresh does not support window functions.
• An incremental refresh supports scalar aggregate functions that do not contain a GROUP BY clause.
• The incremental refresh of a materialized view that involves a GROUP BY clause supports only aggregate functions such as SUM and COUNT, and the aggregate functions can only use simple columns, and the output must contain 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 do not contain the DISTINCT keyword. The arguments of aggregate functions are columns of the base table.
4. The SELECT clause can contain COUNT(*), and supports aggregate functions except for the MIN and MAX functions.

Limitations on incremental refreshes of a materialized view involving multiple tables in a simple 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 but no more than five tables that are joined through 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 (mlogs) are created for 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 the 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 the materialized view does not contain expressions with random output values, such as ROWNUM, RAND, and SYSDATE.

Examples

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 to manually perform the incremental refresh of the materialized view in ON DEMAND mode. Select the col2 column in the test_tbl1 table, calculate the aggregate results of count(*), count(col3), and sum(col3), and group the results by the col2 column. Then, record the results in the materialized view.

     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 to manually perform the incremental refresh of the materialized view in ON DEMAND mode. Based on the test_tbl1 table, calculate the aggregate results of count(*), count(col3), and sum(col3). Then, record the results in the materialized view.

     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 to manually perform the incremental refresh of the materialized view in ON DEMAND mode. Based on the test_tbl1 table, calculate the aggregate results of count(col3) and sum(col3). Then, record the results in the materialized view.

     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 to manually perform the incremental refresh of the materialized view in ON DEMAND mode. Select the col2 and col3 columns in the test_tbl1 table, calculate the aggregate results of count(*), count(col3), and sum(col3), and group the results by the col2 and col3 columns. Then, record the results in the materialized view.

     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 to manually perform the incremental refresh of the materialized view in ON DEMAND mode. Select the col2 column in the test_tbl1 table, calculate the aggregate results of count(*), count(col3), sum(col3), and avg(col3), calculate the custom calcol1 and calcol2 columns, and group the results by the col2 column. Then, record the results in the materialized view.

     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 to manually perform the incremental refresh of the materialized view in ON DEMAND mode. Select the col2 column in the test_tbl1 table, and calculate the aggregate results of count(*), count(col3), sum(col3), count(col3*col3), sum(col3*col3), and STDDEV(col3), and group the results by the col2 column. Then, record the results in the materialized view.

     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 an incremental refresh of a materialized value involving multiple tables

1. Create base tables named t1 and t2.

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

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

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

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

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

3. Create an incrementally refreshed materialized view named mv1_t1_t2 obtained by joining 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;
   

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: Fast refresh
                                                            --   ? Forcible refresh
                                                            --   C|c: Full refresh
                                                            --   A|a: Always refresh, 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 degree of parallelism (DOP) of the materialized view refresh operation. This parameter is supported only in OceanBase Database.

Here is an example:

1. Insert 3 data records to 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. Use DBMS_MVIEW.REFRESH to manually refresh the mv1_test_tbl1 materialized view.

   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 DOP of the refresh operation. This option supports only full refresh and does not take effect on incremental refresh.

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:DOP

   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 of materialized view refreshes

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

Statistics of materialized view refreshes provide the following benefits:

• Reporting: The system provides statistics overviews of ongoing and historical 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 historical 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 of materialized views, see DBMS_MVIEW_STATS overview.

View statistics of materialized view refreshes

References

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