This topic describes how to create a materialized view by using SQL statements.
Note
OceanBase AI Database does not currently support directly modifying the properties of a materialized view, such as the update time or refresh strategy. In this case, you can consider dropping and recreating the materialized view to achieve the desired modification.
Privilege requirements
You must have the CREATE TABLE privilege to create a materialized view. For more information about OceanBase AI Database privileges, see Permission classification.
Syntax
The SQL statement 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;
The parameters are described as follows:
view_name: 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 thecolumn_listclause, separating column names with commas.PRIMARY KEY(column_list): Optional. Specifies the primary key for the materialized view.table_option_list: Optional. Specifies the table options for the materialized view.partition_option: Optional. Specifies the partition options for the materialized view.mv_column_group_option: Optional. Specifies the storage format for the materialized view. If not specified, a row-based materialized view is created by default.refresh_clause: Optional. Specifies the refresh method and parallelism for the materialized view.query_rewrite_clause: Optional. Specifies whether automatic query rewrite is enabled for the materialized view.on_query_computation_clause: Optional. Specifies whether the materialized view is a normal materialized view or a real-time materialized view.AS view_select_stmt: Defines the query (SELECT) statement for the materialized view data. This statement retrieves data from the base table and stores the results in the materialized view.Note
- OceanBase AI Database supports creating fully refreshed materialized views with external tables as the base table.
- OceanBase AI Database supports adding the
AS OF PROCTIME()clause when creating a materialized view for the base table. UsingAS OF PROCTIME()outside the base table location for the materialized view will result in an error.AS OF PROCTIME()specifies to skip refreshing this table during incremental refreshes, and the table specified byAS OF PROCTIME()does not need to create mlogs. - OceanBase AI Database supports declaring a normal view as a dimension table (
AS OF PROCTIME()), which can then serve as the base table for an incremental refresh materialized view.
For detailed parameter descriptions on the materialized view creation syntax, see CREATE MATERIALIZED VIEW.
Create a materialized view
Create a regular materialized view
When creating a materialized view, you can omit the DISABLE ON QUERY COMPUTATION clause or specify it to create a regular materialized view.
Example:
Create table
tbl1as the base table for the materialized view.CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 VARCHAR(20), col3 INT);Create a materialized view named
mv_tbl1based on tabletbl1.CREATE MATERIALIZED VIEW mv_tbl1 AS SELECT col1, col2 FROM tbl1 WHERE col3 >= 20;Or
CREATE MATERIALIZED VIEW mv_tbl1 DISABLE ON QUERY COMPUTATION AS SELECT col1, col2 FROM tbl1 WHERE col3 >= 20;
Create a nested materialized view
A nested materialized view is a materialized view built on an existing materialized view.
OceanBase AI Database supports specifying a refresh strategy when creating a nested materialized view. The valid values are as follows:
INDIVIDUAL: The default value, indicating independent refresh.INCONSISTENT: Indicates cascading non-consistent refresh.CONSISTENT: Indicates cascading consistent refresh.
Note
For non-nested materialized views, cascading refresh does not occur. Specifying any refresh strategy is meaningless, and they all default to independent refresh. The 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.
Limitations of nested materialized views
- To support incremental refresh for nested materialized views, you need to create mlogs on the materialized view (base table).
- If a materialized view undergoes a full refresh, then any dependent materialized views (nested materialized views) must first undergo a full refresh before they can perform an incremental refresh later; otherwise, an error will be reported.
- Nested materialized views do not support being created as real-time materialized views. That is, you cannot specify the
ENABLE ON QUERY COMPUTATIONclause when creating a nested materialized view.
Example:
Create table
tbl3as the base table for the materialized view.CREATE TABLE tbl3(id INT, name VARCHAR(30), PRIMARY KEY(id));Create table
tbl4as the base table for the materialized view.CREATE TABLE tbl4(id INT, age INT, PRIMARY KEY(id));Create a materialized view named
mv1_tbl3_tbl4based on tablestbl3andtbl4.CREATE MATERIALIZED VIEW mv1_tbl3_tbl4 (PRIMARY KEY (id1, id2)) REFRESH COMPLETE AS SELECT tbl3.id id1, tbl4.id id2, tbl3.name, tbl4.age FROM tbl3, tbl4 WHERE tbl3.id = tbl4.id;Create a materialized view (nested materialized view) named
mv_mv1_tbl3_tbl4based on the materialized viewmv1_tbl3_tbl4.CREATE MATERIALIZED VIEW mv_mv1_tbl3_tbl4 REFRESH COMPLETE AS SELECT SUM(AGE) age_sum FROM mv1_tbl3_tbl4;Create a materialized view (nested materialized view) named
mv1_mv1_tbl3_tbl4based on the materialized viewmv1_tbl3_tbl4, with the refresh strategy set toINCONSISTENT.CREATE MATERIALIZED VIEW mv1_mv1_tbl3_tbl4 REFRESH COMPLETE INCONSISTENT AS SELECT SUM(AGE) age_sum FROM mv1_tbl3_tbl4;
Create a real-time materialized view
When creating a materialized view, specify the ENABLE ON QUERY COMPUTATION clause to create a real-time materialized view.
Considerations for real-time materialized views
Before creating a real-time materialized view, you must create materialized view logs for all base tables that the materialized view depends on. OceanBase AI Database supports automatic management of materialized view logs. By default, the system automatically creates materialized view logs. For more information, see Materialized view logs.
Only specific types of materialized views can be specified as real-time materialized views. If a materialized view does not meet the requirements, specifying it as a real-time materialized view will result in an error. The requirements for real-time materialized views are the same as those for materialized views that support incremental refresh. For more information, see the Basic requirements for incremental refresh section in Refresh a materialized view.
- Materialized views that use the
MINorMAXfunction do not support real-time materialized views. - Aggregated materialized views with outer joins do not support real-time materialized views.
- Materialized views with set queries do not support real-time materialized views.
- Nested materialized views cannot be created as real-time materialized views.
- Materialized views that use the
When the value of a system variable in the session executing the query is inconsistent with the value of the session variable that was persisted in the materialized view when it was created, you need to modify the system variable value in the session to match the value of the session variable persisted in the real-time materialized view. Otherwise, the real-time materialized view will be unavailable, meaning query rewrite for the real-time materialized view will not take effect or querying the real-time materialized view directly will result in an error.
Example:
Create table
tbl2as the base table for the materialized view.CREATE TABLE tbl2(col1 INT, col2 INT, col3 INT);Create a real-time materialized view named
mv_tbl2based on tabletbl2.CREATE MATERIALIZED VIEW mv_tbl2 ENABLE ON QUERY COMPUTATION AS SELECT col1, count(*) AS cnt FROM tbl2 GROUP BY col1;After creating a real-time materialized view, you can check whether it is a real-time materialized view by querying the DBA_MVIEWS view.
SELECT MVIEW_NAME, ON_QUERY_COMPUTATION FROM oceanbase.DBA_MVIEWS WHERE MVIEW_NAME = 'mv_tbl2';The query result is as follows:
+------------+----------------------+ | MVIEW_NAME | ON_QUERY_COMPUTATION | +------------+----------------------+ | mv_tbl2 | Y | +------------+----------------------+ 1 row in setView the execution plan of the real-time materialized view.
EXPLAIN BASIC SELECT * FROM mv_tbl2;The following execution plan shows that during execution, data is read from both the materialized view and the mlog of the base table on which the view depends. These two sets of data are then computed and integrated to obtain the real-time materialized view data.
The query result is as follows:
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ============================================== | | |ID|OPERATOR |NAME | | | ---------------------------------------------- | | |0 |HASH GROUP BY | | | | |1 |└─SUBPLAN SCAN |INNER_RT_MV$$| | | |2 | └─UNION ALL | | | | |3 | ├─TABLE FULL SCAN |mv_tbl2 | | | |4 | └─HASH GROUP BY | | | | |5 | └─SUBPLAN SCAN |DLT_T$$ | | | |6 | └─WINDOW FUNCTION | | | | |7 | └─TABLE FULL SCAN|mlog$_tbl2 | | | ============================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([INNER_RT_MV$$.col1], [cast(T_FUN_SUM(INNER_RT_MV$$.cnt), BIGINT(20, 0))]), filter([T_FUN_SUM(INNER_RT_MV$$.cnt) > cast(0, DECIMAL_INT(64, | | 0))]), rowset=16 | | group([INNER_RT_MV$$.col1]), agg_func([T_FUN_SUM(INNER_RT_MV$$.cnt)]) | | 1 - output([INNER_RT_MV$$.col1], [INNER_RT_MV$$.cnt]), filter(nil), rowset=16 | | access([INNER_RT_MV$$.col1], [INNER_RT_MV$$.cnt]) | | 2 - output([UNION([1])], [UNION([2])]), filter(nil), rowset=16 | | 3 - output([mv_tbl2.col1], [cast(mv_tbl2.cnt, DECIMAL_INT(42, 0))]), filter(nil), rowset=16 | | access([mv_tbl2.col1], [mv_tbl2.cnt]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([mv_tbl2.__pk_increment]), range(MIN ; MAX)always true | | 4 - 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)]) | | 5 - 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]) | | 6 - output([mlog$_tbl2.OLD_NEW$$], [mlog$_tbl2.SEQUENCE$$], [T_FUN_MAX(mlog$_tbl2.SEQUENCE$$)], [T_FUN_MIN(mlog$_tbl2.SEQUENCE$$)], [mlog$_tbl2.col1]), filter(nil), rowset=16 | | win_expr(T_FUN_MAX(mlog$_tbl2.SEQUENCE$$)), partition_by([mlog$_tbl2.M_ROW$$]), order_by(nil), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED | | FOLLOWING) | | win_expr(T_FUN_MIN(mlog$_tbl2.SEQUENCE$$)), partition_by([mlog$_tbl2.M_ROW$$]), order_by(nil), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED | | FOLLOWING) | | 7 - output([mlog$_tbl2.M_ROW$$], [mlog$_tbl2.SEQUENCE$$], [mlog$_tbl2.OLD_NEW$$], [mlog$_tbl2.col1], [ORA_ROWSCN]), filter([ORA_ROWSCN > last_refresh_scn(500452)]), rowset=16 | | access([mlog$_tbl2.M_ROW$$], [mlog$_tbl2.SEQUENCE$$], [mlog$_tbl2.OLD_NEW$$], [mlog$_tbl2.col1], [ORA_ROWSCN]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([mlog$_tbl2.M_ROW$$], [mlog$_tbl2.SEQUENCE$$]), range(MIN,MIN ; MAX,MAX)always true | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 38 rows in set
Create a materialized view with query rewrite enabled
When creating a materialized view, you can specify the ENABLE QUERY REWRITE clause to enable automatic query rewrite for it. For more information about materialized view rewrite and rewrite control, see Query rewrite using materialized views.
Notice
A materialized view defined with the ENABLE QUERY REWRITE clause does not guarantee that queries will be rewritten through it. If a materialized view does not meet the query rewrite criteria, no error is reported, but it will not be used for rewrite. Since the default value of the system variable query_rewrite_enabled is false, by default, a materialized view with the ENABLE QUERY REWRITE clause defined will not be used for rewrite.
Example:
Create a materialized view named
mv_spj_tbl1based on tabletbl1and enable automatic query rewrite.CREATE MATERIALIZED VIEW mv_spj_tbl1 ENABLE QUERY REWRITE AS SELECT * FROM tbl1;After creating the materialized view, you can check whether automatic query rewrite is enabled for it through the DBA_MVIEWS view.
SELECT MVIEW_NAME, REWRITE_ENABLED FROM oceanbase.DBA_MVIEWS WHERE MVIEW_NAME = 'mv_spj_tbl1';The query result is as follows:
+-------------+-----------------+ | MVIEW_NAME | REWRITE_ENABLED | +-------------+-----------------+ | mv_spj_tbl1 | Y | +-------------+-----------------+ 1 row in set
Create a materialized view in columnar format
OceanBase AI Database supports materialized views in row, columnar, and hybrid row-column formats. You can explicitly create a materialized view in columnar or hybrid row-column format by specifying the mv_column_group_option option. If a materialized view is a large wide table formed by joining multiple tables, creating it in columnar format can improve the performance of certain queries. To create a materialized view in columnar format, specify WITH COLUMN GROUP(each column).
Note
If the mv_column_group_option option is not specified, a materialized view in row format is created by default.
Example:
Create a materialized view named mv_ec_tbl1 in columnar format based on table tbl1.
CREATE MATERIALIZED VIEW mv_ec_tbl1
WITH COLUMN GROUP(each column)
AS SELECT *
FROM tbl1;
Add a primary key when creating a materialized view
Notice
After specifying a primary key for a materialized view, if the data does not satisfy the primary key constraint during maintenance or update, the view maintenance will fail.
Example:
Create a materialized view named mv_pk_tbl1 based on table tbl1 and specify its primary key.
CREATE MATERIALIZED VIEW mv_pk_tbl1(v_id, v_name, PRIMARY KEY(v_id))
AS SELECT col1, col2
FROM tbl1
WHERE col3 >= 20;
Add table options and partition options when creating a materialized view
When creating a materialized view, you can set table options and design and configure appropriate partition options based on data characteristics and access patterns to improve query performance and management efficiency.
For detailed parameter descriptions of table options and partition options, see CREATE TABLE.
Example:
Create a materialized view named mv_pp_tbl1 based on table tbl1. Specify the query parallelism for the materialized view as 5, hash partition the materialized view by the col1 column into 8 partitions, use the records from table tbl1 that satisfy the condition col3 >= 20 as the base table for queries, and use the query results as the data for the materialized view.
CREATE MATERIALIZED VIEW mv_pp_tbl1
PARALLEL 5
PARTITION BY HASH(col1) PARTITIONS 8
AS SELECT col1, col2
FROM tbl1
WHERE col3 >= 20;
Add an index to a materialized view
You cannot create an index directly in the statement that creates a materialized view. However, you can use the CREATE INDEX statement or the ALTER TABLE statement to create an index for a materialized view.
Examples:
Create an index named
idx1_mv_tbl1on columncol1of the materialized viewmv_tbl1.CREATE INDEX idx1_mv_tbl1 ON mv_tbl1(col1);Create an index named
idx2_mv_tbl1on columncol2of the materialized viewmv_tbl1.ALTER TABLE mv_tbl1 ADD INDEX idx2_mv_tbl1(col2);
Materialized view refresh
OceanBase AI Database supports four refresh strategies for materialized views: complete refresh, incremental refresh, hybrid refresh, and never refresh. The details are as follows:
- Complete refresh: Recalculate the data of the entire materialized view to ensure that the data in the view is completely consistent with the source table.
- Incremental refresh: Refresh only the data related to changes in the source table, avoiding a full recalculation of the entire view.
- Hybrid refresh: The default option. It first attempts an incremental refresh. If the incremental refresh fails, it performs a complete refresh.
- Never refresh: The materialized view is refreshed only when it is created and cannot be refreshed again after creation.
For more information about materialized view refresh, see Refresh a materialized view.
Create a materialized view with complete refresh
When creating a materialized view, use the REFRESH COMPLETE clause to set the refresh strategy to complete refresh.
Notice
If a materialized view undergoes a complete refresh, any dependent materialized views (nested materialized views) must also perform a complete refresh before they can perform an incremental refresh later on; otherwise, an error will occur.
Example:
Create a materialized view named mv_rc_tbl1 based on table tbl1. Specify the refresh strategy as complete refresh (REFRESH COMPLETE) and specify to select columns col1 and col2 from table tbl1 where col3 is greater than or equal to 20 as the data source for the materialized view.
CREATE MATERIALIZED VIEW mv_rc_tbl1
REFRESH COMPLETE
AS SELECT col1, col2
FROM tbl1
WHERE col3 >= 20;
Create a materialized view with complete refresh based on an external table
OceanBase AI Database supports using an external table as the base table to create a materialized view with complete refresh.
For information about external tables, see External tables and external files.
Example:
Notice
The commands involving IPs in this example have been masked. During verification, you should fill in your machine's actual IP address.
The following example shows how to create an external table when the external file is located locally and in MySQL-compatible mode of OceanBase AI Database. The steps are as follows:
Prepare the external file.
Run the following command to create a file named
ext_tbl1.csvin the/home/admindirectory on the machine where you want to log in to the OBServer node.[admin@xxx /home/admin]# vi ext_tbl1.csvThe content of the file is as follows:
1,'A1','2025-01-01' 2,'A2','2025-02-01' 3,'A3','2025-03-01'Set the import file path.
Notice
For security reasons, when setting the system variable
secure_file_priv, you must connect to the database via a local socket to execute the SQL statement that modifies this global variable. For more information, see secure_file_priv.Run the following command to log in to the machine where the OBServer node is located.
ssh admin@10.10.10.1Run the following command to connect to the
mysql001tenant via a local Unix Socket connection.obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******Run the following SQL command to set the import path to
/home/admin.SET GLOBAL secure_file_priv = "/home/admin";
Reconnect to the
mysql001tenant.Example:
obclient -h10.10.10.1 -P2881 -uroot@mysql001 -p****** -A -Ddb_testCreate the external table
ext_tbl1.CREATE EXTERNAL TABLE ext_tbl1 ( id INT, name VARCHAR(50), c_date DATE ) LOCATION = '/home/admin' FORMAT = ( TYPE = 'CSV' FIELD_DELIMITER = ',' FIELD_OPTIONALLY_ENCLOSED_BY ='\'' ) PATTERN = 'ext_tbl1.csv';Create a fully refreshed materialized view
mv_ext_tbl1based on the external tableext_tbl1.CREATE MATERIALIZED VIEW mv_ext_tbl1 REFRESH COMPLETE AS SELECT * FROM ext_tbl1;Query the data in the materialized view
mv_ext_tbl1.SELECT * FROM mv_ext_tbl1;The query result is as follows:
+------+------+------------+ | id | name | c_date | +------+------+------------+ | 1 | A1 | 2025-01-01 | | 3 | A3 | 2025-03-01 | | 2 | A2 | 2025-02-01 | +------+------+------------+ 3 rows in set
Create an incremental refresh materialized view
When creating a materialized view, use the REFRESH FAST clause to set the refresh strategy to incremental refresh.
Considerations for creating an incremental refresh materialized view
Incremental refresh materialized views currently support SQL statements for single non-aggregated tables, single aggregated tables, multi-table joins, multi-table join aggregations, and set queries (
UNION ALL). For SQL statements that do not meet these five scenarios, incremental refresh is not supported. Requirements for incremental refresh SQL statements are detailed in the Incremental Refresh section of Refresh a materialized view.The
REFRESH FASTmethod uses record information in the materialized view log to determine the content that needs incremental refresh. OceanBase AI Database supports automatic materialized view log management. By default, the system automatically creates materialized view logs. For details, see Materialized view logs.When creating a materialized view, you can add the
AS OF PROCTIME()clause to the base table. IfAS OF PROCTIME()is used outside the base table position, an error will occur.AS OF PROCTIME()is used to specify that this table should be skipped during incremental refreshes, thereby accelerating the incremental refresh of the materialized view. Moreover, tables specified withAS OF PROCTIME()do not require an mlog to be created. If an alias needs to be used for this table, it must follow theAS OF PROCTIME()clause.When declaring a regular view as a dimension table (
AS OF PROCTIME()) as the base table for an incremental refresh materialized view, the following limitations apply:- Similar to the base table, not all tables used in the materialized view are allowed to be dimension tables.
Example:
Create table
tbl5as the base table for the materialized view.CREATE TABLE tbl5 (col1 INT PRIMARY KEY, col2 INT, col3 INT);Create a materialized view named
mv_tbl5based on tabletbl5. Specify the refresh strategy for the materialized view as incremental refresh. In the query part, specify to group by thecol2column from tabletbl5and calculate the sum of thecol3column (sum_col3) as the result of the materialized view.CREATE MATERIALIZED VIEW mv2_tbl5 REFRESH FAST AS SELECT col2, SUM(col3) sum_col3 FROM tbl5 GROUP BY col2;Create a materialized view named
mv2_tbl5_tbl1based on tablestbl5andtbl1. Specify the refresh strategy for the materialized view as incremental refresh, and perform an inner join (INNER JOIN) on thecol1field. UseAS OF PROCTIME()to specify that tabletbl1should be skipped during incremental refreshes of the materialized view.CREATE MATERIALIZED VIEW mv2_tbl5_tbl1 REFRESH FAST ON DEMAND AS SELECT t5.col1 tbl5_c1, t1.col1 tbl1_c1, t5.col2 tbl5_c2, t1.col2 tbl1_c2 FROM tbl5 t5 INNER JOIN tbl1 AS OF PROCTIME() t1 ON t5.col1 = t1.col1 WHERE t5.col2 = 3;Create an incremental refresh materialized view based on a regular view declared with
AS OF PROCTIME().Create a view named
v1_tbl5based on tabletbl5.obclient> CREATE VIEW v1_tbl5 AS SELECT * FROM tbl5;Create a materialized view named
mv3_tbl5_v_tbl5based on tablestbl5andv1_tbl5. Specify the refresh strategy for the materialized view as incremental refresh, and connect on thecol1field. UseAS OF PROCTIME()to specify viewv1_tbl5as the dimension table.obclient> CREATE MATERIALIZED VIEW mv3_tbl5_v_tbl5 AS SELECT a.col1 a_c1, b.col1 b_c1 FROM tbl5 a JOIN v1_tbl5 AS OF PROCTIME() b ON a.col1 = b.col1;
Create a hybrid refresh materialized view (default option)
When creating a materialized view, omit or specify the REFRESH FORCE clause to set the refresh strategy to hybrid refresh.
Example:
Create a materialized view named mv_rf_tbl1 based on table tbl1. Specify the refresh strategy for the materialized view as hybrid refresh. Specify to select columns col1 and col2 from table tbl1 where col3 is greater than or equal to 20 as the data source for the materialized view.
CREATE MATERIALIZED VIEW mv_rf_tbl1
REFRESH FORCE
AS SELECT col1, col2
FROM tbl1
WHERE col3 >= 20;
Specify the refresh parallelism when creating a materialized view
When creating a materialized view, specify the refresh parallelism using REFRESH ... PARALLEL n.
Example:
Create a materialized view named mv_rcp3_tbl1 based on table tbl1. Specify the refresh strategy for the materialized view as complete refresh (REFRESH COMPLETE), and specify the query parallelism as 16 and the refresh parallelism as 5. Select columns col1 and col2 from table tbl1 where col3 is greater than or equal to 20 as the data source for the materialized view.
obclient> CREATE MATERIALIZED VIEW mv_rcp3_tbl1
PARALLEL 16
REFRESH COMPLETE PARALLEL 5 ON DEMAND
AS SELECT col1, col2
FROM tbl1
WHERE col3 >= 20;
Create a never-refresh materialized view
When creating a materialized view, you can use the NEVER REFRESH clause to specify 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.
Example:
Create a materialized view named mv_nr_tbl1 based on table tbl1. Specify the refresh strategy for the materialized view as never to refresh. Specify to select columns col1 and col2 from table tbl1 where col3 is greater than or equal to 20 as the data source for the materialized view.
CREATE MATERIALIZED VIEW mv_nr_tbl1
NEVER REFRESH
AS SELECT col1, col2
FROM tbl1
WHERE col3 >= 20;
Create a materialized view with automatic refresh
When creating a materialized view, you can use the START WITH datetime_expr and NEXT datetime_expr clauses to specify a background automatic refresh task for the materialized view.
Notice
If the NEXT clause is used, the time expression in the refresh plan must be set to a future point in time; otherwise, an error will occur.
Example:
Create a materialized view named mv_rc_swn_tbl1 based on table tbl1, specifying a full refresh strategy for the materialized view. Set the initial refresh time in the refresh plan to the current date, and then refresh the materialized view every 1 day thereafter.
CREATE MATERIALIZED VIEW mv_rc_swn_tbl1
REFRESH COMPLETE
START WITH sysdate() NEXT sysdate() + interval 1 day
AS SELECT col1, col2
FROM tbl1
WHERE col3 >= 20;