Create a materialized view

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

Required privileges

You need the CREATE TABLE privilege to create a materialized view. For more information about privileges in OceanBase Database, see Privilege types in Oracle 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] [refresh_clause [query_rewrite_clause] [on_query_computation_clause]] AS view_select_stmt; 

For more information about the parameters in the syntax, see CREATE MATERIALIZED VIEW.

Create test tables

1. Create a table named tbl1.

   CREATE TABLE tbl1 (id NUMBER PRIMARY KEY, name VARCHAR2(20), age NUMBER);
   

2. Create a table named tbl2.

   CREATE TABLE tbl2 (id NUMBER PRIMARY KEY, tbl1_id NUMBER NOT NULL, notes VARCHAR2(50),
     CONSTRAINT fk_tbl2  FOREIGN KEY (tbl1_id)  REFERENCES tbl1 (id));
   

3. Create a table named tbl3.

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

Create a materialized view with a primary key

Here is an example:

Create a materialized view named mv0_tbl1 with a primary key specified.

CREATE MATERIALIZED VIEW mv0_tbl1(v_id, v_name, PRIMARY KEY(v_id))
  AS SELECT id, name
     FROM tbl1
     WHERE age >= 20;

Table options and partition options for a materialized view

When you create a materialized view, you can set table options and partition options based on the data characteristics and access method to improve query performance and management efficiency.

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

Here is an example:

Create a materialized view named mv_tbl1. Set the degree of parallelism (DOP) to 5 for the materialized view, and HASH-partition the view by the id column into eight partitions. Query the tbl1 base table for data that meets the age >= 20 condition and record the query result in the materialized view.

CREATE MATERIALIZED VIEW mv_tbl1(id, name)
  PARALLEL 5
  PARTITION BY HASH(id) PARTITIONS 8
  AS SELECT id, name
     FROM tbl1
     WHERE age >= 20;

Create an index for a materialized view

Here is an example:

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

CREATE INDEX idx_mv_tbl1 ON mv_tbl1(id); 

Refresh mode

OceanBase Database supports the ON DEMAND refresh mode, in which a materialized view is refreshed based on your needs.

You can also call the DBMS_MVIEW package to manually refresh a materialized view, or set periodical refresh of a materialized view by using the START WITH ... NEXT... statement.

For more information about the DBMS_MVIEW package, see Overview.

Refresh methods

OceanBase Database supports the following three methods of refreshing materialized views:

• Full refresh: In this method, OceanBase Database recalculates the data of the entire materialized view to ensure data consistency with the base table.

• Incremental refresh: In this method, OceanBase Database refreshes only the data related to changes in the base table.

  Notice

  To perform an incremental refresh of a materialized view, you need to create a materialized view log on the base table before you create the materialized view. For information about how to create a materialized view log, see Create a materialized view log.

• Hybrid refresh: In this method, OceanBase Database performs an incremental refresh first. If the incremental refresh fails, OceanBase Database performs a full refresh. The hybrid refresh method is used by default.

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

Full refresh

You can specify to perform the full refresh of a materialized view by using the REFRESH COMPLETE clause.

Here is an example:

Create a materialized view named mv1_tbl1. Specify to manually perform the full refresh on the materialized view in ON DEMAND mode. Set the data sources of the materialized view to the id and name columns of the tbl1 table with a filter condition that the age column values are greater than or equal to 20.

CREATE MATERIALIZED VIEW mv1_tbl1(id, name)
  REFRESH COMPLETE ON DEMAND
  AS SELECT id, name
     FROM tbl1
     WHERE age >= 20;

Incremental refresh

You can specify to perform the incremental refresh of a materialized view by using the REFRESH FAST clause.

Here is an example:

1. Create a materialized view log for the tbl3 table. Specify the SEQUENCE option for the materialized view log. This option specifies to identify changes of the specified columns by using sequential numbers. In this example, changes in the col2 and col3 columns are recorded.

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

2. Create a materialized view named mv_tbl3. Specify to manually perform the incremental refresh of the materialized view in ON DEMAND mode. Group the data selected from the tbl3 table by the col2 column and obtain the number of records in each group (cnt). Calculate the number of non-empty col3 records (cnt_col3) and the sum of col3 column values (sum_col3). Then, record the results in the materialized view.

   CREATE MATERIALIZED VIEW mv_tbl3
     REFRESH FAST ON DEMAND 
     AS SELECT col2, COUNT(*) cnt, COUNT(col3) cnt_col3, SUM(col3) sum_col3 
       FROM tbl3 
       GROUP BY col2;
   

Hybrid refresh (default option)

You can specify to perform the hybrid refresh of a materialized view by using the REFRESH FORCE clause. The hybrid refresh method is used by default.

Here is an example:

Create a materialized view named mv2_tbl1. Specify to manually perform the hybrid refresh on the materialized view in ON DEMAND mode. Set the data sources of the materialized view to the id and name columns of the tbl1 table with a filter condition that the age column values are greater than or equal to 20.

CREATE MATERIALIZED VIEW mv2_tbl1(id, name)
  REFRESH FORCE ON DEMAND
  AS SELECT id, name
     FROM tbl1
     WHERE age >= 20;

Never refresh

You can specify not to perform a refresh of a materialized view by using the NEVER REFRESH clause. If you specify the NEVER REFRESH clause, the materialized view is refreshed only when it is created, and will not be refreshed again.

Here is an example:

Create a materialized view named mv3_tbl1. Specify not to perform a refresh of the materialized view. Set the data sources of the materialized view to the id and name columns of the tbl1 table with a filter condition that the age column values are greater than or equal to 20.

CREATE MATERIALIZED VIEW mv3_tbl1(id, name)
  NEVER REFRESH
  AS SELECT id, name
     FROM tbl1
     WHERE age >= 20;

Automatically refresh a 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 task to automatically refresh the materialized view.

Here is an example:

Create a materialized view named mv_tbl1_tbl2. Specify the full refresh method for the materialized view. Set the initial refresh date of the refresh schedule of the materialized view to the current date, and the refresh interval to 1 day. Query the tbl1 and tbl2 tables for id, name and notes columns for the materialized view and use the t1.id = t2.tbl1_id join condition.

CREATE MATERIALIZED VIEW mv_tbl1_tbl2
  REFRESH COMPLETE
    START WITH current_date NEXT current_date + 1
  AS SELECT t1.id, t1.name, t2.notes
      FROM tbl1 t1, tbl2 t2
      WHERE t1.id = t2.tbl1_id;

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.

A real-time materialized view is a database object that can obtain real-time data. It uses materialized view logs (mlogs) to capture and handle changes of underlying base tables to ensure that data in the materialized view reflects the latest state. During execution of a query, the real-time materialized view instantly integrates these changes through online computation to display updated data. This way, you can obtain the query result updated in real time even if the materialized view does not physically store the latest data.

Considerations

You can specify only materialized views of specific types as real-time materialized views. If you attempt to 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 incrementally refreshed materialized views. For more information, see the limitations on incremental refreshes in the Refresh a materialized view topic.

Here is an example:

1. Create a test table named tbl4.

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

2. Create a materialized view log.

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

3. Create a real-time materialized view named mv1_tbl4.

   CREATE MATERIALIZED VIEW mv1_tbl4
     REFRESH COMPLETE ON DEMAND
     ENABLE ON QUERY COMPUTATION
     AS SELECT col1, count(*) AS cnt
         FROM tbl4
         GROUP BY col1;
   

4. Query the DBA_MVIEWS view to check whether the created materialized view is a real-time materialized view.

   SELECT MVIEW_NAME, ON_QUERY_COMPUTATION FROM sys.DBA_MVIEWS WHERE MVIEW_NAME = 'MV1_TBL4';
   

   The return result is as follows:

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

5. Query the execution plan for the real-time materialized view.

   EXPLAIN BASIC SELECT * FROM mv1_tbl4;
   

   According to the execution plan below, during execution, the system will read data from both the materialized view and the mlog of the base table on which the materialized view depends, and integrate the two parts of data through computation to obtain real-time materialized view data.

   The return result is as follows:

   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | Query Plan                                                                                                                                                                       |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | =============================================                                                                                                                                    |
   | |ID|OPERATOR                 |NAME          |                                                                                                                                    |
   | ---------------------------------------------                                                                                                                                    |
   | |0 |HASH FULL OUTER JOIN     |              |                                                                                                                                    |
   | |1 |├─SUBPLAN SCAN           |DLT_BASIC_MV$$|                                                                                                                                    |
   | |2 |│ └─HASH GROUP BY        |              |                                                                                                                                    |
   | |3 |│   └─SUBPLAN SCAN       |DLT_T$$       |                                                                                                                                    |
   | |4 |│     └─WINDOW FUNCTION  |              |                                                                                                                                    |
   | |5 |│       └─TABLE FULL SCAN|MLOG$_TBL4    |                                                                                                                                    |
   | |6 |└─TABLE FULL SCAN        |MV1_TBL4      |                                                                                                                                    |
   | =============================================                                                                                                                                    |
   | Outputs & filters:                                                                                                                                                               |
   | -------------------------------------                                                                                                                                            |
   |   0 - output([cast(NVL(MV1_TBL4.COL1, DLT_BASIC_MV$$.COL1), NUMBER(-1, 0))], [cast(CASE WHEN MV1_TBL4.CNT IS NULL THEN DLT_BASIC_MV$$.CNT WHEN DLT_BASIC_MV$$.CNT                |
   |       IS NULL THEN MV1_TBL4.CNT ELSE MV1_TBL4.CNT + DLT_BASIC_MV$$.CNT END, NUMBER(38, 0))]), filter([CASE WHEN MV1_TBL4.CNT IS NULL THEN DLT_BASIC_MV$$.CNT WHEN                |
   |       DLT_BASIC_MV$$.CNT IS NULL THEN MV1_TBL4.CNT ELSE MV1_TBL4.CNT + DLT_BASIC_MV$$.CNT END > cast(0, NUMBER(-1, -85))]), rowset=16                                            |
   |       equal_conds([MV1_TBL4.COL1 <=> DLT_BASIC_MV$$.COL1]), other_conds(nil)                                                                                                     |
   |   1 - output([DLT_BASIC_MV$$.COL1], [DLT_BASIC_MV$$.CNT]), filter(nil), rowset=16                                                                                                |
   |       access([DLT_BASIC_MV$$.COL1], [DLT_BASIC_MV$$.CNT])                                                                                                                        |
   |   2 - 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)])                                                                                                                                          |
   |   3 - 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])                                                                  |
   |   4 - output([MLOG$_TBL4.OLD_NEW$$], [MLOG$_TBL4.SEQUENCE$$], [T_FUN_MAX(MLOG$_TBL4.SEQUENCE$$)], [T_FUN_MIN(MLOG$_TBL4.SEQUENCE$$)], [MLOG$_TBL4.COL1]), filter(nil), rowset=16 |
   |       win_expr(T_FUN_MAX(MLOG$_TBL4.SEQUENCE$$)), partition_by([MLOG$_TBL4.M_ROW$$]), order_by(nil), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED             |
   |       FOLLOWING)                                                                                                                                                                 |
   |       win_expr(T_FUN_MIN(MLOG$_TBL4.SEQUENCE$$)), partition_by([MLOG$_TBL4.M_ROW$$]), order_by(nil), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED             |
   |       FOLLOWING)                                                                                                                                                                 |
   |   5 - output([MLOG$_TBL4.M_ROW$$], [MLOG$_TBL4.SEQUENCE$$], [MLOG$_TBL4.OLD_NEW$$], [MLOG$_TBL4.COL1], [ORA_ROWSCN]), filter([cast(ORA_ROWSCN, NUMBER(-1,                        |
   |        -1)) > last_refresh_scn(500069)]), rowset=16                                                                                                                              |
   |       access([MLOG$_TBL4.M_ROW$$], [MLOG$_TBL4.SEQUENCE$$], [MLOG$_TBL4.OLD_NEW$$], [MLOG$_TBL4.COL1], [ORA_ROWSCN]), partitions(p0)                                             |
   |       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                                                |
   |       range_key([MLOG$_TBL4.M_ROW$$], [MLOG$_TBL4.SEQUENCE$$]), range(MIN,MIN ; MAX,MAX)always true                                                                              |
   |   6 - output([MV1_TBL4.COL1], [MV1_TBL4.CNT]), filter(nil), rowset=16                                                                                                            |
   |       access([MV1_TBL4.COL1], [MV1_TBL4.CNT]), partitions(p0)                                                                                                                    |
   |       is_index_back=false, is_global_index=false,                                                                                                                                |
   |       range_key([MV1_TBL4.__pk_increment]), range(MIN ; MAX)always true                                                                                                          |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   40 rows in set
   

Rewrite a query based on a materialized view

When you create a materialized view, you can specify the ENABLE QUERY REWRITE clause to make the materialized view eligible for query rewriting. For more information, see Rewrite queries based on materialized views.

Here is an example:

1. Create a materialized view named mv4_tbl1 with the ENABLE QUERY REWRITE clause specified.

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

2. Query the DBA_MVIEWS view to check whether the created materialized view is eligible for query rewriting.

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

   The return result is as follows:

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

References

• CREATE MATERIALIZED VIEW
• Create a materialized view log
• Query materialized views
• Drop a materialized view
• Refresh a materialized view