PostgreSQL 9.3物化视图使用

物化视图在Oracle里面是很早就内置的一个功能，而PostgreSQL也很早就将功能代码做出来，方式是类似create table as....，只是一直没有内置，9.3版本终于将此作为一个内置的功能点来使用，下面分享下最新版本的物化视图使用。目前postgres9.3在官网上有4个安装包，分别是9.3.0（stable version）、9.3.0 beta1、9.3.0betal2和9.3.0rc版本（release candidate version），所以我们下载稳定的9.3.0 stable版本。下载地址： http://www.postgresql.org/ftp/source/v9.3.0/
安装略。一、语法CREATE MATERIALIZED VIEW table_name[ （column_name [, ...] ） ][ WITH （ storage_parameter [= value] [, ... ] ） ][ TABLESPACE tablespace_name ]AS query[ WITH [ NO ] DATA ]二、说明
storage_parameter是存储参数，诸如填充因子（fillfactor）等,tablespace可以指定表空间,比较关键的是后面的as query with [no] data,后面示例描述

三、示例

1.创建基础表[postgres@primary ~]$ psqlpsql （9.3.0）Type "help" for help.postgres=# create table test_kenyon（id int,vname text）; CREATE TABLEpostgres=# insert into test_kenyon select generate_series（1,20）,"kenyon good boy"||generate_series（1,20）;INSERT 0 20postgres=# select * from test_kenyon ; id | vname ----+-------------------1 | kenyon good boy12 | kenyon good boy23 | kenyon good boy34 | kenyon good boy45 | kenyon good boy56 | kenyon good boy67 | kenyon good boy78 | kenyon good boy89 | kenyon good boy9 10 | kenyon good boy10 11 | kenyon good boy11 12 | kenyon good boy12 13 | kenyon good boy13 14 | kenyon good boy14 15 | kenyon good boy15 16 | kenyon good boy16 17 | kenyon good boy17 18 | kenyon good boy18 19 | kenyon good boy19 20 | kenyon good boy20（20 rows）2.创建物化视图postgres=# create materialized view mv_test_kenyonas select * from test_kenyon where id > 10;SELECT 10postgres=# select * from mv_test_kenyon; id | vname ----+------------------- 11 | kenyon good boy11 12 | kenyon good boy12 13 | kenyon good boy13 14 | kenyon good boy14 15 | kenyon good boy15 16 | kenyon good boy16 17 | kenyon good boy17 18 | kenyon good boy18 19 | kenyon good boy19 20 | kenyon good boy20（10 rows）postgres=# d+List of relations Schema |Name| Type|Owner | Size| Description --------+----------------+-------------------+----------+-------+------------- public | mv_test_kenyon | materialized view | postgres | 16 kB |public | test_kenyon| table | postgres | 16 kB | （2 rows）postgres=# d mv_test_kenyonMaterialized view "public.mv_test_kenyon" Column |Type | Modifiers --------+---------+----------- id | integer |vname| text|--size有大小（默认空表是8kb,而这里是16kb）说明存储了数据，有相应的物理文件，并且有类似表的结构3.物化视图更新postgres=# insert into test_kenyon values（21,"bad boy"）;INSERT 0 1postgres=# insert into test_kenyon values（22,"bad boy2"）;INSERT 0 1postgres=# select * from test_kenyon where id>20; id |vname ----+---------- 21 | bad boy 22 | bad boy2（2 rows）postgres=# select * from mv_test_kenyon where id>20; id | vname ----+-------（0 rows）--物化视图的数据没有刷新过来--刷新物化视图数据postgres=# refresh materialized view mv_test_kenyon;REFRESH MATERIALIZED VIEWpostgres=# select * from mv_test_kenyon where id>20; id |vname ----+---------- 21 | bad boy 22 | bad boy2（2 rows）--使用with no data刷新postgres=# insert into test_kenyon values（32,"bad boy3"）; INSERT 0 1postgres=# select * from mv_test_kenyon where id>20;id |vname ----+---------- 21 | bad boy 22 | bad boy2（2 rows）postgres=# refresh materialized view mv_test_kenyon with no data;REFRESH MATERIALIZED VIEWpostgres=# d+ List of relations Schema |Name| Type|Owner |Size| Description --------+----------------+-------------------+----------+------------+------------- public | mv_test_kenyon | materialized view | postgres | 8192 bytes |public | test_kenyon| table | postgres | 16 kB| （2 rows）postgres=# select * from mv_test_kenyon;ERROR:materialized view "mv_test_kenyon" has not been populatedHINT:Use the REFRESH MATERIALIZED VIEW command.使用了with no data刷新后会导致物化视图里面的数据清除干净，并使物化视图不可用，如果需要继续使用，需要使用REFRESH MATERIALIZED VIEW view_name来恢复。

4.删除物化视图postgres=# drop materialized view mv_test_kenyon ;DROP MATERIALIZED VIEWpostgres=# --如果有其他约束在物化视图上，需要加cascade来级联删除四、应用场景和优劣势
可以将复杂的SQL写成视图来调用，并可增大数据的安全性
另外物化视图与普通视图比因为直接扫描数据，通常扫描的数据更少，在有索引的支持下，效率更高，网络消耗也更少，特别是跨DB，跨服务器的查询
与普通视图相比的劣势是数据需要不定时地刷新才能获取到最实时的数据。

五 、总结
1.物化视图当前是全量刷新，暂不支持增量刷新
2.刷新参数with data是全量更新物化视图内容,且是默认参数；with no data会清除物化视图内容，释放物化视图所占的空间，并使物化视图不可用

六、参考：
http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html http://wiki.postgresql.org/wiki/Materialized_Views相关阅读：PostgreSQL删除表中重复数据行 http://www.linuxidc.com/Linux/2013-07/87780.htmPostgreSQL数据库连接池PgBouncer的搭建 http://www.linuxidc.com/Linux/2013-06/85928.htmWindows平台编译 PostgreSQL http://www.linuxidc.com/Linux/2013-05/85114.htmPostgreSQL备份心得笔记 http://www.linuxidc.com/Linux/2013-04/82812.htmPostgreSQL 的详细介绍：请点这里
PostgreSQL 的下载地址：请点这里在优化SQL语句中使用虚拟索引Oracle 10g/11g下如何将物理Standby库临时激活用于测试相关资讯物化视图 PostgreSQL 9.3 PostgreSQL视图

Oracle物化视图失效的几种情况及测（01/04/2015 11:41:16）
CentOS 6.3环境下yum安装（05/15/2014 18:53:08）
Oracle物化视图测试（11/26/2013 11:48:01）

物化视图刷新时报0RA-01400的错误（10/30/2014 19:18:50）
物化视图定义不当引发Oracle性能问（02/22/2014 20:40:09）
PostgreSQL 9.3 RC1 发布（08/27/2013 14:41:20）

本文评论查看全部评论（0）