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 option was specified for the materialized view when it was created.
  • The materialized view contains only the SELECT JOIN and WHERE clauses, namely, select-project-join (SPJ) queries. If a matching materialized view does not meet the requirements, it will not be 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 in the query statement exactly matches that 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.
  • 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.1, 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 will attempt 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.

  A sample statement is as follows:

  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.

  A sample statement is as follows:

  SET query_rewrite_integrity = 'stale_tolerated';
  

Here is an example:

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;
   

   The FROM clause in the input query statement must exactly match that in the materialized view. The WHERE condition in the input query statement must contain all WHERE conditions in the materialized view.

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

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

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 by using 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 will be 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 will not be 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
   

References

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