物化视图查询改写

通过使用物化视图来加速查询，当对基表执行查询时，系统会自动将查询重写为使用现有物化视图的查询。该方法的原理是将查询语句与物化视图的定义进行匹配，如果发现匹配的物化视图，则自动将查询重写为使用物化视图的查询，这样可以大大提高查询性能和效率。

物化视图查询改写使用限制

物化视图满足以下要求：
- 创建物化视图时，需指定 ENABLE QUERY REWRITE 子句。
  
  注意
  
  OceanBase 数据库 Oracle 模式下，创建支持改写的物化视图时必须指定刷新方式（refresh_clause）。
- 需要物化视图仅包含 SELECT、JOIN、WHERE 与 GROUP BY 子句，即 SPJG 查询。对于不满足条件的物化视图，不会报错，但不会被用于改写。
当前查询满足以下要求：
- 查询为 SELECT 查询，不是集合查询或层次查询，并且不包含窗口函数。
- FROM 子句中包含物化视图中出现全部的表，可以包含物化视图中不存在的表，并且连接顺序是兼容的。
- WHERE 条件物化视图是当前查询的子集，当前查询有聚合的情况下需要完全匹配。例如，物化视图的 WHERE 是 c1 > 10，当前查询的 WHERE 是 c1 > 10 AND c2 >20。这样物化视图的条件 {c1 > 10} 是当前查询 {c1>10, c2>20} 的子集。
- 在查询无聚合的情况下，要求物化视图没有聚合。
- 在查询有聚合的情况下，要求物化视图没有聚合或物化视图包含查询的所有 GROUP BY 列。如果使用带有聚合的物化视图进行改写，要求 FROM 与 WHERE 完全匹配。
- 当前查询所涉及的 SELECT 项、WHERE、HAVING 和 GROUP BY 等条件中的列，都需要包含在物化视图的 SELECT 语句中的列中。

规则/代价检查

查询改写时，OceanBase 数据库当前版本不进行代价检查，但规则检查要求改写后的 WHERE 条件能够使用至少一个物化视图上的索引。此外，当有超过 10 个的物化视图存在时，物化视图查询改写将仅尝试使用前 10 个物化视图。

物化视图改写

控制物化视图查询改写的系统变量如下：

query_rewrite_enabled：用于是否开启物化视图改写功能。该变量的详细介绍信息，参见 query_rewrite_enabled。

示例如下：
```
SET query_rewrite_enabled = 'force';
```
query_rewrite_integrity：用于指定物化视图改写的数据一致性检查级别。该变量的详细介绍信息，参见 query_rewrite_integrity。

示例如下：
```
SET query_rewrite_integrity = 'stale_tolerated';
```

通过以下示例展示物化视图改写：

创建表 test_tbl1。

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

创建表 test_tbl2。

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

创建名为 mv_test_tbl1_tbl2 的物化视图，并开启当前物化视图的自动改写。

CREATE MATERIALIZED VIEW mv_test_tbl1_tbl2
    NEVER REFRESH
    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;

当输入查询 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 test_tbl1.col1; 时，发生物化视图查询改写。

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 test_tbl1.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;

物化视图查询改写控制

物化视图查询改写控制包括 MV_REWRITE、NO_MV_REWRITE 两个 Hint，并且这两个 Hint 的优先级高于系统变量 query_rewrite_enabled。

MV_REWRITE

MV_REWRITE 语法如下：

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

mv_name_list:
    mv_name [, mv_name ...]

单独使用 MV_REWRITE Hint 时可以跳过物化视图查询改写的规则/代价检查，直接使用可以使用的改写。Hint 后指定一个或多个物化视图的情况，除了跳过规则/代价检查外，物化视图查询改写将只使用指定的物化视图尝试改写，忽略所有没被指定的物化视图。

使用 MV_REWRITE Hint 指定物化视图时，无法强制使用没有 ENABLE QUERY REWRITE（开启当前物化视图的自动改写）子句的物化视图，无法在系统变量 query_rewrite_integrity 设置为 enforced 的情况下强制使用非实时物化视图。

NO_MV_REWRITE

NO_MV_REWRITE 语法如下：

/*+ NO_MV_REWRITE (@ queryblock) */

禁止物化视图查询改写，可以指定 query block。

物化视图查询改写控制 Hint 使用示例

创建基表 tbl2。

CREATE TABLE tbl2 (col1 INT, col2 INT);

向基表 tbl2 中插入两条数据。

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

返回结果如下：

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

创建物化视图 mv1_tbl2，并开启当前物化视图的自动改写。

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

创建物化视图 mv2_tbl2，并开启当前物化视图的自动改写。

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

设置系统变量 query_rewrite_integrity 为 stale_tolerated。

说明

MV_REWRITE 和 NO_MV_REWRITE Hint 的优先级高于系统变量 query_rewrite_enabled，因此不需要设置 query_rewrite_enabled。但是需要设置 query_rewrite_integrity 为 stale_tolerated 才可以使用非实时物化视图进行改写。
```
SET query_rewrite_integrity = 'stale_tolerated';
```

使用 MV_REWRITE Hint 用物化视图尝试进行改写，并跳过改写代价/规则检查。下面两条查询都将使用物化视图 mv1_tbl2 进行改写。

/*+mv_rewrite*/ 将尝试使用符合改写条件的物化视图进行改写，一旦找到了符合改写要求的物化视图，后续的物化视图将不再被考虑，并跳过改写代价/规则检查。

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

返回结果如下：

+----------------------------------------------------------------------------------------------+
| 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 > cast(1, NUMBER(-1, -85))]), 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(mv1_tbl2)*/ 将尝试使用 mv2_tbl2 进行改写，并跳过改写代价/规则检查。

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

返回结果如下：

+-------------------------------------------------------------------------+
| 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

虽然查询指定使用 mv2_tbl2 来查询改写，由于查询语句的 WHERE 条件不满足要求，mv2_tbl2 无法用于查询改写，所以此查询不会进行物化视图查询改写。

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

返回结果如下：

+--------------------------------------------------------------------------------------+
| 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 < cast(1, NUMBER(-1, -85))]), 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

使用 /*+ no_mv_rewrite*/ Hint，不会进行物化视图查询改写。

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

返回结果如下：

+--------------------------------------------------------------------------------------+
| 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 > cast(1, NUMBER(-1, -85))]), 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

物化视图查询改写使用场景

OceanBase 数据库物化视图改写支持非聚合物化视图的 FROM 连接兼容，查询包含物化视图中不存在的表的改写，并且支持了使用带有聚合的物化视图改写，以及聚合上卷改写。

说明

以下示例中查询语句创建的物化视图是全量刷新物化视图。增量刷新物化视图的查询语句如果满足对应条件，同样支持相同场景的查询改写。

连接顺序兼容

在物化视图与查询的 FROM 中的连接顺序不同，但两者的连接顺序经过变换后等价的情况下，可以使用物化视图改写。

物化视图改写会通过冲突检测器，检查查询的连接顺序与物化视图是否兼容。

示例如下：

物化视图中查询语句：

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;

当前查询语句：

SELECT t1.c1, t2.c1, t3.c1
FROM t1 INNER JOIN t3 ON t1.c1 = t3.c1, t2
WHERE t1.c1 = t2.c1;

查询改写后语句：

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

连接类型兼容

在物化视图的连接类型与查询不同，但可以通过添加补偿谓词的方式兼容的情况下，可以使用物化视图改写。

连接类型兼容需要满足以下条件，才可以进行改写：

物化视图的连接包含更多补空行，例如查询需要 INNER JOIN，物化视图是 LEFT OUTER JOIN 或 FULL OUTER JOIN。
当前连接节点上存在非空列，可以用于过滤补空行。
当前节点上的过滤谓词可以上拉至 WHERE。

示例如下：

物化视图中查询语句：

SELECT t1.c1 t1c1, t2.pk t2pk
FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;

当前查询语句：

SELECT t1.c1
FROM t1, t2
WHERE t1.c1 = t2.c1;

查询改写后语句：

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

查询包含物化视图中不存在的表

查询中包含物化视图中不存在的表，物化视图改写会检查连接顺序，多余的表是否可以在物化视图之后进行连接，如果可以则进行改写。

示例如下：

物化视图中查询语句：

SELECT t1.c1 t1c1, t1.pk t1pk
FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;

当前查询语句：

SELECT t1.c1
FROM t1, t2, t3
WHERE t1.c1 = t2.c1
  AND t3.c1 = t1.c1;

查询改写后语句：

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

带有聚合的物化视图改写

如果使用带有聚合的物化视图进行改写，需要查询与物化视图的 FROM 部分顺序兼容，并且查询不能包含物化视图中没有的表，查询的 WHERE 部分也需要与物化视图完全匹配。

示例如下：

物化视图中查询语句：

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

当前查询语句：

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

查询改写后语句：

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

聚合上卷改写

如果物化视图的聚合列与查询不同，则当查询的聚合列是物化视图聚合列的子集时可以使用物化视图改写。这种情况下，只支持部分聚合函数，其余要求与使用带有聚合的物化视图改写一致。

聚合上卷支持的聚合函数：COUNT、SUM、BIT_AND、BIT_OR、BIT_XOR、MIN、MAX。

示例如下：

物化视图中查询语句：

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

当前查询语句：

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

查询改写后语句：

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

物化视图查询改写使用限制

注意

规则/代价检查

物化视图改写

物化视图查询改写控制

MV_REWRITE

NO_MV_REWRITE

物化视图查询改写控制 Hint 使用示例

说明

物化视图查询改写使用场景

说明

连接顺序兼容

连接类型兼容

查询包含物化视图中不存在的表

带有聚合的物化视图改写

聚合上卷改写

相关文档