Rewrite queries based on materialized views

You can use materialized views to speed up queries. When you enter a query statement without specifying materialized views, the system automatically matches the query statement against definitions of materialized views and rewrites it into a query statement that contains the matching materialized view. This can significantly improve query performance and efficiency.

Limitations

• A materialized view eligible for query rewriting must meet the following requirements:

  • The ENABLE QUERY REWRITE clause was specified for the materialized view when it was created.
  • The materialized view contains only the SELECT, JOIN, WHERE, and GROUP BY clauses, namely, select-project-join (SPJ) queries. If a matching materialized view does not meet the requirements, it is not used for query rewriting, but the system does not return an error.

• The query statement must meet the following requirements:

  • The query statement must be a SELECT statement that does not contain window functions, and cannot be a set-based or hierarchical statement.
  • The FROM clause contains all tables of the materialized view, and the join order or join type in the FROM clause is different from that of the materialized view. The FROM clause can also contain tables not involved in the materialized view.
  • The WHERE condition in the materialized view is a subset of that in the query statement. If the query statement contains an aggregate function, the WHERE conditions must exactly match. For example, the WHERE condition in the materialized view is c1 > 10, and the WHERE condition in the query statement is c1 > 10 AND c2 > 20. In this case, the condition {c1 > 10} in the materialized view is a subset of the condition {c1 > 10 AND c2 > 20} in the query statement.
  • If the query does not contain aggregate functions, the materialized view must not involve aggregation.
  • If the query contains aggregate functions, the materialized view must not involve aggregation or must contain all GROUP BY columns of the query. If an aggregate materialized view is used for rewriting a query, the FROM and WHERE clauses must exactly match.
  • The columns in the SELECT, WHERE, HAVING, and GROUP BY clauses in the query statement must all be included in the SELECT clause of the materialized view.

Rule and cost check

In OceanBase Database V4.3.3, the system does not check the cost during query rewriting. However, the rule check requires that the WHERE condition in the rewritten query statement use an index on at least one materialized view. If more than 10 materialized views are available, the system attempts to use only the first 10 materialized views during query rewriting.

Rewrite a query based on a materialized view

The system variables that control materialized view-based query rewriting are as follows:

• query_rewrite_enabled: specifies whether to enable materialized view-based query rewriting. For more information about this variable, see query_rewrite_enabled.

  Here is an example:

  SET query_rewrite_enabled = 'force';
  

• query_rewrite_integrity: specifies the level of data consistency check for materialized view-based query rewriting. For more information about this variable, see query_rewrite_integrity.

  Here is an example:

  SET query_rewrite_integrity = 'stale_tolerated';
  

Here is an example of materialized view-based query rewriting:

1. Create a table named test_tbl1.

   CREATE TABLE test_tbl1 (col1 INT, col2 INT, col3 INT);
   

2. Create a table named test_tbl2.

   CREATE TABLE test_tbl2 (col1 INT, col2 INT, col3 INT);
   

3. Create a materialized view named mv_test_tbl1_tbl2 with the ENABLE QUERY REWRITE clause specified.

   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;
   

4. Enter the 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; statement. The system automatically rewrites it.

   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;
   

Control materialized view-based query rewriting

You can use the MV_REWRITE and NO_MV_REWRITE hints to control materialized view-based query rewriting. The two hints have a higher priority than the query_rewrite_enabled variable.

MV_REWRITE hint

The syntax of the MV_REWRITE hint is as follows:

/*+ MV_REWRITE (@ queryblock [mv_name_list]) */

mv_name_list:
    mv_name [, mv_name ...]

When you use the MV_REWRITE hint alone, the system skips rule and cost check and uses all available materialized views for query rewriting. If you specify one or more materialized views after the hint, the system skips rule and cost check and uses only the specified materialized views for query rewriting.

When you use the MV_REWRITE hint to specify materialized views, you cannot forcibly use materialized views created without the ENABLE QUERY REWRITE clause or forcibly use non-real-time materialized views when the query_rewrite_integrity variable is set to enforced.

NO_MV_REWRITE hint

The syntax of the NO_MV_REWRITE hint is as follows:

/*+ NO_MV_REWRITE (@ queryblock) */

You can specify a query block in this hint.

Examples

1. Create a base table named tbl2.

   CREATE TABLE tbl2 (col1 INT, col2 INT);
   

2. Insert two records into the tbl2 table.

   INSERT INTO tbl2 VALUES (1,2),(3,4);
   

   The return result is as follows:

   Query OK, 2 rows affected
   Records: 2  Duplicates: 0  Warnings: 0
   

3. Create a materialized view named mv1_tbl2 with the ENABLE QUERY REWRITE clause specified.

   CREATE MATERIALIZED VIEW mv1_tbl2 NEVER REFRESH ENABLE QUERY REWRITE AS SELECT * FROM tbl2;
   

4. Create a materialized view named mv2_tbl2 with the ENABLE QUERY REWRITE clause specified.

   CREATE MATERIALIZED VIEW mv2_tbl2 NEVER REFRESH ENABLE QUERY REWRITE AS SELECT * FROM tbl2 WHERE tbl2.col1 > 1;
   

5. Set the query_rewrite_integrity variable to stale_tolerated.

   Note

   The MV_REWRITE and NO_MV_REWRITE hints have a higher priority than the query_rewrite_enabled variable. Therefore, you do not need to specify the query_rewrite_enabled variable. However, you must set the query_rewrite_integrity variable to stale_tolerated to use non-real-time materialized views for query rewriting.

   SET query_rewrite_integrity = 'stale_tolerated';
   

6. Use the MV_REWRITE hint to attempt to rewrite a query statement based on a materialized view while skipping cost and rule check. The following two query statements are rewritten based on the mv1_tbl2 materialized view.

   • The /*+mv_rewrite*/ hint attempts to find a materialized view that meets the query rewriting requirements and then rewrites the query statement based on the materialized view while skipping cost and rule check. After the hint finds a materialized view that meets the query rewriting requirements, subsequent materialized views are ignored.

     EXPLAIN SELECT /*+mv_rewrite*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 > 1 GROUP BY col1;
     

     The return 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
     

   • The /*+mv_rewrite(mv2_tbl2)*/ hint attempts to rewrite the query statement based on the mv2_tbl2 materialized view while skipping cost and rule check.

     EXPLAIN SELECT /*+mv_rewrite(mv2_tbl2)*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 > 1 GROUP BY col1;
     

     The return 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
     

7. The hint specifies to rewrite the query statement based on the mv2_tbl2 materialized view. However, the WHERE condition in the query statement does not meet the requirements for query rewriting. Therefore, the mv2_tbl2 materialized view cannot be used for query rewriting, and the query statement is not rewritten.

   EXPLAIN SELECT /*+mv_rewrite(mv2_tbl2)*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 < 1 GROUP BY col1;
   

   The return 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
   

8. Use the /*+ no_mv_rewrite*/ hint to disable materialized view-based query rewriting.

   EXPLAIN SELECT /*+no_mv_rewrite*/ count(*), col1 FROM tbl2 WHERE tbl2.col1 > 1 GROUP BY col1;
   

   The return 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
   

Scenarios

OceanBase Database supports query rewriting in the following scenarios when specific conditions are met: (1) The join order or join type in the FROM clause of the original query is different from that of the materialized view. (2) The original query contains tables not included in the materialized view. (3) The original query needs to be rewritten into a query based on an aggregate materialized view. (4) Aggregation rollup is involved.

Compatibility with the join order

If the join order in a materialized view is different from that in the FROM clause of a query, but the join orders are equivalent after conversion, the query can still be rewritten based on the materialized view.

Before a query is rewritten based on a materialized view, the conflict detector checks whether the join order of the query is compatible with that of the materialized view.

Here is an example:

Query statement in a 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;

New query statement after rewriting:

SELECT mv.t1c1, mv.t2c1, mv.t3c1
FROM mv;

Compatibility with the join type

If the join type in a materialized view is different from that in a query, but the join types can be compatible by adding compensating predicates, we recommend that you rewrite the query based on the materialized view.

To rewrite a query whose join type is different from that in a materialized view, make sure that the following conditions are met:

1. The join in the materialized view contains more padding lines. For example, the query requires an INNER JOIN, but the materialized view uses a LEFT OUTER JOIN or FULL OUTER JOIN.
2. The current node contains non-empty columns, which can be used to filter padding lines.
3. The filter predicates on the current node can be pulled up to the WHERE clause.

Here is an example:

Query statement in a 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;

New query statement after rewriting:

SELECT mv.t1c1 
FROM mv 
WHERE mv.t2pk IS NOT NULL;

Rewrite a query that involves tables not included in a materialized view

Before rewriting a query that involves tables not included in a materialized view, the system checks the join order and verifies whether the tables not included in the materialized view can be joined after the query is rewritten. If yes, the system rewrites the query.

Here is an example:

Query statement in a 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;

New query statement after rewriting:

SELECT mv.t1c1 
FROM mv, t3 
WHERE t3.c1 = mv.t1c1
  AND mv.t1pk IS NOT NULL;

Rewrite a query based on an aggregate materialized view

To rewrite a query based on an aggregate materialized view, make sure that the join order in the query is compatible with that in the FROM part of the materialized view, the query does not involve tables not included in the materialized view, and the WHERE clause in the query exactly matches that of the materialized view.

Here is an example:

Query statement in a 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;

New query statement after rewriting:

SELECT mv.sumc1, mv.c2, mv.c3
FROM mv;

Rewrite a query involving aggregation rollup

If the aggregate column of a query is different from that of a materialized view but is a subset of the aggregate column of the materialized view, you can rewrite the query based on the materialized view. In this case, only partial aggregate functions are supported, and the requirements are the same as those for rewriting a query based on an aggregate materialized view.

Aggregate functions supported for aggregation rollup are COUNT, SUM, BIT_AND, BIT_OR, BIT_XOR, MIN, and MAX.

Here is an example:

Query statement in a materialized view:

SELECT SUM(c1) sumc1, COUNT(c1) cntc1, c2, c3
FROM t1
WHERE c1 > 10
GROUP BY c2, c3;

Current query statement:

SELECT SUM(c1), COUNT(c1), c2
FROM t1
WHERE c1 > 10
  AND c3 = 10
GROUP BY c2;

New query statement after rewriting:

SELECT SUM(mv.sumc1), COUNT_SUM(mv.cntc1), mv.c2
FROM mv
WHERE mv.c3 = 10
GROUP BY mv.c2;

References

• CREATE MATERIALIZED VIEW
• Create a materialized view
• Query a materialized view
• Drop a materialized view
• Refresh a materialized view