Materialized views can be used to accelerate queries. When a query is executed on the base table, the system automatically rewrites it into a query that uses an existing materialized view. The principle behind this method is to match the query statement against the definitions of materialized views. If a matching materialized view is found, the query is automatically rewritten to use that materialized view, which significantly improves query performance and efficiency.
Limitations on materialized view query rewrite
The materialized view must meet the following requirements:
- The
ENABLE QUERY REWRITEclause must be specified when creating the materialized view. - The materialized view must contain only the
SELECT,JOIN,WHERE, andGROUP BYclauses, that is, it must be an SPJG query. Materialized views that do not meet this condition will not cause an error but will not be used for rewrite.
- The
The current query must meet the following requirements:
- The query must be a
SELECTstatement, not a set query or hierarchical query, and must not contain window functions. - The
FROMclause must include all tables present in the materialized view'sFROMclause, can include tables not present in the materialized view, and the join order must be compatible. - The
WHEREcondition of the materialized view must be a subset of the current query'sWHEREcondition. If the current query involves aggregation, the conditions must match exactly. For example, if the materialized view'sWHEREisc1 > 10and the current query'sWHEREisc1 > 10 AND c2 > 20, then the materialized view's condition {c1 > 10} is a subset of the current query's condition {c1 > 10, c2 > 20}. - If the query does not involve aggregation, the materialized view must also not involve aggregation.
- If the query involves aggregation, the materialized view must either not involve aggregation or contain all
GROUP BYcolumns of the query. If a materialized view with aggregation is used for rewrite, theFROMandWHEREclauses must match exactly. - All columns referenced in the
SELECTlist,WHERE,HAVING, andGROUP BYclauses of the current query must be included in theSELECTstatement of the materialized view.
- The query must be a
Rule and cost checking
During query rewrite, the current version of OceanBase AI Database does not perform cost checking. However, rule checking requires that the rewritten WHERE condition can use an index on at least one materialized view. Additionally, when more than 10 materialized views exist, the materialized view query rewrite will only attempt to use the first 10 materialized views.
Materialized view rewrite
The system variables that control materialized view query rewrite are as follows:
query_rewrite_enabled: Specifies whether to enable materialized view rewrite. For more information about this variable, see query_rewrite_enabled.Example:
SET query_rewrite_enabled = 'force';query_rewrite_integrity: Specifies the data consistency check level for materialized view rewrite. For more information about this variable, see query_rewrite_integrity.Example:
SET query_rewrite_integrity = 'stale_tolerated';
The following example demonstrates materialized view rewrite:
Create table
test_tbl1.CREATE TABLE test_tbl1 (col1 INT, col2 INT, col3 INT);Create table
test_tbl2.CREATE TABLE test_tbl2 (col1 INT, col2 INT, col3 INT);Create a materialized view named
mv_test_tbl1_tbl2and enable automatic rewrite for this materialized view.CREATE MATERIALIZED VIEW mv_test_tbl1_tbl2 ENABLE QUERY REWRITE AS SELECT t1.col1 col1, t1.col2 t1col2, t1.col3 t1col3, t2.col2 t2col2, t2.col3 t2col3 FROM test_tbl1 t1, test_tbl2 t2 WHERE t1.col1 = t2.col1;When the query
SELECT count(*), test_tbl1.col1 col1 FROM test_tbl1, test_tbl2 WHERE test_tbl1.col1 = test_tbl2.col1 AND test_tbl2.col2 > 10 GROUP BY col1;is executed, materialized view query rewrite occurs.SELECT count(*), test_tbl1.col1 col1 FROM test_tbl1, test_tbl2 WHERE test_tbl1.col1 = test_tbl2.col1 AND test_tbl2.col2 > 10 GROUP BY col1; MV REWRITE ==> SELECT count(*), mv_test_tbl1_tbl2.col1 col1 FROM mv_test_tbl1_tbl2 WHERE mv_test_tbl1_tbl2.t2col2 > 10 GROUP BY mv_test_tbl1_tbl2.col1;
Materialized view query rewrite control
Materialized view query rewrite control includes two hints: MV_REWRITE and NO_MV_REWRITE. These two hints have a higher priority than the system variable query_rewrite_enabled.
MV_REWRITE
The syntax for MV_REWRITE is as follows:
/*+ MV_REWRITE (@ queryblock [mv_name_list]) */
mv_name_list:
mv_name [, mv_name ...]
When using the MV_REWRITE hint alone, it skips the rule/cost checks for materialized view query rewrite and directly applies any available rewrite. When one or more materialized views are specified after the hint, in addition to skipping the rule/cost checks, the materialized view query rewrite will only attempt to use the specified materialized views, ignoring all unspecified ones.
When using the MV_REWRITE hint to specify materialized views, you cannot force the use of a materialized view without the ENABLE QUERY REWRITE clause (which enables automatic rewrite for the current materialized view), nor can you force the use of a non-real-time materialized view when the system variable query_rewrite_integrity is set to enforced.
NO_MV_REWRITE
The syntax for NO_MV_REWRITE is as follows:
/*+ NO_MV_REWRITE (@ queryblock) */
Prohibits materialized view query rewrite. You can specify the query block.
Examples of using materialized view query rewrite control hints
Create the base table
tbl2.CREATE TABLE tbl2 (col1 INT, col2 INT);Insert two rows of data into the base table
tbl2.INSERT INTO tbl2 VALUES (1,2),(3,4);The result is as follows:
Query OK, 2 rows affected Records: 2 Duplicates: 0 Warnings: 0Create the materialized view
mv1_tbl2and enable automatic rewrite for the current materialized view.CREATE MATERIALIZED VIEW mv1_tbl2 NEVER REFRESH ENABLE QUERY REWRITE AS SELECT * FROM tbl2;Create the materialized view
mv2_tbl2and enable automatic rewrite for the current materialized view.CREATE MATERIALIZED VIEW mv2_tbl2 NEVER REFRESH ENABLE QUERY REWRITE AS SELECT * FROM tbl2 WHERE tbl2.col1 > 1;Set the system variable
query_rewrite_integritytostale_tolerated.Note
The
MV_REWRITEandNO_MV_REWRITEhints have a higher priority than the system variablequery_rewrite_enabled. Therefore, you do not need to setquery_rewrite_enabled. However, you must setquery_rewrite_integritytostale_toleratedto use non-real-time materialized views for rewriting.SET query_rewrite_integrity = 'stale_tolerated';Use the
MV_REWRITEhint to attempt rewriting with materialized views and skip the cost/rule check. Both of the following queries will use the materialized viewmv1_tbl2for rewriting./*+mv_rewrite*/attempts to use a materialized view that meets the rewrite conditions. Once a suitable materialized view is found, subsequent ones are not considered, and the cost/rule check is skipped.EXPLAIN SELECT /*+mv_rewrite*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 > 1 GROUP BY col1;The result is as follows:
+------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------+ | ===================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------- | | |0 |HASH GROUP BY | |1 |3 | | | |1 |└─TABLE FULL SCAN|mv1_tbl2|1 |3 | | | ===================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT(*)], [mv1_tbl2.col1]), filter(nil), rowset=16 | | group([mv1_tbl2.col1]), agg_func([T_FUN_COUNT(*)]) | | 1 - output([mv1_tbl2.col1]), filter([mv1_tbl2.col1 > 1]), rowset=16 | | access([mv1_tbl2.col1]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([mv1_tbl2.__pk_increment]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------+ 14 rows in set/*+mv_rewrite(mv2_tbl2)*/attempts to usemv2_tbl2for rewriting and skips the cost/rule check.EXPLAIN SELECT /*+mv_rewrite(mv2_tbl2)*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 > 1 GROUP BY col1;The result is as follows:
+-------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------+ | ===================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | ----------------------------------------------------- | | |0 |HASH GROUP BY | |1 |3 | | | |1 |└─TABLE FULL SCAN|mv2_tbl2|1 |3 | | | ===================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT(*)], [mv2_tbl2.col1]), filter(nil), rowset=16 | | group([mv2_tbl2.col1]), agg_func([T_FUN_COUNT(*)]) | | 1 - output([mv2_tbl2.col1]), filter(nil), rowset=16 | | access([mv2_tbl2.col1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([mv2_tbl2.__pk_increment]), range(MIN ; MAX)always true | +-------------------------------------------------------------------------+ 14 rows in set
Although the query specifies to use
mv2_tbl2for query rewriting, theWHEREcondition of the query does not meet the requirements, somv2_tbl2cannot be used for query rewriting. Therefore, this query will not undergo materialized view query rewriting.EXPLAIN SELECT /*+mv_rewrite(mv2_tbl2)*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 < 1 GROUP BY col1;The result is as follows:
+------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------+ | ================================================= | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ------------------------------------------------- | | |0 |HASH GROUP BY | |1 |3 | | | |1 |└─TABLE FULL SCAN|tbl2|1 |3 | | | ================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT(*)], [tbl2.col1]), filter(nil), rowset=16 | | group([tbl2.col1]), agg_func([T_FUN_COUNT(*)]) | | 1 - output([tbl2.col1]), filter([tbl2.col1 < 1]), rowset=16 | | access([tbl2.col1]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([tbl2.__pk_increment]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------+ 14 rows in setUsing the
/*+ no_mv_rewrite*/hint prevents materialized view query rewriting.EXPLAIN SELECT /*+no_mv_rewrite*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 > 1 GROUP BY col1;The result is as follows:
+------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------+ | ================================================= | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ------------------------------------------------- | | |0 |HASH GROUP BY | |1 |3 | | | |1 |└─TABLE FULL SCAN|tbl2|1 |3 | | | ================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT(*)], [tbl2.col1]), filter(nil), rowset=16 | | group([tbl2.col1]), agg_func([T_FUN_COUNT(*)]) | | 1 - output([tbl2.col1]), filter([tbl2.col1 > 1]), rowset=16 | | access([tbl2.col1]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([tbl2.__pk_increment]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------+ 14 rows in set
Usage scenarios for materialized view query rewrite
OceanBase AI Database's materialized view rewrite supports FROM join compatibility with non-aggregated materialized views, rewriting queries that contain tables not present in the materialized view. It also supports rewriting with aggregated materialized views and aggregation rollup.
Note
The query statement in the following example creates a fully refreshed materialized view. If an incremental refreshed materialized view query statement meets the corresponding conditions, it also supports query rewriting in the same scenario.
Join order compatibility
Materialized view rewrite can be used when the join order between the materialized view and the FROM clause of the query is different, but the two join orders are equivalent after transformation.
Materialized view rewrite uses a conflict detector to check whether the query's join order is compatible with the materialized view.
Example:
Query statement in the materialized view:
SELECT t1.c1 t1c1, t2.c1 t2c1, t3.c1 t3c1
FROM t1 INNER JOIN t2 ON t1.c1 = t2.c1
INNER JOIN t3 ON t1.c1 = t3.c1;
Current query statement:
SELECT t1.c1, t2.c1, t3.c1
FROM t1 INNER JOIN t3 ON t1.c1 = t3.c1, t2
WHERE t1.c1 = t2.c1;
Rewritten query statement:
SELECT mv.t1c1, mv.t2c1, mv.t3c1
FROM mv;
Join type compatibility
Materialized view rewrite can be used when the join type in the materialized view is different from the query's, but compatibility can be achieved by adding compensatory predicates.
Join type compatibility requires the following conditions to be met for rewriting:
- The materialized view's join includes more null-filling rows. For example, if the query requires
INNER JOIN, the materialized view isLEFT OUTER JOINorFULL OUTER JOIN. - There is a non-null column at the current join node that can be used to filter out null-filling rows.
- The filtering predicate at the current node can be pulled up to the
WHEREclause.
Example:
Query statement in the materialized view:
SELECT t1.c1 t1c1, t2.pk t2pk
FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
Current query statement:
SELECT t1.c1
FROM t1, t2
WHERE t1.c1 = t2.c1;
Rewritten query statement:
SELECT mv.t1c1
FROM mv
WHERE mv.t2pk IS NOT NULL;
Querying tables not present in a materialized view
When a query contains tables that are not present in the materialized view, the query rewrite process checks the join order to see if the extra tables can be joined after the materialized view. If so, the query is rewritten.
Example:
Query statement in the materialized view:
SELECT t1.c1 t1c1, t1.pk t1pk
FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
Current query statement:
SELECT t1.c1
FROM t1, t2, t3
WHERE t1.c1 = t2.c1
AND t3.c1 = t1.c1;
Rewritten query statement:
SELECT mv.t1c1
FROM mv, t3
WHERE t3.c1 = mv.t1c1
AND mv.t1pk IS NOT NULL;
Rewrite with aggregate materialized views
If rewriting is performed using a materialized view with aggregates, the query must be compatible with the FROM clause order of the materialized view, and the query must not contain tables not present in the materialized view. The WHERE clause of the query must also exactly match that of the materialized view.
Example:
Query statement in the materialized view:
SELECT SUM(c1) sumc1, c2, c3
FROM t1
WHERE c1 > 10
GROUP BY c2, c3;
Current query statement:
SELECT SUM(c1), c2, c3
FROM t1
WHERE c1 > 10
GROUP BY c2, c3;
Rewritten query statement:
SELECT mv.sumc1, mv.c2, mv.c3
FROM mv;
Aggregation rollup rewrite
If the aggregate columns in the materialized view are different from those in the query, the materialized view can be used for rewrite when the query's aggregate columns are a subset of the materialized view's aggregate columns. In this case, only partial aggregate functions are supported; other requirements remain consistent with those for rewriting with a materialized view containing aggregates.
Aggregate functions supported for aggregation rollup: COUNT, SUM, BIT_AND, BIT_OR, BIT_XOR, MIN, MAX.
Example:
Query statement in the materialized view:
SELECT SUM(c1) sumc1, COUNT(c1) cntc1, c2, c3
FROM t1
WHERE c1 > 10
GROUP BY c2, c3;
Original query statement:
SELECT SUM(c1), COUNT(c1), c2
FROM t1
WHERE c1 > 10
AND c3 = 10
GROUP BY c2;
Rewritten query statement:
SELECT SUM(mv.sumc1), COUNT_SUM(mv.cntc1), mv.c2
FROM mv
WHERE mv.c3 = 10
GROUP BY mv.c2;