Create a materialized view

This topic describes how to create a materialized view by using SQL statements.

Privilege requirements

To create a materialized view, you must have the CREATE TABLE privilege. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode.

Syntax

The syntax of the SQL statement for creating a materialized view is as follows:

CREATE MATERIALIZED VIEW view_name [([column_list] [PRIMARY KEY(column_list)])]
    [table_option_list]
    [partition_option]
    [mv_column_group_option]
    [refresh_clause]
    [query_rewrite_clause]
    [on_query_computation_clause]
    AS view_select_stmt;

Parameters:

• view_name: the name of the materialized view to be created.

• column_list: an optional parameter that specifies the column list of the materialized view. If you want to specify the names of the view columns, you can use the column_list clause and separate the column names with commas.

• PRIMARY KEY(column_list): an optional parameter that specifies the primary key of the materialized view.

• table_option_list: an optional parameter that specifies the table options of the materialized view.

• partition_option: an optional parameter that specifies the partitioning options of the materialized view.

• mv_column_group_option: an optional parameter that specifies the storage format of the materialized view. If this option is not specified, the default storage format is rowstore.

• refresh_clause: an optional parameter that specifies the refresh method of the materialized view.

• query_rewrite_clause: an optional parameter that specifies whether to enable automatic query rewriting for the materialized view.

• on_query_computation_clause: an optional parameter that specifies whether the materialized view is a standard materialized view or a real-time materialized view.

• AS view_select_stmt: the query statement used to define the data of the materialized view. This statement is used to retrieve data from the base table and store the results in the materialized view.

  Note

  For OceanBase Database V4.3.5:

  • Starting from V4.3.5 BP2, you can create a full-refresh materialized view based on a standard view or an external table.
  • Starting from V4.3.5 BP4, you can add the AS OF PROCTIME() clause to the base table when you create a materialized view. If you use the AS OF PROCTIME() clause outside the base table of the materialized view, an error is returned. The AS OF PROCTIME() clause specifies to skip the refresh of this table during incremental refreshes. The table specified by the AS OF PROCTIME() clause does not need to have an mlog.
  • Starting from V4.3.5 BP5, a standard view declared as a dimension table (AS OF PROCTIME()) can be used as the base table of an incremental-refresh materialized view.

For more information about the syntax for creating a materialized view, see CREATE MATERIALIZED VIEW.

Create a materialized view

Create a standard materialized view

When you create a materialized view, omit or specify the DISABLE ON QUERY COMPUTATION clause to create a standard materialized view.

Here is an example:

1. Create a table tbl1 as the base table of the materialized view.

   CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);
   

2. Create a materialized view named mv_tbl1 based on the tbl1 table. You can specify the DISABLE ON QUERY COMPUTATION clause or omit it. The following two statements are equivalent.

   CREATE MATERIALIZED VIEW mv_tbl1
       AS SELECT col1, col2
         FROM tbl1
         WHERE col3 >= 20;
   

   or

   CREATE MATERIALIZED VIEW mv_tbl1
       DISABLE ON QUERY COMPUTATION
       AS SELECT col1, col2
         FROM tbl1
         WHERE col3 >= 20;
   

Create a nested materialized view

A nested materialized view is a materialized view built on an existing materialized view. For example, in the following figure, materialized view mv1 is built on tables tbl1 and tbl2, and is a typical materialized view. Materialized view mv2 is built on materialized view mv1 and table tbl3, and is a nested materialized view. Similarly, materialized view mv3 is built on materialized views mv1 and mv2, and is also a nested materialized view.

In OceanBase Database V4.3.5, starting from V4.3.5 BP3, you can specify the refresh strategy for a nested materialized view. Valid values:

• INDIVIDUAL: The default value. This option indicates that the materialized view is refreshed independently.
• INCONSISTENT: This option indicates that the materialized view is refreshed in an inconsistent manner.
• CONSISTENT: This option indicates that the materialized view is refreshed in a consistent manner.

Limitations on nested materialized views

• To support incremental refreshes of nested materialized views, you must create an mlog on the materialized view (base table).
• If a materialized view is fully refreshed, the dependent materialized view (nested materialized view) must be fully refreshed before it can be incrementally refreshed. Otherwise, an error will be returned.
• If a materialized view (nested materialized view) is a real-time materialized view, you must incrementally refresh the underlying materialized view to update the mlog. This is because the query results of a real-time materialized view are obtained by simulating the mlog to merge the results of the underlying materialized view and itself. Therefore, you must incrementally refresh the underlying materialized view to update the mlog to ensure that the data of the real-time materialized view is up to date.

Here is an example:

1. Create table tbl3 as the base table of a materialized view.

   CREATE TABLE tbl3(id INT, name VARCHAR(30), PRIMARY KEY(id));
   

2. Create table tbl4 as the base table of a materialized view.

   CREATE TABLE tbl4(id INT, age INT, PRIMARY KEY(id));
   

3. Create a materialized view mv1_tbl3_tbl4 based on tables tbl3 and tbl4.

   CREATE MATERIALIZED VIEW mv1_tbl3_tbl4 (PRIMARY KEY (id1, id2))
       REFRESH COMPLETE
       AS SELECT tbl3.id id1, tbl4.id id2, tbl3.name, tbl4.age
           FROM tbl3, tbl4
           WHERE tbl3.id = tbl4.id;
   

4. Create a materialized view (nested materialized view) mv_mv1_tbl3_tbl4 based on materialized view mv1_tbl3_tbl4.

   CREATE MATERIALIZED VIEW mv_mv1_tbl3_tbl4
       REFRESH COMPLETE
       AS SELECT SUM(AGE) age_sum
           FROM mv1_tbl3_tbl4;
   

5. Create a materialized view (nested materialized view) mv1_mv1_tbl3_tbl4 based on materialized view mv1_tbl3_tbl4. The refresh strategy is INCONSISTENT.

   CREATE MATERIALIZED VIEW mv1_mv1_tbl3_tbl4
       REFRESH COMPLETE INCONSISTENT
       AS SELECT SUM(AGE) age_sum
           FROM mv1_tbl3_tbl4;
   

Create a real-time materialized view

When you create a materialized view, specify the ENABLE ON QUERY COMPUTATION clause to create a real-time materialized view.

Considerations

• Before you create a real-time materialized view, you must create a materialized view log for each base table on which the materialized view depends.

  Note

  For OceanBase Database V4.3.5, the automatic management of materialized view logs is supported starting from V4.3.5 BP4. If you enable automatic management of materialized view logs, you do not need to create materialized view logs for base tables before you create a real-time materialized view. OceanBase Database automatically creates materialized view logs or updates the definitions of existing materialized view logs to include the columns required by the new real-time materialized view. For more information, see Automatic management of materialized view logs.

• Only specific types of materialized views support being specified as real-time materialized views. If you specify a materialized view that does not meet the requirements as a real-time materialized view, an error will occur. The requirements for a real-time materialized view are the same as those for an incrementally refreshed materialized view. For more information, see the Basic requirements for incremental refresh section in Refresh a materialized view.

• If a materialized view (nested materialized view) is a real-time materialized view, you must incrementally refresh the lower-level materialized view to update the materialized view log (mlog). This is because the query results of a real-time materialized view are obtained by simulating the mlog and merging the query results of the lower-level materialized view and itself. Therefore, you must incrementally refresh the lower-level materialized view to update the mlog, ensuring that the data of the real-time materialized view is up to date.

Here is an example:

1. Create a base table tbl2.

   CREATE TABLE tbl2(col1 INT, col2 INT, col3 INT);
   

2. Create a materialized view log on tbl2.

   CREATE MATERIALIZED VIEW LOG ON tbl2
       WITH PRIMARY KEY, ROWID, SEQUENCE (col1, col2, col3) INCLUDING NEW VALUES;
   

3. Create a real-time materialized view mv_tbl2 based on tbl2.

   CREATE MATERIALIZED VIEW mv_tbl2
       ENABLE ON QUERY COMPUTATION
       AS SELECT col1, count(*) AS cnt
          FROM tbl2
          GROUP BY col1;
   

4. After the real-time materialized view is created, you can query the DBA_MVIEWS view to check whether the materialized view is a real-time materialized view.

   SELECT MVIEW_NAME, ON_QUERY_COMPUTATION
   FROM oceanbase.DBA_MVIEWS
   WHERE MVIEW_NAME = 'mv_tbl2';
   

   The return result is as follows:

   +------------+----------------------+
   | MVIEW_NAME | ON_QUERY_COMPUTATION |
   +------------+----------------------+
   | mv_tbl2    | Y                    |
   +------------+----------------------+
   1 row in set
   

5. View the execution plan of the real-time materialized view.

   EXPLAIN BASIC SELECT * FROM mv_tbl2;
   

   From the following execution plan, you can see that data is simultaneously read from the materialized view and the mlog of the base table on which the materialized view depends. The two parts of data are then calculated and integrated to obtain the real-time data of the materialized view.

   The return result is as follows:

   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                                                                       |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | ==============================================                                                                                                                                   |
   | |ID|OPERATOR                   |NAME         |                                                                                                                                   |
   | ----------------------------------------------                                                                                                                                   |
   | |0 |HASH GROUP BY              |             |                                                                                                                                   |
   | |1 |└─SUBPLAN SCAN             |INNER_RT_MV$$|                                                                                                                                   |
   | |2 |  └─UNION ALL              |             |                                                                                                                                   |
   | |3 |    ├─TABLE FULL SCAN      |mv_tbl2      |                                                                                                                                   |
   | |4 |    └─HASH GROUP BY        |             |                                                                                                                                   |
   | |5 |      └─SUBPLAN SCAN       |DLT_T$$      |                                                                                                                                   |
   | |6 |        └─WINDOW FUNCTION  |             |                                                                                                                                   |
   | |7 |          └─TABLE FULL SCAN|mlog$_tbl2   |                                                                                                                                   |
   | ==============================================                                                                                                                                   |
   | Outputs & filters:                                                                                                                                                               |
   | -------------------------------------                                                                                                                                            |
   |   0 - output([INNER_RT_MV$$.col1], [cast(T_FUN_SUM(INNER_RT_MV$$.cnt), BIGINT(20, 0))]), filter([T_FUN_SUM(INNER_RT_MV$$.cnt) > cast(0, DECIMAL_INT(64,                          |
   |       0))]), rowset=16                                                                                                                                                           |
   |       group([INNER_RT_MV$$.col1]), agg_func([T_FUN_SUM(INNER_RT_MV$$.cnt)])                                                                                                      |
   |   1 - output([INNER_RT_MV$$.col1], [INNER_RT_MV$$.cnt]), filter(nil), rowset=16                                                                                                  |
   |       access([INNER_RT_MV$$.col1], [INNER_RT_MV$$.cnt])                                                                                                                          |
   |   2 - output([UNION([1])], [UNION([2])]), filter(nil), rowset=16                                                                                                                 |
   |   3 - output([mv_tbl2.col1], [cast(mv_tbl2.cnt, DECIMAL_INT(42, 0))]), filter(nil), rowset=16                                                                                    |
   |       access([mv_tbl2.col1], [mv_tbl2.cnt]), partitions(p0)                                                                                                                      |
   |       is_index_back=false, is_global_index=false,                                                                                                                                |
   |       range_key([mv_tbl2.__pk_increment]), range(MIN ; MAX)always true                                                                                                           |
   |   4 - output([DLT_T$$.col1], [T_FUN_SUM(CASE WHEN DLT_T$$.OLD_NEW$$ = 'N' THEN 1 ELSE -1 END)]), filter(nil), rowset=16                                                          |
   |       group([DLT_T$$.col1]), agg_func([T_FUN_SUM(CASE WHEN DLT_T$$.OLD_NEW$$ = 'N' THEN 1 ELSE -1 END)])                                                                         |
   |   5 - output([DLT_T$$.OLD_NEW$$], [DLT_T$$.col1]), filter([DLT_T$$.OLD_NEW$$ = 'N' AND DLT_T$$.SEQUENCE$$ = DLT_T$$.MAXSEQ$$ OR DLT_T$$.OLD_NEW$$ = 'O'                          |
   |       AND DLT_T$$.SEQUENCE$$ = DLT_T$$.MINSEQ$$]), rowset=16                                                                                                                     |
   |       access([DLT_T$$.OLD_NEW$$], [DLT_T$$.SEQUENCE$$], [DLT_T$$.MAXSEQ$$], [DLT_T$$.MINSEQ$$], [DLT_T$$.col1])                                                                  |
   |   6 - output([mlog$_tbl2.OLD_NEW$$], [mlog$_tbl2.SEQUENCE$$], [T_FUN_MAX(mlog$_tbl2.SEQUENCE$$)], [T_FUN_MIN(mlog$_tbl2.SEQUENCE$$)], [mlog$_tbl2.col1]), filter(nil), rowset=16 |
   |       win_expr(T_FUN_MAX(mlog$_tbl2.SEQUENCE$$)), partition_by([mlog$_tbl2.M_ROW$$]), order_by(nil), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED             |
   |       FOLLOWING)                                                                                                                                                                 |
   |       win_expr(T_FUN_MIN(mlog$_tbl2.SEQUENCE$$)), partition_by([mlog$_tbl2.M_ROW$$]), order_by(nil), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED             |
   |       FOLLOWING)                                                                                                                                                                 |
   |   7 - output([mlog$_tbl2.M_ROW$$], [mlog$_tbl2.SEQUENCE$$], [mlog$_tbl2.OLD_NEW$$], [mlog$_tbl2.col1], [ORA_ROWSCN]), filter([ORA_ROWSCN > last_refresh_scn(500452)]), rowset=16 |
   |       access([mlog$_tbl2.M_ROW$$], [mlog$_tbl2.SEQUENCE$$], [mlog$_tbl2.OLD_NEW$$], [mlog$_tbl2.col1], [ORA_ROWSCN]), partitions(p0)                                             |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                                                |
   |       range_key([mlog$_tbl2.M_ROW$$], [mlog$_tbl2.SEQUENCE$$]), range(MIN,MIN ; MAX,MAX)always true                                                                              |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   38 rows in set
   

Create a materialized view that enables query rewriting

When you create a materialized view, you can specify the ENABLE QUERY REWRITE clause to enable automatic query rewriting for the materialized view. For more information about query rewriting and rewriting control for materialized views, see Query rewriting for materialized views.

Here is an example:

1. Create a materialized view named mv_spj_tbl1 based on the tbl1 table and enable automatic query rewriting for the materialized view.

   CREATE MATERIALIZED VIEW mv_spj_tbl1
       ENABLE QUERY REWRITE
       AS SELECT *
          FROM tbl1;
   

2. After the materialized view is created, you can query the DBA_MVIEWS view to check whether automatic query rewriting is enabled for the materialized view.

   SELECT MVIEW_NAME, REWRITE_ENABLED
   FROM oceanbase.DBA_MVIEWS
   WHERE MVIEW_NAME = 'mv_spj_tbl1';
   

   The returned result is as follows:

   +-------------+-----------------+
   | MVIEW_NAME  | REWRITE_ENABLED |
   +-------------+-----------------+
   | mv_spj_tbl1 | Y               |
   +-------------+-----------------+
   1 row in set
   

Create a columnstore materialized view

OceanBase Database supports materialized views in rowstore, columnstore, and rowstore-columnstore redundant formats. You can specify the mv_column_group_option option to create a columnstore or rowstore-columnstore redundant materialized view. If a materialized view is a wide table formed by joining multiple tables, creating a columnstore materialized view can improve the performance of some queries. You can specify the WITH COLUMN GROUP(each column) option to create a columnstore materialized view.

Here is an example:

Create a columnstore materialized view mv_ec_tbl1 based on the tbl1 table.

CREATE MATERIALIZED VIEW mv_ec_tbl1
    WITH COLUMN GROUP(each column)
    AS SELECT *
       FROM tbl1;

Add a primary key when you create a materialized view

Here is an example:

Create a materialized view named mv_pk_tbl1 based on the tbl1 table and specify a primary key for the materialized view.

CREATE MATERIALIZED VIEW mv_pk_tbl1(v_id, v_name, PRIMARY KEY(v_id))
    AS SELECT col1, col2
       FROM tbl1
       WHERE col3 >= 20;

Add table options and partitioning options when you create a materialized view

When you create a materialized view, you can specify table options and partitioning options based on data characteristics and access patterns to improve query performance and management efficiency.

For more information about the table options and partitioning options, see CREATE TABLE.

Here is an example:

Create a materialized view named mv_pp_tbl1 based on the tbl1 table. Set the parallelism of the materialized view to 5, partition the materialized view by using the col1 column as the hash partitioning column, and divide the materialized view into 8 partitions. Query the records in the tbl1 table that meet the condition col3 >= 20 as the base table, and use the query results as the data of the materialized view.

CREATE MATERIALIZED VIEW mv_pp_tbl1
    PARALLEL 5
    PARTITION BY HASH(col1) PARTITIONS 8
    AS SELECT col1, col2
       FROM tbl1
       WHERE col3 >= 20;

Add an index to a materialized view

You cannot directly create an index in the statement for creating a materialized view. Instead, you can use the CREATE INDEX statement or the ALTER TABLE statement to create an index for a materialized view.

Here are some examples:

• Create an index named idx1_mv_tbl1 on the col1 column of the materialized view mv_tbl1.

  CREATE INDEX idx1_mv_tbl1 ON mv_tbl1(col1);
  

• Create an index named idx2_mv_tbl1 on the col2 column of the materialized view mv_tbl1.

  ALTER TABLE mv_tbl1 ADD INDEX idx2_mv_tbl1(col2);
  

Refresh a materialized view

OceanBase Database supports the following refresh strategies for materialized views: full refresh, incremental refresh, hybrid refresh, and never refresh.

• Full refresh: Recalculate all data in the materialized view to ensure that the data in the view is consistent with that in the source table.
• Incremental refresh: Refresh only the data that is related to changes in the source table to avoid recalculating the entire view.
• Hybrid refresh: The default option. First, an incremental refresh is attempted. If the incremental refresh fails, a full refresh is performed.
• Never refresh: A materialized view is refreshed only when it is created, and no further refreshes are allowed after the creation.

For more information about how to refresh a materialized view, see Refresh a materialized view.

Create a materialized view with a complete refresh strategy

When you create a materialized view, you can use the REFRESH COMPLETE clause to set the refresh strategy to complete refresh.

Here is an example:

Create a materialized view named mv_rc_tbl1 based on the tbl1 table. Set the refresh strategy to complete refresh (REFRESH COMPLETE). Specify that the data source of the materialized view consists of the col1 and col2 columns from the tbl1 table where col3 is greater than or equal to 20.

CREATE MATERIALIZED VIEW mv_rc_tbl1
    REFRESH COMPLETE
    AS SELECT col1, col2
       FROM tbl1
       WHERE col3 >= 20;

Create a materialized view with a complete refresh strategy based on an external table

OceanBase Database supports creating a materialized view with a complete refresh strategy based on an external table starting from V4.3.5 BP2.

For more information about external tables, see About external tables.

Here is an example:

The following example creates an external table in two scenarios: when the external file is stored locally and when it is stored in the MySQL mode of OceanBase Database. The steps are as follows:

1. Prepare the external file.

   Execute the following command to create a file named ext_tbl1.csv in the /home/admin directory on the server where the OBServer node is located.

   [admin@xxx /home/admin]# vi ext_tbl1.csv
   

   The content of the file is as follows:

   1,'A1','2025-01-01'
   2,'A2','2025-02-01'
   3,'A3','2025-03-01'
   

2. Set the import file path.

   Notice

   For security reasons, you can only set the secure_file_priv system variable by connecting to the database through a local Unix socket. For more information, see secure_file_priv.

   1. Execute the following command to log in to the server where the OBServer node is located.

      ssh admin@10.10.10.1
      

   2. Execute the following command to connect to the mysql001 tenant through a local Unix socket.

      obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
      

   3. Execute the following SQL statement to set the import path to /home/admin.

      SET GLOBAL secure_file_priv = "/home/admin";
      

3. Reconnect to the mysql001 tenant.

   Here is an example:

   obclient -h10.10.10.1 -P2881 -uroot@mysql001 -p****** -A -Ddb_test
   

4. Create an external table named ext_tbl1.

   CREATE EXTERNAL TABLE ext_tbl1 ( 
       id INT, 
       name VARCHAR(50), 
       c_date    DATE
       )
       LOCATION = '/home/admin'
       FORMAT = (
         TYPE = 'CSV'
         FIELD_DELIMITER = ','
         FIELD_OPTIONALLY_ENCLOSED_BY ='\''
         )
       PATTERN = 'ext_tbl1.csv';
   

5. Create a materialized view named mv_ext_tbl1 with a complete refresh strategy based on the ext_tbl1 external table.

   CREATE MATERIALIZED VIEW mv_ext_tbl1
       REFRESH COMPLETE
       AS SELECT *
           FROM ext_tbl1;
   

6. Query the data in the mv_ext_tbl1 materialized view.

   SELECT * FROM mv_ext_tbl1;
   

   The execution result is as follows:

   +------+------+------------+
   | id   | name | c_date     |
   +------+------+------------+
   |    1 | A1   | 2025-01-01 |
   |    3 | A3   | 2025-03-01 |
   |    2 | A2   | 2025-02-01 |
   +------+------+------------+
   3 rows in set
   

Create a materialized view with incremental refresh

When you create a materialized view, you can use the REFRESH FAST clause to specify the incremental refresh strategy.

Considerations

• Incremental refresh is supported for the following types of SQL statements that define materialized views: non-aggregated single-table queries, aggregated single-table queries, multi-table joins, multi-table joined aggregations, and collection queries (UNION ALL). If an SQL statement does not meet these conditions, incremental refresh is not supported. For more information about the incremental refresh requirements, see the Incremental refresh section in Refresh a materialized view.

  Note

  For OceanBase Database V4.3.5, incremental refresh is supported for single-table non-aggregated queries and collection queries only in V4.3.5 BP3 and later.

• You must create a materialized view log for a base table before you create a materialized view with incremental refresh. This is because the REFRESH FAST method uses the records in the materialized view log to determine the contents to be incrementally refreshed. For more information, see Materialized view log.

  Note

  For OceanBase Database V4.3.5, automatic management of materialized view logs is supported starting with V4.3.5 BP4. If automatic mlog management is enabled, users do not need to manually create mlogs for base tables before creating incrementally refreshed materialized views—OceanBase will automatically create the necessary mlogs or update existing mlog table definitions to include the columns required by the new materialized view. For more details, see Automatic management of materialized view logs.

• You can specify the AS OF PROCTIME() clause for a base table when you create a materialized view. If you specify the AS OF PROCTIME() clause outside the base table declaration, an error is returned. For more information, see the Usage notes section of the Materialized view refresh topic.

  • You can use the AS OF PROCTIME() clause to specify the base table to be skipped during incremental refresh. This accelerates the incremental refresh of the materialized view. Note that you do not need to create a materialized view log for a table specified by the AS OF PROCTIME() clause. If you want to use an alias for such a table, specify the alias after the AS OF PROCTIME() clause.

    Note

    For OceanBase Database V4.3.5, OceanBase Database V4.3.5 BP4 supports specifying the AS OF PROCTIME() clause for a base table when you create a materialized view.

  • When you use a standard view declared as a dimension table (AS OF PROCTIME()) as a base table for an incremental refresh materialized view, the following limitations apply:

    • A materialized view cannot use all dimension tables as base tables.

    Note

    For OceanBase Database V4.3.5, standard views declared as dimension tables (using AS OF PROCTIME()) can be used as base tables for incrementally refreshed materialized views starting with V4.3.5 BP5.

Example:

1. Create a base table tbl5 for the materialized view. Table tbl5 includes the col1 column as the primary key, and the col2 and col3 columns.

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

2. Create a materialized view log for tbl5. The SEQUENCE option specifies the use of a sequence number to identify changed data. The columns in the WITH SEQUENCE list specify the columns to record, including col2 and col3.

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

3. Create a materialized view named mv_tbl5 for tbl5. The REFRESH FAST clause specifies the incremental refresh strategy. In the SELECT statement, the result to be returned by the materialized view is defined. The statement groups the results of tbl5 by the values of the col2 column, and counts the number of records, records of non-null values of the col3 column, and the sum of values of the col3 column in each group.

   CREATE MATERIALIZED VIEW mv_tbl5
       REFRESH FAST
       AS SELECT col2, COUNT(*) cnt, COUNT(col3) cnt_col3, SUM(col3) sum_col3 
          FROM tbl5 
          GROUP BY col2;
   

4. Create a materialized view named mv2_tbl5_tbl1 for tbl5 and tbl1. The REFRESH FAST ON DEMAND clause specifies the incremental refresh strategy. The INNER JOIN clause specifies the field col1 as the connecting field. The AS OF PROCTIME() clause specifies tbl1 to be skipped during incremental refresh.

   CREATE MATERIALIZED VIEW mv2_tbl5_tbl1
       REFRESH FAST ON DEMAND
       AS SELECT 
           t5.col1 tbl5_c1,
           t1.col1 tbl1_c1,
           t5.col2 tbl5_c2,
           t1.col2 tbl1_c2
       FROM tbl5 t5
           INNER JOIN tbl1 AS OF PROCTIME() t1
           ON t5.col1 = t1.col1
       WHERE t5.col2 = 3;
   

5. Create an incremental refresh materialized view by referencing a standard view declared as AS OF PROCTIME().

   1. Create a standard view v1_tbl5 for tbl5.

      obclient> CREATE VIEW v1_tbl5 AS SELECT * FROM tbl5;
      

   2. Create a materialized view named mv3_tbl5_v_tbl5 for tbl5 and v1_tbl5. The REFRESH FAST clause specifies the incremental refresh strategy. The JOIN clause specifies the field col1 as the connecting field. The AS OF PROCTIME() clause specifies v1_tbl5 as a dimension table.

      obclient> CREATE MATERIALIZED VIEW mv3_tbl5_v_tbl5
          AS SELECT
              a.col1 a_c1,
              b.col1 b_c1
          FROM tbl5 a JOIN v1_tbl5 AS OF PROCTIME() b
          ON a.col1 = b.col1;
      

Create a materialized view with a mixed refresh strategy (default option)

When you create a materialized view, omitting or specifying the REFRESH FORCE clause sets the refresh strategy to mixed refresh.

Here is an example:

Create a materialized view named mv_rf_tbl1 based on table tbl1, set the refresh strategy to mixed refresh, and specify that the data source of the materialized view consists of the col1 and col2 columns in tbl1 where col3 is greater than or equal to 20.

CREATE MATERIALIZED VIEW mv_rf_tbl1
    REFRESH FORCE
    AS SELECT col1, col2
       FROM tbl1
       WHERE col3 >= 20;

Create a materialized view that never refreshes

When you create a materialized view, use the NEVER REFRESH clause to specify that the materialized view does not need to be refreshed. This means that the materialized view is only refreshed when it is created and cannot be refreshed again after it is created.

Here is an example:

Create a materialized view named mv_nr_tbl1 based on table tbl1, set the refresh strategy to never refresh, and specify that the data source of the materialized view consists of the col1 and col2 columns in tbl1 where col3 is greater than or equal to 20.

CREATE MATERIALIZED VIEW mv_nr_tbl1
    NEVER REFRESH
    AS SELECT col1, col2
       FROM tbl1
       WHERE col3 >= 20;

Create a materialized view with automatic refresh

When you create a materialized view, you can specify the START WITH datetime_expr and NEXT datetime_expr clauses to create a background automatic refresh task for the materialized view.

Here is an example:

Create a materialized view named mv_rc_swn_tbl1 based on the tbl1 table. Specify the refresh strategy of the materialized view as a complete refresh. Specify the initial refresh time of the materialized view as the current date, and specify that the materialized view is refreshed every 1 day after the initial refresh.

CREATE MATERIALIZED VIEW mv_rc_swn_tbl1
    REFRESH COMPLETE
        START WITH sysdate() NEXT sysdate() + interval 1 day
    AS SELECT col1, col2
       FROM tbl1
       WHERE col3 >= 20;

References

• DBMS_MVIEW overview
• Overview of materialized views
• Materialized view logs
• Refresh materialized views
• Query rewriting for materialized views
• Query materialized views
• Modify materialized views
• Drop materialized views