This is an automated email from the ASF dual-hosted git repository.
kassiez pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris-website.git
The following commit(s) were added to refs/heads/master by this push:
new 4e15495be3b [doc](mtmv) Add more doc for mtmv (#1794)
4e15495be3b is described below
commit 4e15495be3b8b13dc31d06fb5068efd5039c0765
Author: seawinde <w...@selectdb.com>
AuthorDate: Mon Feb 10 14:11:51 2025 +0800
[doc](mtmv) Add more doc for mtmv (#1794)
## Versions
- [x] dev
- [x] 3.0
- [x] 2.1
- [ ] 2.0
## Languages
- [x] Chinese
- [x] English
## Docs Checklist
- [] Checked by AI
- [ ] Test Cases Built
---
.../functions-and-demands.md | 104 +++++++++++++++---
.../async-materialized-view/overview.md | 2 +-
.../functions-and-demands.md | 95 +++++++++++++---
.../async-materialized-view/overview.md | 2 +-
.../functions-and-demands.md | 115 +++++++++++++++-----
.../async-materialized-view/overview.md | 2 +-
.../functions-and-demands.md | 119 ++++++++++++++++-----
.../async-materialized-view/overview.md | 2 +-
.../functions-and-demands.md | 91 ++++++++++++----
.../async-materialized-view/overview.md | 2 +-
.../functions-and-demands.md | 95 +++++++++++-----
.../async-materialized-view/overview.md | 2 +-
12 files changed, 499 insertions(+), 132 deletions(-)
diff --git
a/docs/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
b/docs/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
index c7bfa172df3..2511c9647c8 100644
---
a/docs/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
+++
b/docs/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
@@ -80,6 +80,15 @@ Check for base table partition data changes since last
refresh and refresh only
REFRESH MATERIALIZED VIEW mvName AUTO;
```
+:::tip
+If the base table used in the SQL definition of the materialized view is a
JDBC table,
+Doris cannot perceive changes in the table data. When refreshing the
materialized view,
+it is necessary to specify COMPLETE. If AUTO is specified, it may result in
the base table
+having data, but the materialized view being empty after the refresh.
Currently,
+when refreshing the materialized view, Doris can only perceive data changes in
internal
+tables and Hive data source tables; support for other data sources is being
gradually implemented.
+:::
+
Refresh all materialized view partitions without checking for base table
changes:
```sql
@@ -253,7 +262,6 @@ CREATE MATERIALIZED VIEW mv_1_1
BUILD DEFERRED
REFRESH COMPLETE
ON SCHEDULE EVERY 1 DAY STARTS '2024-12-01 20:30:00'
-PROPERTIES ('replication_num' = '1')
AS
SELECT
l_linestatus,
@@ -273,7 +281,6 @@ CREATE MATERIALIZED VIEW mv_1_1
BUILD IMMEDIATE
REFRESH COMPLETE
ON COMMIT
-PROPERTIES ('replication_num' = '1')
AS
SELECT
l_linestatus,
@@ -867,7 +874,7 @@ as
select l_shipdate, o_orderdate, l_partkey,
l_suppkey, sum(o_totalprice) as sum_total
from lineitem
- left join orders on lineitem.l_orderkey = orders.o_orderkey and
l_shipdate = o_orderdate
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate
group by
l_shipdate,
o_orderdate,
@@ -887,7 +894,7 @@ insert into lineitem values
```sql
select l_shipdate, o_orderdate, l_partkey, l_suppkey, sum(o_totalprice) as
sum_total
from lineitem
- left join orders on lineitem.l_orderkey = orders.o_orderkey and
l_shipdate = o_orderdate
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate
group by
l_shipdate,
o_orderdate,
@@ -905,7 +912,7 @@ FROM mv7
union all
select t1.l_shipdate, o_orderdate, t1.l_partkey, t1.l_suppkey,
sum(o_totalprice) as sum_total
from (select * from lineitem where l_shipdate = '2023-10-21') t1
- left join orders on t1.l_orderkey = orders.o_orderkey and
t1.l_shipdate = o_orderdate
+left join orders on t1.l_orderkey = orders.o_orderkey and t1.l_shipdate =
o_orderdate
group by
t1.l_shipdate,
o_orderdate,
@@ -946,7 +953,6 @@ inner join orders on lineitem.l_orderkey =
orders.o_orderkey;
CREATE MATERIALIZED VIEW mv8_0
BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL
DISTRIBUTED BY RANDOM BUCKETS 2
-PROPERTIES ('replication_num' = '1')
AS
select
l_linenumber,
@@ -982,6 +988,39 @@ Note:
2. Nested materialized view transparent rewriting is disabled by default. See
the related settings below for how to enable it.
+
+### Aggregate Query Using Non-Aggregate Materialized View Rewrite
+If the query is an aggregate query and the materialized view does not contain
aggregates,
+but the materialized view can provide all the columns used in the query, then
it can also be rewritten.
+For example, if the query first performs a join and then a group by
aggregation,
+hitting a materialized view that includes the join will also yield benefits.
+
+```sql
+CREATE MATERIALIZED VIEW mv10_0
+BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+DISTRIBUTED BY RANDOM BUCKETS 2
+as
+select l_shipdate, o_orderdate, l_partkey,
+ l_suppkey, o_totalprice
+from lineitem
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate;
+```
+
+The following query can hit the mv10_0 materialized view, saving the
computation of the
+lineitem join orders join:
+
+```sql
+select l_shipdate, o_orderdate, l_partkey,
+ l_suppkey, sum(o_totalprice) as sum_total
+from lineitem
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate
+group by
+ l_shipdate,
+ o_orderdate,
+ l_partkey,
+ l_suppkey;
+```
+
### Explain Query Transparent Rewriting Status
To view materialized view transparent rewriting hits, used for viewing and
debugging.
@@ -1032,6 +1071,9 @@ explain memo plan <query_sql>
- Pausing/resuming/canceling/refreshing materialized views: Requires
materialized view creation permission
### Modifying Materialized Views
+
+#### Modifying Materialized View Properties
+
```sql
ALTER MATERIALIZED VIEW mv_1
SET(
@@ -1040,6 +1082,33 @@ SET(
```
For more details, see [ALTER ASYNC MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/ALTER-ASYNC-MATERIALIZED-VIEW)
+#### Materialized View Renaming, i.e., Atomic Replacement of Materialized Views
+```sql
+CREATE MATERIALIZED VIEW mv9_0
+BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES ('replication_num' = '1')
+AS
+select
+ l_linenumber,
+ o_custkey,
+ o_orderkey,
+ o_orderstatus,
+ l_partkey,
+ l_suppkey,
+ l_orderkey
+from lineitem
+inner join orders on lineitem.l_orderkey = orders.o_orderkey;
+```
+Replace the materialized view mv7 with mv9_0 and delete mv7:
+
+```sql
+ALTER MATERIALIZED VIEW mv7
+REPLACE WITH MATERIALIZED VIEW mv9_0
+PROPERTIES('swap' = 'false');
+```
+
+
### Dropping Materialized Views
```sql
DROP MATERIALIZED VIEW mv_1;
@@ -1066,9 +1135,8 @@ For more details, see [RESUME MATERIALIZED
VIEW](../../../sql-manual/sql-stateme
For more details, see [CANCEL MATERIALIZED VIEW
TASK](../../../sql-manual/sql-statements/table-and-view/materialized-view/CANCEL-MATERIALIZED-VIEW-TASK)
-### Metadata Queries
-#### Querying Materialized View Information
+### Querying Materialized View Information
```sql
SELECT *
@@ -1106,7 +1174,7 @@ SyncWithBaseTables: 1
- **SchemaChangeDetail:** Explains the reason for SCHEMA_CHANGE.
-- **RefreshState:** Status of the last refresh task. If FAIL, indicates
execution failed - use the `tasks()` command to identify the cause. See
[Viewing Materialized View Task Status](#viewing-materialized-view-task-status)
section.
+- **RefreshState:** Status of the last refresh task. If FAIL, indicates
execution failed - use the `tasks()` command to identify the cause. See
[Viewing Materialized View Task Status](### Querying Refresh Task Information)
section.
- **SyncWithBaseTables:** Whether synchronized with base tables. 1 means
synchronized, 0 means not synchronized. If not synchronized, use `show
partitions` to check which partitions are out of sync. See the section below on
checking SyncWithBaseTables status for partitioned materialized views.
@@ -1115,18 +1183,20 @@ For transparent rewriting, materialized views typically
have two states:
- **Normal:** The materialized view is available for transparent rewriting.
- **Unavailable/Abnormal:** The materialized view cannot be used for
transparent rewriting. However, it can still be queried directly.
-For more details, see
[MV_INFOS](../../../sql-manual/sql-functions/table-valued-functions/mv-infos)
+For more details, see
[MV_INFOS](../../../sql-manual/sql-functions/table-valued-functions/mv_infos)
-#### Querying Refresh Task Information
+### Querying Refresh Task Information
Each materialized view has one Job, and each refresh creates a new Task, with
a 1:n relationship between Jobs and Tasks.
To view a materialized view's Task status by name, run the following query to
check refresh task status and progress:
```sql
-SELECT *
+SELECT *
FROM tasks("type"="mv")
-WHERE mvName = 'mv_name'
-ORDER BY CreateTime DESC \G
+WHERE
+ MvDatabaseName = 'mv_db_name' and
+ mvName = 'mv_name'
+ORDER BY CreateTime DESC \G
```
Example output:
@@ -1173,7 +1243,7 @@ NeedRefreshPartitions:
["p_20231023_20231024","p_20231019_20231020","p_20231020_
For more details, see
[TASKS](../../../sql-manual/sql-functions/table-valued-functions/tasks?_highlight=task)
-#### Querying Materialized View Jobs
+### Querying Materialized View Jobs
```sql
SELECT *
@@ -1183,7 +1253,7 @@ WHERE Name="inner_mtmv_75043";
For more details, see
[JOBS](../../../sql-manual/sql-functions/table-valued-functions/jobs)
-#### Querying Materialized View Partition Information
+### Querying Materialized View Partition Information
Checking SyncWithBaseTables Status for Partitioned Materialized Views
@@ -1204,7 +1274,7 @@ Check the `SyncWithBaseTables` field - false indicates
the partition is not avai
For more details, see [SHOW
PARTITIONS](../../../sql-manual/sql-statements/table-and-view/table/SHOW-PARTITIONS)
-#### Viewing Materialized View Table Structure
+### Viewing Materialized View Table Structure
For more details, see
[DESCRIBE](../../../sql-manual/sql-statements/table-and-view/table/DESC-TABLE)
diff --git
a/docs/query-acceleration/materialized-view/async-materialized-view/overview.md
b/docs/query-acceleration/materialized-view/async-materialized-view/overview.md
index 304bf997f6f..2ce35e7391b 100644
---
a/docs/query-acceleration/materialized-view/async-materialized-view/overview.md
+++
b/docs/query-acceleration/materialized-view/async-materialized-view/overview.md
@@ -40,8 +40,8 @@ when query requests arrive, thus avoiding the overhead of
re-executing complex q
## Limitations
- **Consistency of Asynchronous Materialized Views with Base Table Data**:
Asynchronous materialized views will eventually be consistent with the base
table data, but they cannot be synchronized in real-time, meaning real-time
consistency cannot be maintained.
- **Support for Window Function Queries**: Currently, if a query contains
window functions, it is not supported to transparently rewrite that query to
utilize materialized views.
-- **Materialized Views with ORDER BY and Queries**: If the materialized view
itself contains an ORDER BY clause, the system does not currently support using
that materialized view for transparent query rewriting. However, please note
that the query itself can still include an ORDER BY clause.
- **Materialized Views Joining More Tables than Query Tables**: If the number
of tables joined in the materialized view exceeds the number of tables involved
in the query (for example, if the query only involves t1 and t2, while the
materialized view includes t1, t2, and an additional t3), the system currently
does not support transparently rewriting that query to utilize the materialized
view.
+- If the materialized view contains set operations such as UNION ALL, LIMIT,
ORDER BY, or CROSS JOIN, the materialized view can be built normally, but it
cannot be used for transparent rewriting.
## Principle Introduction
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
index cac8d36dd81..488aa627338 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
@@ -80,6 +80,12 @@ CREATE MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW mvName AUTO;
```
+:::tip 提示
+如果物化视图定义 SQL 使用的基表是 JDBC 表，Doris 无法感知表数据变化，刷新物化视图时需要指定 COMPLETE。
+如果指定了 AUTO，会导致基表有数据，但是刷新后物化视图没数据。
+刷新物化视图时，目前 Doris 只能感知内表和 Hive 数据源表数据变化，其他数据源逐步支持中。
+:::
+
不校验基表的分区数据自上次刷新后是否有变化，直接刷新物化视图的所有分区。
```sql
@@ -856,7 +862,7 @@ as
select l_shipdate, o_orderdate, l_partkey,
l_suppkey, sum(o_totalprice) as sum_total
from lineitem
- left join orders on lineitem.l_orderkey = orders.o_orderkey and
l_shipdate = o_orderdate
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate
group by
l_shipdate,
o_orderdate,
@@ -876,7 +882,7 @@ insert into lineitem values
```sql
select l_shipdate, o_orderdate, l_partkey, l_suppkey, sum(o_totalprice) as
sum_total
from lineitem
- left join orders on lineitem.l_orderkey = orders.o_orderkey and
l_shipdate = o_orderdate
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate
group by
l_shipdate,
o_orderdate,
@@ -894,7 +900,7 @@ FROM mv7
union all
select t1.l_shipdate, o_orderdate, t1.l_partkey, t1.l_suppkey,
sum(o_totalprice) as sum_total
from (select * from lineitem where l_shipdate = '2023-10-21') t1
- left join orders on t1.l_orderkey = orders.o_orderkey and
t1.l_shipdate = o_orderdate
+left join orders on t1.l_orderkey = orders.o_orderkey and t1.l_shipdate =
o_orderdate
group by
t1.l_shipdate,
o_orderdate,
@@ -940,7 +946,6 @@ inner join orders on lineitem.l_orderkey =
orders.o_orderkey;
CREATE MATERIALIZED VIEW mv8_0
BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL
DISTRIBUTED BY RANDOM BUCKETS 2
-PROPERTIES ('replication_num' = '1')
AS
select
l_linenumber,
@@ -974,6 +979,37 @@ where o_orderstatus = 'o'
2. 嵌套物化视图透明改写默认关闭，开启方式见下面的相关设置。
+
+### 聚合查询使用非聚合物化视图改写
+如果查询是聚合查询，物化视图不包含聚合，但是物化视图可以提供查询使用的所有列，那么也可以改写，比如查询先是 join
+连接，之后是 group by 聚合，命中包含 join 连接的物化视图，那么也是有收益的。
+
+
+```sql
+CREATE MATERIALIZED VIEW mv10_0
+BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+DISTRIBUTED BY RANDOM BUCKETS 2
+as
+select l_shipdate, o_orderdate, l_partkey,
+ l_suppkey, o_totalprice
+from lineitem
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate;
+```
+
+如下查询可以命中 mv10_0 的物化视图，节省了 `lineitem join orders` 连接的计算
+```sql
+select l_shipdate, o_orderdate, l_partkey,
+ l_suppkey, sum(o_totalprice) as sum_total
+from lineitem
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate
+group by
+ l_shipdate,
+ o_orderdate,
+ l_partkey,
+ l_suppkey;
+```
+
+
### Explain 查询透明改写情况
查询透明改写命中情况，用于查看和调试。
@@ -1025,12 +1061,44 @@ explain memo plan <query_sql>
- 暂停/恢复/取消/刷新物化视图：需要具有物化视图的创建权限。
### 物化视图修改
+
+#### 修改物化视图属性
+
```sql
ALTER MATERIALIZED VIEW mv_1
SET(
"grace_period" = "10"
);
```
+
+#### 物化视图重命名，即物化视图原子替换
+
+```sql
+
+CREATE MATERIALIZED VIEW mv9_0
+BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES ('replication_num' = '1')
+AS
+select
+ l_linenumber,
+ o_custkey,
+ o_orderkey,
+ o_orderstatus,
+ l_partkey,
+ l_suppkey,
+ l_orderkey
+from lineitem
+inner join orders on lineitem.l_orderkey = orders.o_orderkey;
+```
+
+使用 mv9_0 的物化视图替换 mv7，并且删除 mv7。
+```sql
+ALTER MATERIALIZED VIEW mv7
+REPLACE WITH MATERIALIZED VIEW mv9_0
+PROPERTIES('swap' = 'false');
+```
+
详情参考 [ALTER ASYNC MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Data-Definition-Statements/Alter/ALTER-ASYNC-MATERIALIZED-VIEW)
@@ -1062,9 +1130,8 @@ SHOW CREATE MATERIALIZED VIEW mv_1;
详情参考 [CANCEL MATERIALIZED VIEW
TASK](../../../sql-manual/sql-statements/table-and-view/materialized-view/CANCEL-MATERIALIZED-VIEW-TASK)
-### 元数据查询
-#### 查询物化视图信息
+### 查询物化视图信息
```sql
SELECT *
@@ -1104,7 +1171,7 @@ SyncWithBaseTables: 1
- **SchemaChangeDetail：** 表示 SCHEMA_CHANGE 发生的原因。
-- **RefreshState：** 物化视图最后一次任务刷新的状态。如果为 FAIL，代表执行失败，可以通过 `tasks()
`命令进一步定位失败原因。Task 命令见本文[查看物化视图 Task 状态](#查看物化视图-task-状态)。
+- **RefreshState：** 物化视图最后一次任务刷新的状态。如果为 FAIL，代表执行失败，可以通过 `tasks()
`命令进一步定位失败原因。Task 命令见本文[查询刷新任务 TASK 信息](### 查询刷新任务 TASK 信息)。
- **SyncWithBaseTables：** 是否和基表数据同步。1 为同步，0 为不同步。如果不同步，可通过 `show partitions`
进一步判断哪个分区不同步。`show partitions` 见下文分区物化视图查看 SyncWithBaseTables 状态方法。
@@ -1115,10 +1182,10 @@ SyncWithBaseTables: 1
- **不可用、状态不正常：** 指的是物化视图不能用于透明改写的简称。尽管如此，该物化视图还是可以直查的。
-详情参考
[MV_INFOS](../../../sql-manual/sql-functions/table-valued-functions/mv-infos)
+详情参考
[MV_INFOS](../../../sql-manual/sql-functions/table-valued-functions/mv_infos)
-#### 查询刷新任务 TASK 信息
+### 查询刷新任务 TASK 信息
每个物化视图有一个 Job，每次刷新会有一个新的 Task，Job 和 Task 是 1:n 的关系。
根据物化视图名称查看物化视图的 Task 状态，运行如下语句，可以查看刷新任务的状态和进度：
@@ -1126,7 +1193,9 @@ SyncWithBaseTables: 1
```sql
SELECT *
FROM tasks("type"="mv")
-WHERE mvName = 'mv_name'
+WHERE
+MvDatabaseName = 'mv_db_name' and
+mvName = 'mv_name'
ORDER BY CreateTime DESC \G
```
@@ -1175,7 +1244,7 @@ NeedRefreshPartitions:
["p_20231023_20231024","p_20231019_20231020","p_20231020_
详情参考
[TASKS](../../../sql-manual/sql-functions/table-valued-functions/tasks?_highlight=task)
-#### 查询物化视图对应的 JOB
+### 查询物化视图对应的 JOB
```sql
SELECT *
@@ -1186,7 +1255,7 @@ WHERE Name="inner_mtmv_75043";
详情参考 [JOBS](../../../sql-manual/sql-functions/table-valued-functions/jobs)
-#### 查询物化视图的分区信息：
+### 查询物化视图的分区信息：
分区物化视图查看 SyncWithBaseTables 状态方法
@@ -1208,7 +1277,7 @@ show partitions from mv11;
详情参考 [SHOW
PARTITIONS](../../../sql-manual/sql-statements/table-and-view/table/SHOW-PARTITIONS)
-#### 查看物化视图表结构
+### 查看物化视图表结构
详情参考
[DESCRIBE](../../../sql-manual/sql-statements/table-and-view/table/DESC-TABLE)
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/query-acceleration/materialized-view/async-materialized-view/overview.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/query-acceleration/materialized-view/async-materialized-view/overview.md
index 6f8be2e8d42..638934b3520 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/query-acceleration/materialized-view/async-materialized-view/overview.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/query-acceleration/materialized-view/async-materialized-view/overview.md
@@ -37,9 +37,9 @@ under the License.
## 使用限制
- 异步物化视图与基表数据一致性：异步物化视图与基表的数据最终会保持一致，但无法实时同步，即无法保持实时一致性。
- 窗口函数查询支持：当前，如果查询中包含了窗口函数，暂不支持将该查询透明地改写为利用物化视图的形式。
-- 包含ORDER BY的物化视图与查询：若物化视图本身包含了ORDER
BY子句，则系统暂不支持将该物化视图用于透明改写查询。但请注意，查询本身仍然可以包含ORDER BY子句。
- 物化视图连接表多于查询表：如果物化视图所连接的表数量多于查询所涉及的表（例如，查询仅涉及t1和t2，而物化视图则包含了t1、t2以及额外的t3），
则系统目前不支持将该查询透明地改写为利用该物化视图的形式。
+- 如果物化视图包含 UNION ALL等集合操作, LIMIT，ORDER BY，CROSS JOIN，物化视图可以正常构建，但是不能用于透明改写。
## 原理介绍
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
index 553ed65f0a6..3d275547335 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
@@ -80,6 +80,12 @@ CREATE MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW mvName AUTO;
```
+:::tip 提示
+如果物化视图定义 SQL 使用的基表是 JDBC 表，Doris 无法感知表数据变化，刷新物化视图时需要指定 COMPLETE。
+如果指定了 AUTO，会导致基表有数据，但是刷新后物化视图没数据。
+刷新物化视图时，目前 Doris 只能感知内表和 Hive 数据源表数据变化，其他数据源逐步支持中。
+:::
+
不校验基表的分区数据自上次刷新后是否有变化，直接刷新物化视图的所有分区。
```sql
@@ -251,8 +257,7 @@ STARTS 的时间要晚于当前的时间
CREATE MATERIALIZED VIEW mv_1_1
BUILD DEFERRED
REFRESH COMPLETE
-ON SCHEDULE EVERY 1 DAY STARTS '2024-12-01 20:30:00'
-PROPERTIES ('replication_num' = '1')
+ON SCHEDULE EVERY 1 DAY STARTS '2024-12-01 20:30:00'
AS
SELECT
l_linestatus,
@@ -272,8 +277,7 @@ LEFT JOIN lineitem ON l_orderkey = o_orderkey;
CREATE MATERIALIZED VIEW mv_1_1
BUILD IMMEDIATE
REFRESH COMPLETE
-ON COMMIT
-PROPERTIES ('replication_num' = '1')
+ON COMMIT
AS
SELECT
l_linestatus,
@@ -408,7 +412,6 @@ BUILD DEFERRED REFRESH AUTO ON MANUAL
partition by(`k2`)
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES (
-'partition_sync_limit'='1',
'partition_sync_time_unit'='DAY'
)
AS
@@ -874,7 +877,7 @@ as
select l_shipdate, o_orderdate, l_partkey,
l_suppkey, sum(o_totalprice) as sum_total
from lineitem
- left join orders on lineitem.l_orderkey = orders.o_orderkey and
l_shipdate = o_orderdate
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate
group by
l_shipdate,
o_orderdate,
@@ -894,7 +897,7 @@ insert into lineitem values
```sql
select l_shipdate, o_orderdate, l_partkey, l_suppkey, sum(o_totalprice) as
sum_total
from lineitem
- left join orders on lineitem.l_orderkey = orders.o_orderkey and
l_shipdate = o_orderdate
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate
group by
l_shipdate,
o_orderdate,
@@ -912,7 +915,7 @@ FROM mv7
union all
select t1.l_shipdate, o_orderdate, t1.l_partkey, t1.l_suppkey,
sum(o_totalprice) as sum_total
from (select * from lineitem where l_shipdate = '2023-10-21') t1
- left join orders on t1.l_orderkey = orders.o_orderkey and
t1.l_shipdate = o_orderdate
+left join orders on t1.l_orderkey = orders.o_orderkey and t1.l_shipdate =
o_orderdate
group by
t1.l_shipdate,
o_orderdate,
@@ -962,7 +965,6 @@ inner join orders on lineitem.l_orderkey =
orders.o_orderkey;
CREATE MATERIALIZED VIEW mv8_0
BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL
DISTRIBUTED BY RANDOM BUCKETS 2
-PROPERTIES ('replication_num' = '1')
AS
select
l_linenumber,
@@ -996,6 +998,40 @@ where o_orderstatus = 'o'
2. 嵌套物化视图透明改写默认关闭，开启方式见下面的相关设置。
+### 聚合查询使用非聚合物化视图改写
+
+:::tip 提示
+从 2.1.5 版本开始支持嵌套物化视图改写。
+:::
+
+如果查询是聚合查询，物化视图不包含聚合，但是物化视图可以提供查询使用的所有列，那么也可以改写，比如查询先是 join
+连接，之后是 group by 聚合，命中包含 join 连接的物化视图，那么也是有收益的。
+
+
+```sql
+CREATE MATERIALIZED VIEW mv10_0
+BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+DISTRIBUTED BY RANDOM BUCKETS 2
+as
+select l_shipdate, o_orderdate, l_partkey,
+ l_suppkey, o_totalprice
+from lineitem
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate;
+```
+
+如下查询可以命中 mv10_0 的物化视图，节省了 `lineitem join orders` 连接的计算
+```sql
+select l_shipdate, o_orderdate, l_partkey,
+ l_suppkey, sum(o_totalprice) as sum_total
+from lineitem
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate
+group by
+ l_shipdate,
+ o_orderdate,
+ l_partkey,
+ l_suppkey;
+```
+
### Explain 查询透明改写情况
查询透明改写命中情况，用于查看和调试。
@@ -1047,12 +1083,45 @@ explain memo plan <query_sql>
- 暂停/恢复/取消/刷新物化视图：需要具有物化视图的创建权限。
### 物化视图修改
+
+#### 修改物化视图属性
```sql
ALTER MATERIALIZED VIEW mv_1
SET(
"grace_period" = "10"
);
```
+
+详情参考 [ALTER ASYNC MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Data-Definition-Statements/Alter/ALTER-ASYNC-MATERIALIZED-VIEW)
+
+#### 物化视图重命名，即物化视图原子替换
+
+```sql
+
+CREATE MATERIALIZED VIEW mv9_0
+BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES ('replication_num' = '1')
+AS
+select
+ l_linenumber,
+ o_custkey,
+ o_orderkey,
+ o_orderstatus,
+ l_partkey,
+ l_suppkey,
+ l_orderkey
+from lineitem
+inner join orders on lineitem.l_orderkey = orders.o_orderkey;
+```
+
+使用 mv9_0 的物化视图替换 mv7，并且删除 mv7。
+```sql
+ALTER MATERIALIZED VIEW mv7
+REPLACE WITH MATERIALIZED VIEW mv9_0
+PROPERTIES('swap' = 'false');
+```
+
详情参考 [ALTER ASYNC MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Data-Definition-Statements/Alter/ALTER-ASYNC-MATERIALIZED-VIEW)
@@ -1061,7 +1130,7 @@ SET(
DROP MATERIALIZED VIEW mv_1;
```
-详情参考 [DROP ASYNC MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Data-Definition-Statements/Drop/DROP-ASYNC-MATERIALIZED-VIEW)
+详情参考 [DROP ASYNC MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/DROP-ASYNC-MATERIALIZED-VIEW)
### 查看物化视图创建语句
@@ -1069,7 +1138,7 @@ DROP MATERIALIZED VIEW mv_1;
SHOW CREATE MATERIALIZED VIEW mv_1;
```
-详情参考 [SHOW CREATE MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Show-Statements/SHOW-CREATE-MATERIALIZED-VIEW/)
+详情参考 [SHOW CREATE MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/SHOW-CREATE-MATERIALIZED-VIEW)
:::tip 提示
从 2.1.5 版本开始支持。
@@ -1077,19 +1146,17 @@ SHOW CREATE MATERIALIZED VIEW mv_1;
### 暂停物化视图
-详情参考 [PAUSE MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Utility-Statements/PAUSE-MATERIALIZED-VIEW)
+详情参考 [PAUSE MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/PAUSE-MATERIALIZED-VIEW)
### 启用物化视图
-详情参考 [RESUME MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Utility-Statements/RESUME-MATERIALIZED-VIEW)
+详情参考 [RESUME MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/RESUME-MATERIALIZED-VIEW)
### 取消物化视图刷新任务
-详情参考 [CANCEL MATERIALIZED VIEW
TASK](../../../sql-manual/sql-statements/Utility-Statements/CANCEL-MATERIALIZED-VIEW-TASK)
-
-### 元数据查询
+详情参考 [CANCEL MATERIALIZED VIEW
TASK](../../../sql-manual/sql-statements/table-and-view/materialized-view/CANCEL-MATERIALIZED-VIEW-TASK)
-#### 查询物化视图信息
+### 查询物化视图信息
```sql
SELECT *
@@ -1129,7 +1196,7 @@ SyncWithBaseTables: 1
- **SchemaChangeDetail：** 表示 SCHEMA_CHANGE 发生的原因。
-- **RefreshState：** 物化视图最后一次任务刷新的状态。如果为 FAIL，代表执行失败，可以通过 `tasks()
`命令进一步定位失败原因。Task 命令见本文[查看物化视图 Task 状态](#查看物化视图-task-状态)。
+- **RefreshState：** 物化视图最后一次任务刷新的状态。如果为 FAIL，代表执行失败，可以通过 `tasks()
`命令进一步定位失败原因。Task 命令见本文[查询刷新任务 TASK 信息](### 查询刷新任务 TASK 信息)。
- **SyncWithBaseTables：** 是否和基表数据同步。1 为同步，0 为不同步。如果不同步，可通过 `show partitions`
进一步判断哪个分区不同步。`show partitions` 见下文分区物化视图查看 SyncWithBaseTables 状态方法。
@@ -1143,7 +1210,7 @@ SyncWithBaseTables: 1
详情参考
[MV_INFOS](../../../sql-manual/sql-functions/table-valued-functions/mv_infos)
-#### 查询刷新任务 TASK 信息
+### 查询刷新任务 TASK 信息
每个物化视图有一个 Job，每次刷新会有一个新的 Task，Job 和 Task 是 1:n 的关系。
根据物化视图名称查看物化视图的 Task 状态，运行如下语句，可以查看刷新任务的状态和进度：
@@ -1200,7 +1267,7 @@ NeedRefreshPartitions:
["p_20231023_20231024","p_20231019_20231020","p_20231020_
详情参考
[TASKS](../../../sql-manual/sql-functions/table-valued-functions/tasks?_highlight=task)
-#### 查询物化视图对应的 JOB
+### 查询物化视图对应的 JOB
```sql
SELECT *
@@ -1211,7 +1278,7 @@ WHERE Name="inner_mtmv_75043";
详情参考 [JOBS](../../../sql-manual/sql-functions/table-valued-functions/jobs)
-#### 查询物化视图的分区信息：
+### 查询物化视图的分区信息：
分区物化视图查看 SyncWithBaseTables 状态方法
@@ -1230,12 +1297,12 @@ show partitions from mv11;
主要查看 `SyncWithBaseTables` 字段是否为 true。false 表示此分区不可用于透明改写。
-详情参考 [SHOW
PARTITIONS](../../../sql-manual/sql-statements/Show-Statements/SHOW-PARTITIONS)
+详情参考 [SHOW
PARTITIONS](../../../sql-manual/sql-statements/table-and-view/table/SHOW-PARTITIONS)
-#### 查看物化视图表结构
+### 查看物化视图表结构
-详情参考 [DESCRIBE](../../../sql-manual/sql-statements/Utility-Statements/DESCRIBE)
+详情参考
[DESCRIBE](../../../sql-manual/sql-statements/table-and-view/table/DESC-TABLE)
### 相关配置
#### Session Variables 开关
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query-acceleration/materialized-view/async-materialized-view/overview.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query-acceleration/materialized-view/async-materialized-view/overview.md
index 3af1a903729..d74207ce714 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query-acceleration/materialized-view/async-materialized-view/overview.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query-acceleration/materialized-view/async-materialized-view/overview.md
@@ -37,9 +37,9 @@ under the License.
## 使用限制
- 异步物化视图与基表数据一致性：异步物化视图与基表的数据最终会保持一致，但无法实时同步，即无法保持实时一致性。
- 窗口函数查询支持：当前，如果查询中包含了窗口函数，暂不支持将该查询透明地改写为利用物化视图的形式。
-- 包含ORDER BY的物化视图与查询：若物化视图本身包含了ORDER
BY子句，则系统暂不支持将该物化视图用于透明改写查询。但请注意，查询本身仍然可以包含ORDER BY子句。
- 物化视图连接表多于查询表：如果物化视图所连接的表数量多于查询所涉及的表（例如，查询仅涉及t1和t2，而物化视图则包含了t1、t2以及额外的t3），
则系统目前不支持将该查询透明地改写为利用该物化视图的形式。
+- 如果物化视图包含 UNION ALL等集合操作, LIMIT，ORDER BY，CROSS JOIN，物化视图可以正常构建，但是不能用于透明改写。
## 原理介绍
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
index 553ed65f0a6..ff2aa1a321b 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
@@ -80,6 +80,12 @@ CREATE MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW mvName AUTO;
```
+:::tip 提示
+如果物化视图定义 SQL 使用的基表是 JDBC 表，Doris 无法感知表数据变化，刷新物化视图时需要指定 COMPLETE。
+如果指定了 AUTO，会导致基表有数据，但是刷新后物化视图没数据。
+刷新物化视图时，目前 Doris 只能感知内表和 Hive 数据源表数据变化，其他数据源逐步支持中。
+:::
+
不校验基表的分区数据自上次刷新后是否有变化，直接刷新物化视图的所有分区。
```sql
@@ -251,8 +257,7 @@ STARTS 的时间要晚于当前的时间
CREATE MATERIALIZED VIEW mv_1_1
BUILD DEFERRED
REFRESH COMPLETE
-ON SCHEDULE EVERY 1 DAY STARTS '2024-12-01 20:30:00'
-PROPERTIES ('replication_num' = '1')
+ON SCHEDULE EVERY 1 DAY STARTS '2024-12-01 20:30:00'
AS
SELECT
l_linestatus,
@@ -272,8 +277,7 @@ LEFT JOIN lineitem ON l_orderkey = o_orderkey;
CREATE MATERIALIZED VIEW mv_1_1
BUILD IMMEDIATE
REFRESH COMPLETE
-ON COMMIT
-PROPERTIES ('replication_num' = '1')
+ON COMMIT
AS
SELECT
l_linestatus,
@@ -408,7 +412,6 @@ BUILD DEFERRED REFRESH AUTO ON MANUAL
partition by(`k2`)
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES (
-'partition_sync_limit'='1',
'partition_sync_time_unit'='DAY'
)
AS
@@ -874,7 +877,7 @@ as
select l_shipdate, o_orderdate, l_partkey,
l_suppkey, sum(o_totalprice) as sum_total
from lineitem
- left join orders on lineitem.l_orderkey = orders.o_orderkey and
l_shipdate = o_orderdate
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate
group by
l_shipdate,
o_orderdate,
@@ -894,7 +897,7 @@ insert into lineitem values
```sql
select l_shipdate, o_orderdate, l_partkey, l_suppkey, sum(o_totalprice) as
sum_total
from lineitem
- left join orders on lineitem.l_orderkey = orders.o_orderkey and
l_shipdate = o_orderdate
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate
group by
l_shipdate,
o_orderdate,
@@ -912,7 +915,7 @@ FROM mv7
union all
select t1.l_shipdate, o_orderdate, t1.l_partkey, t1.l_suppkey,
sum(o_totalprice) as sum_total
from (select * from lineitem where l_shipdate = '2023-10-21') t1
- left join orders on t1.l_orderkey = orders.o_orderkey and
t1.l_shipdate = o_orderdate
+left join orders on t1.l_orderkey = orders.o_orderkey and t1.l_shipdate =
o_orderdate
group by
t1.l_shipdate,
o_orderdate,
@@ -962,7 +965,6 @@ inner join orders on lineitem.l_orderkey =
orders.o_orderkey;
CREATE MATERIALIZED VIEW mv8_0
BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL
DISTRIBUTED BY RANDOM BUCKETS 2
-PROPERTIES ('replication_num' = '1')
AS
select
l_linenumber,
@@ -996,6 +998,40 @@ where o_orderstatus = 'o'
2. 嵌套物化视图透明改写默认关闭，开启方式见下面的相关设置。
+### 聚合查询使用非聚合物化视图改写
+
+:::tip 提示
+从 2.1.5 版本开始支持嵌套物化视图改写。
+:::
+
+如果查询是聚合查询，物化视图不包含聚合，但是物化视图可以提供查询使用的所有列，那么也可以改写，比如查询先是 join
+连接，之后是 group by 聚合，命中包含 join 连接的物化视图，那么也是有收益的。
+
+
+```sql
+CREATE MATERIALIZED VIEW mv10_0
+BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+DISTRIBUTED BY RANDOM BUCKETS 2
+as
+select l_shipdate, o_orderdate, l_partkey,
+ l_suppkey, o_totalprice
+from lineitem
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate;
+```
+
+如下查询可以命中 mv10_0 的物化视图，节省了 `lineitem join orders` 连接的计算
+```sql
+select l_shipdate, o_orderdate, l_partkey,
+ l_suppkey, sum(o_totalprice) as sum_total
+from lineitem
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate
+group by
+ l_shipdate,
+ o_orderdate,
+ l_partkey,
+ l_suppkey;
+```
+
### Explain 查询透明改写情况
查询透明改写命中情况，用于查看和调试。
@@ -1047,12 +1083,45 @@ explain memo plan <query_sql>
- 暂停/恢复/取消/刷新物化视图：需要具有物化视图的创建权限。
### 物化视图修改
+
+#### 修改物化视图属性
```sql
ALTER MATERIALIZED VIEW mv_1
SET(
"grace_period" = "10"
);
```
+
+详情参考 [ALTER ASYNC MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Data-Definition-Statements/Alter/ALTER-ASYNC-MATERIALIZED-VIEW)
+
+#### 物化视图重命名，即物化视图原子替换
+
+```sql
+
+CREATE MATERIALIZED VIEW mv9_0
+BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL
+DISTRIBUTED BY RANDOM BUCKETS 2
+PROPERTIES ('replication_num' = '1')
+AS
+select
+ l_linenumber,
+ o_custkey,
+ o_orderkey,
+ o_orderstatus,
+ l_partkey,
+ l_suppkey,
+ l_orderkey
+from lineitem
+inner join orders on lineitem.l_orderkey = orders.o_orderkey;
+```
+
+使用 mv9_0 的物化视图替换 mv7，并且删除 mv7。
+```sql
+ALTER MATERIALIZED VIEW mv7
+REPLACE WITH MATERIALIZED VIEW mv9_0
+PROPERTIES('swap' = 'false');
+```
+
详情参考 [ALTER ASYNC MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Data-Definition-Statements/Alter/ALTER-ASYNC-MATERIALIZED-VIEW)
@@ -1061,7 +1130,7 @@ SET(
DROP MATERIALIZED VIEW mv_1;
```
-详情参考 [DROP ASYNC MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Data-Definition-Statements/Drop/DROP-ASYNC-MATERIALIZED-VIEW)
+详情参考 [DROP ASYNC MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/DROP-ASYNC-MATERIALIZED-VIEW)
### 查看物化视图创建语句
@@ -1069,7 +1138,7 @@ DROP MATERIALIZED VIEW mv_1;
SHOW CREATE MATERIALIZED VIEW mv_1;
```
-详情参考 [SHOW CREATE MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Show-Statements/SHOW-CREATE-MATERIALIZED-VIEW/)
+详情参考 [SHOW CREATE MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/SHOW-CREATE-MATERIALIZED-VIEW)
:::tip 提示
从 2.1.5 版本开始支持。
@@ -1077,19 +1146,17 @@ SHOW CREATE MATERIALIZED VIEW mv_1;
### 暂停物化视图
-详情参考 [PAUSE MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Utility-Statements/PAUSE-MATERIALIZED-VIEW)
+详情参考 [PAUSE MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/PAUSE-MATERIALIZED-VIEW)
### 启用物化视图
-详情参考 [RESUME MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Utility-Statements/RESUME-MATERIALIZED-VIEW)
+详情参考 [RESUME MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/RESUME-MATERIALIZED-VIEW)
### 取消物化视图刷新任务
-详情参考 [CANCEL MATERIALIZED VIEW
TASK](../../../sql-manual/sql-statements/Utility-Statements/CANCEL-MATERIALIZED-VIEW-TASK)
-
-### 元数据查询
+详情参考 [CANCEL MATERIALIZED VIEW
TASK](../../../sql-manual/sql-statements/table-and-view/materialized-view/CANCEL-MATERIALIZED-VIEW-TASK)
-#### 查询物化视图信息
+### 查询物化视图信息
```sql
SELECT *
@@ -1119,9 +1186,9 @@ SyncWithBaseTables: 1
- **SyncWithBaseTables：** 表示物化视图和基表的数据是否一致。
- - 对于全量构建的物化视图，此字段为 1，表明此物化视图可用于透明改写。
+ - 对于全量构建的物化视图，此字段为 1，表明此物化视图可用于透明改写。
- -
对于分区增量的物化视图，分区物化视图是否可用，是以分区粒度去看的。也就是说，即使物化视图的部分分区不可用，但只要查询的是有效分区，那么此物化视图依旧可用于透明改写。是否能透明改写，主要看查询所用分区的
`SyncWithBaseTables` 字段是否一致。如果 `SyncWithBaseTables` 是 1，此分区可用于透明改写；如果是
0，则不能用于透明改写。
+ -
对于分区增量的物化视图，分区物化视图是否可用，是以分区粒度去看的。也就是说，即使物化视图的部分分区不可用，但只要查询的是有效分区，那么此物化视图依旧可用于透明改写。是否能透明改写，主要看查询所用分区的
`SyncWithBaseTables` 字段是否一致。如果 `SyncWithBaseTables` 是 1，此分区可用于透明改写；如果是
0，则不能用于透明改写。
- **JobName：** 物化视图构建 Job 的名称，每个物化视图有一个 Job，每次刷新会有一个新的 Task，Job 和 Task 是 1:n
的关系
@@ -1129,7 +1196,7 @@ SyncWithBaseTables: 1
- **SchemaChangeDetail：** 表示 SCHEMA_CHANGE 发生的原因。
-- **RefreshState：** 物化视图最后一次任务刷新的状态。如果为 FAIL，代表执行失败，可以通过 `tasks()
`命令进一步定位失败原因。Task 命令见本文[查看物化视图 Task 状态](#查看物化视图-task-状态)。
+- **RefreshState：** 物化视图最后一次任务刷新的状态。如果为 FAIL，代表执行失败，可以通过 `tasks()
`命令进一步定位失败原因。Task 命令见本文[查询刷新任务 TASK 信息](### 查询刷新任务 TASK 信息)。
- **SyncWithBaseTables：** 是否和基表数据同步。1 为同步，0 为不同步。如果不同步，可通过 `show partitions`
进一步判断哪个分区不同步。`show partitions` 见下文分区物化视图查看 SyncWithBaseTables 状态方法。
@@ -1143,7 +1210,7 @@ SyncWithBaseTables: 1
详情参考
[MV_INFOS](../../../sql-manual/sql-functions/table-valued-functions/mv_infos)
-#### 查询刷新任务 TASK 信息
+### 查询刷新任务 TASK 信息
每个物化视图有一个 Job，每次刷新会有一个新的 Task，Job 和 Task 是 1:n 的关系。
根据物化视图名称查看物化视图的 Task 状态，运行如下语句，可以查看刷新任务的状态和进度：
@@ -1200,7 +1267,7 @@ NeedRefreshPartitions:
["p_20231023_20231024","p_20231019_20231020","p_20231020_
详情参考
[TASKS](../../../sql-manual/sql-functions/table-valued-functions/tasks?_highlight=task)
-#### 查询物化视图对应的 JOB
+### 查询物化视图对应的 JOB
```sql
SELECT *
@@ -1211,7 +1278,7 @@ WHERE Name="inner_mtmv_75043";
详情参考 [JOBS](../../../sql-manual/sql-functions/table-valued-functions/jobs)
-#### 查询物化视图的分区信息：
+### 查询物化视图的分区信息：
分区物化视图查看 SyncWithBaseTables 状态方法
@@ -1230,12 +1297,12 @@ show partitions from mv11;
主要查看 `SyncWithBaseTables` 字段是否为 true。false 表示此分区不可用于透明改写。
-详情参考 [SHOW
PARTITIONS](../../../sql-manual/sql-statements/Show-Statements/SHOW-PARTITIONS)
+详情参考 [SHOW
PARTITIONS](../../../sql-manual/sql-statements/table-and-view/table/SHOW-PARTITIONS)
-#### 查看物化视图表结构
+### 查看物化视图表结构
-详情参考 [DESCRIBE](../../../sql-manual/sql-statements/Utility-Statements/DESCRIBE)
+详情参考
[DESCRIBE](../../../sql-manual/sql-statements/table-and-view/table/DESC-TABLE)
### 相关配置
#### Session Variables 开关
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/query-acceleration/materialized-view/async-materialized-view/overview.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/query-acceleration/materialized-view/async-materialized-view/overview.md
index 3af1a903729..d74207ce714 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/query-acceleration/materialized-view/async-materialized-view/overview.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-3.0/query-acceleration/materialized-view/async-materialized-view/overview.md
@@ -37,9 +37,9 @@ under the License.
## 使用限制
- 异步物化视图与基表数据一致性：异步物化视图与基表的数据最终会保持一致，但无法实时同步，即无法保持实时一致性。
- 窗口函数查询支持：当前，如果查询中包含了窗口函数，暂不支持将该查询透明地改写为利用物化视图的形式。
-- 包含ORDER BY的物化视图与查询：若物化视图本身包含了ORDER
BY子句，则系统暂不支持将该物化视图用于透明改写查询。但请注意，查询本身仍然可以包含ORDER BY子句。
- 物化视图连接表多于查询表：如果物化视图所连接的表数量多于查询所涉及的表（例如，查询仅涉及t1和t2，而物化视图则包含了t1、t2以及额外的t3），
则系统目前不支持将该查询透明地改写为利用该物化视图的形式。
+- 如果物化视图包含 UNION ALL等集合操作, LIMIT，ORDER BY，CROSS JOIN，物化视图可以正常构建，但是不能用于透明改写。
## 原理介绍
diff --git
a/versioned_docs/version-2.1/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
b/versioned_docs/version-2.1/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
index c484b3df964..9734a2f3282 100644
---
a/versioned_docs/version-2.1/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
+++
b/versioned_docs/version-2.1/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
@@ -80,6 +80,15 @@ Check for base table partition data changes since last
refresh and refresh only
REFRESH MATERIALIZED VIEW mvName AUTO;
```
+:::tip
+If the base table used in the SQL definition of the materialized view is a
JDBC table,
+Doris cannot perceive changes in the table data. When refreshing the
materialized view,
+it is necessary to specify COMPLETE. If AUTO is specified, it may result in
the base table
+having data, but the materialized view being empty after the refresh.
Currently,
+when refreshing the materialized view, Doris can only perceive data changes in
internal
+tables and Hive data source tables; support for other data sources is being
gradually implemented.
+:::
+
Refresh all materialized view partitions without checking for base table
changes:
```sql
@@ -253,7 +262,6 @@ CREATE MATERIALIZED VIEW mv_1_1
BUILD DEFERRED
REFRESH COMPLETE
ON SCHEDULE EVERY 1 DAY STARTS '2024-12-01 20:30:00'
-PROPERTIES ('replication_num' = '1')
AS
SELECT
l_linestatus,
@@ -272,8 +280,7 @@ In this example, the refresh timing is set to immediate
refresh upon creation (`
CREATE MATERIALIZED VIEW mv_1_1
BUILD IMMEDIATE
REFRESH COMPLETE
-ON COMMIT
-PROPERTIES ('replication_num' = '1')
+ON COMMIT
AS
SELECT
l_linestatus,
@@ -412,7 +419,6 @@ ON MANUAL
PARTITION BY (k2)
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES (
-'partition_sync_limit'='1',
'partition_sync_time_unit'='DAY'
)
AS
@@ -883,7 +889,7 @@ as
select l_shipdate, o_orderdate, l_partkey,
l_suppkey, sum(o_totalprice) as sum_total
from lineitem
- left join orders on lineitem.l_orderkey = orders.o_orderkey and
l_shipdate = o_orderdate
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate
group by
l_shipdate,
o_orderdate,
@@ -903,7 +909,7 @@ insert into lineitem values
```sql
select l_shipdate, o_orderdate, l_partkey, l_suppkey, sum(o_totalprice) as
sum_total
from lineitem
- left join orders on lineitem.l_orderkey = orders.o_orderkey and
l_shipdate = o_orderdate
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate
group by
l_shipdate,
o_orderdate,
@@ -921,7 +927,7 @@ FROM mv7
union all
select t1.l_shipdate, o_orderdate, t1.l_partkey, t1.l_suppkey,
sum(o_totalprice) as sum_total
from (select * from lineitem where l_shipdate = '2023-10-21') t1
- left join orders on t1.l_orderkey = orders.o_orderkey and
t1.l_shipdate = o_orderdate
+left join orders on t1.l_orderkey = orders.o_orderkey and t1.l_shipdate =
o_orderdate
group by
t1.l_shipdate,
o_orderdate,
@@ -966,7 +972,6 @@ inner join orders on lineitem.l_orderkey =
orders.o_orderkey;
CREATE MATERIALIZED VIEW mv8_0
BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL
DISTRIBUTED BY RANDOM BUCKETS 2
-PROPERTIES ('replication_num' = '1')
AS
select
l_linenumber,
@@ -1002,6 +1007,45 @@ Note:
2. Nested materialized view transparent rewriting is disabled by default. See
the related settings below for how to enable it.
+
+### Aggregate Query Using Non-Aggregate Materialized View Rewrite
+
+:::tip Note
+Starting from version 2.1.5, nested materialized view rewriting is supported.
+:::
+
+If the query is an aggregate query and the materialized view does not contain
aggregates,
+but the materialized view can provide all the columns used in the query, then
it can also be rewritten.
+For example, if the query first performs a join and then a group by
aggregation,
+hitting a materialized view that includes the join will also yield benefits.
+
+```sql
+CREATE MATERIALIZED VIEW mv10_0
+BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+DISTRIBUTED BY RANDOM BUCKETS 2
+as
+select l_shipdate, o_orderdate, l_partkey,
+ l_suppkey, o_totalprice
+from lineitem
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate;
+```
+
+The following query can hit the mv10_0 materialized view, saving the
computation of the
+lineitem join orders join:
+
+```sql
+select l_shipdate, o_orderdate, l_partkey,
+ l_suppkey, sum(o_totalprice) as sum_total
+from lineitem
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate
+group by
+ l_shipdate,
+ o_orderdate,
+ l_partkey,
+ l_suppkey;
+```
+
+
### Explain Query Transparent Rewriting Status
To view materialized view transparent rewriting hits, used for viewing and
debugging.
@@ -1052,27 +1096,31 @@ explain memo plan <query_sql>
- Pausing/resuming/canceling/refreshing materialized views: Requires
materialized view creation permission
### Modifying Materialized Views
+
+#### Modifying Materialized View Properties
+
```sql
ALTER MATERIALIZED VIEW mv_1
SET(
"grace_period" = "10"
);
```
-For more details, see [ALTER ASYNC MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Data-Definition-Statements/Alter/ALTER-ASYNC-MATERIALIZED-VIEW)
+
+For more details, see [ALTER ASYNC MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/ALTER-ASYNC-MATERIALIZED-VIEW)
### Dropping Materialized Views
```sql
DROP MATERIALIZED VIEW mv_1;
```
-For more details, see [DROP ASYNC MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Data-Definition-Statements/Drop/DROP-ASYNC-MATERIALIZED-VIEW)
+For more details, see [DROP ASYNC MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/DROP-ASYNC-MATERIALIZED-VIEW)
### Viewing Materialized View Creation Statement
```sql
SHOW CREATE MATERIALIZED VIEW mv_1;
```
-For more details, see [SHOW CREATE MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Show-Statements/SHOW-CREATE-MATERIALIZED-VIEW/)
+For more details, see [SHOW CREATE MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/SHOW-CREATE-MATERIALIZED-VIEW)
:::tip Note
Supported starting from version 2.1.5.
@@ -1080,19 +1128,18 @@ Supported starting from version 2.1.5.
### Pausing Materialized Views
-For more details, see [PAUSE MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Utility-Statements/PAUSE-MATERIALIZED-VIEW)
+For more details, see [PAUSE MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/PAUSE-MATERIALIZED-VIEW-JOB)
### Resuming Materialized Views
-For more details, see [RESUME MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Utility-Statements/RESUME-MATERIALIZED-VIEW)
+For more details, see [RESUME MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/RESUME-MATERIALIZED-VIEW-JOB)
### Canceling Materialized View Refresh Tasks
-For more details, see [CANCEL MATERIALIZED VIEW
TASK](../../../sql-manual/sql-statements/Utility-Statements/CANCEL-MATERIALIZED-VIEW-TASK)
+For more details, see [CANCEL MATERIALIZED VIEW
TASK](../../../sql-manual/sql-statements/table-and-view/materialized-view/CANCEL-MATERIALIZED-VIEW-TASK)
-### Metadata Queries
-#### Querying Materialized View Information
+### Querying Materialized View Information
```sql
SELECT *
@@ -1130,7 +1177,7 @@ SyncWithBaseTables: 1
- **SchemaChangeDetail:** Explains the reason for SCHEMA_CHANGE.
-- **RefreshState:** Status of the last refresh task. If FAIL, indicates
execution failed - use the `tasks()` command to identify the cause. See
[Viewing Materialized View Task Status](#viewing-materialized-view-task-status)
section.
+- **RefreshState:** Status of the last refresh task. If FAIL, indicates
execution failed - use the `tasks()` command to identify the cause. See
[Querying Refresh Task Information](### Querying Refresh Task Information)
section.
- **SyncWithBaseTables:** Whether synchronized with base tables. 1 means
synchronized, 0 means not synchronized. If not synchronized, use `show
partitions` to check which partitions are out of sync. See the section below on
checking SyncWithBaseTables status for partitioned materialized views.
@@ -1141,7 +1188,7 @@ For transparent rewriting, materialized views typically
have two states:
For more details, see
[MV_INFOS](../../../sql-manual/sql-functions/table-valued-functions/mv_infos)
-#### Querying Refresh Task Information
+### Querying Refresh Task Information
Each materialized view has one Job, and each refresh creates a new Task, with
a 1:n relationship between Jobs and Tasks.
To view a materialized view's Task status by name, run the following query to
check refresh task status and progress:
@@ -1197,7 +1244,7 @@ NeedRefreshPartitions:
["p_20231023_20231024","p_20231019_20231020","p_20231020_
For more details, see
[TASKS](../../../sql-manual/sql-functions/table-valued-functions/tasks?_highlight=task)
-#### Querying Materialized View Jobs
+### Querying Materialized View Jobs
```sql
SELECT *
@@ -1226,11 +1273,11 @@ show partitions from mv11;
Check the `SyncWithBaseTables` field - false indicates the partition is not
available for transparent rewriting.
-For more details, see [SHOW
PARTITIONS](../../../sql-manual/sql-statements/Show-Statements/SHOW-PARTITIONS)
+For more details, see [SHOW
PARTITIONS](../../../sql-manual/sql-statements/table-and-view/table/SHOW-PARTITIONS)
-#### Viewing Materialized View Table Structure
+### Viewing Materialized View Table Structure
-For more details, see
[DESCRIBE](../../../sql-manual/sql-statements/Utility-Statements/DESCRIBE)
+For more details, see
[DESCRIBE](../../../sql-manual/sql-statements/table-and-view/table/DESC-TABLE)
### Related Configuration
#### Session Variables
diff --git
a/versioned_docs/version-2.1/query-acceleration/materialized-view/async-materialized-view/overview.md
b/versioned_docs/version-2.1/query-acceleration/materialized-view/async-materialized-view/overview.md
index 2d0c857a415..6c54bf173aa 100644
---
a/versioned_docs/version-2.1/query-acceleration/materialized-view/async-materialized-view/overview.md
+++
b/versioned_docs/version-2.1/query-acceleration/materialized-view/async-materialized-view/overview.md
@@ -40,8 +40,8 @@ when query requests arrive, thus avoiding the overhead of
re-executing complex q
## Limitations
- **Consistency of Asynchronous Materialized Views with Base Table Data**:
Asynchronous materialized views will eventually be consistent with the base
table data, but they cannot be synchronized in real-time, meaning real-time
consistency cannot be maintained.
- **Support for Window Function Queries**: Currently, if a query contains
window functions, it is not supported to transparently rewrite that query to
utilize materialized views.
-- **Materialized Views with ORDER BY and Queries**: If the materialized view
itself contains an ORDER BY clause, the system does not currently support using
that materialized view for transparent query rewriting. However, please note
that the query itself can still include an ORDER BY clause.
- **Materialized Views Joining More Tables than Query Tables**: If the number
of tables joined in the materialized view exceeds the number of tables involved
in the query (for example, if the query only involves t1 and t2, while the
materialized view includes t1, t2, and an additional t3), the system currently
does not support transparently rewriting that query to utilize the materialized
view.
+- If the materialized view contains set operations such as UNION ALL, LIMIT,
ORDER BY, or CROSS JOIN, the materialized view can be built normally, but it
cannot be used for transparent rewriting.
## Principle Introduction
diff --git
a/versioned_docs/version-3.0/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
b/versioned_docs/version-3.0/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
index c484b3df964..33727a3361f 100644
---
a/versioned_docs/version-3.0/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
+++
b/versioned_docs/version-3.0/query-acceleration/materialized-view/async-materialized-view/functions-and-demands.md
@@ -80,6 +80,15 @@ Check for base table partition data changes since last
refresh and refresh only
REFRESH MATERIALIZED VIEW mvName AUTO;
```
+:::tip
+If the base table used in the SQL definition of the materialized view is a
JDBC table,
+Doris cannot perceive changes in the table data. When refreshing the
materialized view,
+it is necessary to specify COMPLETE. If AUTO is specified, it may result in
the base table
+having data, but the materialized view being empty after the refresh.
Currently,
+when refreshing the materialized view, Doris can only perceive data changes in
internal
+tables and Hive data source tables; support for other data sources is being
gradually implemented.
+:::
+
Refresh all materialized view partitions without checking for base table
changes:
```sql
@@ -253,7 +262,6 @@ CREATE MATERIALIZED VIEW mv_1_1
BUILD DEFERRED
REFRESH COMPLETE
ON SCHEDULE EVERY 1 DAY STARTS '2024-12-01 20:30:00'
-PROPERTIES ('replication_num' = '1')
AS
SELECT
l_linestatus,
@@ -272,8 +280,7 @@ In this example, the refresh timing is set to immediate
refresh upon creation (`
CREATE MATERIALIZED VIEW mv_1_1
BUILD IMMEDIATE
REFRESH COMPLETE
-ON COMMIT
-PROPERTIES ('replication_num' = '1')
+ON COMMIT
AS
SELECT
l_linestatus,
@@ -412,7 +419,6 @@ ON MANUAL
PARTITION BY (k2)
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES (
-'partition_sync_limit'='1',
'partition_sync_time_unit'='DAY'
)
AS
@@ -883,7 +889,7 @@ as
select l_shipdate, o_orderdate, l_partkey,
l_suppkey, sum(o_totalprice) as sum_total
from lineitem
- left join orders on lineitem.l_orderkey = orders.o_orderkey and
l_shipdate = o_orderdate
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate
group by
l_shipdate,
o_orderdate,
@@ -903,7 +909,7 @@ insert into lineitem values
```sql
select l_shipdate, o_orderdate, l_partkey, l_suppkey, sum(o_totalprice) as
sum_total
from lineitem
- left join orders on lineitem.l_orderkey = orders.o_orderkey and
l_shipdate = o_orderdate
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate
group by
l_shipdate,
o_orderdate,
@@ -921,7 +927,7 @@ FROM mv7
union all
select t1.l_shipdate, o_orderdate, t1.l_partkey, t1.l_suppkey,
sum(o_totalprice) as sum_total
from (select * from lineitem where l_shipdate = '2023-10-21') t1
- left join orders on t1.l_orderkey = orders.o_orderkey and
t1.l_shipdate = o_orderdate
+left join orders on t1.l_orderkey = orders.o_orderkey and t1.l_shipdate =
o_orderdate
group by
t1.l_shipdate,
o_orderdate,
@@ -966,7 +972,6 @@ inner join orders on lineitem.l_orderkey =
orders.o_orderkey;
CREATE MATERIALIZED VIEW mv8_0
BUILD IMMEDIATE REFRESH COMPLETE ON MANUAL
DISTRIBUTED BY RANDOM BUCKETS 2
-PROPERTIES ('replication_num' = '1')
AS
select
l_linenumber,
@@ -1002,6 +1007,45 @@ Note:
2. Nested materialized view transparent rewriting is disabled by default. See
the related settings below for how to enable it.
+
+### Aggregate Query Using Non-Aggregate Materialized View Rewrite
+
+:::tip Note
+Starting from version 2.1.5, nested materialized view rewriting is supported.
+:::
+
+If the query is an aggregate query and the materialized view does not contain
aggregates,
+but the materialized view can provide all the columns used in the query, then
it can also be rewritten.
+For example, if the query first performs a join and then a group by
aggregation,
+hitting a materialized view that includes the join will also yield benefits.
+
+```sql
+CREATE MATERIALIZED VIEW mv10_0
+BUILD IMMEDIATE REFRESH AUTO ON MANUAL
+DISTRIBUTED BY RANDOM BUCKETS 2
+as
+select l_shipdate, o_orderdate, l_partkey,
+ l_suppkey, o_totalprice
+from lineitem
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate;
+```
+
+The following query can hit the mv10_0 materialized view, saving the
computation of the
+lineitem join orders join:
+
+```sql
+select l_shipdate, o_orderdate, l_partkey,
+ l_suppkey, sum(o_totalprice) as sum_total
+from lineitem
+left join orders on lineitem.l_orderkey = orders.o_orderkey and l_shipdate =
o_orderdate
+group by
+ l_shipdate,
+ o_orderdate,
+ l_partkey,
+ l_suppkey;
+```
+
+
### Explain Query Transparent Rewriting Status
To view materialized view transparent rewriting hits, used for viewing and
debugging.
@@ -1052,27 +1096,31 @@ explain memo plan <query_sql>
- Pausing/resuming/canceling/refreshing materialized views: Requires
materialized view creation permission
### Modifying Materialized Views
+
+#### Modifying Materialized View Properties
+
```sql
ALTER MATERIALIZED VIEW mv_1
SET(
"grace_period" = "10"
);
```
-For more details, see [ALTER ASYNC MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Data-Definition-Statements/Alter/ALTER-ASYNC-MATERIALIZED-VIEW)
+
+For more details, see [ALTER ASYNC MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/ALTER-ASYNC-MATERIALIZED-VIEW)
### Dropping Materialized Views
```sql
DROP MATERIALIZED VIEW mv_1;
```
-For more details, see [DROP ASYNC MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Data-Definition-Statements/Drop/DROP-ASYNC-MATERIALIZED-VIEW)
+For more details, see [DROP ASYNC MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/DROP-ASYNC-MATERIALIZED-VIEW)
### Viewing Materialized View Creation Statement
```sql
SHOW CREATE MATERIALIZED VIEW mv_1;
```
-For more details, see [SHOW CREATE MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Show-Statements/SHOW-CREATE-MATERIALIZED-VIEW/)
+For more details, see [SHOW CREATE MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/SHOW-CREATE-MATERIALIZED-VIEW)
:::tip Note
Supported starting from version 2.1.5.
@@ -1080,19 +1128,18 @@ Supported starting from version 2.1.5.
### Pausing Materialized Views
-For more details, see [PAUSE MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Utility-Statements/PAUSE-MATERIALIZED-VIEW)
+For more details, see [PAUSE MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/PAUSE-MATERIALIZED-VIEW-JOB)
### Resuming Materialized Views
-For more details, see [RESUME MATERIALIZED
VIEW](../../../sql-manual/sql-statements/Utility-Statements/RESUME-MATERIALIZED-VIEW)
+For more details, see [RESUME MATERIALIZED
VIEW](../../../sql-manual/sql-statements/table-and-view/materialized-view/RESUME-MATERIALIZED-VIEW-JOB)
### Canceling Materialized View Refresh Tasks
-For more details, see [CANCEL MATERIALIZED VIEW
TASK](../../../sql-manual/sql-statements/Utility-Statements/CANCEL-MATERIALIZED-VIEW-TASK)
+For more details, see [CANCEL MATERIALIZED VIEW
TASK](../../../sql-manual/sql-statements/table-and-view/materialized-view/CANCEL-MATERIALIZED-VIEW-TASK)
-### Metadata Queries
-#### Querying Materialized View Information
+### Querying Materialized View Information
```sql
SELECT *
@@ -1121,8 +1168,8 @@ SyncWithBaseTables: 1
```
- **SyncWithBaseTables:** Indicates whether the materialized view is
synchronized with base tables.
- - For fully built materialized views, a value of 1 indicates the view is
available for transparent rewriting.
- - For incrementally partitioned materialized views, availability is
determined at the partition level. Even if some partitions are unavailable, the
view can still be used for transparent rewriting if the queried partitions are
valid. The ability to use transparent rewriting depends on the
`SyncWithBaseTables` value of the queried partitions - 1 means available, 0
means unavailable.
+ - For fully built materialized views, a value of 1 indicates the view is
available for transparent rewriting.
+ - For incrementally partitioned materialized views, availability is
determined at the partition level. Even if some partitions are unavailable, the
view can still be used for transparent rewriting if the queried partitions are
valid. The ability to use transparent rewriting depends on the
`SyncWithBaseTables` value of the queried partitions - 1 means available, 0
means unavailable.
- **JobName:** Name of the materialized view's build job. Each materialized
view has one Job, and each refresh creates a new Task, with a 1:n relationship
between Jobs and Tasks.
@@ -1130,7 +1177,7 @@ SyncWithBaseTables: 1
- **SchemaChangeDetail:** Explains the reason for SCHEMA_CHANGE.
-- **RefreshState:** Status of the last refresh task. If FAIL, indicates
execution failed - use the `tasks()` command to identify the cause. See
[Viewing Materialized View Task Status](#viewing-materialized-view-task-status)
section.
+- **RefreshState:** Status of the last refresh task. If FAIL, indicates
execution failed - use the `tasks()` command to identify the cause. See
[Querying Refresh Task Information](### Querying Refresh Task Information)
section.
- **SyncWithBaseTables:** Whether synchronized with base tables. 1 means
synchronized, 0 means not synchronized. If not synchronized, use `show
partitions` to check which partitions are out of sync. See the section below on
checking SyncWithBaseTables status for partitioned materialized views.
@@ -1141,7 +1188,7 @@ For transparent rewriting, materialized views typically
have two states:
For more details, see
[MV_INFOS](../../../sql-manual/sql-functions/table-valued-functions/mv_infos)
-#### Querying Refresh Task Information
+### Querying Refresh Task Information
Each materialized view has one Job, and each refresh creates a new Task, with
a 1:n relationship between Jobs and Tasks.
To view a materialized view's Task status by name, run the following query to
check refresh task status and progress:
@@ -1197,7 +1244,7 @@ NeedRefreshPartitions:
["p_20231023_20231024","p_20231019_20231020","p_20231020_
For more details, see
[TASKS](../../../sql-manual/sql-functions/table-valued-functions/tasks?_highlight=task)
-#### Querying Materialized View Jobs
+### Querying Materialized View Jobs
```sql
SELECT *
@@ -1226,11 +1273,11 @@ show partitions from mv11;
Check the `SyncWithBaseTables` field - false indicates the partition is not
available for transparent rewriting.
-For more details, see [SHOW
PARTITIONS](../../../sql-manual/sql-statements/Show-Statements/SHOW-PARTITIONS)
+For more details, see [SHOW
PARTITIONS](../../../sql-manual/sql-statements/table-and-view/table/SHOW-PARTITIONS)
-#### Viewing Materialized View Table Structure
+### Viewing Materialized View Table Structure
-For more details, see
[DESCRIBE](../../../sql-manual/sql-statements/Utility-Statements/DESCRIBE)
+For more details, see
[DESCRIBE](../../../sql-manual/sql-statements/table-and-view/table/DESC-TABLE)
### Related Configuration
#### Session Variables
diff --git
a/versioned_docs/version-3.0/query-acceleration/materialized-view/async-materialized-view/overview.md
b/versioned_docs/version-3.0/query-acceleration/materialized-view/async-materialized-view/overview.md
index 2d0c857a415..6c54bf173aa 100644
---
a/versioned_docs/version-3.0/query-acceleration/materialized-view/async-materialized-view/overview.md
+++
b/versioned_docs/version-3.0/query-acceleration/materialized-view/async-materialized-view/overview.md
@@ -40,8 +40,8 @@ when query requests arrive, thus avoiding the overhead of
re-executing complex q
## Limitations
- **Consistency of Asynchronous Materialized Views with Base Table Data**:
Asynchronous materialized views will eventually be consistent with the base
table data, but they cannot be synchronized in real-time, meaning real-time
consistency cannot be maintained.
- **Support for Window Function Queries**: Currently, if a query contains
window functions, it is not supported to transparently rewrite that query to
utilize materialized views.
-- **Materialized Views with ORDER BY and Queries**: If the materialized view
itself contains an ORDER BY clause, the system does not currently support using
that materialized view for transparent query rewriting. However, please note
that the query itself can still include an ORDER BY clause.
- **Materialized Views Joining More Tables than Query Tables**: If the number
of tables joined in the materialized view exceeds the number of tables involved
in the query (for example, if the query only involves t1 and t2, while the
materialized view includes t1, t2, and an additional t3), the system currently
does not support transparently rewriting that query to utilize the materialized
view.
+- If the materialized view contains set operations such as UNION ALL, LIMIT,
ORDER BY, or CROSS JOIN, the materialized view can be built normally, but it
cannot be used for transparent rewriting.
## Principle Introduction
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
