Refresh a materialized view

Note

Incremental refreshes

Basic requirements for incremental refreshes of single tables

• The MAX and MIN aggregate functions are not supported.

• Complex scenarios such as inline views, UNION, and subqueries are not supported.

• Expressions that generate 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 materialized view that supports incremental refreshes 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 the GROUP BY scenario, only aggregate functions such as SUM and COUNT are supported, and only simple columns can be used in the aggregate functions. The GROUP BY clause must meet 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 contain all the GROUP BY columns.
  3. The aggregate functions cannot contain the DISTINCT keyword, and the parameters of the aggregate functions must be basic columns.
  4. The SELECT clause must contain COUNT(*). Other aggregate functions are supported, and the following aggregate functions are currently not supported: MIN and MAX.

  Aggregate function	SELECT clause must contain a dependency 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 decomposed into SUM and COUNT... (The calculation method may change, which may result in precision loss.)	SUM (col1),COUNT(col1)

Basic requirements for incremental refreshes of simple join queries across multiple tables

1. The FROM table must be a base table and cannot be an inline view, view, or materialized view.
2. The FROM table must contain at least two tables connected by inner joins. The number of tables connected must be less than or equal to 5.
3. The FROM table must have a primary key, and the primary key must be output in the SELECT clause.
4. Mlogs must be created for the FROM table, and the columns used in the view must be included in the mlogs.
5. The view definition must not contain subqueries.
6. The view definition must not contain the GROUP BY, ROLLUP, HAVING, WINDOW FUNCTION, DISTINCT, ORDER BY, LIMIT, or FETCH clause. It must be a simple join query (SPJ query).
7. The view definition must not contain expressions that generate unstable output values, such as ROWNUM, RAND, and SYSDATE.

Incremental refreshes

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 the incremental refresh method.

   • Create a materialized view named mv1_test_tbl1. Set the refresh method of the materialized view to incremental and specify that an incremental refresh can be manually triggered 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 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. Set the refresh method of the materialized view to incremental and specify that an incremental refresh can be manually triggered when needed; the query part of the materialized view calculates the aggregate results of count(*), count(col3), and sum(col3) based on all columns 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. Set the refresh method of the materialized view to incremental and specify that an incremental refresh can be manually triggered when needed; the query part of the materialized view calculates the results of count(col3) and sum(col3) based on all columns 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. Set the refresh method of the materialized view to incremental and specify that an incremental refresh can be manually triggered when needed; the query part of the materialized view returns 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 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. Set the refresh method of the materialized view to incremental and specify that an incremental refresh can be manually triggered 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), sum(col3), and avg(col3), as well as the values of some custom columns calcol1 and calcol2, based on 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. Set the refresh method of the materialized view to incremental and specify that an incremental refresh can be manually triggered 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), sum(col3), count(col3*col3), sum(col3*col3), and STDDEV(col3) based on 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;
     

Incremental refreshes of multi-table materialized views

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 mlogs 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 the mv1_t1_t2 materialized view that incrementally refreshes the join result 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;
   

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 statement

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 OceanBase Database.

Here is an example:

1. Insert 3 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 and has no effect on incremental refreshes.

Here is an example:

Refresh the materialized view while specifying the degree of parallelism.

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 the DDL statement.

   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 refresh operations. These statistics can be queried from specific views. Current and historical statistics on materialized view refresh operations 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 of refresh operations, both current and historical, to help you track and monitor refresh performance.

• Diagnosis: current and historical statistics are available for in-depth analysis of refresh performance. For example, if a materialized view refresh takes a long time, 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') 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 statistics on materialized view refreshes, see DBMS_MVIEW_STATS overview.

Views displaying materialized view refresh information

References

• Overview of materialized views
• Create materialized views
• Query materialized views
• Drop materialized views