Purpose
This statement is used to create a materialized view.
A materialized view is a special type of database object that stores a copy of query results and is periodically refreshed (or can be refreshed manually) to maintain the data in its latest state. Materialized views can contain aggregations, joins, and subqueries, and can be indexed and partitioned to further improve performance.
Privilege requirements
Creating a materialized view requires the CREATE TABLE privilege. For more information about OceanBase AI Database privileges, see Permission classification.
Syntax
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;
column_list:
column_name [, column_name ...]
refresh_clause:
REFRESH [refresh_option [PARALLEL n] [nested_refresh_option]] [mv_refresh_on_clause]
| NEVER REFRESH
refresh_option:
COMPLETE
| FAST
| FORCE
nested_refresh_option:
INDIVIDUAL
| INCONSISTENT
| CONSISTENT
mv_refresh_on_clause:
[ON DEMAND] [[START WITH expr] [NEXT expr]]
query_rewrite_clause:
DISABLE QUERY REWRITE
| ENABLE QUERY REWRITE
on_query_computation_clause:
DISABLE ON QUERY COMPUTATION
| ENABLE ON QUERY COMPUTATION
mv_column_group_option:
WITH COLUMN GROUP(all columns)
| WITH COLUMN GROUP(each column)
| WITH COLUMN GROUP(all columns, each column)
Parameters
Note
In the statement for creating a materialized view, you cannot directly create an index. You can use the CREATE INDEX statement or the ALTER TABLE statement to create an index for the materialized view.
Parameters |
Description |
|---|---|
| view_name | Specifies the name of the materialized view to be created. |
| column_list | Optional. Specifies the list of columns for the materialized view. If you want to assign explicit names to the view columns, you can use the column_list clause, separating the column names with commas.
Note
|
| column_name | Specifies the column name of the materialized view. By default, the column names retrieved by the SELECT statement are used as the view column names. |
| PRIMARY KEY | Specifies the primary key of the materialized view.
Notice
|
| table_option_list | Optional. Specifies the table options for the materialized view. Like a regular table, a materialized view can have its own Table Options set individually. For more information about parameters, see CREATE TABLE. |
| partition_option | Optional. Specifies the partition options for the materialized view. Similar to a regular table, a materialized view can have its own partitions set independently. For more information about parameters, see CREATE TABLE. |
| mv_column_group_option | Optional. Specifies the storage format of the materialized view. If not specified, a row-based materialized view is created by default. For more information, see mv_column_group_option. |
| refresh_clause | Optional. Specifies the refresh method for the materialized view. For more information, see refresh_clause. |
| mv_refresh_on_clause | Optional. Specifies the refresh timing for the materialized view. For more information, see mv_refresh_on_clause. |
| query_rewrite_clause | Optional. Specifies whether automatic query rewrite is enabled for the current materialized view. For more information, see query_rewrite_clause. |
| on_query_computation_clause | Optional. Specifies whether the current materialized view is an on-query materialized view. For more information, see on_query_computation_clause. |
| view_select_stmt | Used to define the query for materialized view data (SELECT) statement. This statement is used to retrieve data from the base table and store the result in the materialized view. The syntax of view_select_stmt is the same as that of a regular SELECT statement. For more information, see SELECT.
Note
|
mv_column_group_option
WITH COLUMN GROUP(all columns): Specifies to create a row-store format materialized view.WITH COLUMN GROUP(each column): Specifies to create a column-store format materialized view.WITH COLUMN GROUP(all columns, each column): Specifies to create a hybrid row-column store format materialized view.
Note
You can use the SHOW CREATE TABLE view_name; or SHOW CREATE VIEW view_name; command to view the definition of a materialized view and determine its storage format.
refresh_clause
REFRESH [refresh_option [PARALLEL n] [nested_refresh_option]] [mv_refresh_on_clause]: Specifies the refresh method and parallelism for the materialized view.refresh_option: Specifies the refresh method for the materialized view. Optional. If no refresh method is specified, the default isFORCE.COMPLETE: Indicates a complete refresh, which recalculates the data for the entire materialized view to ensure the data in the view is fully consistent with the source table.FAST: Indicates an incremental refresh, which only refreshes the data related to changes in the source table, avoiding a full recalculation of the entire view.Notice
- The
REFRESH FASTmethod uses the record information in the materialized view's log to determine what needs to be refreshed incrementally. Therefore, when using incremental refresh for a materialized view, you must create the materialized view log (mlog) for the base table before creating the materialized view. - All columns used in the incremental refresh of a materialized view must be included in the mlog.
- The
FORCE: The default value. It specifies to perform a hybrid refresh. In other words, the system first attempts an incremental refresh. If the incremental refresh fails, it then performs a full refresh.nested_refresh_option: Specifies the refresh strategy for nested materialized views. This option is optional. If not specified, the default isINDIVIDUAL. Valid values:INDIVIDUAL: The default value, indicating an independent refresh.INCONSISTENT: Indicates a cascading non-consistent flush.CONSISTENT: indicates a cascading consistent refresh.
Note
For non-nested materialized views, cascading refresh is not supported. Specifying any refresh strategy is meaningless as they all default to independent refresh. The three specified refresh strategies only take effect for background tasks. When manually scheduling a refresh using the PL package (DBMS_MVIEW.REFRESH), the refresh is executed according to the specified PL parameters.
PARALLEL n: Specifies the refresh parallelism, which is the degree of parallelism used when refreshing the materialized view.nmust be an integer greater than or equal to 0. Whennis 0, the system variablemview_refresh_dopis used to determine the refresh parallelism.
NEVER REFRESH: specifies that the materialized view does not need to be refreshed. This means the materialized view is refreshed only when it is created and cannot be refreshed again after creation.
mv_refresh_on_clause
Note
Aside from defining a refresh plan using the mv_refresh_on_clause clause, you can also manually refresh a materialized view by calling the dbms_mviews.refresh stored procedure.
ON DEMAND: Optional. Specifies to refresh the materialized view on demand.[[START WITH expr] [NEXT expr]]: Optional. Defines the refresh interval.[START WITH expr]: Optional. The time expression at which the refresh plan starts.[NEXT expr]: Optional. The expression that specifies the time for the next refresh.Notice
- If the
NEXTclause is used, the time expression for refreshing the plan must be set to a future point in time; otherwise, an error will occur. - To periodically and automatically schedule refresh tasks for a materialized view, define
NEXTto set the scheduling cycle.
- If the
It is recommended to use sysdate() to represent the current time in the local timezone. An example time expression is as follows:
START WITH sysdate() NEXT sysdate() + INTERVAL 1 DAY
The preceding clause means that the materialized view will be refreshed every day starting from the current time (sysdate()).
query_rewrite_clause
Notice
This feature requires that the materialized view contains only the SELECT JOIN and WHERE clauses, that is, it must be an SPJ query. Materialized views that do not meet this condition will not cause an error but will not be used for query rewrite.
DISABLE QUERY REWRITE: The default value. Specifies that automatic query rewrite is not enabled for the current materialized view.ENABLE QUERY REWRITE: Specifies to enable automatic query rewrite for the current materialized view.
For more information about materialized view query rewrite, see Query rewrite using materialized views.
on_query_computation_clause
DISABLE ON QUERY COMPUTATION: The default value, which specifies to create a normal materialized view.ENABLE ON QUERY COMPUTATION: Specifies to create a real-time materialized view.Notice
- Materialized views that use the
MIN/MAXfunction do not support real-time materialization. - Aggregate materialized views with outer joins do not support real-time materialization.
- Materialized views with set queries do not support real-time materialization.
- Nested materialized views cannot be created as real-time materialized views.
- Materialized views that use the
For more information about real-time materialized views, see the Create a real-time materialized view section in Create a materialized view.
Examples
Create the table
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. Partition the materialized view by hash on thecol1column into8partitions. Use the complete refresh method to refresh the materialized view, starting from the current date and refreshing every1day. Use the records in thetest_tbl1table that satisfy the conditioncol3 >= 30as the base table for queries, and use the query results as the data for 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;Create a materialized view named
mv2_test_tbl1and specify its primary key.CREATE MATERIALIZED VIEW mv2_test_tbl1(c1, c2, c3, PRIMARY KEY(c1)) AS SELECT col1, col2, col3 FROM test_tbl1;Create a materialized view
mv3_test_tbl1in columnar format.CREATE MATERIALIZED VIEW mv3_test_tbl1 WITH COLUMN GROUP(each column) AS SELECT col1, col2, col3 FROM test_tbl1;View the definition of the materialized view
mv3_test_tbl1.SHOW CREATE VIEW mv3_test_tbl1;