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: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
