Create a materialized view

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

Prerequisites

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

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

Create a test table

1. Create a table named tbl1.

   CREATE TABLE tbl1 (id INT PRIMARY KEY, name VARCHAR(20), age INT);
   

2. Create a table named tbl2.

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

3. Create a table named tbl3.

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

Create a materialized view with a primary key

Here is an example:

Create a materialized view named mv0_tbl1 and set the primary key.

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 idx1_mv_tbl1 on the id column of the mv_tbl1 materialized view.

  CREATE INDEX idx1_mv_tbl1 ON mv_tbl1(id); 
  

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

  ALTER TABLE mv_tbl1 ADD INDEX idx2_mv_tbl1(name);
  

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 three refresh methods for materialized views.

• Complete refresh (COMPLETE): In a complete refresh, OceanBase Database recalculates the data of the entire materialized view to ensure data consistency with the base table.

• Incremental refresh (FAST): In an incremental refresh, OceanBase Database refreshes only the data related to changes in the base table.

  Notice

  This method determines the content for an incremental refresh based on the records in the materialized view log. To perform an incremental refresh for a materialized view, you need to create a materialized view log of 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 (FORCE): In a hybrid refresh, OceanBase Database performs an incremental refresh first, and then performs a complete refresh if the incremental refresh fails. This is the default refresh method.

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

Complete refresh

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

Here is an example:

Create a materialized view named mv1_tbl1. Specify complete refresh as the refresh method for the materialized view, and specify that you can manually initiate a refresh as needed. Specify to use the id and name columns in the tbl1 table whose age column values are greater than or equal to 20 as the data source of the materialized view.

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 an incremental refresh for a materialized view by using the REFRESH FAST clause.

Here is an example:

1. Create a materialized view log of the tbl3 table. Specify the SEQUENCE option for the materialized view log, which means to identify data changes of 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 incremental refresh as the refresh method for the materialized view, and specify that you can manually initiate a refresh as needed. In the query part, specify to group data in the tbl3 table by the col2 column, calculate the total number of records (cnt), number of non-empty records in the col3 column (cnt_col3), and sum of col3 column values (sum_col3) in each group, and 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

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

Here is an example:

Create a materialized view named mv2_tbl1. Specify hybrid refresh as the refresh method for the materialized view, and specify that you can manually initiate a refresh as needed. Specify to use the id and name columns in the tbl1 table whose age column values are greater than or equal to 20 as the data source of the materialized view.

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 for 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 never refresh as the refresh method for the materialized view. Specify to use the id and name columns in the tbl1 table whose age column values are greater than or equal to 20 as the data source of the materialized view.

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 complete refresh as the refresh method for the materialized view, and the current date as the initial refresh date for the refresh schedule of the materialized view, and specify to refresh the materialized view at an interval of two days. In the query part, specify to use the id, name, and notes columns in the tbl1 and tbl2 tables as the data source of the materialized view and use the t1.id = t2.tbl1_id join condition.

CREATE MATERIALIZED VIEW mv_tbl1_tbl2
  REFRESH COMPLETE
    START WITH sysdate() NEXT sysdate() + interval 1 day
  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 to capture and handle changes of underlying base tables to ensure that data in the materialized view reflects the latest state. During the 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 is 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
     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 oceanbase.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 the execution, the system reads data from both the materialized view and the materialized view log of the base table on which the materialized view depends, and integrates 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(cast(mv1_tbl4.col1, BIGINT(11, 0)), cast(DLT_BASIC_MV$$.col1, BIGINT(11, 0))), INT(11, 0))], [cast(CASE WHEN mv1_tbl4.cnt IS NULL                         |
   |       THEN DLT_BASIC_MV$$.cnt WHEN DLT_BASIC_MV$$.cnt IS NULL THEN cast(mv1_tbl4.cnt, DECIMAL_INT(43, 0)) ELSE cast(mv1_tbl4.cnt, DECIMAL_INT(43, 0)) + DLT_BASIC_MV$$.cnt       |
   |       END, BIGINT(20, 0))]), filter([CASE WHEN mv1_tbl4.cnt IS NULL THEN DLT_BASIC_MV$$.cnt WHEN DLT_BASIC_MV$$.cnt IS NULL THEN cast(mv1_tbl4.cnt, DECIMAL_INT(43,              |
   |        0)) ELSE cast(mv1_tbl4.cnt, DECIMAL_INT(43, 0)) + DLT_BASIC_MV$$.cnt END > cast(0, DECIMAL_INT(43, 0))]), 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$$ = '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)])                                                                         |
   |   3 - 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])                                                                  |
   |   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([ORA_ROWSCN > last_refresh_scn(500082)]), 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                                                                                                          |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   38 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 enable automatic query rewriting based on this materialized view. 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
       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 oceanbase.DBA_MVIEWS WHERE MVIEW_NAME = 'mv4_tbl1';
   

   The return result is as follows:

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

Columnstore materialized view

OceanBase Database supports rowstore, columnstore, and hybrid rowstore-columnstore materialized views. You can use the mv_column_group_option option to specify the storage mode of a materialized view to be created.

Create a columnstore materialized view

If the materialized view to be created is a wide table that joins multiple tables, you can create a columnstore materialized view to 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 named mv7_tbl1.

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

Create a hybrid rowstore-columnstore materialized view

You can specify the WITH COLUMN GROUP(all columns, each column) option to create a hybrid rowstore-columnstore materialized view.

Here is an example:

Create a hybrid rowstore-columnstore materialized view named mv8_tbl1.

CREATE MATERIALIZED VIEW mv8_tbl1
  WITH COLUMN GROUP(all columns, each column)
  AS SELECT *
    FROM tbl1;

References

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