1. 物化视图介绍
对于普通视图而言，数据会存储在基表中，这导致每次查询都相当于执行了视图定义的SELECT语句。对于物化视图而言，数据会进行单独存储，这导致物化视图的查询是对存储数据直接访问（省去了连接运算）。下面对KingbaseES物化视图的使用进行介绍。
2. CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW语句被用来创建一个物化视图。创建物化视图时可以指定物化视图的刷新方式，CREATE MATERIALIZED VIEW主要语法如下：
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name [ (column_name [, ...] ) ] [ USING method ] [ WITH ( storage_parameter [= value] [, ... ] ) ] [ TABLESPACE tablespace_name ] AS query [ WITH [ NO ] DATA ] |
KingbaseES在Oracle模式可以指定刷新方式，并支持以下语法：
CREATE MATERIALIZED VIEW LOG ON table_name [WITH {CTID | PRIMARY KEY | ROWID}] CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name [ (column_name [, ...] ) ] [ USING method ] [ WITH ( storage_parameter [= value] [, ... ] ) ] [ TABLESPACE tablespace_name ] [NEVER REFRESH | REFRESH {[ FAST | COMPLETE | FORCE ] | ON {DEMAND | COMMIT} | WITH {CTID | PRIMARY KEY | ROWID}}] AS query [ WITH [ NO ] DATA ] |
需要注意的是，KingbaseES当前对commit刷新只做了语法兼容，并不支持其功能。
示例：创建简单的物化视图
--创建基表 create table base1(id int, name text); create table base2(id int, birthday text); --创建物化视图 CREATE MATERIALIZED VIEW mymatview AS SELECT base1.id id,name,birthday FROM base1, base2 where base1.id = base2.id; |
示例：在创建时填充数据
--基表插入数据 INSERT INTO base1 values(1,'TOM'); INSERT INTO base2 values(1,'2020-01-01'); --创建物化视图,填充数据 CREATE MATERIALIZED VIEW mymatview1 AS SELECT base1.id id,name,birthday FROM base1, base2 where base1.id = base2.id; --查询物化视图 Select * from mymatview1; |
示例：创建时不填充数据，后续刷新物化视图填充
--创建物化视图,不填充数据 CREATE MATERIALIZED VIEW mymatview2 AS SELECT base1.id id,name,birthday FROM base1, base2 where base1.id = base2.id with no data; --查询物化视图 Select * from mymatview2; --物化视图刷新后，可以正常显示数据 REFRESH MATERIALIZED VIEW mymatview2; Select * from mymatview2; |
示例：物化视图指定刷新方式
--创建物化视图,指定全量刷新 CREATE MATERIALIZED VIEW mymatview3 REFRESH COMPLETE AS SELECT base1.id id,name,birthday FROM base1, base2 where base1.id = base2.id with no data; --创建物化视图时,无物化日志时不能指定增量刷新 CREATE MATERIALIZED VIEW mymatview4 REFRESH Fast AS SELECT base1.id id,name,birthday FROM base1, base2 where base1.id = base2.id; --创建物化视图时,指定默认的刷新方式 CREATE MATERIALIZED VIEW mymatview4 REFRESH FORCE AS SELECT base1.id id,name,birthday FROM base1, base2 where base1.id = base2.id; --当前创建物化视图时, 不支持commit的刷新方式 CREATE MATERIALIZED VIEW mymatview5 REFRESH ON COMMIT FORCE AS SELECT base1.id id,name,birthday FROM base1, base2 where base1.id = base2.id; --创建物化视图，指定DEMAND刷新 CREATE MATERIALIZED VIEW mymatview5 REFRESH ON DEMAND FORCE AS SELECT base1.id id,name,birthday FROM base1, base2 where base1.id = base2.id; |
3. REFRESH MATERIALIZED VIEW
在上面的示例中，我们已经创建了物化视图，当基表数据发生变化时，物化视图就需要刷新，否则就无法获取物化视图中的数据。物化视图的刷新命令为REFRESH MATERIALIZED VIEW，具体语法如下：
REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name [ WITH [ NO ] DATA ] |
示例：基表数据变化后，物化视图将不可见
--对基表添加数据 INSERT INTO base1 values(2,'john'); INSERT INTO base2 values(2,'2021-01-01'); --未刷新时查询物化视图，发现数据没有同步更新 Select * from mymatview1; --刷新后，新插入的数据可以获取 REFRESH MATERIALIZED VIEW mymatview1; Select * from mymatview1; --使用with no data刷新，将导致视图无法被访问 REFRESH MATERIALIZED VIEW mymatview1 with no data; Select * from mymatview1; |