原 PG和GreenPlum中的物化视图
Tags： 原创PGGreenPlumPostgreSQL物化视图
简介
物化视图（也叫实体化视图）是PostgreSQL9.3版本才支持的一个功能，所以GreenPlum和PG都可以使用物化视图，物化视图的使用规则和视图是一样的，和视图不同的是，物化视图将结果集持久化在表中；
一个物化视图对应一个SQL语句，查询时去对应的结果集表查询（跟视图查询方式有些区别）。
也可以基于视图来创建物化视图。
创建物化视图语句：
1 | CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab; |
语法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | postgres=# \h REFRESH Command: REFRESH MATERIALIZED VIEW Description: replace the contents of a materialized view Syntax: REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name [ WITH [ NO ] DATA ] postgres=# postgres=# \h CREATE MATERIALIZED Command: CREATE MATERIALIZED VIEW Description: define a new materialized view Syntax: CREATE MATERIALIZED VIEW table_name [ (column_name [, ...] ) ] [ WITH ( storage_parameter [= value] [, ... ] ) ] [ TABLESPACE tablespace_name ] AS query [ WITH [ NO ] DATA ] postgres=# |
在PostgreSQL中，创建物化视图时，WITH NO DATA和WITH DATA选项控制了物化视图创建时是否立即填充数据。
WITH NO DATA: 当在创建物化视图时使用WITH NO DATA选项，PostgreSQL只会创建物化视图的结构，但不会立即填充任何数据进去。这意味着物化视图在创建后是空的。你可以在之后通过执行REFRESH MATERIALIZED VIEW命令来手动填充数据。这种方法适用于那些数据集非常大，或者填充数据操作需要在非高峰时段进行的场景，以便减少对系统资源的即时压力。
WITH DATA: 如果在创建物化视图时不指定WITH NO DATA，或者明确指定WITH DATA，那么PostgreSQL会在创建物化视图的同时立即执行底层查询并将结果集填充到物化视图中。这意味着物化视图创建后即可直接使用，无需额外的刷新操作。这对于数据集较小，或者需要立即可用的物化视图场景非常有用，但可能会在创建过程中对系统造成较大的资源消耗，尤其是对于大型查询或在资源紧张的系统上。
总结来说，WITH NO DATA选项让你能够分两步走：先创建物化视图的结构，之后再根据需要填充数据；而省略此选项或使用WITH DATA则是在创建时立即完成数据填充，物化视图创建后即可立即用于查询，但可能会有更高的即时资源需求。选择哪种方式取决于数据量大小、资源可用性和对数据即时性的要求。
在PostgreSQL中，使用REFRESH MATERIALIZED VIEW命令更新物化视图的内容时，可选的WITH [ NO ] DATA参数控制了刷新操作的具体行为：
•WITH DATA: 当使用WITH DATA或者根本不指定（因为默认行为就是WITH DATA），该命令会重新执行物化视图定义中的查询，以最新的基础数据来填充物化视图。这意味着物化视图会被更新为反映当前数据源的最新状态，所有相关的数据更改都会被包含进来。这对于确保物化视图数据的时效性和准确性非常重要。
•WITH NO DATA: 如果指定WITH NO DATA，执行REFRESH MATERIALIZED VIEW命令时，物化视图的已有数据会被清除，但不会用新的数据填充。 此时查询会报错：ERROR: materialized view "sales_summary_mv" has not been populated HINT: Use the REFRESH MATERIALIZED VIEW command. 这将导致物化视图变为一个空的结构，不包含任何数据。这种操作在某些维护场景下可能有用，比如当你打算之后以特定条件或在特定时间点手动填充数据，或者想要重置物化视图状态时。
总结来说，WITH DATA是常规的刷新操作，用于更新物化视图以反映最新的底层数据；而WITH NO DATA则是清空物化视图中的数据而不进行数据的重新填充，适用于需要清空视图内容的特定维护场景。
物化视图所支持功能
物化视图无法对表进行插入更新删除操作;
物化视图有系统表字段信息；
支持创建空间索引和属性字段索引，也可以在物化视图上创建索引（GIST、BRIN、SP-GiST、以及字段索引等）；
视图和物化视图区别
视图
- 结果未持久化在服务器
- 支持触发器和rule创建
- 不支持索引
- 通过定义触发器或规则可实现视图更新
- 每次的数据查询都是最新的
物化视图
- 结果集持久化在服务器
- 不支持触发器和rule创建
- 支持索引
- 对外部表创建物化视图，提高查询速度
- 数据更新只能采用refresh操作
刷新物化视图
1）：全量刷新，PostgreSQL9.3版本只有该刷新规则;
1 | REFRESH MATERIALIZED VIEW mymatview |
2）：增量刷新，PostgreSQL9.4版本增加的一个刷新规则，多一个关键字CONCURRENTLY
1 | REFRESH MATERIALIZED VIEW CONCURRENTLY mymatview |
区别
全量更新： 直接去基础表里面查询数据，但是刷新过程中会对该物化视图的所有的select操作阻塞，但刷新效率快。
增量刷新：
① 增量刷新需要物化视图具备唯一字段索引，将基础表查询出来的数据和现在物化视图表进行对比，填充差量数据，但刷新过程中不会对该物化视图的select操作进行阻塞，但刷新比全量更新慢。 若没有唯一字段，则会报错：
ERROR: cannot refresh materialized view "public.sales_summary_mv" concurrentlyHINT: Create a unique index with no WHERE clause on one or more columns of the materialized view.② 若是GP数据库，创建的物化视图默认为DISTRIBUTED RANDOMLY，若要实现增量更新，则在创建物化视图的时候需要显式加上“distributed by (字段1,字段2,...)”，否则后期不能创建唯一索引，会报错“
ERROR: UNIQUE and DISTRIBUTED RANDOMLY are incompatible”本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
