Refresh a materialized view

Note

Full refreshes can be time-consuming. Especially when a large amount of data is read and processed. Therefore, before you perform a full refresh, you should always consider the time required for the full refresh.

Incremental refreshes

At present, incremental refreshes support SQL statements that aggregate data in a single table, join multiple tables, and aggregate data in multiple joined tables. Incremental refreshes are not supported for other SQL statements. For more information, see the requirements for incremental refresh SQL statements.

Basic requirements for incremental refreshes of single-table aggregates

• The incremental refresh does not support the MAX and MIN aggregate functions.

• The incremental refresh does not support complex scenarios such as inline views, UNION, and subqueries.

• The incremental refresh does not support expressions with unstable output values, such as ROWNUM and RAND.

• The incremental refresh does not support ROLLUP or HAVING.

• The incremental refresh does not support ORDER BY.

• The incremental refresh does not support window functions.

• 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 (SA) statement.

• For materialized views in GROUP BY scenarios, only aggregate functions such as SUM and COUNT are supported, and only simple columns can be used in these aggregate functions. The requirements for the GROUP BY clause are as follows:

  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 supported and other requirements are as follows. At present, the MIN and MAX functions are not supported.

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

Single-table aggregate incremental refreshes

1. Create a table named test_tbl1.

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

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.

   • 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.

     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.

     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;
     

Basic requirements for incremental refreshes of multi-table joins

1. The FROM table must be a base table and cannot be an inline view, a view, or a materialized view.
2. The FROM table must contain at least two tables connected by inner joins.
3. The FROM table must have a primary key, and the primary key must be output in the SELECT clause.
4. An mlog must be created for the FROM table, and the columns used in the view must be included in the mlog.
5. The view definition must not contain subqueries.
6. The view definition must not contain the ROLLUP, HAVING, WINDOW FUNCTION, DISTINCT, ORDER BY, LIMIT, or FETCH clause.
7. The view definition must not contain expressions that use unstable output values such as ROWNUM, RAND, and SYSDATE.

Perform incremental refreshes on a materialized view that contains multiple tables

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 a materialized view that performs incremental refreshes on 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;
   

Here is an example of creating indexes on the tables that a materialized view depends on:

1. (Optional) Execute the following statements to drop the related test data.

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

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

2. Execute the following statement to create a table named t1 and an index named idx_t1_c2.

   CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT, c2 INT, c3 INT, c4 INT, c5 INT);
   CREATE INDEX idx_t1_c2 ON t1(c2);
   

3. Execute the following statement to create a table named t2 and an index named idx_t2_c3.

   CREATE TABLE t2(c1 INT PRIMARY KEY AUTO_INCREMENT, c2 INT, c3 INT, c4 INT, c5 INT);
   CREATE INDEX idx_t2_c3 ON t2(c3);
   

4. Execute the following statements to create mlogs on 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. Execute the following statement to 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. 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);
   

Basic requirements

The basic requirements for aggregate incremental refreshes of multiple tables are the union of the [basic requirements for aggregate incremental refreshes of single tables](#Single-table aggregate incremental refreshes) and the [basic requirements for join-based incremental refreshes](#Multi-table join-based incremental refreshes).

Example of aggregate incremental refreshes of multiple tables

1. Create base tables named 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 mlogs for 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 a real-time materialized view named mv1_t3_t4 for aggregate incremental refreshes 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

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

Refresh a materialized view using the REFRESH procedure

DBMS_MVIEW.REFRESH (
    IN mv_name          VARCHAR(65535),              -- The name of the materialized view.
    IN method           VARCHAR(65535) DEFAULT 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.
    IN refresh_parallel INT DEFAULT 1);              -- The degree of parallelism of the refresh.

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 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 |
   +------+------+----------+----------+
   |    1 |    1 |        1 |        1 |
   |    2 |    1 |        1 |        2 |
   |    3 |    1 |        1 |        3 |
   +------+------+----------+----------+
   3 rows in set
   

Degree of parallelism

You can set the refresh_parallel parameter to specify the degree of parallelism of this refresh. Currently, this parameter applies only to complete refreshes and has no effect on incremental refreshes.

Here is an example:

Refresh a materialized view in parallel.

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

Automatically refresh 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 created 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.

      At the system level:

      SET _ENABLE_PARALLEL_DDL = 1;
      

      At the session level:

      SET SESSION _ENABLE_PARALLEL_DDL = 1;
      

   2. Set the value of the degree of parallelism.

      At the system level:

      SET _FORCE_PARALLEL_DDL_DOP = 8;
      

      At the session level:

      SET SESSION _FORCE_PARALLEL_DDL_DOP = 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 through 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 refreshes, both current and historical, to help you track and monitor refresh performance.

• Diagnosis: through current and historical statistics, you can analyze and optimize refresh performance. For example, if a 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') 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 of materialized views
• Create materialized views
• Query materialized views
• Drop materialized views