Updating data in the base table may cause the data in the materialized view to become inconsistent with the base table's data. To maintain the consistency of the materialized view, OceanBase AI Database refreshes the materialized view.
OceanBase AI Database supports two materialized view refresh strategies: complete refresh and incremental refresh. It also supports both manual and automatic refresh scheduling.
Note
Refreshing a materialized view automatically updates all its indexes.
Complete refresh
OceanBase AI Database performs a complete refresh by using an external table refresh method. That is, it creates a hidden table, executes the refresh statement on the hidden table, and then swaps the original table with the hidden table. Therefore, a complete refresh operation requires additional space and completely rebuilds indexes (if any).
Considerations for complete refresh
- A complete refresh can be a very time-consuming process, especially when a large amount of data needs to be read and processed. Therefore, you should always consider the time required for a complete refresh before performing it.
- A complete refresh is performed if the column types of the current base table match those of the corresponding columns in the materialized view. If they do not match, a complete refresh cannot be performed.
- If a materialized view undergoes a complete refresh, any materialized views (nested materialized views) that depend on it must also undergo a complete refresh before any subsequent incremental refreshes can be performed; otherwise, an error will occur.
Incremental refresh
Incremental refresh is currently supported for materialized views in the following SQL statement types: non-aggregated single-table, aggregated single-table, multi-table join, multi-table join with aggregation, and set queries (UNION ALL). SQL statements that do not fall into these five categories do not support incremental refresh. For details on the required SQL statements for incremental refresh, see the description below.
Notice
- Incremental refresh (
REFRESH FAST) uses the records in the materialized view log to determine the content to be incrementally refreshed. OceanBase AI Database supports automatic management of materialized view logs. By default, the system automatically creates the corresponding mlog or updates the existing mlog definition to include the columns required by the newly created materialized view. For more information, see the Automatic management of materialized view logs section in Materialized view logs. - The examples in this topic use the automatic management of materialized view logs. If you need to manually create an mlog, see the Manual management of materialized view logs section in Materialized view logs.
Non-aggregated incremental refresh for a single table
Example of non-aggregated incremental refresh for a single table
Create the table
tbl1.CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 INT, col3 INT, col4 INT);Create a materialized view
mv_tbl1with incremental refresh based on tabletbl1.CREATE MATERIALIZED VIEW mv_tbl1 REFRESH FAST ON DEMAND AS SELECT col1, col2 FROM tbl1;
Aggregated incremental refresh for a single table
The basic requirements for aggregated incremental refresh of a single table are as follows:
The
FROMtable must be a base table, not an inline view or a regular view.Note
Since nested materialized views do not support real-time materialized views, real-time materialized views cannot be used when the
FROMtable is a materialized view.There is only one table in the
FROMclause.The
FROMtable has mlogs created, and all columns used in the view definition exist in the mlogs.The view definition does not contain subqueries.
Window functions are not supported.
The view definition does not contain clauses such as
ROLLUP,HAVING,DISTINCT,ORDER BY,LIMIT, orFETCH.If the query contains
DISTINCT, then the output columns in the materialized view that can be updated incrementally must be unique. Therefore, in this case, you can directly prohibit the use ofDISTINCTor remove it.Statements without
GROUP BYmust be scalar aggregates.For materialized views in
GROUP BYscenarios, aggregate functions such asSUMandCOUNTare supported, and only simple columns can be used in the aggregate functions. The requirements forGROUP BYare as follows:- The
GROUP BYclause must be the standardGROUP BYsyntax;ROLLUPandHAVINGare not supported. - The
SELECTclause must include allGROUP BYcolumns. - The aggregate function does not contain the
DISTINCTkeyword.
- The
Materialized views using
MIN/MAXfunctions do not support real-time materialized views.Note
OceanBase AI Database supports using non-basic columns as parameters for the
MIN/MAXaggregate functions, and also supports using non-basic columns in theGROUP BYclause.
Example of aggregated incremental refresh for a single table
Create the table
test_tbl1.CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 INT, col3 INT, col4 INT);Create a materialized view with incremental refresh.
Create a materialized view named
mv1_test_tbl1. Specify the refresh method for the materialized view as incremental refresh, and allow manual triggering of refresh operations when needed. The query part of the materialized view selects columncol2from tabletest_tbl1and calculates the aggregate result ofsum(col3), grouped by the values in columncol2.CREATE MATERIALIZED VIEW mv1_test_tbl1 REFRESH FAST ON DEMAND AS SELECT col2, sum(col3) sum_col3 FROM test_tbl1 GROUP BY col2;Create a materialized view named
mv2_test_tbl1. Specify the refresh method for the materialized view as incremental refresh, and allow manual triggering of refresh operations when needed. The query part of the materialized view selects columnscol2andcol3from tabletest_tbl1and calculates the aggregate result ofsum(col3).CREATE MATERIALIZED VIEW mv2_test_tbl1 REFRESH FAST ON DEMAND AS SELECT col2, col3, sum(col3) sum_col3 FROM test_tbl1 GROUP BY col2, col3;Create a materialized view named
mv3_test_tbl1. Specify the refresh method for the materialized view as incremental refresh, and allow manual triggering of refresh operations when needed. The query part of the materialized view selects columncol2from tabletest_tbl1and calculates aggregate results such assum(col3)andavg(col3). It also computes some custom columnscalcol1andcalcol2, grouped by the values in columncol2.CREATE MATERIALIZED VIEW mv3_test_tbl1 REFRESH FAST ON DEMAND AS SELECT col2, sum(col3) sum_col3, avg(col3) avg_col3, avg(col3) * sum(col3)/col2 calcol1, col2+sum(col3) calcol2 FROM test_tbl1 GROUP BY col2;Create a materialized view named
mv4_test_tbl1. Specify the refresh method for the materialized view as incremental refresh, and allow manual triggering of refresh operations when needed. The query part of the materialized view selects columncol2from tabletest_tbl1and calculates aggregate results such assum(col3),count(col3*col3),sum(col3*col3), andSTDDEV(col3), grouped by the values in columncol2.CREATE MATERIALIZED VIEW mv4_test_tbl1 REFRESH FAST ON DEMAND AS SELECT col2, sum(col3) sum_col3, count(col3*col3) cnt_col3_2, sum(col3*col3) sum_col3_2, STDDEV(col3) stddev_col3 FROM test_tbl1 GROUP BY col2;Create a materialized view named
mv5_test_tbl1. Specify the refresh method for the materialized view as incremental refresh, and allow manual triggering of refresh operations when needed. The query part of the materialized view selects columnscol1andcol2from tabletest_tbl1and calculates the sum of the minimum value ofcol3and the maximum value ofcol4for each group, grouping the data by combinations ofcol1andcol2.CREATE MATERIALIZED VIEW mv5_test_tbl1 REFRESH FAST ON DEMAND AS SELECT col1, col2, MIN(col3) + MAX(col4) AS min_max_val FROM test_tbl1 GROUP BY col1, col2;Use non-basic columns in
GROUP BY.Create table
m_tbl1.obclient> CREATE TABLE m_tbl1(col1 INT PRIMARY KEY, col2 DATETIME, col3 INT, col4 INT, col5 INT, col6 INT);Create a materialized view
m_tbl1_mv1with single-table aggregation and incremental refresh.obclient> CREATE MATERIALIZED VIEW m_tbl1_mv1 REFRESH FAST ON DEMAND AS SELECT col3, DATE(col2) gby_2, MAX(col4) max_c4, MIN(col5 + col6) min_c5_c6 FROM m_tbl1 GROUP BY col3, DATE(col2);
Incremental refresh with multi-table joins
The basic requirements for incremental refresh with multi-table joins are as follows:
The
FROMtables must be base tables; they cannot be inline views, etc.The
FROMclause must contain at least two tables.Notice
- The incremental refresh feature for materialized views in OceanBase AI Database supports multi-table outer joins (
LEFT JOIN/RIGHT JOIN). - Limitations on outer joins: The join tree must be a left-deep join tree that is equivalent to
INNER JOINfirst, thenLEFT JOIN. - If the
SELECTclause of an incremental refresh materialized view contains aLEFT JOINoperator, specifying a primary key (PRIMARY KEY) or unique index (UNIQUE INDEX) during creation is prohibited to avoid refresh exceptions caused by constraint conflicts. - Materialized views with outer joins do not support real-time materialized views.
- The incremental refresh feature for materialized views in OceanBase AI Database supports multi-table outer joins (
The view definition must not contain subqueries.
The view definition must not contain clauses such as
ROLLUP,HAVING,WINDOW FUNCTION,DISTINCT,ORDER BY,LIMIT, andFETCH.The view definition must not use expressions that produce unstable output values such as
ROWNUM,RAND, andSYSDATE.
Example of incremental refresh with multi-table joins
Create base tables
tb1andtb2.CREATE TABLE tb1(c1 INT PRIMARY KEY, c2 INT, c3 INT);CREATE TABLE tb2(c1 INT PRIMARY KEY, c4 INT, c5 INT);Create an incremental refresh materialized view named
mv1_tb1_tb2that connects tablestb1andtb2.CREATE MATERIALIZED VIEW mv1_tb1_tb2 REFRESH FAST AS SELECT tb1.c1, tb1.c2, tb2.c4 FROM tb1 JOIN tb2 ON tb1.c1=tb2.c1;
Note
- To achieve optimal performance for simple join materialized views in both incremental refresh and real-time scenarios, it is recommended to create indexes on the materialized view and its dependent base tables as follows:
- Create an index on the join key of each table to improve join performance for incremental updates and real-time queries.
- Create indexes on the primary key columns of all base tables within the materialized view.
- An increase in the number of
JOINtables in a materialized view typically degrades both its incremental refresh performance and the query performance of real-time materialized views.
The following example demonstrates creating indexes for a materialized view and its dependent base tables:
(Optional) Use the following statement to delete the relevant test data.
Skip this step if the following database objects do not exist.
DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; DROP MATERIALIZED VIEW rt_mv1;Use the following statement to create table
t1and indexidx_t1_c2.CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT, c2 INT, c3 INT, c4 INT, c5 INT); CREATE INDEX idx_t1_c2 ON t1(c2);Use the following statement to create table
t2and indexidx_t2_c3.CREATE TABLE t2(c1 INT PRIMARY KEY AUTO_INCREMENT, c2 INT, c3 INT, c4 INT, c5 INT); CREATE INDEX idx_t2_c3 ON t2(c3);Use the following statement to create a real-time materialized view named
rt_mv1.CREATE MATERIALIZED VIEW rt_mv1 NEVER REFRESH ENABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE AS SELECT t1.c1 AS t1_c1, t2.c1 AS t2_c1, t1.c2 AS t1_c2, t2.c2 AS t2_c2, t1.c3 AS t1_c3, t2.c3 AS t2_c3 FROM t1, t2 WHERE t1.c2 = t2.c3;Use the following statement to create indexes on the primary key columns of all base tables within the materialized view.
CREATE INDEX idx_mv_t1_c1 ON rt_mv1(t1_c1); CREATE INDEX idx_mv_t2_c1 ON rt_mv1(t2_c1);
Incremental refresh with multi-table aggregation
The basic requirements for incremental refresh with multi-table aggregation are as follows:
- The basic requirements for incremental refresh with multi-table aggregation are the union of the basic requirements for incremental refresh with single-table aggregation and incremental refresh with multi-table join.
- Incremental refresh is supported for aggregate materialized views with outer joins. The limitations on outer joins are the same as those for non-aggregate outer join materialized views. The limitations on the aggregate part are the same as those for inner join aggregate materialized views. However, aggregate materialized views with outer joins do not support
MIN/MAXaggregate functions or real-time materialized views.
Example of incremental refresh with multi-table aggregation
Create base tables
t3andt4.CREATE TABLE t3(c1 INT, c2 INT, c3 INT, c4 INT, PRIMARY KEY(c1));CREATE TABLE t4(c1 INT, c2 INT, c3 INT, c4 INT, PRIMARY KEY(c1));Create a real-time materialized view
mv1_t3_t4for aggregated incremental refresh by joining tablest3andt4.CREATE MATERIALIZED VIEW mv1_t3_t4 REFRESH FAST ENABLE ON QUERY COMPUTATION AS SELECT t3.c1, SUM(t4.c4) sum_c4, AVG(t4.c4) avg_c4 FROM t3, t4 WHERE t3.c2 = t4.c3 GROUP BY t3.c1;
Incremental refresh of set queries
Set materialized views support UNION ALL set queries and incremental refresh. An incremental refresh of a set query allows any materialized view that supports incremental refresh to be used in the branches of the UNION ALL query, except outer-join materialized views.
The basic requirements for incremental refresh of set queries are as follows:
A materialized view with a set query does not support real-time materialized views.
The top-level query is
UNION ALL, the view definition does not contain subqueries, and there are noORDER BY/LIMIT/FETCHclauses.The output column types at the same projection position must be the same across all branches of the
UNION ALL. Forced type conversion to a consistent type usingCASTin sub-branches is not supported.For example, a multi-table join materialized view requires that the primary keys of all joined tables appear in the
SELECTstatement. TheseSELECToutput columns cannot have additionalCASTadded due toUNION ALL.Each branch of the
UNION ALLhas constants of the same type/value at the same projection position; constants serve as identifier columns to distinguish between branches.
Example of incremental refresh for a set query
Create base tables
ua_tbl1andua_tbl2.CREATE TABLE ua_tbl1 (col1 INT PRIMARY KEY, col2 INT, col3 INT, col4 INT);CREATE TABLE ua_tbl2 (col1 INT PRIMARY KEY, col2 INT, col3 INT, col4 INT);Create a materialized view
mv_ua_tbl1_tbl2with incremental refresh for set queries.CREATE MATERIALIZED VIEW mv_ua_tbl1_tbl2 REFRESH FAST ON DEMAND AS SELECT a.col1 as a_c1, b.col1 as b_c1, a.col2 val FROM ua_tbl1 a INNER JOIN ua_tbl2 b ON a.col2 = b.col3 UNION ALL SELECT col1 a_c1, col2 b_c1, count(*) val FROM ua_tbl1 GROUP BY col1, col2;
Parallelism control mechanism for materialized view refreshes
OceanBase AI Database supports controlling the parallelism of materialized view refresh operations:
Refresh parallelism:
- Explicitly specified refresh parallelism: If the refresh parallelism is specified in the materialized view definition using
REFRESH ... PARALLEL n, the explicitly specified value is used during the refresh operation. - Implicitly specified refresh parallelism: If the refresh parallelism is not explicitly specified, the system uses the value of the
mview_refresh_dopsystem variable set in the current session as the default parallelism to control the concurrency level of the refresh operation.
- Explicitly specified refresh parallelism: If the refresh parallelism is specified in the materialized view definition using
View materialized view parallelism information
Use the DBA_MVIEWS view to query the background refresh parallelism set for a materialized view.
Note
The
DBA_MVIEWSview only shows the parallelism specified for the materialized view.- If the
REFRESH_DOPfield is non-zero, the materialized view's background refresh task will use the parallelism corresponding toREFRESH_DOP. - When
REFRESH_DOPis 0, the value of the globally setmview_refresh_dopis used.
- If the
Use the DBA_MVREF_RUN_STATS view to query the historical refresh parallelism of a materialized view.
Example:
Create a materialized view named
mv0_t1.obclient> CREATE MATERIALIZED VIEW mv0_t1 REFRESH COMPLETE ON DEMAND START WITH sysdate() NEXT sysdate() + INTERVAL 10 SECOND AS SELECT c1, c2 FROM t1; ```Query the background refresh parallelism set for the materialized view
mv0_t1.obclient> SELECT OWNER, MVIEW_NAME,REFRESH_DOP FROM oceanbase.DBA_MVIEWS WHERE OWNER = 'db_test' AND MVIEW_NAME = 'mv0_t1';The result is as follows:
+---------+------------+-------------+ | OWNER | MVIEW_NAME | REFRESH_DOP | +---------+------------+-------------+ | db_test | mv0_t1 | 0 | +---------+------------+-------------+ 1 row in setChange the refresh parallelism for the materialized view
mv0_t1to 8.obclient> ALTER MATERIALIZED VIEW mv0_t1 REFRESH PARALLEL 8;Query the background refresh parallelism set for the materialized view
mv0_t1.obclient> SELECT OWNER, MVIEW_NAME,REFRESH_DOP FROM oceanbase.DBA_MVIEWS WHERE OWNER = 'db_test' AND MVIEW_NAME = 'mv0_t1';The result is as follows:
+---------+------------+-------------+ | OWNER | MVIEW_NAME | REFRESH_DOP | +---------+------------+-------------+ | db_test | mv0_t1 | 8 | +---------+------------+-------------+ 1 row in set
Set mview_refresh_dop
mview_refresh_dop is a system variable in OceanBase AI Database that controls the default parallelism for materialized view refresh operations. By setting its value appropriately, you can significantly improve refresh efficiency and optimize database performance.
When mview_refresh_dop is set to 0 or 1, parallel refresh capability is not used. For more information about the system variable mview_refresh_dop, see mview_refresh_dop.
Example:
Set the current session's parallelism to 5.
SET mview_refresh_dop = 5;Set the global session parallelism to 5.
SET GLOBAL mview_refresh_dop = 5;Note
Setting a global-level variable is invalid for the current session. It takes effect only after you log off and establish a new session.
Manually refresh a materialized view
When the refresh mode of a materialized view is ON DEMAND, you can use DBMS_MVIEW.REFRESH to manually refresh it. For a materialized view defined for incremental refresh, you can specify a full refresh during manual refresh.
Note
Only the owner of the materialized view or a tenant administrator can perform the refresh operation.
The syntax is as follows:
DBMS_MVIEW.REFRESH (
mv_name IN VARCHAR(65535)
[, method IN VARCHAR(65535) DEFAULT NULL]
[, refresh_parallel IN INT DEFAULT 0]
[, nested IN BOOLEAN DEFAULT FALSE]
[, async IN BOOLEAN DEFAULT FALSE]
The parameters are described as follows:
Parameters |
Note |
|---|---|
| mv_name | The name of the materialized view to be refreshed. |
| method | Defines the refresh strategy of the materialized view. You can specify the default strategy when creating the materialized view. Valid values:
|
| refresh_parallel | The concurrency of the refresh operation, that is, the number of threads that perform the refresh operation simultaneously. |
| nested | Specifies the refresh mode for nested materialized views. Valid values:
|
| async | Specifies whether to perform asynchronous refresh. Valid values:
|
For more information about DBMS_MVIEW.REFRESH, see REFRESH.
Example:
Insert
3rows of data into thetest_tbl1table.INSERT INTO test_tbl1 VALUES (1, 1, 1, 1),(2, 2, 2, 2),(3, 3, 3, 3);View the information of the materialized view
mv1_test_tbl1.SELECT * FROM mv1_test_tbl1;The query result is as follows:
Empty setManually refresh the materialized view
mv1_test_tbl1usingDBMS_MVIEW.REFRESH.Refresh the materialized view using the refresh options set for the materialized view:
CALL DBMS_MVIEW.REFRESH('mv1_test_tbl1');Refresh the materialized view by specifying the refresh options:
CALL DBMS_MVIEW.REFRESH('mv1_test_tbl1', 'c');
View the information of the materialized view
mv1_test_tbl1again.SELECT * FROM mv1_test_tbl1;The query result is as follows:
+------+------+----------+----------+ | col2 | cnt | cnt_col3 | sum_col3 | +------+------+----------+----------+ | 1 | 1 | 1 | 1 | | 2 | 1 | 1 | 2 | | 3 | 1 | 1 | 3 | +------+------+----------+----------+ 3 rows in set
Manually refresh a materialized view and set the refresh parallelism
For manual materialized view refreshes, you can set the system variable mview_refresh_dop to specify the default parallelism for materialized view refresh operations.
You can also explicitly set the refresh parallelism for the current operation by specifying refresh_parallel when calling the REFRESH function.
Notice
If the refresh parallelism is not explicitly specified and the mview_refresh_dop variable is set to 0 or 1, parallel refresh is disabled.
Example:
Set the current session's parallelism to 5.
SET mview_refresh_dop = 5;Manually refresh a materialized view:
Explicitly set the refresh parallelism to 8. This refresh will run with a parallelism of 8.
CALL DBMS_MVIEW.REFRESH('mv1', 'c', 8);The refresh parallelism is not explicitly specified, so it defaults to the session variable value. This refresh will run with a parallelism of 5.
CALL DBMS_MVIEW.REFRESH('mv1', 'c');
Automatic refresh of materialized views
When creating a materialized view, if you specify the START WITH datetime_expr and NEXT datetime_expr clauses, the system creates a background automatic refresh task for the materialized view when the specified conditions are met.
Note
You can view the status of materialized view background refresh tasks through the DBA_SCHEDULER_JOBS view. For more information about viewing materialized views, see Query materialized views.
Parallelism for automatic materialized view refresh
When automatically refreshing materialized views in the background, you can specify the parallelism using one of the following two methods:
Note
The following priority order applies to the parallelisms.
Specify the refresh parallelism when creating the materialized view.
Example:
CREATE MATERIALIZED VIEW mv_t1 REFRESH COMPLETE PARALLEL 8 ON DEMAND START WITH sysdate() NEXT sysdate() + INTERVAL 10 SECOND AS SELECT c1, c2 FROM t1;Set the global session variable
mview_refresh_dopas the refresh parallelism.Automatic refresh operations are performed by internal sessions. To make this effective for background sessions, you must set the Global-level mview_refresh_dop.
Notice
If the parallelism is not explicitly specified when creating the materialized view, and the
mview_refresh_dopvariable is 0 or 1, parallel refresh is not enabled for the background refresh task.Example:
Set the global session parallelism to 5.
SET GLOBAL mview_refresh_dop = 5;Create the materialized view:
If the refresh parallelism is specified as 8 when creating the materialized view, the background refresh task will use 8 as the refresh parallelism.
CREATE MATERIALIZED VIEW mv1_t1 REFRESH COMPLETE PARALLEL 8 ON DEMAND START WITH sysdate() NEXT sysdate() + INTERVAL 10 SECOND AS SELECT c1, c2 FROM t1;If the refresh parallelism is not specified when creating the materialized view, the background refresh task will use the value of the
mview_refresh_dopvariable, which is 5, as the refresh parallelism.CREATE MATERIALIZED VIEW mv2_t1 REFRESH COMPLETE ON DEMAND START WITH sysdate() NEXT sysdate() + INTERVAL 10 SECOND AS SELECT c1, c2 FROM t1;
Refresh a nested materialized view
Note
OceanBase AI Database supports creating nested materialized views based on materialized views without primary keys.
Notice
Nested materialized views cannot be created as real-time materialized views. That is, you cannot specify the ENABLE ON QUERY COMPUTATION clause when creating a nested materialized view.
Refresh rules for nested materialized views
Nested materialized views support the same refresh methods as non-nested materialized views, including complete and incremental refreshes. Although refreshing a nested materialized view only requires its directly dependent user tables and materialized views (and their mlogs), since a nested materialized view is built upon other materialized views, its data consistency depends on those underlying views. This means that to keep the data in a nested materialized view up to date through refresh, you must first ensure that the data in all its dependent materialized views is also up to date, i.e., you need to refresh them first.
OceanBase AI Database supports cascading refresh for nested materialized views. Cascading refresh is divided into cascading non-consistent refresh and cascading consistent refresh:
Cascading non-consistent refresh: Refreshes all materialized views that a nested materialized view depends on from bottom to top. The refresh of each materialized view does not guarantee data consistency, and the data timestamps read from the dependent base tables are inconsistent. Cascading non-consistent refresh is suitable for batch synchronization scenarios, such as when business systems periodically synchronize data from upstream sources. After data synchronization is complete, cascading non-consistent refresh can be used to maintain the final consistency of the materialized views.
Cascading consistent refresh: A snapshot-consistent cascading refresh that ensures that after the entire cascading refresh is complete, all base tables seen by the upper-level materialized views have consistent data timestamps. Cascading consistent refresh is suitable for real-time data synchronization scenarios, as it guarantees that after each cascading refresh, the data snapshots in the materialized views are at the same timestamp.
Example of refreshing a nested materialized view
If a materialized view undergoes a complete refresh, any materialized views that depend on it must first undergo a complete refresh before they can be refreshed incrementally later on; otherwise, an error will be reported.
Example:
Create table
tbl1and insert a row of data.CREATE TABLE tbl1(id INT, name VARCHAR(30), PRIMARY KEY(id));INSERT INTO tbl1 VALUES (1, 'jack');Create table
tbl2and insert a row of data.CREATE TABLE tbl2(id INT, age INT, PRIMARY KEY(id));INSERT INTO tbl2 VALUES (1, 21);Create materialized view
mv1based ontbl1andtbl2.CREATE MATERIALIZED VIEW mv1 (PRIMARY KEY (id1, id2)) REFRESH FAST ON DEMAND AS SELECT tbl1.id id1, tbl2.id id2, tbl1.NAME, tbl2.AGE FROM tbl1, tbl2 WHERE tbl1.id = tbl2.id;Create a materialized view (nested materialized view)
mv2based on materialized viewmv1.CREATE MATERIALIZED VIEW mv2 REFRESH FAST AS SELECT COUNT(*) cnt, COUNT(AGE) age_cnt, SUM(AGE) age_sum FROM mv1;Query data from the materialized view
mv1.SELECT * FROM mv1;The query result is as follows:
+------+------+------+------+ | id1 | id2 | NAME | AGE | +------+------+------+------+ | 1 | 1 | jack | 21 | +------+------+------+------+ 1 row in setQuery data from the materialized view
mv2.SELECT * FROM mv2;The query result is as follows:
+------+---------+---------+ | cnt | age_cnt | age_sum | +------+---------+---------+ | 1 | 1 | 21 | +------+---------+---------+ 1 row in setInsert one row of data into each of the tables
tbl1andtbl2.INSERT INTO tbl1 VALUES (2, 'rose');INSERT INTO tbl2 VALUES (2, 19);Perform an incremental refresh on the materialized view
mv1.CALL dbms_mview.refresh('mv1', 'f');Query data from the materialized view
mv1.SELECT * FROM mv1;The query result is as follows:
+------+------+------+------+ | id1 | id2 | NAME | AGE | +------+------+------+------+ | 1 | 1 | jack | 21 | | 2 | 2 | rose | 19 | +------+------+------+------+ 2 rows in setPerform an incremental refresh on the materialized view
mv2.CALL dbms_mview.refresh('mv2', 'f');Query data from the materialized view
mv2.SELECT * FROM mv2;The query result is as follows:
+------+---------+---------+ | cnt | age_cnt | age_sum | +------+---------+---------+ | 2 | 2 | 40 | +------+---------+---------+ 1 row in setInsert one row of data into each of the tables
tbl1andtbl2again.INSERT INTO tbl1 VALUES (3, 'mary');INSERT INTO tbl2 VALUES (3, 25);Perform a complete refresh on materialized view
mv1.CALL dbms_mview.refresh('mv1', 'c');View the data in materialized view
mv1again.SELECT * FROM mv1;The result is as follows:
+------+------+------+------+ | id1 | id2 | NAME | AGE | +------+------+------+------+ | 1 | 1 | jack | 21 | | 2 | 2 | rose | 19 | | 3 | 3 | mary | 25 | +------+------+------+------+ 3 rows in setPerform an incremental refresh on materialized view
mv2again.CALL dbms_mview.refresh('mv2', 'f');The result is as follows:
ERROR 9760 (HY000): cannot fast refresh materialized viewNote
Since
mv1has undergone a complete refresh, directly performing an incremental refresh onmv2will cause an error. A complete refresh must be performed first.View the data in materialized view
mv2again.SELECT * FROM mv2;The result is as follows:
+------+---------+---------+ | cnt | age_cnt | age_sum | +------+---------+---------+ | 2 | 2 | 40 | +------+---------+---------+ 1 row in setPerform a complete refresh on materialized view
mv2.CALL dbms_mview.refresh('mv2', 'c');View the data in materialized view
mv2again.SELECT * FROM mv2;The result is as follows:
+------+---------+---------+ | cnt | age_cnt | age_sum | +------+---------+---------+ | 3 | 3 | 65 | +------+---------+---------+ 1 row in set
Materialized view refresh monitoring information
OceanBase AI Database supports monitoring materialized view refresh operations, providing the execution status and performance data of current and historical refresh tasks. This monitoring information helps evaluate refresh efficiency, identify potential performance bottlenecks, and provide a basis for subsequent tuning.
Monitoring features:
- Reporting: Provides an overview of current and historical materialized view refresh operations, including key metrics such as the actual time spent on each refresh, facilitating tracking and monitoring of refresh performance.
- Diagnostics: Through detailed refresh records and performance data, you can deeply analyze the causes of materialized view refresh delays or performance degradation. For example, you can determine whether it is due to increased system load or a larger volume of data changes that prolongs the refresh time.
Hierarchical diagnostic report (REFRESH_REPORT)
OceanBase AI Database supports the subprogram DBMS_MVIEW.REFRESH_REPORT, which generates a hierarchical diagnostic report for a single materialized view refresh task, helping users with performance tuning and troubleshooting.
The report is output in XML/JSON format. For more information about hierarchical diagnostic reports, see REFRESH_REPORT.
Example:
Retrieve the diagnostic report in XML format by using refresh_id.
obclient> SELECT DBMS_MVIEW.REFRESH_REPORT(refresh_id=> 100023);
Collection of statistics
To improve query performance and optimize execution plans, OceanBase AI Database supports collecting statistics on the data distribution of tables and indexes. For materialized views, their data distribution can also be collected through a similar mechanism, helping the database better perform query optimization.
You can collect statistics for materialized views by using the following methods:
- Use the
ANALYZE TABLEstatement. For more information, see ANALYZE. - Call the
DBMS_STATS.GATHER_TABLE_STATS('database_name', 'table_name')system procedure to collect statistics. For more information, see GATHER_TABLE_STATS.
Example:
Collect statistics for the materialized view mv2.
obclient> ANALYZE TABLE mv2 UPDATE HISTOGRAM ON cnt,age_cnt,age_sum WITH 15 BUCKETS;
Materialized view-related views
View name |
Feature description |
|---|---|
| DBA_MVIEWS | Displays materialized view information. |
| DBA_MVREF_STATS_SYS_DEFAULTS | The system-wide default value for the materialized view refresh history statistics attribute. |
| DBA_MVREF_STATS_PARAMS | Displays the refresh statistics attributes associated with each materialized view. |
| DBA_MVREF_RUN_STATS | Displays information about each refresh operation of a materialized view, with each operation identified by a REFRESH_ID. |
| DBA_MVREF_STATS | Displays basic timing statistics for materialized view refreshes. |
| DBA_MVREF_CHANGE_STATS | Displays statistics related to materialized view refresh. |
| DBA_MVREF_STMT_STATS | Displays information associated with the refresh statement. |
| DBA_SCHEDULER_JOBS | Displays information about all scheduler jobs in the database. |