This is an automated email from the ASF dual-hosted git repository.
luzhijing 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 af16e67b4864 [doc](mtmv) Optimize query rewrite by materialized view
sample code (#438)
af16e67b4864 is described below
commit af16e67b4864334d24735bfb1a7ba63cadb17172
Author: seawinde <[email protected]>
AuthorDate: Wed Mar 20 22:40:46 2024 +0800
[doc](mtmv) Optimize query rewrite by materialized view sample code (#438)
---
.../query-async-materialized-view.md | 174 ++++++++++++++-------
.../Create/CREATE-ASYNC-MATERIALIZED-VIEW.md | 26 +--
.../query-async-materialized-view.md | 94 ++++++++---
.../Create/CREATE-ASYNC-MATERIALIZED-VIEW.md | 25 +--
.../query-async-materialized-view.md | 94 ++++++++---
.../Create/CREATE-ASYNC-MATERIALIZED-VIEW.md | 23 +--
.../query-async-materialized-view.md | 174 ++++++++++++++-------
.../Create/CREATE-ASYNC-MATERIALIZED-VIEW.md | 24 +--
8 files changed, 436 insertions(+), 198 deletions(-)
diff --git
a/docs/query-acceleration/async-materialized-view/query-async-materialized-view.md
b/docs/query-acceleration/async-materialized-view/query-async-materialized-view.md
index e6ff886bc5be..2d166fe0f3ec 100644
---
a/docs/query-acceleration/async-materialized-view/query-async-materialized-view.md
+++
b/docs/query-acceleration/async-materialized-view/query-async-materialized-view.md
@@ -1,7 +1,7 @@
---
{
- "title": "Querying Async Materialized View",
- "language": "en"
+ "title": "Querying Async Materialized View",
+ "language": "en"
}
---
@@ -29,10 +29,10 @@ under the License.
Doris's asynchronous materialized views employ an algorithm based on the SPJG
(SELECT-PROJECT-JOIN-GROUP-BY) pattern
structure information for transparent rewriting.
-Doris can analyze the structural information of query SQL, automatically
search for suitable materialized views,
+Doris can analyze the structural information of query SQL, automatically
search for suitable materialized views,
and attempt transparent rewriting, utilizing the optimal materialized view to
express the query SQL.
-By utilizing precomputed materialized view results,
+By utilizing precomputed materialized view results,
significant improvements in query performance and a reduction in computational
costs can be achieved.
Using the three tables: lineitem, orders, and partsupp from TPC-H, let's
describe the capability of directly querying
@@ -58,9 +58,14 @@ CREATE TABLE IF NOT EXISTS lineitem (
)
DUPLICATE KEY(l_orderkey, l_partkey, l_suppkey, l_linenumber)
PARTITION BY RANGE(l_shipdate)
- (FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
+(FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
DISTRIBUTED BY HASH(l_orderkey) BUCKETS 3
PROPERTIES ("replication_num" = "1");
+
+insert into lineitem values
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k',
'2023-10-17', '2023-10-17', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (2, 4, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k',
'2023-10-18', '2023-10-18', '2023-10-18', 'a', 'b', 'yyyyyyyyy'),
+ (3, 2, 4, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k',
'2023-10-19', '2023-10-19', '2023-10-19', 'a', 'b', 'yyyyyyyyy');
```
```sql
CREATE TABLE IF NOT EXISTS orders (
@@ -79,6 +84,12 @@ CREATE TABLE IF NOT EXISTS orders (
FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
DISTRIBUTED BY HASH(o_orderkey) BUCKETS 3
PROPERTIES ("replication_num" = "1");
+
+ insert into orders values
+ (1, 1, 'o', 9.5, '2023-10-17', 'a', 'b', 1, 'yy'),
+ (1, 1, 'o', 10.5, '2023-10-18', 'a', 'b', 1, 'yy'),
+ (2, 1, 'o', 11.5, '2023-10-19', 'a', 'b', 1, 'yy'),
+ (3, 1, 'o', 12.5, '2023-10-19', 'a', 'b', 1, 'yy');
```
```sql
@@ -94,32 +105,37 @@ CREATE TABLE IF NOT EXISTS orders (
PROPERTIES (
"replication_num" = "1"
);
+
+ insert into partsupp values
+ (2, 3, 9, 10.01, 'supply1'),
+ (4, 3, 10, 11.01, 'supply2'),
+ (2, 3, 10, 11.01, 'supply3');
```
## Direct Query of Materialized View
A materialized view can be considered as a table and can be queried just like
a regular table.
-The syntax for defining a materialized view, details can be found in
+The syntax for defining a materialized view, details can be found in
[CREATE-ASYNC-MATERIALIZED-VIEW](../../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md)
Materialized view definition:
```sql
CREATE MATERIALIZED VIEW mv1
BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
-DISTRIBUTED BY RANDOM BUCKETS 12
+DISTRIBUTED BY RANDOM BUCKETS 3
PROPERTIES ('replication_num' = '1')
AS
SELECT t1.l_linenumber,
o_custkey,
o_orderdate
-FROM (SELECT * FROM lineitem WHERE l_linenumber > 1) t1
- LEFT OUTER JOIN orders
- ON l_orderkey = o_orderkey;
+FROM
+ (SELECT * FROM lineitem WHERE l_linenumber > 1) t1
+LEFT OUTER JOIN orders ON l_orderkey = o_orderkey;
```
Query statement:
Direct queries can be performed on the materialized view with additional
filtering conditions and aggregations.
-
+
```sql
SELECT l_linenumber,
o_custkey
@@ -131,20 +147,25 @@ WHERE l_linenumber > 1 and o_orderdate = '2023-12-31';
### Join rewriting
-Join rewriting refers to when the tables used in the query and the
materialization are the same.
-In this case, the optimizer will attempt transparent rewriting by either
joining the input of the materialized
+Join rewriting refers to when the tables used in the query and the
materialization are the same.
+In this case, the optimizer will attempt transparent rewriting by either
joining the input of the materialized
view with the query or placing the join in the outer layer of the query's
WHERE clause.
-This pattern of rewriting is supported for multi-table joins and supports
inner and left join types.
+This pattern of rewriting is supported for multi-table joins and supports
inner and left join types.
Support for other types is continually expanding.
**Case 1:**
-The following case can undergo transparent rewriting. The condition
`l_linenumber > 1` allows for pull-up,
+The following case can undergo transparent rewriting. The condition
`l_linenumber > 1` allows for pull-up,
enabling transparent rewriting by expressing the query using the precomputed
results of the materialized view.
Materialized view definition:
```sql
+CREATE MATERIALIZED VIEW mv2
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT t1.l_linenumber,
o_custkey,
o_orderdate
@@ -160,19 +181,24 @@ SELECT l_linenumber,
FROM lineitem
LEFT OUTER JOIN orders
ON l_orderkey = o_orderkey
-WHERE l_linenumber > 1 and o_orderdate = '2023-12-31';
+WHERE l_linenumber > 1 and o_orderdate = '2023-10-18';
```
**Case 2:**
-JOIN Derivation occurs when the join type between the query and the
materialized view does not match.
-In cases where the materialization can provide all the necessary data for the
query, transparent rewriting can
+JOIN Derivation occurs when the join type between the query and the
materialized view does not match.
+In cases where the materialization can provide all the necessary data for the
query, transparent rewriting can
still be achieved by compensating predicates outside the join through
predicate push down.
For example:
Materialized view definition:
```sql
+CREATE MATERIALIZED VIEW mv3
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT
l_shipdate, l_suppkey, o_orderdate
sum(o_totalprice) AS sum_total,
@@ -191,7 +217,7 @@ o_orderdate;
Query statement:
```sql
SELECT
- l_shipdate, l_suppkey, o_orderdate
+ l_shipdate, l_suppkey, o_orderdate,
sum(o_totalprice) AS sum_total,
max(o_totalprice) AS max_total,
min(o_totalprice) AS min_total,
@@ -199,7 +225,7 @@ SELECT
count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN
o_custkey ELSE null END) AS bitmap_union_basic
FROM lineitem
INNER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey AND l_shipdate =
o_orderdate
-WHERE o_orderdate = '2023-12-11' AND l_suppkey = 3
+WHERE o_orderdate = '2023-10-18' AND l_suppkey = 3
GROUP BY
l_shipdate,
l_suppkey,
@@ -207,21 +233,26 @@ o_orderdate;
```
### Aggregate rewriting
-In the definitions of both the query and the materialized view, the aggregated
dimensions can either be consistent or inconsistent.
+In the definitions of both the query and the materialized view, the aggregated
dimensions can either be consistent or inconsistent.
Filtering of results can be achieved by using fields from the dimensions in
the WHERE clause.
-The dimensions used in the materialized view need to encompass those used in
the query,
+The dimensions used in the materialized view need to encompass those used in
the query,
and the metrics utilized in the query can be expressed using the metrics of
the materialized view.
**Case 1**
-The following case can undergo transparent rewriting. The query and the
materialized view use consistent dimensions
-for aggregation, allowing the use of fields from the dimensions to filter
results. The query will attempt to use the
+The following case can undergo transparent rewriting. The query and the
materialized view use consistent dimensions
+for aggregation, allowing the use of fields from the dimensions to filter
results. The query will attempt to use the
expressions after SELECT in the materialized view.
Materialized view definition:
```sql
+CREATE MATERIALIZED VIEW mv4
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT
o_shippriority, o_comment,
count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN
o_custkey ELSE null END) AS cnt_1,
@@ -256,17 +287,22 @@ o_comment;
**Case 2**
-The following query can be transparently rewritten: the query and the
materialization use aggregated dimensions
-that are inconsistent, but the dimensions used in the materialized view
encompass those used in the query.
+The following query can be transparently rewritten: the query and the
materialization use aggregated dimensions
+that are inconsistent, but the dimensions used in the materialized view
encompass those used in the query.
The query can filter results using fields from the dimensions.
-The query will attempt to roll up using the functions after SELECT, such as
the materialized view's
-bitmap_union will eventually roll up into bitmap_union_count, maintaining
consistency with the semantics of
+The query will attempt to roll up using the functions after SELECT, such as
the materialized view's
+bitmap_union will eventually roll up into bitmap_union_count, maintaining
consistency with the semantics of
the count(distinct) in the query.
Materialized view definition:
```sql
+CREATE MATERIALIZED VIEW mv5
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT
l_shipdate, o_orderdate, l_partkey, l_suppkey,
sum(o_totalprice) AS sum_total,
@@ -295,7 +331,7 @@ SELECT
count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN
o_custkey ELSE null END) AS bitmap_union_basic
FROM lineitem
LEFT OUTER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey AND
l_shipdate = o_orderdate
-WHERE o_orderdate = '2023-12-11' AND l_partkey = 3
+WHERE o_orderdate = '2023-10-18' AND l_partkey = 3
GROUP BY
l_shipdate,
l_suppkey;
@@ -314,8 +350,8 @@ Temporary support for the aggregation roll-up functions is
as follows:
| bitmap_union_count | bitmap_union | bitmap_union_count
|
## Query partial Transparent Rewriting (Coming soon)
-When the number of tables in the materialized view is greater than the query,
if the materialized view
-satisfies the conditions for JOIN elimination for tables more than the query,
transparent rewriting can also occur.
+When the number of tables in the materialized view is greater than the query,
if the materialized view
+satisfies the conditions for JOIN elimination for tables more than the query,
transparent rewriting can also occur.
For example:
**Case 1**
@@ -323,6 +359,11 @@ For example:
Materialized view definition:
```sql
+ CREATE MATERIALIZED VIEW mv6
+ BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+ DISTRIBUTED BY RANDOM BUCKETS 3
+ PROPERTIES ('replication_num' = '1')
+ AS
SELECT
l_linenumber,
o_custkey,
@@ -344,8 +385,8 @@ Query statement:
```
## Union Rewriting (Coming soon)
-When the materialized view is not sufficient to provide all the data for the
query, it can use Union to return
-data by combining the original table and the materialized view.
+When the materialized view is not sufficient to provide all the data for the
query, it can use Union to return
+data by combining the original table and the materialized view.
For example:
**Case 1**
@@ -353,6 +394,11 @@ For example:
Materialized view definition:
```sql
+CREATE MATERIALIZED VIEW mv7
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT
o_orderkey,
o_custkey,
@@ -394,21 +440,21 @@ WHERE o_orderkey > 5 AND o_orderkey <= 10;
The unit of `grace_period` is seconds, referring to the permissible time for
inconsistency between the materialized
view and the data in the underlying base tables.
-For example, setting `grace_period` to 0 means requiring the materialized view
to be consistent with the base
-table data before it can be used for transparent rewriting. As for external
tables,
+For example, setting `grace_period` to 0 means requiring the materialized view
to be consistent with the base
+table data before it can be used for transparent rewriting. As for external
tables,
since changes in data cannot be perceived, the materialized view is used with
them.
-Regardless of whether the data in the external table is up-to-date or not,
this materialized view can be used for
-transparent rewriting. If the external table is configured with an HMS
metadata source,
-it becomes capable of perceiving data changes. Configuring the metadata source
and enabling data change
+Regardless of whether the data in the external table is up-to-date or not,
this materialized view can be used for
+transparent rewriting. If the external table is configured with an HMS
metadata source,
+it becomes capable of perceiving data changes. Configuring the metadata source
and enabling data change
perception functionality will be supported in subsequent iterations.
-Setting `grace_period` to 10 means allowing a 10-second delay between the data
in the materialized view and
-the data in the base tables. If there is a delay of up to 10 seconds between
the data in the materialized
-view and the data in the base tables, the materialized view can still be used
for transparent rewriting within
+Setting `grace_period` to 10 means allowing a 10-second delay between the data
in the materialized view and
+the data in the base tables. If there is a delay of up to 10 seconds between
the data in the materialized
+view and the data in the base tables, the materialized view can still be used
for transparent rewriting within
that time frame.
-For internal tables in the materialized view, you can control the maximum
delay allowed for the data used by
-the transparent rewriting by setting the `grace_period` property.
+For internal tables in the materialized view, you can control the maximum
delay allowed for the data used by
+the transparent rewriting by setting the `grace_period` property.
Refer to
[CREATE-ASYNC-MATERIALIZED-VIEW](../../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md)
**Viewing and Debugging Transparent Rewrite Hit Information**
@@ -418,12 +464,20 @@ It will display a concise overview of the transparent
rewriting process.
`explain <query_sql>` The information returned is as follows, with the
relevant information pertaining to materialized views extracted:
```text
-| MaterializedView
|
-| MaterializedViewRewriteFail:
|
-| MaterializedViewRewriteSuccessButNotChose:
|
-| Names:
|
-| MaterializedViewRewriteSuccessAndChose:
|
-| Names: mv1
+| MaterializedView
|
+| MaterializedViewRewriteSuccessAndChose:
|
+| Names: mv5
|
+| MaterializedViewRewriteSuccessButNotChose:
|
+|
|
+| MaterializedViewRewriteFail:
|
+| Name: mv4
|
+| FailSummary: Match mode is invalid, View struct info is invalid
|
+| Name: mv3
|
+| FailSummary: Match mode is invalid, Rewrite compensate predicate by view
fail, View struct info is invalid
|
+| Name: mv1
|
+| FailSummary: The columns used by query are not in view, View struct info
is invalid
|
+| Name: mv2
|
+| FailSummary: The columns used by query are not in view, View struct info
is invalid
```
**MaterializedViewRewriteSuccessAndChose**: Transparent rewrite succeeded, and
the materialized view names list
@@ -452,20 +506,24 @@ you can execute the following statement. It will provide
a detailed breakdown of
## Limitations
-- The materialized view definition statement only allows SELECT, FROM, WHERE,
JOIN, and GROUP BY clauses.
-The input for JOIN can include simple GROUP BY (aggregation on a single
table).
-Supported types of JOIN operations include INNER and LEFT OUTER JOIN.
-Support for other types of JOIN operations will be gradually added.
+- The materialized view definition statement only allows SELECT, FROM, WHERE,
JOIN, and GROUP BY clauses.
+ The input for JOIN can include simple GROUP BY (aggregation on a single
table).
+ Supported types of JOIN operations include INNER and LEFT OUTER JOIN.
+ Support for other types of JOIN operations will be gradually added.
- Materialized views based on External Tables do not guarantee strong
consistency in query results.
- The use of non-deterministic functions to build materialized views is not
supported,
-including rand, now, current_time, current_date, random, uuid, etc.
+ including rand, now, current_time, current_date, random, uuid, etc.
+
+- Transparent rewriting does not support window functions.
-- Transparent rewriting does not support window functions and LIMIT.
+- There is LIMIT in queries and materialized views, and transparent rewriting
is not supported for the time being.
- Currently, materialized view definitions cannot utilize views or other
materialized views.
-- Currently, WHERE clause compensation supports scenarios where the
materialized view does not have a WHERE clause,
-but the query does, or where the materialized view has a WHERE clause and the
query's WHERE clause is a superset
-of the materialized view's. Range condition compensation is not yet supported
but will be added gradually.
+- When the query or materialized view has no data, transparent rewriting is
not supported.
+
+- Currently, WHERE clause compensation supports scenarios where the
materialized view does not have a WHERE clause,
+ but the query does, or where the materialized view has a WHERE clause and
the query's WHERE clause is a superset
+ of the materialized view's. Range condition compensation is not yet
supported but will be added gradually.
diff --git
a/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
b/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
index cf21b31a747a..f1fd88afd572 100644
---
a/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
+++
b/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
@@ -99,6 +99,18 @@ COMPLETE：Full refresh
AUTO：Try to refresh incrementally as much as possible. If incremental refresh
is not possible, refresh in full
+The SQL definition and partition fields of the materialized view need to meet
the following conditions for partition incremental updates:
+
+- At least one of the base tables used by the materialized view must be a
partitioned table.
+- The partitioned tables used by the materialized view must employ list or
range partitioning strategies.
+- The top-level partition column in the materialized view can only have one
partition field.
+- The SQL of the materialized view needs to use partition columns from the
base table, such as after the SELECT clause.
+- If GROUP BY is used, the partition column fields must be after the GROUP BY.
+- If window functions are used, the partition column fields must be after the
PARTITION BY.
+- Data changes should occur on partitioned tables. If they occur on
non-partitioned tables, the materialized view needs to be fully rebuilt.
+- Using the fields that generate nulls in the JOIN as partition fields in the
materialized view prohibits partition incremental updates.
+- The null attribute of partition fields in the base table used by the
materialized view must not be empty if it comes from an internal table. If it
comes from an external table in Hive, the null attribute of the base table can
be empty.
+
```sql
refreshMethod
: COMPLETE | AUTO
@@ -165,19 +177,7 @@ There are two types of partitioning methods for
materialized views. If no partit
For example, if the base table is a range partition with a partition field of
`create_time` and partitioning by day, and `partition by(ct) as select
create_time as ct from t1` is specified when creating a materialized view,
then the materialized view will also be a range partition with a partition
field of 'ct' and partitioning by day
-The selection of partition fields and the definition of materialized views
must meet the following constraints to be successfully created;
-otherwise, an error "Unable to find a suitable base table for partitioning"
will occur:
-
-- At least one of the base tables used by the materialized view must be a
partitioned table.
-- Partitioned tables used by the materialized view must employ list or range
partitioning strategies.
-- The top-level partition column in the materialized view can only have one
partition field.
-- The SQL of the materialized view needs to use partition columns from the
base table.
-- If GROUP BY is used, the partition column fields must be after the GROUP BY.
-- If window functions are used, the partition column fields must be after the
PARTITION BY.
-- Data changes should occur on partitioned tables. If they occur on
non-partitioned tables, the materialized view needs to be fully rebuilt.
-- Using the fields that generate nulls in the JOIN as partition fields in the
materialized view prohibits partition incremental updates.
-- The base table partition table referenced by the materialized view currently
only supports internal tables and HIVE tables. The attribute of the partition
column of the inner table cannot be NULL. The HIVE table allows NULL.
-
+The selection of partition fields and the definition of materialized views
must meet the conditions for partition incremental updates for the materialized
view to be created successfully; otherwise, an error "Unable to find a suitable
base table for partitioning" will occur.
#### property
The materialized view can specify both the properties of the table and the
properties unique to the materialized view.
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/query-acceleration/async-materialized-view/query-async-materialized-view.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/query-acceleration/async-materialized-view/query-async-materialized-view.md
index 8e379153ffda..0496303c909d 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/query-acceleration/async-materialized-view/query-async-materialized-view.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/query-acceleration/async-materialized-view/query-async-materialized-view.md
@@ -1,7 +1,7 @@
---
{
- "title": "查询异步物化视图",
- "language": "zh-CN"
+ "title": "查询异步物化视图",
+ "language": "zh-CN"
}
---
@@ -54,9 +54,14 @@ CREATE TABLE IF NOT EXISTS lineitem (
)
DUPLICATE KEY(l_orderkey, l_partkey, l_suppkey, l_linenumber)
PARTITION BY RANGE(l_shipdate)
- (FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
+(FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
DISTRIBUTED BY HASH(l_orderkey) BUCKETS 3
PROPERTIES ("replication_num" = "1");
+
+insert into lineitem values
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k',
'2023-10-17', '2023-10-17', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (2, 4, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k',
'2023-10-18', '2023-10-18', '2023-10-18', 'a', 'b', 'yyyyyyyyy'),
+ (3, 2, 4, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k',
'2023-10-19', '2023-10-19', '2023-10-19', 'a', 'b', 'yyyyyyyyy');
```
```sql
CREATE TABLE IF NOT EXISTS orders (
@@ -75,6 +80,12 @@ CREATE TABLE IF NOT EXISTS orders (
FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
DISTRIBUTED BY HASH(o_orderkey) BUCKETS 3
PROPERTIES ("replication_num" = "1");
+
+ insert into orders values
+ (1, 1, 'o', 9.5, '2023-10-17', 'a', 'b', 1, 'yy'),
+ (1, 1, 'o', 10.5, '2023-10-18', 'a', 'b', 1, 'yy'),
+ (2, 1, 'o', 11.5, '2023-10-19', 'a', 'b', 1, 'yy'),
+ (3, 1, 'o', 12.5, '2023-10-19', 'a', 'b', 1, 'yy');
```
```sql
@@ -90,6 +101,11 @@ CREATE TABLE IF NOT EXISTS orders (
PROPERTIES (
"replication_num" = "1"
);
+
+ insert into partsupp values
+ (2, 3, 9, 10.01, 'supply1'),
+ (4, 3, 10, 11.01, 'supply2'),
+ (2, 3, 10, 11.01, 'supply3');
```
## 直查物化视图
@@ -103,7 +119,7 @@ mv 定义:
```sql
CREATE MATERIALIZED VIEW mv1
BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
-DISTRIBUTED BY RANDOM BUCKETS 12
+DISTRIBUTED BY RANDOM BUCKETS 3
PROPERTIES ('replication_num' = '1')
AS
SELECT t1.l_linenumber,
@@ -121,7 +137,7 @@ ON l_orderkey = o_orderkey;
SELECT l_linenumber,
o_custkey
FROM mv1
-WHERE l_linenumber > 1 and o_orderdate = '2023-12-31';
+WHERE l_linenumber > 1 and o_orderdate = '2023-10-18';
```
## 透明改写能力
@@ -136,6 +152,11 @@ Join 改写指的是查询和物化使用的表相同，可以在物化视图和
mv 定义:
```sql
+CREATE MATERIALIZED VIEW mv2
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT t1.l_linenumber,
o_custkey,
o_orderdate
@@ -150,7 +171,7 @@ SELECT l_linenumber,
FROM lineitem
LEFT OUTER JOIN orders
ON l_orderkey = o_orderkey
-WHERE l_linenumber > 1 and o_orderdate = '2023-12-31';
+WHERE l_linenumber > 1 and o_orderdate = '2023-10-18';
```
**用例2:**
@@ -161,8 +182,13 @@ JOIN衍生，当查询和物化视图的 JOIN 的类型不一致时，如果物
mv 定义:
```sql
+CREATE MATERIALIZED VIEW mv3
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT
- l_shipdate, l_suppkey, o_orderdate
+ l_shipdate, l_suppkey, o_orderdate,
sum(o_totalprice) AS sum_total,
max(o_totalprice) AS max_total,
min(o_totalprice) AS min_total,
@@ -179,7 +205,7 @@ o_orderdate;
查询语句:
```sql
SELECT
- l_shipdate, l_suppkey, o_orderdate
+ l_shipdate, l_suppkey, o_orderdate,
sum(o_totalprice) AS sum_total,
max(o_totalprice) AS max_total,
min(o_totalprice) AS min_total,
@@ -187,7 +213,7 @@ SELECT
count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN
o_custkey ELSE null END) AS bitmap_union_basic
FROM lineitem
INNER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey AND l_shipdate =
o_orderdate
-WHERE o_orderdate = '2023-12-11' AND l_suppkey = 3
+WHERE o_orderdate = '2023-10-18' AND l_suppkey = 3
GROUP BY
l_shipdate,
l_suppkey,
@@ -205,6 +231,11 @@ o_orderdate;
mv 定义:
```sql
+CREATE MATERIALIZED VIEW mv4
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT
o_shippriority, o_comment,
count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN
o_custkey ELSE null END) AS cnt_1,
@@ -246,6 +277,11 @@ o_comment;
mv 定义:
```sql
+CREATE MATERIALIZED VIEW mv5
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT
l_shipdate, o_orderdate, l_partkey, l_suppkey,
sum(o_totalprice) AS sum_total,
@@ -273,7 +309,7 @@ SELECT
count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN
o_custkey ELSE null END) AS bitmap_union_basic
FROM lineitem
LEFT OUTER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey AND
l_shipdate = o_orderdate
-WHERE o_orderdate = '2023-12-11' AND l_partkey = 3
+WHERE o_orderdate = '2023-10-18' AND l_partkey = 3
GROUP BY
l_shipdate,
l_suppkey;
@@ -298,6 +334,11 @@ l_suppkey;
mv 定义:
```sql
+ CREATE MATERIALIZED VIEW mv6
+ BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+ DISTRIBUTED BY RANDOM BUCKETS 3
+ PROPERTIES ('replication_num' = '1')
+ AS
SELECT
l_linenumber,
o_custkey,
@@ -325,6 +366,11 @@ mv 定义:
mv 定义:
```sql
+CREATE MATERIALIZED VIEW mv7
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT
o_orderkey,
o_custkey,
@@ -378,12 +424,20 @@ WHERE o_orderkey > 5 AND o_orderkey <= 10;
`explain <query_sql>` 返回的信息如下，截取了物化视图相关的信息
```text
-| MaterializedView
|
-| MaterializedViewRewriteFail:
|
-| MaterializedViewRewriteSuccessButNotChose:
|
-| Names:
|
-| MaterializedViewRewriteSuccessAndChose:
|
-| Names: mv1
+| MaterializedView
|
+| MaterializedViewRewriteSuccessAndChose:
|
+| Names: mv5
|
+| MaterializedViewRewriteSuccessButNotChose:
|
+|
|
+| MaterializedViewRewriteFail:
|
+| Name: mv4
|
+| FailSummary: Match mode is invalid, View struct info is invalid
|
+| Name: mv3
|
+| FailSummary: Match mode is invalid, Rewrite compensate predicate by view
fail, View struct info is invalid
|
+| Name: mv1
|
+| FailSummary: The columns used by query are not in view, View struct info
is invalid
|
+| Name: mv2
|
+| FailSummary: The columns used by query are not in view, View struct info
is invalid
```
**MaterializedViewRewriteSuccessAndChose**：透明改写成功，并且CBO选择的物化视图名称列表。
@@ -408,10 +462,12 @@ WHERE o_orderkey > 5 AND o_orderkey <= 10;
## 限制
- 物化视图定义语句中只允许包含 SELECT、FROM、WHERE、JOIN、GROUP BY 语句，JOIN 的输入可以包含简单的 GROUP
BY（单表聚合），其中JOIN的支持的类型为
-INNER 和 LEFT OUTER JOIN 其他类型的 JOIN 操作逐步支持。
+ INNER 和 LEFT OUTER JOIN 其他类型的 JOIN 操作逐步支持。
- 基于 External Table 的物化视图不保证查询结果强一致。
- 不支持使用非确定性函数来构建物化视图，包括 rand、now、current_time、current_date、random、uuid等。
-- 不支持窗口函数和 LIMIT 的透明改写。
+- 不支持窗口函数的透明改写。
+- 查询和物化视图中有 LIMIT，暂时不支持透明改写。
- 物化视图的定义暂时不能使用视图和物化视图。
+- 当查询或者物化视图没有数据时，不支持透明改写。
- 目前 WHERE 条件补偿，支持物化视图没有 WHERE，查询有 WHERE情况的条件补偿；或者物化视图有 WHERE 且查询的 WHERE
条件是物化视图的超集。
-目前暂时还不支持范围的条件补偿，比如物化视图定义是 a > 5，查询是 a > 10，逐步支持。
\ No newline at end of file
+ 目前暂时还不支持范围的条件补偿，比如物化视图定义是 a > 5，查询是 a > 10，逐步支持。
\ No newline at end of file
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
index 658ca00115d5..4452e529f412 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/current/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
@@ -97,7 +97,19 @@ BUILD IMMEDIATE
COMPLETE：全量刷新
-AUTO：尽量增量刷新，如果不能增量刷新，就全量刷新
+AUTO：尽量增量刷新，如果不能分区增量刷新，就全量刷新
+
+物化视图的 SQL 定义和分区字段需要满足如下条件才可以进行分区增量更新
+
+- 物化视图使用的 base table 中至少有一个是分区表。
+- 物化视图使用的分区表，必须使用 list 或者 range 分区策略。
+- 物化视图最顶层的分区列只能有一个分区字段。
+- 物化视图的 SQL 中需要使用了 base table 中的分区列，比如在 Select 后。
+- 如果使用了group by，分区列的字段一定要在 group by 后。
+- 如果使用了 window 函数，分区列的字段一定要在partition by后。
+- 数据变更应发生在分区表上，如果发生在非分区表，物化视图需要全量构建。
+- 物化视图使用 Join 的 null 产生端的字段作为分区字段，不能分区增量更新。
+- 物化视图使用的 base table 表分区字段，如果来源于内表，base table 的 null 属性不能为空，如果来源于 hive 外表，base
table 的 null 属性可以为空。
```sql
refreshMethod
@@ -165,16 +177,7 @@ KEY(k1,k2)
例如：基表是range分区，分区字段为`create_time`并按天分区，创建物化视图时指定`partition by(ct) as select
create_time as ct from t1`
那么物化视图也会是range分区，分区字段为`ct`,并且按天分区
-分区字段的选择和物化视图的定义需要满足如下约束才可以创建成功，否则会报错 `Unable to find a suitable base table for
partitioning`
-- 物化视图使用的 base table 中至少有一个是分区表。
-- 物化视图使用的分区表，必须使用 list 或者 range 分区策略。
-- 物化视图最顶层的分区列只能有一个分区字段。
-- 物化视图的 SQL 需要使用了 base table 中的分区列。
-- 如果使用了group by，分区列的字段一定要在 group by 后。
-- 如果使用了 window 函数，分区列的字段一定要在partition by后。
-- 数据变更应发生在分区表上，如果发生在非分区表，物化视图需要全量构建。
-- 物化视图使用 Join 的 null 产生端的字段作为分区字段，不能分区增量更新。
-- 物化视图引用的 base table 分区表，目前只支持内表和 HIVE 表。其中内表的分区列的属性不能是 NULL。HIVE表允许为 NULL。
+分区字段的选择和物化视图的定义需要满足分区增量更新的条件，物化视图才可以创建成功，否则会报错 `Unable to find a suitable base
table for partitioning`
#### property
物化视图既可以指定table的property，也可以指定物化视图特有的property。
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query-acceleration/async-materialized-view/query-async-materialized-view.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query-acceleration/async-materialized-view/query-async-materialized-view.md
index 8e379153ffda..0496303c909d 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query-acceleration/async-materialized-view/query-async-materialized-view.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/query-acceleration/async-materialized-view/query-async-materialized-view.md
@@ -1,7 +1,7 @@
---
{
- "title": "查询异步物化视图",
- "language": "zh-CN"
+ "title": "查询异步物化视图",
+ "language": "zh-CN"
}
---
@@ -54,9 +54,14 @@ CREATE TABLE IF NOT EXISTS lineitem (
)
DUPLICATE KEY(l_orderkey, l_partkey, l_suppkey, l_linenumber)
PARTITION BY RANGE(l_shipdate)
- (FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
+(FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
DISTRIBUTED BY HASH(l_orderkey) BUCKETS 3
PROPERTIES ("replication_num" = "1");
+
+insert into lineitem values
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k',
'2023-10-17', '2023-10-17', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (2, 4, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k',
'2023-10-18', '2023-10-18', '2023-10-18', 'a', 'b', 'yyyyyyyyy'),
+ (3, 2, 4, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k',
'2023-10-19', '2023-10-19', '2023-10-19', 'a', 'b', 'yyyyyyyyy');
```
```sql
CREATE TABLE IF NOT EXISTS orders (
@@ -75,6 +80,12 @@ CREATE TABLE IF NOT EXISTS orders (
FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
DISTRIBUTED BY HASH(o_orderkey) BUCKETS 3
PROPERTIES ("replication_num" = "1");
+
+ insert into orders values
+ (1, 1, 'o', 9.5, '2023-10-17', 'a', 'b', 1, 'yy'),
+ (1, 1, 'o', 10.5, '2023-10-18', 'a', 'b', 1, 'yy'),
+ (2, 1, 'o', 11.5, '2023-10-19', 'a', 'b', 1, 'yy'),
+ (3, 1, 'o', 12.5, '2023-10-19', 'a', 'b', 1, 'yy');
```
```sql
@@ -90,6 +101,11 @@ CREATE TABLE IF NOT EXISTS orders (
PROPERTIES (
"replication_num" = "1"
);
+
+ insert into partsupp values
+ (2, 3, 9, 10.01, 'supply1'),
+ (4, 3, 10, 11.01, 'supply2'),
+ (2, 3, 10, 11.01, 'supply3');
```
## 直查物化视图
@@ -103,7 +119,7 @@ mv 定义:
```sql
CREATE MATERIALIZED VIEW mv1
BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
-DISTRIBUTED BY RANDOM BUCKETS 12
+DISTRIBUTED BY RANDOM BUCKETS 3
PROPERTIES ('replication_num' = '1')
AS
SELECT t1.l_linenumber,
@@ -121,7 +137,7 @@ ON l_orderkey = o_orderkey;
SELECT l_linenumber,
o_custkey
FROM mv1
-WHERE l_linenumber > 1 and o_orderdate = '2023-12-31';
+WHERE l_linenumber > 1 and o_orderdate = '2023-10-18';
```
## 透明改写能力
@@ -136,6 +152,11 @@ Join 改写指的是查询和物化使用的表相同，可以在物化视图和
mv 定义:
```sql
+CREATE MATERIALIZED VIEW mv2
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT t1.l_linenumber,
o_custkey,
o_orderdate
@@ -150,7 +171,7 @@ SELECT l_linenumber,
FROM lineitem
LEFT OUTER JOIN orders
ON l_orderkey = o_orderkey
-WHERE l_linenumber > 1 and o_orderdate = '2023-12-31';
+WHERE l_linenumber > 1 and o_orderdate = '2023-10-18';
```
**用例2:**
@@ -161,8 +182,13 @@ JOIN衍生，当查询和物化视图的 JOIN 的类型不一致时，如果物
mv 定义:
```sql
+CREATE MATERIALIZED VIEW mv3
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT
- l_shipdate, l_suppkey, o_orderdate
+ l_shipdate, l_suppkey, o_orderdate,
sum(o_totalprice) AS sum_total,
max(o_totalprice) AS max_total,
min(o_totalprice) AS min_total,
@@ -179,7 +205,7 @@ o_orderdate;
查询语句:
```sql
SELECT
- l_shipdate, l_suppkey, o_orderdate
+ l_shipdate, l_suppkey, o_orderdate,
sum(o_totalprice) AS sum_total,
max(o_totalprice) AS max_total,
min(o_totalprice) AS min_total,
@@ -187,7 +213,7 @@ SELECT
count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN
o_custkey ELSE null END) AS bitmap_union_basic
FROM lineitem
INNER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey AND l_shipdate =
o_orderdate
-WHERE o_orderdate = '2023-12-11' AND l_suppkey = 3
+WHERE o_orderdate = '2023-10-18' AND l_suppkey = 3
GROUP BY
l_shipdate,
l_suppkey,
@@ -205,6 +231,11 @@ o_orderdate;
mv 定义:
```sql
+CREATE MATERIALIZED VIEW mv4
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT
o_shippriority, o_comment,
count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN
o_custkey ELSE null END) AS cnt_1,
@@ -246,6 +277,11 @@ o_comment;
mv 定义:
```sql
+CREATE MATERIALIZED VIEW mv5
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT
l_shipdate, o_orderdate, l_partkey, l_suppkey,
sum(o_totalprice) AS sum_total,
@@ -273,7 +309,7 @@ SELECT
count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN
o_custkey ELSE null END) AS bitmap_union_basic
FROM lineitem
LEFT OUTER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey AND
l_shipdate = o_orderdate
-WHERE o_orderdate = '2023-12-11' AND l_partkey = 3
+WHERE o_orderdate = '2023-10-18' AND l_partkey = 3
GROUP BY
l_shipdate,
l_suppkey;
@@ -298,6 +334,11 @@ l_suppkey;
mv 定义:
```sql
+ CREATE MATERIALIZED VIEW mv6
+ BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+ DISTRIBUTED BY RANDOM BUCKETS 3
+ PROPERTIES ('replication_num' = '1')
+ AS
SELECT
l_linenumber,
o_custkey,
@@ -325,6 +366,11 @@ mv 定义:
mv 定义:
```sql
+CREATE MATERIALIZED VIEW mv7
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT
o_orderkey,
o_custkey,
@@ -378,12 +424,20 @@ WHERE o_orderkey > 5 AND o_orderkey <= 10;
`explain <query_sql>` 返回的信息如下，截取了物化视图相关的信息
```text
-| MaterializedView
|
-| MaterializedViewRewriteFail:
|
-| MaterializedViewRewriteSuccessButNotChose:
|
-| Names:
|
-| MaterializedViewRewriteSuccessAndChose:
|
-| Names: mv1
+| MaterializedView
|
+| MaterializedViewRewriteSuccessAndChose:
|
+| Names: mv5
|
+| MaterializedViewRewriteSuccessButNotChose:
|
+|
|
+| MaterializedViewRewriteFail:
|
+| Name: mv4
|
+| FailSummary: Match mode is invalid, View struct info is invalid
|
+| Name: mv3
|
+| FailSummary: Match mode is invalid, Rewrite compensate predicate by view
fail, View struct info is invalid
|
+| Name: mv1
|
+| FailSummary: The columns used by query are not in view, View struct info
is invalid
|
+| Name: mv2
|
+| FailSummary: The columns used by query are not in view, View struct info
is invalid
```
**MaterializedViewRewriteSuccessAndChose**：透明改写成功，并且CBO选择的物化视图名称列表。
@@ -408,10 +462,12 @@ WHERE o_orderkey > 5 AND o_orderkey <= 10;
## 限制
- 物化视图定义语句中只允许包含 SELECT、FROM、WHERE、JOIN、GROUP BY 语句，JOIN 的输入可以包含简单的 GROUP
BY（单表聚合），其中JOIN的支持的类型为
-INNER 和 LEFT OUTER JOIN 其他类型的 JOIN 操作逐步支持。
+ INNER 和 LEFT OUTER JOIN 其他类型的 JOIN 操作逐步支持。
- 基于 External Table 的物化视图不保证查询结果强一致。
- 不支持使用非确定性函数来构建物化视图，包括 rand、now、current_time、current_date、random、uuid等。
-- 不支持窗口函数和 LIMIT 的透明改写。
+- 不支持窗口函数的透明改写。
+- 查询和物化视图中有 LIMIT，暂时不支持透明改写。
- 物化视图的定义暂时不能使用视图和物化视图。
+- 当查询或者物化视图没有数据时，不支持透明改写。
- 目前 WHERE 条件补偿，支持物化视图没有 WHERE，查询有 WHERE情况的条件补偿；或者物化视图有 WHERE 且查询的 WHERE
条件是物化视图的超集。
-目前暂时还不支持范围的条件补偿，比如物化视图定义是 a > 5，查询是 a > 10，逐步支持。
\ No newline at end of file
+ 目前暂时还不支持范围的条件补偿，比如物化视图定义是 a > 5，查询是 a > 10，逐步支持。
\ No newline at end of file
diff --git
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
index 7a616d384876..9821c74e806c 100644
---
a/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
+++
b/i18n/zh-CN/docusaurus-plugin-content-docs/version-2.1/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
@@ -99,6 +99,18 @@ COMPLETE：全量刷新
AUTO：尽量增量刷新，如果不能增量刷新，就全量刷新
+物化视图的 SQL 定义和分区字段需要满足如下条件才可以进行分区增量更新
+
+- 物化视图使用的 base table 中至少有一个是分区表。
+- 物化视图使用的分区表，必须使用 list 或者 range 分区策略。
+- 物化视图最顶层的分区列只能有一个分区字段。
+- 物化视图的 SQL 中需要使用了 base table 中的分区列，比如在 Select 后。
+- 如果使用了group by，分区列的字段一定要在 group by 后。
+- 如果使用了 window 函数，分区列的字段一定要在partition by后。
+- 数据变更应发生在分区表上，如果发生在非分区表，物化视图需要全量构建。
+- 物化视图使用 Join 的 null 产生端的字段作为分区字段，不能分区增量更新。
+- 物化视图使用的 base table 表分区字段，如果来源于内表，base table 的 null 属性不能为空，如果来源于 hive 外表，base
table 的 null 属性可以为空。
+
```sql
refreshMethod
: COMPLETE | AUTO
@@ -165,15 +177,8 @@ KEY(k1,k2)
例如：基表是range分区，分区字段为`create_time`并按天分区，创建物化视图时指定`partition by(ct) as select
create_time as ct from t1`
那么物化视图也会是range分区，分区字段为`ct`,并且按天分区
-分区字段的选择和物化视图的定义需要满足如下约束才可以创建成功，否则会报错 `Unable to find a suitable base table for
partitioning`
-- 物化视图使用的 base table 中至少有一个是分区表。
-- 物化视图使用的分区表，必须使用 list 或者 range 分区策略。
-- 物化视图最顶层的分区列只能有一个分区字段。
-- 物化视图的 SQL 需要使用了 base table 中的分区列。
-- 如果使用了group by，分区列的字段一定要在 group by 后。
-- 如果使用了 window 函数，分区列的字段一定要在partition by后。
-- 数据变更应发生在分区表上，如果发生在非分区表，物化视图需要全量构建。
-- 物化视图使用 Join 的 null 产生端的字段作为分区字段，不能分区增量更新。
+分区字段的选择和物化视图的定义需要满足分区增量更新的条件，物化视图才可以创建成功，否则会报错 `Unable to find a suitable base
table for partitioning`
+
#### property
物化视图既可以指定table的property，也可以指定物化视图特有的property。
diff --git
a/versioned_docs/version-2.1/query-acceleration/async-materialized-view/query-async-materialized-view.md
b/versioned_docs/version-2.1/query-acceleration/async-materialized-view/query-async-materialized-view.md
index e6ff886bc5be..2d166fe0f3ec 100644
---
a/versioned_docs/version-2.1/query-acceleration/async-materialized-view/query-async-materialized-view.md
+++
b/versioned_docs/version-2.1/query-acceleration/async-materialized-view/query-async-materialized-view.md
@@ -1,7 +1,7 @@
---
{
- "title": "Querying Async Materialized View",
- "language": "en"
+ "title": "Querying Async Materialized View",
+ "language": "en"
}
---
@@ -29,10 +29,10 @@ under the License.
Doris's asynchronous materialized views employ an algorithm based on the SPJG
(SELECT-PROJECT-JOIN-GROUP-BY) pattern
structure information for transparent rewriting.
-Doris can analyze the structural information of query SQL, automatically
search for suitable materialized views,
+Doris can analyze the structural information of query SQL, automatically
search for suitable materialized views,
and attempt transparent rewriting, utilizing the optimal materialized view to
express the query SQL.
-By utilizing precomputed materialized view results,
+By utilizing precomputed materialized view results,
significant improvements in query performance and a reduction in computational
costs can be achieved.
Using the three tables: lineitem, orders, and partsupp from TPC-H, let's
describe the capability of directly querying
@@ -58,9 +58,14 @@ CREATE TABLE IF NOT EXISTS lineitem (
)
DUPLICATE KEY(l_orderkey, l_partkey, l_suppkey, l_linenumber)
PARTITION BY RANGE(l_shipdate)
- (FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
+(FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
DISTRIBUTED BY HASH(l_orderkey) BUCKETS 3
PROPERTIES ("replication_num" = "1");
+
+insert into lineitem values
+ (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k',
'2023-10-17', '2023-10-17', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),
+ (2, 4, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k',
'2023-10-18', '2023-10-18', '2023-10-18', 'a', 'b', 'yyyyyyyyy'),
+ (3, 2, 4, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k',
'2023-10-19', '2023-10-19', '2023-10-19', 'a', 'b', 'yyyyyyyyy');
```
```sql
CREATE TABLE IF NOT EXISTS orders (
@@ -79,6 +84,12 @@ CREATE TABLE IF NOT EXISTS orders (
FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)
DISTRIBUTED BY HASH(o_orderkey) BUCKETS 3
PROPERTIES ("replication_num" = "1");
+
+ insert into orders values
+ (1, 1, 'o', 9.5, '2023-10-17', 'a', 'b', 1, 'yy'),
+ (1, 1, 'o', 10.5, '2023-10-18', 'a', 'b', 1, 'yy'),
+ (2, 1, 'o', 11.5, '2023-10-19', 'a', 'b', 1, 'yy'),
+ (3, 1, 'o', 12.5, '2023-10-19', 'a', 'b', 1, 'yy');
```
```sql
@@ -94,32 +105,37 @@ CREATE TABLE IF NOT EXISTS orders (
PROPERTIES (
"replication_num" = "1"
);
+
+ insert into partsupp values
+ (2, 3, 9, 10.01, 'supply1'),
+ (4, 3, 10, 11.01, 'supply2'),
+ (2, 3, 10, 11.01, 'supply3');
```
## Direct Query of Materialized View
A materialized view can be considered as a table and can be queried just like
a regular table.
-The syntax for defining a materialized view, details can be found in
+The syntax for defining a materialized view, details can be found in
[CREATE-ASYNC-MATERIALIZED-VIEW](../../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md)
Materialized view definition:
```sql
CREATE MATERIALIZED VIEW mv1
BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
-DISTRIBUTED BY RANDOM BUCKETS 12
+DISTRIBUTED BY RANDOM BUCKETS 3
PROPERTIES ('replication_num' = '1')
AS
SELECT t1.l_linenumber,
o_custkey,
o_orderdate
-FROM (SELECT * FROM lineitem WHERE l_linenumber > 1) t1
- LEFT OUTER JOIN orders
- ON l_orderkey = o_orderkey;
+FROM
+ (SELECT * FROM lineitem WHERE l_linenumber > 1) t1
+LEFT OUTER JOIN orders ON l_orderkey = o_orderkey;
```
Query statement:
Direct queries can be performed on the materialized view with additional
filtering conditions and aggregations.
-
+
```sql
SELECT l_linenumber,
o_custkey
@@ -131,20 +147,25 @@ WHERE l_linenumber > 1 and o_orderdate = '2023-12-31';
### Join rewriting
-Join rewriting refers to when the tables used in the query and the
materialization are the same.
-In this case, the optimizer will attempt transparent rewriting by either
joining the input of the materialized
+Join rewriting refers to when the tables used in the query and the
materialization are the same.
+In this case, the optimizer will attempt transparent rewriting by either
joining the input of the materialized
view with the query or placing the join in the outer layer of the query's
WHERE clause.
-This pattern of rewriting is supported for multi-table joins and supports
inner and left join types.
+This pattern of rewriting is supported for multi-table joins and supports
inner and left join types.
Support for other types is continually expanding.
**Case 1:**
-The following case can undergo transparent rewriting. The condition
`l_linenumber > 1` allows for pull-up,
+The following case can undergo transparent rewriting. The condition
`l_linenumber > 1` allows for pull-up,
enabling transparent rewriting by expressing the query using the precomputed
results of the materialized view.
Materialized view definition:
```sql
+CREATE MATERIALIZED VIEW mv2
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT t1.l_linenumber,
o_custkey,
o_orderdate
@@ -160,19 +181,24 @@ SELECT l_linenumber,
FROM lineitem
LEFT OUTER JOIN orders
ON l_orderkey = o_orderkey
-WHERE l_linenumber > 1 and o_orderdate = '2023-12-31';
+WHERE l_linenumber > 1 and o_orderdate = '2023-10-18';
```
**Case 2:**
-JOIN Derivation occurs when the join type between the query and the
materialized view does not match.
-In cases where the materialization can provide all the necessary data for the
query, transparent rewriting can
+JOIN Derivation occurs when the join type between the query and the
materialized view does not match.
+In cases where the materialization can provide all the necessary data for the
query, transparent rewriting can
still be achieved by compensating predicates outside the join through
predicate push down.
For example:
Materialized view definition:
```sql
+CREATE MATERIALIZED VIEW mv3
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT
l_shipdate, l_suppkey, o_orderdate
sum(o_totalprice) AS sum_total,
@@ -191,7 +217,7 @@ o_orderdate;
Query statement:
```sql
SELECT
- l_shipdate, l_suppkey, o_orderdate
+ l_shipdate, l_suppkey, o_orderdate,
sum(o_totalprice) AS sum_total,
max(o_totalprice) AS max_total,
min(o_totalprice) AS min_total,
@@ -199,7 +225,7 @@ SELECT
count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN
o_custkey ELSE null END) AS bitmap_union_basic
FROM lineitem
INNER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey AND l_shipdate =
o_orderdate
-WHERE o_orderdate = '2023-12-11' AND l_suppkey = 3
+WHERE o_orderdate = '2023-10-18' AND l_suppkey = 3
GROUP BY
l_shipdate,
l_suppkey,
@@ -207,21 +233,26 @@ o_orderdate;
```
### Aggregate rewriting
-In the definitions of both the query and the materialized view, the aggregated
dimensions can either be consistent or inconsistent.
+In the definitions of both the query and the materialized view, the aggregated
dimensions can either be consistent or inconsistent.
Filtering of results can be achieved by using fields from the dimensions in
the WHERE clause.
-The dimensions used in the materialized view need to encompass those used in
the query,
+The dimensions used in the materialized view need to encompass those used in
the query,
and the metrics utilized in the query can be expressed using the metrics of
the materialized view.
**Case 1**
-The following case can undergo transparent rewriting. The query and the
materialized view use consistent dimensions
-for aggregation, allowing the use of fields from the dimensions to filter
results. The query will attempt to use the
+The following case can undergo transparent rewriting. The query and the
materialized view use consistent dimensions
+for aggregation, allowing the use of fields from the dimensions to filter
results. The query will attempt to use the
expressions after SELECT in the materialized view.
Materialized view definition:
```sql
+CREATE MATERIALIZED VIEW mv4
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT
o_shippriority, o_comment,
count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN
o_custkey ELSE null END) AS cnt_1,
@@ -256,17 +287,22 @@ o_comment;
**Case 2**
-The following query can be transparently rewritten: the query and the
materialization use aggregated dimensions
-that are inconsistent, but the dimensions used in the materialized view
encompass those used in the query.
+The following query can be transparently rewritten: the query and the
materialization use aggregated dimensions
+that are inconsistent, but the dimensions used in the materialized view
encompass those used in the query.
The query can filter results using fields from the dimensions.
-The query will attempt to roll up using the functions after SELECT, such as
the materialized view's
-bitmap_union will eventually roll up into bitmap_union_count, maintaining
consistency with the semantics of
+The query will attempt to roll up using the functions after SELECT, such as
the materialized view's
+bitmap_union will eventually roll up into bitmap_union_count, maintaining
consistency with the semantics of
the count(distinct) in the query.
Materialized view definition:
```sql
+CREATE MATERIALIZED VIEW mv5
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT
l_shipdate, o_orderdate, l_partkey, l_suppkey,
sum(o_totalprice) AS sum_total,
@@ -295,7 +331,7 @@ SELECT
count(distinct CASE WHEN o_shippriority > 1 AND o_orderkey IN (1, 3) THEN
o_custkey ELSE null END) AS bitmap_union_basic
FROM lineitem
LEFT OUTER JOIN orders ON lineitem.l_orderkey = orders.o_orderkey AND
l_shipdate = o_orderdate
-WHERE o_orderdate = '2023-12-11' AND l_partkey = 3
+WHERE o_orderdate = '2023-10-18' AND l_partkey = 3
GROUP BY
l_shipdate,
l_suppkey;
@@ -314,8 +350,8 @@ Temporary support for the aggregation roll-up functions is
as follows:
| bitmap_union_count | bitmap_union | bitmap_union_count
|
## Query partial Transparent Rewriting (Coming soon)
-When the number of tables in the materialized view is greater than the query,
if the materialized view
-satisfies the conditions for JOIN elimination for tables more than the query,
transparent rewriting can also occur.
+When the number of tables in the materialized view is greater than the query,
if the materialized view
+satisfies the conditions for JOIN elimination for tables more than the query,
transparent rewriting can also occur.
For example:
**Case 1**
@@ -323,6 +359,11 @@ For example:
Materialized view definition:
```sql
+ CREATE MATERIALIZED VIEW mv6
+ BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+ DISTRIBUTED BY RANDOM BUCKETS 3
+ PROPERTIES ('replication_num' = '1')
+ AS
SELECT
l_linenumber,
o_custkey,
@@ -344,8 +385,8 @@ Query statement:
```
## Union Rewriting (Coming soon)
-When the materialized view is not sufficient to provide all the data for the
query, it can use Union to return
-data by combining the original table and the materialized view.
+When the materialized view is not sufficient to provide all the data for the
query, it can use Union to return
+data by combining the original table and the materialized view.
For example:
**Case 1**
@@ -353,6 +394,11 @@ For example:
Materialized view definition:
```sql
+CREATE MATERIALIZED VIEW mv7
+BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 hour
+DISTRIBUTED BY RANDOM BUCKETS 3
+PROPERTIES ('replication_num' = '1')
+AS
SELECT
o_orderkey,
o_custkey,
@@ -394,21 +440,21 @@ WHERE o_orderkey > 5 AND o_orderkey <= 10;
The unit of `grace_period` is seconds, referring to the permissible time for
inconsistency between the materialized
view and the data in the underlying base tables.
-For example, setting `grace_period` to 0 means requiring the materialized view
to be consistent with the base
-table data before it can be used for transparent rewriting. As for external
tables,
+For example, setting `grace_period` to 0 means requiring the materialized view
to be consistent with the base
+table data before it can be used for transparent rewriting. As for external
tables,
since changes in data cannot be perceived, the materialized view is used with
them.
-Regardless of whether the data in the external table is up-to-date or not,
this materialized view can be used for
-transparent rewriting. If the external table is configured with an HMS
metadata source,
-it becomes capable of perceiving data changes. Configuring the metadata source
and enabling data change
+Regardless of whether the data in the external table is up-to-date or not,
this materialized view can be used for
+transparent rewriting. If the external table is configured with an HMS
metadata source,
+it becomes capable of perceiving data changes. Configuring the metadata source
and enabling data change
perception functionality will be supported in subsequent iterations.
-Setting `grace_period` to 10 means allowing a 10-second delay between the data
in the materialized view and
-the data in the base tables. If there is a delay of up to 10 seconds between
the data in the materialized
-view and the data in the base tables, the materialized view can still be used
for transparent rewriting within
+Setting `grace_period` to 10 means allowing a 10-second delay between the data
in the materialized view and
+the data in the base tables. If there is a delay of up to 10 seconds between
the data in the materialized
+view and the data in the base tables, the materialized view can still be used
for transparent rewriting within
that time frame.
-For internal tables in the materialized view, you can control the maximum
delay allowed for the data used by
-the transparent rewriting by setting the `grace_period` property.
+For internal tables in the materialized view, you can control the maximum
delay allowed for the data used by
+the transparent rewriting by setting the `grace_period` property.
Refer to
[CREATE-ASYNC-MATERIALIZED-VIEW](../../sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md)
**Viewing and Debugging Transparent Rewrite Hit Information**
@@ -418,12 +464,20 @@ It will display a concise overview of the transparent
rewriting process.
`explain <query_sql>` The information returned is as follows, with the
relevant information pertaining to materialized views extracted:
```text
-| MaterializedView
|
-| MaterializedViewRewriteFail:
|
-| MaterializedViewRewriteSuccessButNotChose:
|
-| Names:
|
-| MaterializedViewRewriteSuccessAndChose:
|
-| Names: mv1
+| MaterializedView
|
+| MaterializedViewRewriteSuccessAndChose:
|
+| Names: mv5
|
+| MaterializedViewRewriteSuccessButNotChose:
|
+|
|
+| MaterializedViewRewriteFail:
|
+| Name: mv4
|
+| FailSummary: Match mode is invalid, View struct info is invalid
|
+| Name: mv3
|
+| FailSummary: Match mode is invalid, Rewrite compensate predicate by view
fail, View struct info is invalid
|
+| Name: mv1
|
+| FailSummary: The columns used by query are not in view, View struct info
is invalid
|
+| Name: mv2
|
+| FailSummary: The columns used by query are not in view, View struct info
is invalid
```
**MaterializedViewRewriteSuccessAndChose**: Transparent rewrite succeeded, and
the materialized view names list
@@ -452,20 +506,24 @@ you can execute the following statement. It will provide
a detailed breakdown of
## Limitations
-- The materialized view definition statement only allows SELECT, FROM, WHERE,
JOIN, and GROUP BY clauses.
-The input for JOIN can include simple GROUP BY (aggregation on a single
table).
-Supported types of JOIN operations include INNER and LEFT OUTER JOIN.
-Support for other types of JOIN operations will be gradually added.
+- The materialized view definition statement only allows SELECT, FROM, WHERE,
JOIN, and GROUP BY clauses.
+ The input for JOIN can include simple GROUP BY (aggregation on a single
table).
+ Supported types of JOIN operations include INNER and LEFT OUTER JOIN.
+ Support for other types of JOIN operations will be gradually added.
- Materialized views based on External Tables do not guarantee strong
consistency in query results.
- The use of non-deterministic functions to build materialized views is not
supported,
-including rand, now, current_time, current_date, random, uuid, etc.
+ including rand, now, current_time, current_date, random, uuid, etc.
+
+- Transparent rewriting does not support window functions.
-- Transparent rewriting does not support window functions and LIMIT.
+- There is LIMIT in queries and materialized views, and transparent rewriting
is not supported for the time being.
- Currently, materialized view definitions cannot utilize views or other
materialized views.
-- Currently, WHERE clause compensation supports scenarios where the
materialized view does not have a WHERE clause,
-but the query does, or where the materialized view has a WHERE clause and the
query's WHERE clause is a superset
-of the materialized view's. Range condition compensation is not yet supported
but will be added gradually.
+- When the query or materialized view has no data, transparent rewriting is
not supported.
+
+- Currently, WHERE clause compensation supports scenarios where the
materialized view does not have a WHERE clause,
+ but the query does, or where the materialized view has a WHERE clause and
the query's WHERE clause is a superset
+ of the materialized view's. Range condition compensation is not yet
supported but will be added gradually.
diff --git
a/versioned_docs/version-2.1/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
b/versioned_docs/version-2.1/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
index 36152eef7341..f1fd88afd572 100644
---
a/versioned_docs/version-2.1/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
+++
b/versioned_docs/version-2.1/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-ASYNC-MATERIALIZED-VIEW.md
@@ -99,6 +99,18 @@ COMPLETE：Full refresh
AUTO：Try to refresh incrementally as much as possible. If incremental refresh
is not possible, refresh in full
+The SQL definition and partition fields of the materialized view need to meet
the following conditions for partition incremental updates:
+
+- At least one of the base tables used by the materialized view must be a
partitioned table.
+- The partitioned tables used by the materialized view must employ list or
range partitioning strategies.
+- The top-level partition column in the materialized view can only have one
partition field.
+- The SQL of the materialized view needs to use partition columns from the
base table, such as after the SELECT clause.
+- If GROUP BY is used, the partition column fields must be after the GROUP BY.
+- If window functions are used, the partition column fields must be after the
PARTITION BY.
+- Data changes should occur on partitioned tables. If they occur on
non-partitioned tables, the materialized view needs to be fully rebuilt.
+- Using the fields that generate nulls in the JOIN as partition fields in the
materialized view prohibits partition incremental updates.
+- The null attribute of partition fields in the base table used by the
materialized view must not be empty if it comes from an internal table. If it
comes from an external table in Hive, the null attribute of the base table can
be empty.
+
```sql
refreshMethod
: COMPLETE | AUTO
@@ -165,17 +177,7 @@ There are two types of partitioning methods for
materialized views. If no partit
For example, if the base table is a range partition with a partition field of
`create_time` and partitioning by day, and `partition by(ct) as select
create_time as ct from t1` is specified when creating a materialized view,
then the materialized view will also be a range partition with a partition
field of 'ct' and partitioning by day
-The selection of partition fields and the definition of materialized views
must meet the following constraints to be successfully created;
-otherwise, an error "Unable to find a suitable base table for partitioning"
will occur:
-
-- At least one of the base tables used by the materialized view must be a
partitioned table.
-- Partitioned tables used by the materialized view must employ list or range
partitioning strategies.
-- The top-level partition column in the materialized view can only have one
partition field.
-- The SQL of the materialized view needs to use partition columns from the
base table.
-- If GROUP BY is used, the partition column fields must be after the GROUP BY.
-- If window functions are used, the partition column fields must be after the
PARTITION BY.
-- Data changes should occur on partitioned tables. If they occur on
non-partitioned tables, the materialized view needs to be fully rebuilt.
-- Using the fields that generate nulls in the JOIN as partition fields in the
materialized view prohibits partition incremental updates.
+The selection of partition fields and the definition of materialized views
must meet the conditions for partition incremental updates for the materialized
view to be created successfully; otherwise, an error "Unable to find a suitable
base table for partitioning" will occur.
#### property
The materialized view can specify both the properties of the table and the
properties unique to the materialized view.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
