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 include ROLLUP or HAVING.
  2. The SELECT clause must contain all the GROUP BY columns.
  3. The aggregate functions cannot include 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 vary, 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 joined tables 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 standard SQL query.
7. The view definition must not contain expressions that generate unstable output values, such as ROWNUM, RAND, and SYSDATE.

Incremental refresh example

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 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 the refresh can be manually triggered as 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 the refresh can be manually triggered as needed; the query part of the materialized view calculates the aggregate results of count(*), count(col3), and sum(col3) based on all rows 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 and specify that the refresh can be manually triggered as needed; the query part of the materialized view calculates the results of count(col3) and sum(col3) based on all rows 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 and specify that the refresh can be manually triggered as 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 as grouping keys.

     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 the refresh can be manually triggered as 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), and some custom columns calcol1 and calcol2, based on the values of the col2 column as grouping keys.

     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 the refresh can be manually triggered as 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 as grouping keys.

     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 across 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 for 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 a 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 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 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 |
   +------+------+----------+----------+
   |    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 the 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);

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

<main id="notice" type='explain'>
  <h4>Note</h4>
  <p><ul><li>The specified degree of parallelism takes effect for full refreshes only. It does not apply to incremental refreshes.</li><li>The following examples of SQL statements are for demonstration purposes only. They show how to specify the degree of parallelism and cannot be executed. </li></ul></p>
</main>

1. Specify the degree of parallelism in the hint.

    **Here is an example:**

    ```sql
    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:

        ```sql
        SET _ENABLE_PARALLEL_DDL = 1;
        ```

        At the session level:

        ```sql
        SET SESSION _ENABLE_PARALLEL_DDL = 1;
        ```

    2. Set the value of the degree of parallelism.

        At the system level:

        ```sql
        SET _FORCE_PARALLEL_DDL_DOP = 8;
        ```

        At the session level:

        ```sql
        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:**

    ```sql
    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 of refreshes, both current and historical, to help you track and monitor refresh performance.

* Diagnostics: detailed statistics can help you analyze the performance of refreshes. 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 use 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](../../../../../500.sql-reference/300.pl-reference/200.pl-mysql/1000.pl-system-package-mysql/15900.dbms-stats-mysql/1700.gather-table-stats-mysql.md).

* For more information about how to collect statistics on materialized view refreshes, see [DBMS_MVIEW_STATS overview](../../../../../500.sql-reference/300.pl-reference/200.pl-mysql/1000.pl-system-package-mysql/10050.dbms-mview-stat-mysql/100.dbms-mview-stat-overview-mysql.md).

### Views displaying materialized view refresh information

| **View name** | **Description** |
|------------|--------------|
| [DBA_MVIEWS](../../../../../700.system-views/400.system-view-of-mysql-mode/200.dictionary-view-of-mysql-mode/5000.o-dba_mviews-of-mysql-mode.md) | Displays information about materialized views. |
| [DBA_MVREF_STATS_SYS_DEFAULTS](../../../../../700.system-views/400.system-view-of-mysql-mode/200.dictionary-view-of-mysql-mode/5500.o-dba_mvref_stats_sys_defaults-of-mysql-mode.md) | System-level default values of statistics attributes for materialized view refreshes. |
| [DBA_MVREF_STATS_PARAMS](../../../../../700.system-views/400.system-view-of-mysql-mode/200.dictionary-view-of-mysql-mode/5400.o-dba_mvref_stats_params-of-mysql-mode.md) | Displays the refresh statistics attributes associated with each materialized view. |
| [DBA_MVREF_RUN_STATS](../../../../../700.system-views/400.system-view-of-mysql-mode/200.dictionary-view-of-mysql-mode/5200.o-dba_mvref_run_stats-of-mysql-mode.md) | Displays information about each refresh of materialized views. Each refresh is identified by the REFRESH_ID attribute. |
| [DBA_MVREF_STATS](../../../../../700.system-views/400.system-view-of-mysql-mode/200.dictionary-view-of-mysql-mode/5300.o-dba_mvref_stats-of-mysql-mode.md) | Displays basic timing statistics about materialized view refreshes. |
| [DBA_MVREF_CHANGE_STATS](../../../../../700.system-views/400.system-view-of-mysql-mode/200.dictionary-view-of-mysql-mode/5100.o-dba_mvref_change_stats-of-mysql-mode.md) | Displays statistics about materialized view refreshes. |
| [DBA_MVREF_STMT_STATS](../../../../../700.system-views/400.system-view-of-mysql-mode/200.dictionary-view-of-mysql-mode/5600.o-dba_mvref_stmt_stats-of-mysql-mode.md) | Displays information about refresh statements. |

## References

* [Overview of materialized views](100.materialized-views-overview-of-mysql-mode.md)
* [Create materialized views](200.create-materialized-views-of-mysql-mode.md)
* [Query materialized views](300.view-materialized-views-of-mysql-mode.md)
* [Drop materialized views](400.delete-materialized-views-of-mysql-mode.md)