Create materialized views

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

Privilege requirements

You must have the CREATE TABLE privilege to create a materialized view. For more information about the privileges of OceanBase Database, see Privilege types in Oracle-compatible mode.

Syntax

The syntax 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: optional. The list of columns 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): optional. The primary key of the materialized view.

• table_option_list: optional. The table options of the materialized view.

• partition_option: optional. The partition options of the materialized view.

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

• refresh_clause [query_rewrite_clause] [on_query_computation_clause]: optional. The details are as follows:

  • refresh_clause: the refresh method of the materialized view.
  • query_rewrite_clause: optional. Specifies whether to enable automatic query rewriting for the materialized view.
  • on_query_computation_clause: optional. Specifies whether the materialized view is a standard or real-time materialized view.

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

  Note

  • OceanBase Database supports creating full-refresh materialized views with external tables as base tables.
  • OceanBase Database supports adding the AS OF PROCTIME() clause to the base table when creating a materialized view. If you use the AS OF PROCTIME() clause outside the base table of the materialized view, an error will be returned. The AS OF PROCTIME() clause is used to specify that the table should be skipped during incremental refreshes. Additionally, tables with the AS OF PROCTIME() clause do not require an mlog to be created.
  • OceanBase Database supports using standard views declared as dimension tables (AS OF PROCTIME()) as base tables for incremental-refresh materialized views.

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, you can omit or specify the DISABLE ON QUERY COMPUTATION clause to create a standard materialized view.

Here is an example:

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

   CREATE TABLE tbl1 (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(20), col3 NUMBER);
   

2. Create a materialized view named mv_tbl1 based on the tbl1 table.

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

   or

   CREATE MATERIALIZED VIEW mv_tbl1
       REFRESH FORCE
       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, the materialized view mv1 is built based on the tbl1 and tbl2 tables, which is a typical materialized view. The materialized view mv2 is built based on the mv1 materialized view and the tbl3 table, making it a nested materialized view. Similarly, the materialized view mv3 is built based on the mv1 and mv2 materialized views, making it a nested materialized view as well.

When you create a nested materialized view in OceanBase Database, you can specify the refresh strategy. Valid values are as follows:

• INDIVIDUAL: The default value. This indicates independent refresh.
• INCONSISTENT: This indicates cascading inconsistent refresh.
• CONSISTENT: This indicates cascading consistent refresh.

Limitations on nested materialized views

• To support incremental refresh for nested materialized views, you must create an mlog on the materialized view (base table).
• If a materialized view is fully refreshed, any dependent materialized view (nested materialized view) must be fully refreshed before it can be incrementally refreshed. Otherwise, an error will be reported.
• You cannot create a nested materialized view as a real-time materialized view. In other words, you cannot specify the ENABLE ON QUERY COMPUTATION clause when creating a nested materialized view.

Here is an example:

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

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

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

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

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

   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 nested materialized view named mv_mv1_tbl3_tbl4 based on the mv1_tbl3_tbl4 materialized view.

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

5. Create a nested materialized view named mv1_mv1_tbl3_tbl4 based on the mv1_tbl3_tbl4 materialized view, and set the refresh strategy to 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, you can specify the ENABLE ON QUERY COMPUTATION clause to create a real-time materialized view.

Considerations for creating a real-time materialized view

• 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

  OceanBase Database supports automatic management of materialized view logs. If automatic management of materialized view logs is enabled, you do not need to create a materialized view log for the base table before you create a real-time materialized view. OceanBase Database automatically creates a materialized view log for the base table or updates the definition of the existing materialized view log to include the columns required by the new real-time materialized view. For more information, see Automatic management of materialized view logs.

• Only materialized views of specific types can be 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 be returned. The requirements for real-time materialized views are the same as those for materialized views that support incremental refreshes. For more information, see the Basic requirements for incremental refreshes section in Refresh a materialized view.

  • Materialized views that use the MIN or MAX function cannot be specified as real-time materialized views.
  • Aggregated materialized views that use outer joins cannot be specified as real-time materialized views.
  • Materialized views that use set operations cannot be specified as real-time materialized views.
  • Nested materialized views cannot be specified as real-time materialized views.

• If the values of system variables in the session that executes the query do not match the values of session variables that are fixed in the materialized view, you must modify the values of system variables in the session to match the values of session variables that are fixed in the real-time materialized view. Otherwise, the real-time materialized view will not be available, meaning that query rewriting will not take effect or an error will be returned when you directly query the real-time materialized view.

Here is an example:

1. Create a base table tbl2 for the materialized view.

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

2. Create a materialized view log on the tbl2 table.

   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 the tbl2 table.

   CREATE MATERIALIZED VIEW mv_tbl2
       REFRESH COMPLETE ON DEMAND
       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 sys.DBA_MVIEWS
   WHERE MVIEW_NAME = 'MV_TBL2';
   

   Notice

   In Oracle-compatible mode, if the MVIEW_NAME field in the sys.DBA_MVIEWS view matches the table name, the table name must be in uppercase letters.

   The returned 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 materialized view log of the base table on which the view depends. The data is then calculated and integrated to obtain the real-time materialized view data.

   The returned 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), NUMBER(38, 0))]), filter([T_FUN_SUM(INNER_RT_MV$$.CNT) > cast(0, NUMBER(-1, -85))]), 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], [MV_TBL2.CNT]), 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$$ = cast('N', VARCHAR2(1048576 )) THEN cast(1, NUMBER(-1, -85)) ELSE (T_OP_NEG, cast(1,                        |
   |        NUMBER(-1, -85))) END)]), filter(nil), rowset=16                                                                                                                          |
   |       group([DLT_T$$.COL1]), agg_func([T_FUN_SUM(CASE WHEN DLT_T$$.OLD_NEW$$ = cast('N', VARCHAR2(1048576 )) THEN cast(1, NUMBER(-1, -85)) ELSE (T_OP_NEG,                       |
   |        cast(1, NUMBER(-1, -85))) END)])                                                                                                                                          |
   |   5 - output([DLT_T$$.OLD_NEW$$], [DLT_T$$.COL1]), filter([DLT_T$$.OLD_NEW$$ = cast('N', VARCHAR2(1048576 )) AND DLT_T$$.SEQUENCE$$ = DLT_T$$.MAXSEQ$$ OR                        |
   |       DLT_T$$.OLD_NEW$$ = cast('O', VARCHAR2(1048576 )) 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([cast(ORA_ROWSCN, NUMBER(-1,                        |
   |        -1)) > last_refresh_scn(500155)]), 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                                                                              |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   40 rows in set
   

Create a materialized view with query rewriting enabled

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

Here is an example:

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

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

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

   SELECT MVIEW_NAME, REWRITE_ENABLED
   FROM sys.DBA_MVIEWS
   WHERE MVIEW_NAME = 'MV_SPJ_TBL1';
   

   Notice

   In Oracle-compatible mode, if the MVIEW_NAME field in the sys.DBA_MVIEWS view matches the table name, the table name must be in uppercase letters.

   The returned result is as follows:

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

Create a columnar materialized view

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

Here is an example:

Create a columnar 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;

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

Specify 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 the 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, and partition the materialized view by using the col1 column as the hash partitioning key, with 8 partitions. Query the records in the tbl1 table that satisfy the condition col3 >= 20 as the base table, and use the query result 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;

Create an index on a materialized view

You cannot directly create an index on a materialized view in the CREATE MATERIALIZED VIEW statement. You can use the CREATE INDEX statement to create an index on a materialized view.

Here is an example:

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

CREATE INDEX idx_mv_tbl1 ON mv_tbl1(col1);

Refresh a materialized view

OceanBase Database supports four refresh strategies for materialized views: full refresh, incremental refresh, hybrid refresh, and never refresh. The following table describes these strategies:

• Full refresh: Recomputes the entire materialized view data to ensure that the data in the view is consistent with the data in the source table.
• Incremental refresh: Refreshes only the data that is related to changes in the source table, avoiding the need to fully recalculate the entire view.
• Hybrid refresh: The default strategy. First, it attempts an incremental refresh. If the incremental refresh fails, it performs a full refresh.
• Never refresh: The materialized view is refreshed only when it is created and cannot be refreshed again after creation.

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

Create a materialized view with complete refresh

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

Here is an example:

Create a materialized view named mv_rc_tbl1 based on table tbl1, specify the refresh strategy as complete refresh (REFRESH COMPLETE), and specify col1 and col2 in tbl1 where col3 is greater than or equal to 20 as the data source of the materialized view.

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

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

OceanBase Database allows you to create a materialized view with complete refresh based on an external table.

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

Here is an example:

The following example shows how to create an external table in the local file system and in the Oracle-compatible mode of OceanBase Database. The steps are as follows:

1. Prepare an external file.

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

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

   The content of the file is as follows:

   1,'A1'
   2,'A2'
   3,'A3'
   

2. Set the import file path.

   Notice

   For security reasons, you can set the secure_file_priv system variable only by using a local socket connection to execute an SQL statement to modify the global variable. For more information, see secure_file_priv.

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

      ssh admin@10.10.10.1
      

   2. Execute the following command to connect to the oracle001 tenant by using a local Unix socket.

      obclient -S /home/admin/oceanbase/run/sql.sock -usys@oracle001 -p******
      

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

      SET GLOBAL secure_file_priv = "/home/admin/external_csv";
      

3. Reconnect to the oracle001 tenant.

   Here is an example:

   obclient -h10.10.10.1 -P2881 -usys@oracle001 -p****** -A
   

4. Create an external table named ext_tbl1.

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

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

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

6. Query the data of the materialized view mv_ext_tbl1.

   SELECT * FROM mv_ext_tbl1;
   

   The return result is as follows:

   +------+------+
   | ID   | NAME |
   +------+------+
   |    2 | A2   |
   |    1 | A1   |
   |    3 | A3   |
   +------+------+
   3 rows in set
   

Create a materialized view with incremental refresh

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

Considerations

• Currently, incremental refresh is supported for materialized views that are based on the following types of SQL statements: non-aggregated single-table statements, aggregated single-table statements, multi-table join statements, aggregated multi-table join statements, and UNION ALL statements. For other types of SQL statements, incremental refresh is not supported. For more information about the requirements for incremental refresh, see the Incremental refresh section in Refresh a materialized view.

• The REFRESH FAST method uses the records in the materialized view log to determine the content to be incrementally refreshed. Therefore, when you incrementally refresh a materialized view, you must create a materialized view log for the base table before you create the materialized view. For more information, see Materialized view log.

  Note

  OceanBase Database supports automatic management of materialized view logs. If automatic management is enabled, you do not need to create a materialized view log for the base table before you create an incrementally refreshed materialized view. OceanBase Database automatically creates the corresponding materialized view log or updates the definition of an existing materialized view log to include the columns required by the new materialized view. For more information, see Automatic management of materialized view logs.

• When you create a materialized view, you can add the AS OF PROCTIME() clause to the base table. If you use AS OF PROCTIME() outside the base table, an error is returned.

  • AS OF PROCTIME() specifies that the incremental refresh of the materialized view should skip the table, thereby accelerating the incremental refresh. A table specified by AS OF PROCTIME() does not need to have a materialized view log. If you want to use an alias for the table, you must specify the alias after the AS OF PROCTIME() clause.

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

    • Not all tables in the materialized view can be dimension tables, just like the base table cannot be a dimension table in the materialized view.

Here is an example:

1. Create a table named tbl5 as the base table for the materialized view.

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

2. Create a materialized view log for the tbl5 table, and specify the SEQUENCE option to indicate that the changes are identified by sequence numbers. The columns to be recorded are specified, 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 based on the tbl5 table, and specify the incremental refresh strategy (REFRESH FAST). In the query part, specify to group the records in the tbl5 table by the col2 column, and calculate the number of records in each group (cnt), the number of non-null records in the col3 column (cnt_col3), and the sum of the col3 column (sum_col3) as the results of the materialized view.

   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 based on the tbl5 and tbl1 tables, and specify the incremental refresh strategy. Join the two tables on the col1 column using an inner join (INNER JOIN). Use AS OF PROCTIME() to specify that the tbl1 table should be skipped during the incremental refresh of the materialized view.

   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 incrementally refreshed materialized view based on a standard view declared with the AS OF PROCTIME() clause.

   1. Create a view named v1_tbl5 based on the tbl5 table.

      obclient> CREATE VIEW v1_tbl5 AS SELECT * FROM tbl5;
      

   2. Create a materialized view named mv3_tbl5_v_tbl5 based on the tbl5 and v1_tbl5 tables, and specify the incremental refresh strategy. Join the two tables on the col1 column. Use AS OF PROCTIME() to specify that the v1_tbl5 view is 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 hybrid refresh (default option)

When you create a materialized view, omit or specify the REFRESH FORCE clause to set the refresh strategy to hybrid refresh.

Here is an example:

Create a materialized view named mv_rf_tbl1 based on the tbl1 table, and specify the hybrid refresh strategy (REFRESH FORCE). Specify to select the col1 and col2 columns from the tbl1 table where col3 is greater than or equal to 20 as the data source of the materialized view.

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

Create a never-refreshed materialized view

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 the tbl1 table, and specify the never-refreshed strategy (NEVER REFRESH). Specify to select the col1 and col2 columns from the tbl1 table where col3 is greater than or equal to 20 as the data source of the materialized view.

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

Create an automatically refreshed materialized view

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. The materialized view is refreshed in full. The initial refresh time is set to the current date, and the materialized view is refreshed every 1 hour after that.

CREATE MATERIALIZED VIEW mv_rc_swn_tbl1
    REFRESH COMPLETE
        START WITH current_date NEXT current_date + INTERVAL '1' HOUR
    AS SELECT col1, col2
       FROM tbl1
       WHERE col3 >= 20;

References

• DBMS_MVIEW overview
• Overview of materialized views
• Materialized view logs
• Refresh materialized views
• Materialized view query rewrite
• Query materialized views
• Modify materialized views
• Delete materialized views