Netezza materialized views 物化视图 详解

什么是物化视图

这是一个简单的视图模型，view通过一系列的条件，组合成我们想要的数据，但view中不保存数据，数据在table中，我们称这个table为基表。这是我们最初接触到的视图。

上图中，view和table（基表）中间多了一个对象：materialized table，这个table的作用是存储数据，叫做物化表。materialized table和materialized view一起，组成了这里想讲的概念：materialized view（物化视图）。

一句话来说：物化视图，就是可以存储数据的视图。

物化视图的数据读取

既然都是视图，那么访问数据的方式当然一样，但是一样的select语句，背后的执行逻辑不一样。

SELECT * FROM xxxx_VIEW;

普通视图select逻辑

1. select请求
2. 执行创建视图的sql
3. 返回获取的最新数据

物化视图的select逻辑

1. select请求
2. 返回物化视图存储的数据

对比可以发现，物化视图省略了刷新数据这个步骤。普通视图的创建sql有时候会很复杂，导致数据读取的速度降低，物化视图不需要刷新数据，直接读取物化表数据。

同时，假设基表中有100列，物化表中数据只有10列，对物化表进行查询时，扫描的数据范围仅为10列，这在一定程度上提升了数据扫描速度。

基于这两点，物化视图得以保证最大的优点数据快速读取。

和普通视图一样，物化视图以视图的方式存在，不能Insert、Update、Delete数据

物化视图的更新

注意数据刷新这个点。既然物化视图没有刷新数据这个操作，那么它取得的数据是不是即时的有效的数据？是否有其他更新数据的方法？

Netezza支持创建物化视图，并建议基于有很多列，但只有少数列经常用于查询的table创建一个或者多个物化视图。当基表中的数据更新时，Netezza会自动更新基表对应的物化视图。

到这里为止，物化视图的原理就差不多了，来看点干货。

Netezza物化视图知识点干货

创建物化视图

CREATE MATERIALIZED VIEW XXXX_mview AS 
	SELECT col1, col2, ..., coln
	FROM tableName
	ORDER BY col1;

几个注意点

1. 通常命名格式为 XXXX_MView，也可自定义命名格式。
2. 关键字 FROM 后跟的表叫做基表，基表只能有一张。不能指定外部、临时、系统或群集 基表 （CBT） 作为视图的基表
3. order by 表达式中不能指定 NULLS、LAST 或 DESC。如果没有order by命令，数据排序同基表。
4. select后必须且至少有一列，最多63列，select后的列必须在基表中存在，不能是表达式。可以使用distinct。
5. 不能有Where。

修改或重建物化视图【REPLACE 】

当基表数据发生变化，并且这个变化会影响视图时，如：某列数据长度由VARCHAR(80) 调整为VARCHAR(100)，物化视图查询时会出现错误

ERROR:  Base table/view 'WEATHER' attr 'CITY' has changed (precision); 
rebuild view 'WEATHER_V'

此时需要修改或重建物化视图

CREATE OR REPLACE MATERIALIZED VIEW XXXX_mview AS 
	SELECT col1, col2, ..., coln
	FROM tableName
	ORDER BY col1;

暂停/激活物化视图的自动更新【ALTER XXX SUSPEND/REFRESH】

使用自动刷新方式的物化视图，随着时间的增加，视图的查询性能会下降，因为基表中新增的数据，会以追加的方式，保存至物化表末尾，而不会按排序规则重新排序。想要解决这个问题，可以定期修改或重建物化视图。

另外，在使用nzload导入数据时，因为要同步更新物化视图的数据，所以会导致数据导入速度变慢。建议先暂停物化视图的自动更新，等导入数据完成后再激活。

ALTER VIEWS ON table_a MATERIALIZE SUSPEND;

暂停和表table_a 相关的所有物化视图的自动更新

ALTER VIEW TEST_MVIEW MATERIALIZE SUSPEND;

暂停物化视图TEST_MVIEW的自动更新

ALTER VIEW customers_mview MATERIALIZE REFRESH;

激活物化视图的自动更新并更新一次数据

ALTER VIEWS ON table_a MATERIALIZE REFRESH;

激活和表table_a 相关的所有物化视图的自动更新，并更新一次数据，更新的范围：
a、所有挂起（暂停）的视图
b、未排序数据超过刷新阈值的所有未挂起的视图

设置刷新阈值

刷新阈值的作用：指定物化视图中未排序数据的百分比

SET SYSTEM DEFAULT MATERIALIZE THRESHOLD TO <number>;

可设置的范围【1~99】，默认是20.

删除物化视图

DROP VIEW XXXX_mview;

删除物化视图，系统做了哪些事？

1. 删除视图
2. 删除物化表中数据
3. 删除物化表
4. 释放物化表占用的存储空间

参考网址