Refresh a materialized view

Updating data in the base table can cause the data of the materialized view to be inconsistent with that of the base table. To maintain the data of the materialized view, OceanBase Database refreshes the materialized view. A refresh of a materialized view also automatically updates all its indexes.

Refreshes of materialized views are performed serially with the help of object locks. When refreshing a materialized view, the database does not lock the base table, to enhance concurrency.

Refresh mode (refresh timing)

You can use the ON DEMAND clause to specify that a materialized view be refreshed when data is needed.

You can manually refresh a materialized view by using the DBMS_MVIEW package, or you can specify the START WITH ... NEXT ... clause when you create a materialized view to have it automatically and periodically refreshed.

For more information about the DBMS_MVIEW package, see Overview of DBMS_MVIEW.

Refresh methods

Complete refreshes

Prerequisites

A complete refresh is performed if the data types of all columns in the source table are compatible with those in the materialized view. Otherwise, the complete refresh cannot be performed. If you want to perform a complete refresh but the data types are incompatible, you can convert the data types of the source table columns or create a new table with compatible data types. Note that the new table must have the same name as the original table. Otherwise, the complete refresh will fail.

Refresh method

OceanBase database uses the cross-region refresh method to perform a full refresh, which means creating a hidden table, executing the refresh statement on the hidden table, and then switching between the original table and the hidden table. Therefore, the full refresh operation requires additional space and will fully rebuild indexes (if any).

Incremental refreshes

Limitations

• The MAX and MIN aggregate functions are not supported.

• Incremental updates are not supported in complex scenarios involving inline views, UNION, subqueries, and other similar features.

• Expressions with unstable output values, such as ROWNUM and RAND, are not supported.

• The ROLLUP and HAVING clauses are not supported.

• The ORDER BY clause is not supported.

• Window functions are not supported.

• If the query contains the DISTINCT keyword, the output column of the incremental update materialized view must be unique. In this case, you can either disable the DISTINCT keyword or remove it.

• A statement without the GROUP BY clause must be a scalar aggregate (Scalar Aggregate) statement.

• For materialized views in GROUP BY scenarios, only simple column names are supported in aggregate functions, and the GROUP BY clause must comply with the following requirements:

  1. The GROUP BY clause must be in the standard GROUP BY syntax and cannot contain ROLLUP or HAVING.
  2. The SELECT clause must include all GROUP BY columns.
  3. The aggregate functions cannot contain the DISTINCT keyword, and the parameters must be basic columns.
  4. The SELECT clause must include COUNT(*). Other aggregate functions are supported, except for the MIN and MAX functions.

  Aggregate function	SELECT clause must include a dependency column
  COUNT( expr )	N/A
  SUM ( expr )	COUNT( expr ) or expr 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 divided into SUM and COUNT... (The calculation method may change, which may result in precision loss.)	SUM (col1),COUNT(col1)

Incremental refresh example

1. Create a table named test_tbl1.

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

2. Create an mlog on the test_tbl1 table.

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

3. Create materialized views with incremental refreshes as the refresh method.

   • Create a materialized view named mv1_test_tbl1. Set the refresh method of the materialized view to incremental refresh. You can manually trigger a refresh when needed. The query part of the materialized view returns the col2 column from the test_tbl1 table and calculates the aggregate results of count(*), count(col3), and sum(col3) based on the values in 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. Set the refresh method of the materialized view to incremental refresh. You can manually trigger a refresh when needed. The query part of the materialized view calculates the aggregate results of count(*), count(col3), and sum(col3) based on the data in 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. Set the refresh method of the materialized view to incremental refresh. You can manually trigger a refresh when needed. The query part of the materialized view calculates the results of count(col3) and sum(col3) based on the data in 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. Set the refresh method of the materialized view to incremental refresh. You can manually trigger a refresh when needed. The query part of the materialized view selects the col2 and col3 columns from the test_tbl1 table, and calculates the aggregate results of count(*), count(col3), and sum(col3) based on the values in 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. Set the refresh method of the materialized view to incremental refresh. You can manually trigger a refresh when needed. The query part of the materialized view selects the col2 column from the test_tbl1 table, and calculates the aggregate results of count(*), count(col3), sum(col3), and avg(col3). It also calculates some custom columns calcol1 and calcol2 based on the data in the col2 column. The data in the col2 column is used for grouping.

     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 of the materialized view to incremental refresh. You can manually trigger a refresh when needed. The query part of the materialized view selects the col2 column from the test_tbl1 table, and calculates the aggregate results of count(*), count(col3), sum(col3), count(col3*col3), sum(col3*col3), and STDDEV(col3) based on the data in the col2 column. The data in the col2 column is used for grouping.

     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;
     

Manually refresh a materialized view

If the refresh mode of the materialized view is ON DEMAND, you can manually refresh the materialized view by using the DBMS_MVIEW package.

Refresh a materialized view by using the REFRESH method

DBMS_MVIEW.REFRESH (
   list                   IN     VARCHAR2,                  -- The name of the materialized view. Multiple materialized views are not supported.
   method                 IN     VARCHAR2       := NULL,    -- The refresh options.
                                                            --   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 not supported. They are provided 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 := 1);       -- The degree of parallelism for refreshing the materialized view. This parameter is specific to OB 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 mv1_test_tbl1 materialized view.

   SELECT * FROM mv1_test_tbl1;
   

   The return result is as follows:

   Empty set
   

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

   CALL DBMS_MVIEW.REFRESH('mv1_test_tbl1');
   

4. Query 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
   

Set the degree of parallelism

You can specify the refresh_parallel parameter to set the degree of parallelism for the refresh. Currently, this parameter applies only to complete refreshes, not incremental refreshes.

Here is an example:

Refresh materialized views in parallel.

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

Automatic refresh of materialized views

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

Degree of parallelism

When the system automatically refreshes a materialized view in the background, you can specify the degree of parallelism in the following three ways:

1. Specify the degree of parallelism in the hint.

   Here is an example:

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

2. Specify the degree of parallelism in the session variables of DDL statements.

   Here is an example:

   1. Enable parallel DDL execution.

      ALTER SESSION ENABLE PARALLEL DDL;
      

   2. Set the value of parallel DDL execution.

      ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
      

3. Specify the degree of parallelism when you create a materialized view (table DOP).

   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. These statistics can be queried from specific views. Current and historical statistics on materialized view refreshes are stored in the database. You can analyze the historical statistics on materialized view refreshes to understand the refresh performance in the database.

The statistics on materialized view refreshes serve the following purposes:

• Reporting: provides a summary of the actual execution time for recent refreshes, so that you can track and monitor the refresh performance.

• Diagnosis: current and historical statistics can help you analyze and identify performance issues with materialized view refreshes. For example, if a materialized view refresh takes a long time, the statistics can help you determine whether the performance degradation is caused by increased system load or data change volume.

Statistics collection for materialized views

Statistics are collected for materialized views. You can execute the analyze table statement or the call dbms_stats.gather_table_stats('database_name', 'table_name') procedure 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 statistics on materialized view refreshes, see DBMS_MVIEW_STATS overview.

Views displaying materialized view refresh information

References

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