Netezza materialized views 物化视图 详解
什么是物化视图
这是一个简单的视图模型,view通过一系列的条件,组合成我们想要的数据,但view中不保存数据,数据在table中,我们称这个table为基表。这是我们最初接触到的视图。
上图中,view和table(基表)中间多了一个对象:materialized table,这个table的作用是存储数据,叫做物化表。materialized table和materialized view一起,组成了这里想讲的概念:materialized view(物化视图)。
一句话来说:物化视图,就是可以存储数据的视图。
物化视图的数据读取
既然都是视图,那么访问数据的方式当然一样,但是一样的select语句,背后的执行逻辑不一样。
SELECT * FROM xxxx_VIEW;
普通视图select逻辑
- select请求
- 执行创建视图的sql
- 返回获取的最新数据
物化视图的select逻辑
- select请求
- 返回物化视图存储的数据
对比可以发现,物化视图省略了刷新数据这个步骤。普通视图的创建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;
几个注意点
- 通常命名格式为 XXXX_MView,也可自定义命名格式。
- 关键字 FROM 后跟的表叫做基表,基表只能有一张。不能指定外部、临时、系统或群集 基表 (CBT) 作为视图的基表
- order by 表达式中不能指定 NULLS、LAST 或 DESC。如果没有order by命令,数据排序同基表。
- select后必须且至少有一列,最多63列,select后的列必须在基表中存在,不能是表达式。可以使用distinct。
- 不能有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;
删除物化视图,系统做了哪些事?
- 删除视图
- 删除物化表中数据
- 删除物化表
- 释放物化表占用的存储空间