内容
1.Materialized Views
可以使用CREATE MATERIALIZED VIEW语句在表上创建实例化视图, 如下所示:
create_materialized_view_statement ::= CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] view_name AS select_statement PRIMARY KEY '(' primary_key ')' WITH table_options
例如:
CREATE MATERIALIZED VIEW monkeySpecies_by_population AS SELECT * FROM monkeySpecies WHERE population IS NOT NULL AND species IS NOT NULL PRIMARY KEY (population, species) WITH comment='Allow query by population instead of species';
CREATE MATERIALIZED VIEW语句创建一个实例化视图。 MATERIALIZED视图是一组行,它们对应于SELECT语句中指定的基表中存在的行。 注意,无法直接更新实例化视图中的数据,但对基表的更新将导致视图中的相应内容的更新。
创建MATERIALIZED视图有3个主要部分:
视图中包含的数据的select语句;
视图的主键定义;
视图的选项;
除非使用IF NOT EXISTS选项,否则,尝试创建现有的MATERIALIZED视图将返回错误。
Select Statement
MATERIALIZED视图创建的select语句定义视图中包含哪个基表。Select 仅限于那些基表列的选择。不能使用任何函数,CAST等。也不支持别名。可以使用*作为选择所有列的快捷方式。此外,静态列不能包含在MATERIALIZED视图中,这意味着如果基表具有静态列,则不允许使用SELECT * 。
WHERE子句具有以下限制:
不能包含任何bind_marker;
不属于基表主键的列只能受IS NOT NULL的限制,不允许其他限制;
由于作为视图主键一部分的列不能为空,必须至少受IS NOT NULL的限制;
Primary Key
视图必须具有主键,并且主键必须符合以下限制:
必须包含基表的所有主键列,这确保了视图的每一行恰好对应于基表的一行;
只能包含一个不是基表中主键列的列;
例如,给出以下基表定义:
CREATE TABLE t ( k int, c1 int, c2 int, v1 int, v2 int, PRIMARY KEY (k, c1, c2) )
然后允许以下视图定义:
CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM t WHERE k IS NOT NULL AND c1 IS NOT NULL AND c2 IS NOT NULL PRIMARY KEY (c1, k, c2) CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM t WHERE k IS NOT NULL AND c1 IS NOT NULL AND c2 IS NOT NULL PRIMARY KEY (v1, k, c1, c2)
但以下是不允许的:
// Error: cannot include both v1 and v2 in the primary key as both are not in the base table primary key CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM t WHERE k IS NOT NULL AND c1 IS NOT NULL AND c2 IS NOT NULL AND v1 IS NOT NULL PRIMARY KEY (v1, v2, k, c1, c2) // Error: must include k in the primary as it's a base table primary key column CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM t WHERE c1 IS NOT NULL AND c2 IS NOT NULL PRIMARY KEY (c1, c2)
MV Option
MATERIALIZED视图由表内部实现,因此,创建MV允许与创建表相同的选项。
2. ALTER MATERIALIZED VIEW
创建MV后,可以使用ALTER MATERIALIZED VIEW语句更改实例化视图的选项:
alter_materialized_view_statement :: = ALTER MATERIALIZED VIEW view_name WITH table_options
可以更新的选项与创建时相同,并且与表支持的选项相同。
3. DROP MATERIALIZED VIEW
使用DROP MATERIALIZED VIEW语句删除MATERIALIZED 视图:
drop_materialized_view_statement :: = DROP MATERIALIZED VIEW [IF EXISTS] view_name;
如果视图不存在,则语句将返回错误,除非使用IF EXISTS。