Refresh a materialized view

Note

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 aggregate functions, such as SUM and COUNT, that use simple columns are supported. 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 GROUP BY columns.
  3. The aggregate functions cannot contain the DISTINCT keyword, and the parameters must be basic columns.
  4. The SELECT clause must contain COUNT(*). Other aggregate functions and other requirements are described as follows. At present, the MIN and MAX aggregate functions are not supported.

  Aggregate function	SELECT clause must contain the dependent 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 decomposed 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 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 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 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 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 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 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 and specify that the refresh can be manually triggered as needed; the query part of the materialized view selects the col2 and col3 columns from the test_tbl1 table, calculates the aggregate results of count(*), count(col3), and sum(col3) based on the values in the col2 and col3 columns, and groups the data by col2 and col3.

     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 selects the col2 column from the test_tbl1 table, calculates the aggregate results of count(*), count(col3), sum(col3), and avg(col3), and calculates two custom columns calcol1 and calcol2 based on the values in the col2 column, grouping the data by the values 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 and specify that the refresh can be manually triggered as needed; the query part of the materialized view selects the col2 column from the test_tbl1 table, calculates the aggregate results of count(*), count(col3), sum(col3), count(col3*col3), sum(col3*col3), and STDDEV(col3) based on the values in the col2 column, and groups the data by the values 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;
     

Manually refresh a materialized view

If 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 the refresh. Currently, this parameter applies only to complete refreshes and is ignored for 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 provided for reference only. They 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. You can query these statistics through specific views. Current and historical refresh statistics are stored in the database. You can analyze the historical refresh statistics to understand the refresh performance in the database.

The refresh statistics serve the following purposes:

* Reporting: Provides a summary of the current and historical statistics on materialized view refreshes, including the actual time taken by refreshes, to track and monitor refresh performance.

* Diagnosis: Current and historical statistics can help you analyze and optimize 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')` 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/27700.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/27800.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/27900.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/28000.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/28100.dba_mvref_stats-of-mysql-mode.md) | Displays basic timing statistics of materialized view refreshes. |
| [DBA_MVREF_CHANGE_STATS](../../../../../700.system-views/400.system-view-of-mysql-mode/200.dictionary-view-of-mysql-mode/28200.dba_mvref_change_stats-of-mysql-mode.md) | Displays statistics related to materialized view refreshes. |
| [DBA_MVREF_STMT_STATS](../../../../../700.system-views/400.system-view-of-mysql-mode/200.dictionary-view-of-mysql-mode/28300.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)