Status

## Background

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

## Competitors

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,

CK https://clickhouse.com/docs/en/sql-reference/statements/create/view

## grammar

### Create

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 [fast | complete | never]        -- 3. Refresh method of materialized view,
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
PROPERTIES()                             -- 9. properties
as                                       -- 10. Keywords
select ...;                              -- 11. 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 time')
	(1) on commit: When the base table has a commit action, refresh the map ("Cannot execute across databases")
	(2) on demand: refresh when needed
		[1] Refresh according to the 'start time' and 'end time' set later
		[2] Manual call to refresh
````

### DESC

```sql
DESCRIBE test_mv_view;
````

### SHOW CREATE

```sql
SHOW CREATE MATERIALIZED VIEW test_mv_view;
````

### DROP

```sql
DROP MATERIALIZED VIEW test_mv_view;
````

### ALTER

```sql
alter materialized view materialized view name
refresh [fast | complete | never]
on [commit | demand]
start with start time
next interval
````

### refresh

```sql
REFRESH MATERIALIZED VIEW test_mv_view [complete];
````

## query

```sql
select .... from test_mv_view;
````

Optional Automatically hit multi-table materialized views

## design

### Create

A multi-table materialized view exists as a special type of table. It is essentially a table. All management is the same as a table, but it cannot be updated or imported. It is the same as view in display.

Multi-table materialized view information is recorded in the information_schema library table

The table structure is as follows

```sql
+----------------------+------------+------+-------+---------+-------+
| Field                | Type       | Null | Key   | Default | Extra |
+----------------------+------------+------+-------+---------+-------+
| TABLE_CATALOG        | VARCHAR(*) | Yes  | false | NULL    |       |
| TABLE_SCHEMA         | VARCHAR(*) | Yes  | false | NULL    |       |
| TABLE_NAME           | VARCHAR(*) | Yes  | false | NULL    |       |
| VIEW_DEFINITION      | VARCHAR(*) | Yes  | false | NULL    |       |
| CHECK_OPTION         | VARCHAR(*) | Yes  | false | NULL    |       |
| IS_UPDATABLE         | VARCHAR(*) | Yes  | false | NULL    |       |
| DEFINER              | VARCHAR(*) | Yes  | false | NULL    |       |
| SECURITY_TYPE        | VARCHAR(*) | Yes  | false | NULL    |       |
| CHARACTER_SET_CLIENT | VARCHAR(*) | Yes  | false | NULL    |       |
| COLLATION_CONNECTION | VARCHAR(*) | Yes  | false | NULL    |       |
| STATUS               | VARCHAR(*) | Yes  | false | NULL    |       |
+----------------------+------------+------+-------+---------+-------+

The STATUS field represents the current state of the materialized view, and the optional value [Syncing|OutDate|UpToDate|Never|Error] checks the data update state of the materialized view during display
````

### refresh

 According to the refresh strategy, data is imported or refreshed periodically. If the data is not up-to-date, it will be updated according to the conditions, and the command to refresh the data will be issued at the same time, and it will be converted into a normal view query. You can also refresh manually. The refresh command is essentially insert into select, which is completed in the background. At the same time, change the STATUS in materialized_views to `Syncing`, and change it to UpToDate after synchronization is complete.

### alter 

When performing schema change on a table, it is necessary to judge the impact and whether it is necessary to update the multi-table materialized view

### query

- Multi-table materialized views can be directly queried and automatically hit if possible

### other

- Multi-table materialized views are not suitable for frequently updated tables when updated on commit