Page History

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