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 物化视图在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 boy1  2 | kenyon good boy2  3 | kenyon good boy3  4 | kenyon good boy4  5 | kenyon good boy5  6 | kenyon good boy6  7 | kenyon good boy7  8 | kenyon good boy8  9 | 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_kenyon  as 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)说明存储了数据，有相应的物理文件，并且有类似表的结构--表和物化视图的文件地址postgres=# select oID,pg_relation_filepath(oID),relpages from pg_class where relname = 'test_kenyon'; oID | pg_relation_filepath | relpages-------+----------------------+----------16396 | base/12896/16428   |    0(1 row)postgres=# select oID,relpages from pg_class where relname = 'mv_test_kenyon'; oID | pg_relation_filepath | relpages-------+----------------------+----------16459 | base/12896/16459   |    0(1 row)

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是全量更新(replace)物化视图内容,且是默认参数；with no data会清除物化视图内容，释放物化视图所占的空间，并使物化视图不可用
3.9.4版本预计会提供并发刷新的功能

六、参考： http://www.postgresql.org/docs/9.3/static/sql-creatematerializedvIEw.HTML http://wiki.postgresql.org/wiki/Materialized_VIEws 总结

以上是内存溢出为你收集整理的PostgreSQL 9.3物化视图使用全部内容，希望文章能够帮你解决PostgreSQL 9.3物化视图使用所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。