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 MySQL mode.

Syntax

The syntax for creating a materialized view is as follows:

CREATE MATERIALIZED VIEW view_name [opt_column_list] [opt_table_option_list] [opt_partition_option] [refresh_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 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);
   

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:

• On the id column of the mv_tbl1 materialized view, create an index named idx1_mv_tbl1.

  CREATE INDEX idx1_mv_tbl1 ON mv_tbl1(id); 
  

• On the name column of the mv_tbl1 materialized view, create an index named idx2_mv_tbl1.

  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 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 on 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

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 to perform the full refresh on the materialized view. Set the time of the first refresh of the materialized view to the current date, and the refresh interval to 1 day. Set the data sources of the materialized view to the id, name and notes columns of the tbl1 and tbl2 tables. Specify to join the results based on the t1.id = t2.tbl1_id 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;

References

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