Refresh a materialized view|V4.3.3|OceanBase Database|docs|OceanBase Documentations

Updating data in the base table may 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. Refreshing a materialized view also updates all its indexes automatically.

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.

Complete refresh

Refresh conditions

A complete refresh is performed if the data types of all columns in the source table match those in the materialized view. If they do not match, the complete refresh cannot be performed.

Refresh method

OceanBase Database performs a complete refresh through remote refresh. Specifically, it creates a hidden table, executes the refresh statement on the hidden table, and then switches the source table with the hidden table. Therefore, a complete refresh requires additional storage space and will rebuild all indexes (if any) from scratch.

Note

Incremental refreshes

Notice

The REFRESH FAST method determines the data to be incrementally refreshed based on the records in mlogs. Therefore, to perform an incremental refresh on a materialized view, you must create mlogs for the base tables before you create the materialized view.
All columns used in incremental refreshes of materialized views must be included in the mlogs.

For more information about how to create mlogs, see Create mlogs.

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

Incremental updates are not supported for aggregate functions MAX and MIN.
Incremental updates are not supported in complex scenarios involving inline views, UNION, and subqueries.
Incremental updates are not supported for expressions that generate unstable output values, such as ROWNUM and RAND.
Incremental updates are not supported for ROLLUP and HAVING.
Incremental updates are not supported for ORDER BY.
Incremental updates are not supported for window functions.
If the query contains the DISTINCT keyword, the output column of the 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, are supported, and only simple columns can be used in aggregate functions. The requirements for the GROUP BY clause are as follows:

The GROUP BY clause must be in the standard GROUP BY syntax and cannot contain ROLLUP or HAVING.
The SELECT clause must contain all the GROUP BY columns.
The aggregate functions cannot contain the DISTINCT keyword, and the parameters of the aggregate functions must be basic columns.
The SELECT clause must contain COUNT(*). Other aggregate functions are supported, and the following aggregate functions are currently not supported: MIN and MAX. A breakdown of the supported aggregate functions is as follows:

Aggregate function	SELECT clause must contain the following 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 )

Single-table aggregate incremental refreshes

Create a table named test_tbl1.

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

Create an mlog on the test_tbl1 table.

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

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 is grouped 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 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 is grouped 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;
```

Prerequisites for incremental refreshes of multi-table joins

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

Perform incremental refreshes of a materialized view based on multiple tables

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);

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;

Create a materialized view mv1_t1_t2 that performs incremental refreshes based on the join 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;

Note

To ensure the performance of simple join materialized views and real-time materialized views, we recommend that you create indexes on the following basis for the materialized view and its dependent base tables:
1. Create an index on the join key of each table to enhance the performance of joins in incremental updates and real-time materialized views.
2. Create an index on the primary key column of each base table in the materialized view.
The incremental refresh performance of a materialized view and the query performance of real-time materialized views usually deteriorate as the number of JOIN tables in the materialized view increases.

Here is an example of creating indexes on the materialized view and its dependent base tables:

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

If the following database objects do not exist, skip this step.
```
DROP MATERIALIZED VIEW LOG ON t1;
DROP TABLE t1;
DROP MATERIALIZED VIEW LOG ON t2;
DROP TABLE t2;
DROP MATERIALIZED VIEW rt_mv1;
```

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

CREATE TABLE t1(c1 INT GENERATED BY DEFAULT AS IDENTITY, c2 INT, c3 INT, c4 INT, c5 INT, PRIMARY KEY(c1));
CREATE INDEX idx_t1_c2 ON t1(c2);

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

CREATE TABLE t2(c1 INT GENERATED BY DEFAULT AS IDENTITY, c2 INT, c3 INT, c4 INT, c5 INT, PRIMARY KEY(c1));
CREATE INDEX idx_t2_c3 ON t2(c3);

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;

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;

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 a single table](#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

Create base tables 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));

Create mlogs for tables t3 and t4.

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;

Create a real-time materialized view mv1_t3_t4 that aggregates incremental data of tables t3 and t4.

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

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

Note

Only the owner of the materialized view and the tenant administrator have the privilege to refresh the materialized view.

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

Here is an example:

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);

Query the mv1_test_tbl1 materialized view.
```
SELECT * FROM mv1_test_tbl1;
```
The return result is as follows:
```
Empty set
```
Manually refresh the mv1_test_tbl1 materialized view by using the DBMS_MVIEW.REFRESH procedure.
```
CALL DBMS_MVIEW.REFRESH('mv1_test_tbl1');
```

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

Refresh the parallelism

You can use the refresh_parallel parameter to specify 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 with a specified 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 created for the materialized view in the background.

### Degree of parallelism

When an automatic refresh task is created for a materialized view, you can specify the degree of parallelism in the following three ways during the refresh:

<main id="notice" type='explain'>
  <h4>Note</h4>
  <p><ul><li>The specified degree of parallelism takes effect only for complete refreshes, not incremental refreshes.</li><li>The following examples of SQL statements are provided for reference only. They cannot be executed. </li></ul></p>
</main>

1. Use a hint to specify the degree of parallelism.

    **Here is an example:**

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

2. Use the session variable to specify the degree of parallelism during the refresh.

    **Here is an example:**

    1. Enable parallel DDL.

        ```sql
        ALTER SESSION ENABLE PARALLEL DDL;
        ```

    2. Set the value of the parallel DDL session variable.

        ```sql
        ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
        ```

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

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

The materialized view 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.

* Diagnostics: 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 identify 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/300.pl-oracle/1400.pl-system-package-oracle/15900.dbms-stats-oracle/1700.gather-table-stats-oracle.md).

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

### Views displaying materialized view refresh information

| **View name** | **Description** |
|------------|--------------|
| [ALL_MVIEWS](../../../../../700.system-views/500.system-view-of-oracle-mode/200.dictionary-view-of-oracle-mode/2200.all_mviews-of-oracle-mode.md) | Displays information about materialized views. |
| [DBA_MVREF_STATS_SYS_DEFAULTS](../../../../../700.system-views/500.system-view-of-oracle-mode/200.dictionary-view-of-oracle-mode/9900.dba_mvref_stats_sys_defaults-of-oracle-mode.md) | System-level default values of statistics attributes for materialized view refresh history. |
| [DBA_MVREF_STATS_PARAMS](../../../../../700.system-views/500.system-view-of-oracle-mode/200.dictionary-view-of-oracle-mode/9800.dba_mvref_stats_params-of-oracle-mode.md) | Displays the refresh statistics attributes associated with each materialized view. |
| [DBA_MVREF_RUN_STATS](../../../../../700.system-views/500.system-view-of-oracle-mode/200.dictionary-view-of-oracle-mode/9600.dba_mvref_run_stats-of-oracle-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/500.system-view-of-oracle-mode/200.dictionary-view-of-oracle-mode/9700.dba_mvref_stats-of-oracle-mode.md) | Displays basic timing statistics about materialized view refreshes. |
| [DBA_MVREF_CHANGE_STATS](../../../../../700.system-views/500.system-view-of-oracle-mode/200.dictionary-view-of-oracle-mode/9500.dba_mvref_change_stats-of-oracle-mode.md) | Displays statistics about materialized view refreshes. |
| [DBA_MVREF_STMT_STATS](../../../../../700.system-views/500.system-view-of-oracle-mode/200.dictionary-view-of-oracle-mode/10000.dba_mvref_stmt_stats-of-oracle-mode.md) | Displays information about refresh statements. |

## References

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