Purpose
You can use this statement to create a materialized view.
A materialized view is a special database object that stores the latest copy of query results and is periodically or manually refreshed. Materialized views can contain operations such as aggregation and join operations, and subqueries, and can be indexed and partitioned to further enhance the query performance.
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
CREATE MATERIALIZED VIEW view_name [opt_column_list] [opt_table_option_list] [opt_partition_option] [refresh_clause] AS view_select_stmt;
opt_column_list:
(column_name [, column_name ...])
refresh_clause:
REFRESH [COMPLETE | FAST | FORCE] [mv_refresh_on_clause]
| NEVER REFRESH
mv_refresh_on_clause:
[ON DEMAND] [[START WITH expr] [NEXT expr]]
Parameters
Note
You cannot directly create an index by using the statement that creates a materialized view. You can use the CREATE INDEX or ALTER TABLE statement to create an index for a materialized view.
| Parameter | Description |
|---|---|
| view_name | The name of the materialized view to be created. |
| opt_column_list | Optional. The list of columns in the materialized view. You can use the opt_column_list clause to specify column names for the view and separate them with commas (,).
Note
|
| column_name | The name of a column in the materialized view. |
| opt_table_option_list | Optional. The table options of the materialized view. You can separately set table options for a materialized view just like you do for a normal table. For more information about the parameters, see CREATE TABLE. |
| opt_partition_option | Optional. The partitioning options for the materialized view. You can separately set partitioning options for a materialized view just like you do for a normal table. For more information about the parameters, see CREATE TABLE. |
| refresh_clause | Optional. The method for refreshing the materialized view. For more information, see refresh_clause. |
| mv_refresh_on_clause | Optional. The refresh mode of the materialized view. For more information, see mv_refresh_on_claim. |
| view_select_stmt | The SELECT statement that defines data in the materialized view. You use this statement to retrieve data from a base table and store the results in the materialized view. The syntax of view_select_stmt is the same as that of a regular SELECT statement. For more information about the syntax, see SELECT. |
refresh_clause
REFRESH [FAST | COMPLETE | FORCE] [mv_refresh_on_clause]: specifies how the materialized view is refreshed.[COMPLETE | FAST | FORCE]: the refresh method, which is optional. If you do not specify the refresh method,FORCEis used by default.COMPLETE: specifies to perform the full refresh. In this method, OceanBase Database recalculates the data of the entire materialized view to ensure data consistency with the base table.FAST: specifies to perform the 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.
FORCE: specifies to perform the 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.
NEVER REFRESH: specifies to disable the materialized view refresh. If you specify theNEVER REFRESHclause, the materialized view is refreshed only when it is created, and will not be refreshed again.
mv_refresh_on_clause
Note
In addition to defining the refresh schedule by using the mv_refresh_on_claim clause, you can also call the dbms_mviews.refresh stored procedure to manually refresh a materialized view.
ON DEMAND: Optional. Specifies to refresh the materialized view on demand.[[START WITH expr] [NEXT expr]]: Optional. The refresh interval.[START WITH expr]: Optional. The time expression that defines when the refresh schedule begins.[NEXT expr]: Optional. The time expression that defines when the next refresh starts.Notice
If you use the
NEXTclause, the time expressions of the refreshing schedule must be set to future points in time. Otherwise, an error will occur.
We recommend that you use sysdate() to indicate the current time in the time zone. Here is an example of the time expression:
START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY
The preceding clause specifies to refresh the materialized view once every day, starting from the current time.
Example
Create a table named
test_tbl1.CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT, col4 INT);Create a materialized view named
mv_test_tbl1. HASH-partition the materialized view by thecol1column into eight partitions. Specify to refresh the materialized view once every day by using the full refresh method, starting from the current date. Query thetest_tbl1base table for data that meets thecol3 >= 30condition and record the query result in the materialized view.CREATE MATERIALIZED VIEW mv_test_tbl1 PARTITION BY HASH(col1) PARTITIONS 8 REFRESH COMPLETE START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY AS SELECT col1, col2, col3 FROM test_tbl1 WHERE col3 >= 30;