Description
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 Oracle 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. To create an index for a materialized view, you need to use the CREATE INDEX statement.
| 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 separately 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 SIMPLE 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 current_date to indicate the current time in the time zone. Here is an example of the time expression:
The following clause specifies to refresh the materialized view once every 10 seconds, starting from the current time.
START WITH current_date NEXT current_date + INTERVAL '10' SECONDThe following clause specifies to refresh the materialized view once every 10 hours, starting from the current time.
START WITH current_date NEXT current_date + INTERVAL '10' HOURThe following clause specifies to refresh the materialized view once every day, starting from the current time.
START WITH current_date NEXT current_date + 1
Example
Create a table named
test_tbl1.CREATE TABLE test_tbl1 (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(20), col3 NUMBER, col4 NUMBER);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 current_date NEXT current_date + 1 AS SELECT col1, col2, col3 FROM test_tbl1 WHERE col3 >= 30;