• Created by Unknown User (yangzhg), last updated on Jun 17, 2022 2 minute read

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 3 Next »

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 docs.oracle.com

CK clickhouse.com

Grammar

The syntax refers to oracle design

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

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

  • No labels