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 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: specifies the name of the materialized view to be created.

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

• PRIMARY KEY(column_list): optional. specifies the primary key for the materialized view.

• table_option_list: optional. specifies the table options for the materialized view.

• partition_option: optional. specifies the partitioning options for the materialized view.

• mv_column_group_option: optional. specifies the storage format for the materialized view. If not specified, the default is to create a row-based materialized view.

• refresh_clause: optional. specifies the refresh method for 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 regular or real-time materialized view.

• AS view_select_stmt: specifies the query (SELECT) statement used to define the data for the materialized view. This statement retrieves data from the base table and stores the results in the materialized view.

  Note

  • OceanBase Database allows you to create a full-refresh materialized view with an external table as the base table.
  • OceanBase Database allows you to add the AS OF PROCTIME() clause to the base table when creating a materialized view. If you use AS OF PROCTIME() outside the base table of the materialized view, an error will be returned. AS OF PROCTIME() is used to specify that the refresh should skip this table during incremental refreshes. Additionally, tables with the AS OF PROCTIME() clause do not require an mlog to be created.
  • OceanBase Database allows you to declare a regular view as a dimension table (AS OF PROCTIME()) and use it as the base table for an incremental-refresh materialized view.

For more information about the parameters of the CREATE MATERIALIZED VIEW statement, see CREATE MATERIALIZED VIEW.

Create a materialized view

Create a regular materialized view

To create a regular materialized view, omit or specify the DISABLE ON QUERY COMPUTATION clause.

Here is an example:

1. Create the tbl1 table as the base table for 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.

   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, the mv1 materialized view is built based on the tbl1 and tbl2 tables, which is a typical materialized view. The mv2 materialized view is built based on the mv1 materialized view and the tbl3 table, making it a nested materialized view. Similarly, the mv3 materialized view is built based on the mv1 and mv2 materialized views, also making it a nested materialized view.

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

• INDIVIDUAL: The default value, indicating independent refresh.
• INCONSISTENT: Indicating cascading non-consistent refresh.
• CONSISTENT: Indicating cascading consistent refresh.

Limitations of nested materialized views

• To support incremental refresh for nested materialized views, you need to create an mlog on the base table (materialized view).
• If a materialized view has been fully refreshed, any dependent materialized views (nested materialized views) must be fully refreshed before they can be incrementally refreshed. Otherwise, an error will occur.
• Nested materialized views cannot be created as real-time materialized views. In other words, the ENABLE ON QUERY COMPUTATION clause cannot be specified when creating a nested materialized view.

Here is an example:

1. Create the tbl3 table as the base table for the materialized view.

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

2. Create the tbl4 table as the base table for 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 with the INCONSISTENT refresh strategy.

   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

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

Considerations for real-time materialized views

• Before creating a real-time materialized view, you need to create a materialized view log for each base table that the materialized view depends on.

  Note

  OceanBase Database supports automatic management of materialized view logs. If automatic management of mlogs is enabled, you do not need to create mlogs for base tables when creating a real-time materialized view. OceanBase Database will automatically create the corresponding mlogs or update the existing mlog table definitions to include the columns required by the newly created materialized view. For more information, see Automatic management of materialized view logs.

• Only specific types of materialized views can be specified as real-time materialized views. If a materialized view does not meet the requirements, specifying it as a real-time materialized view will result in an error. The requirements for real-time materialized views are the same as those for materialized views that support incremental refresh. For more information, see the Basic requirements for incremental refresh section in Refresh a materialized view.

  • Materialized views that use the MIN/MAX functions do not support real-time materialized views.
  • Aggregated materialized views with outer joins do not support real-time materialized views.
  • Materialized views with set queries do not support real-time materialized views.
  • Nested materialized views cannot be created as real-time materialized views.

• If the system variable values in the session where the query is executed do not match the session variables that are fixed in the materialized view, you need to modify the system variable values in the session to match the session variables fixed in the real-time materialized view. Otherwise, the real-time materialized view will not be available, meaning that query rewriting for the real-time materialized view will not take effect, or querying the real-time materialized view will result in an error.

Here is an example:

1. Create the tbl2 table as the base table 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 named mv_tbl2 based on the tbl2 table.

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

4. After creating the real-time materialized view, you can view whether the materialized view is a real-time materialized view by querying the DBA_MVIEWS view.

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

   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 during execution, data is read from both the materialized view and the mlog of the base table it depends on. The data from these two sources is then calculated and integrated to obtain the real-time data of the materialized view.

   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), 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 with query rewrite enabled

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

Here is an example:

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

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

2. After creating the materialized view, you can check whether automatic query rewriting is enabled by querying the DBA_MVIEWS 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 use the mv_column_group_option option to explicitly create a columnstore or rowstore-columnstore redundant materialized view. If a materialized view is created by joining multiple tables, creating a columnstore materialized view can improve the performance of certain queries. Use the WITH COLUMN GROUP(each column) clause 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 creating a materialized view

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

For more information about the parameters of 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. Hash partition the materialized view by the col1 column into 8 partitions. Query the records in the tbl1 table that satisfy 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 is an example:

• 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 modes for materialized views: full refresh, incremental refresh, hybrid refresh, and never refresh.

• Full refresh: Recomputes the entire data of the materialized view to ensure that the data in the view is consistent with that in the source table.
• Incremental refresh: Refreshes only the data that is related to changes in the source table to avoid a full recomputation of the view.
• Hybrid refresh: The default mode. 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. 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 creating a materialized view, 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), and specify that the data source for 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 allows you to create a materialized view with a complete refresh strategy based on an external table.

For more information about external tables, see External tables.

Here is an example:

The following example demonstrates how to create an external table in two scenarios: when the external file is located locally and when it is stored in OceanBase Database in MySQL mode. 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 machine 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, when setting the secure_file_priv system variable, you can only modify the global variable by executing an SQL statement through a local socket connection. 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 mysql001 tenant using a local Unix socket.

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

   3. Execute the following SQL command 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 external table ext_tbl1.

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

6. View the data in the materialized view mv_ext_tbl1.

   SELECT * FROM mv_ext_tbl1;
   

   The returned 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 specify the REFRESH FAST clause to set the refresh strategy to incremental refresh.

Considerations

• At present, incremental refresh is supported for materialized views based on SQL statements that meet the following conditions: single-table non-aggregate, single-table aggregate, multi-table join, multi-table join aggregate, and UNION ALL queries. For SQL statements that do not meet these conditions, incremental refresh is not supported. For more information about 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 the materialized view log for the base table before you create the materialized view. For more information about how to create a materialized view log, 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 table 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 the AS OF PROCTIME() clause outside the base table, an error is returned.

  • The AS OF PROCTIME() clause specifies to skip the refresh of the table during incremental refresh, thereby accelerating the incremental refresh of the materialized view. You do not need to create a materialized view log for a table specified with the AS OF PROCTIME() clause. 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 regular view declared with the AS OF PROCTIME() clause as the base table of an incrementally refreshed materialized view, the following limitations apply:

    • All tables in the materialized view cannot be dimension tables, similar to the base table of the materialized view.

Here is an example:

1. Create the tbl5 table as the base table of 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 for the materialized view log. The SEQUENCE option indicates that the change data is identified by the sequence number. Specify the col2 and col3 columns to be recorded in the materialized view log.

   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 for the materialized view. In the query clause, 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 result 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 for the materialized view. Join the tbl5 and tbl1 tables on the col1 column by using the INNER JOIN clause. Use the AS OF PROCTIME() clause to specify that the tbl1 table is to 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 regular view that is 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 for the materialized view. Join the tbl5 and v1_tbl5 tables on the col1 column. Use the AS OF PROCTIME() clause 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 mixed refresh (default option)

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

Here is an example:

Create a materialized view named mv_rf_tbl1 based on the tbl1 table and specify the mixed refresh strategy for the materialized view. Specify the col1 and col2 columns that meet the col3 >= 20 condition 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 materialized view that is never refreshed

When you create a materialized view, you can specify the NEVER REFRESH clause to indicate that the materialized view is not 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-refresh strategy for the materialized view. Specify the col1 and col2 columns that meet the col3 >= 20 condition 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 a materialized view with an automatic refresh schedule

When you create a materialized view, you can use the START WITH datetime_expr and NEXT datetime_expr clauses to set up an 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. Set the refresh strategy to full refresh. Set the initial refresh time to the current date and schedule subsequent refreshes every 1 day.

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;

Related topics

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