You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by GitBox <gi...@apache.org> on 2022/06/17 07:23:15 UTC
[GitHub] [incubator-doris] yangzhg commented on issue #7503: [Roadmap] Support multi table materialized view
yangzhg commented on issue #7503:
URL: https://github.com/apache/incubator-doris/issues/7503#issuecomment-1158575127
## Motivation
At present, materialized views can be created on a single table, and can use pre-computed results to achieve query acceleration, but support for multi-table scenarios cannot be realized. Many query scenarios are relatively simple and the data update frequency is not high. Materialized views can effectively improve query performance and reduce data calculation
## research
Both traditional TP database ORACLE and emerging AP database CK are supported
oracle [https://docs.oracle.com/cd/E11882_01/server.112/e10706/repmview.htm#REPLN003](https://docs.oracle.com/cd/E11882_01/server.112/e10706/ repmview.htm#REPLN003),
CK [https://clickhouse.com/docs/en/sql-reference/statements/create/view](https://clickhouse.com/docs/en/sql-reference/statements/create/view)
## Grammar
The syntax refers to oracle design
```sql
create materialized view mv_name -- 1. Create a materialized view
build [immediate | deferred] -- 2. Create method, default immediate
refresh [force | fast | complete | never] -- 3. Refresh method of materialized view, default force
on [commit | demand] -- 4. Refresh trigger method
start with start_time -- 5. Set the start time
next interval -- 6. Set the interval time
PARTITION BY [range|list] -- 7. Set the partition column
DISTRIBUTED BY hash(cols..) BUCKETS 16 -- 8. Set up buckets
as -- 7. Keywords
select ...; -- 8. select statement
````
explain
```sql
1. "build" -- how to create
(1) 'immediate': Take effect immediately, default.
(2) 'deferred' : Delay until the first refresh to take effect
2. "refresh" refresh method
(1) fast : 'Fast refresh'. Incremental refresh
(2) complete: 'complete refresh'. Update all data when refreshing, including the original data that has been generated in the view
(3) never : never refresh
3. "on" trigger mode (On demand, only need to set 'start_time' and 'interval')
(1) on commit: when the table participating in the materialized view has data updated
(2) on demand: refresh when needed
[1] Refresh according to 'start_time' and 'interval' set later
[2] Manual call to refresh
````
## Design
- A multi-table materialized view exists as a special type of table, which is essentially a table, all management is the same as a table, but it cannot be updated or imported
- Multi-table materialized views can be directly queried
- According to the refresh strategy, data is imported or refreshed regularly
- When performing schema change on a table, it is necessary to judge the impact and whether to update the multi-table materialized view
- Multi-table materialized views are not suitable for frequently updated tables when updated on commit
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org