文档章节

PostgreSQL 9.3物化视图使用

kenyon_君羊
 kenyon_君羊
发布于 2013/09/12 22:13
字数 1059
阅读 8694
收藏 47
物化视图在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 ~]$ psql
psql (9.3.0)
Type "help" for help.

postgres=# create table test_kenyon(id int,vname text);                                                 
CREATE TABLE
postgres=# insert into test_kenyon select generate_series(1,20),'kenyon good boy'||generate_series(1,20);
INSERT 0 20
postgres=# 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 10
postgres=# 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_kenyon
Materialized 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,pg_relation_filepath(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 1
postgres=# insert into test_kenyon values(22,'bad boy2');
INSERT 0 1
postgres=# 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 VIEW
postgres=# 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 1
postgres=# 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 VIEW
postgres=# \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 populated
HINT:  Use the REFRESH MATERIALIZED VIEW command.
使用了with no data刷新后会导致物化视图里面的数据清除干净,并使物化视图不可用,如果需要继续使用,需要使用REFRESH MATERIALIZED VIEW view_name来恢复。

4.删除物化视图
postgres=# drop materialized view mv_test_kenyon ;
DROP MATERIALIZED VIEW
postgres=# 
--如果有其他约束在物化视图上,需要加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

© 著作权归作者所有

共有 人打赏支持
kenyon_君羊
粉丝 499
博文 170
码字总数 121714
作品 0
杭州
其他
私信 提问
加载中

评论(15)

宏哥
宏哥

引用来自“mark35”的评论

引用来自“Raynor1”的评论

引用来自“宏哥”的评论

引用来自“kenyon_君羊”的评论

引用来自“Raynor1”的评论

引用来自“kenyon_君羊”的评论

引用来自“Raynor1”的评论

我还以为这物化视图可以像ORACLE一样。。

比oracle的功能要少很多

嗯。看了一下你测试的功能确实好少。不过我想既然有了后面肯定会完善。

还有一些没写,比如物化视图上的索引、约束以及和触发器结合使用等,期待后面的版本吧

内部实现 真正的物化视图, 其实蛮复杂的
这也是PG在这个实现上遇到问题的真正原因
需要很复杂的逻辑策略来支持

这是不容易呀。。我觉得在未来的某个时候会实现的。嗯。

照pg发展趋势,估计9.4,9.5应该就实现了

这个如果能实现 On demand 和 增量

将会是比index only 更大的进步
mark35
mark35

引用来自“Raynor1”的评论

引用来自“宏哥”的评论

引用来自“kenyon_君羊”的评论

引用来自“Raynor1”的评论

引用来自“kenyon_君羊”的评论

引用来自“Raynor1”的评论

我还以为这物化视图可以像ORACLE一样。。

比oracle的功能要少很多

嗯。看了一下你测试的功能确实好少。不过我想既然有了后面肯定会完善。

还有一些没写,比如物化视图上的索引、约束以及和触发器结合使用等,期待后面的版本吧

内部实现 真正的物化视图, 其实蛮复杂的
这也是PG在这个实现上遇到问题的真正原因
需要很复杂的逻辑策略来支持

这是不容易呀。。我觉得在未来的某个时候会实现的。嗯。

照pg发展趋势,估计9.4,9.5应该就实现了
Raynor1
Raynor1

引用来自“宏哥”的评论

引用来自“kenyon_君羊”的评论

引用来自“Raynor1”的评论

引用来自“kenyon_君羊”的评论

引用来自“Raynor1”的评论

我还以为这物化视图可以像ORACLE一样。。

比oracle的功能要少很多

嗯。看了一下你测试的功能确实好少。不过我想既然有了后面肯定会完善。

还有一些没写,比如物化视图上的索引、约束以及和触发器结合使用等,期待后面的版本吧

内部实现 真正的物化视图, 其实蛮复杂的
这也是PG在这个实现上遇到问题的真正原因
需要很复杂的逻辑策略来支持

这是不容易呀。。我觉得在未来的某个时候会实现的。嗯。
宏哥
宏哥

引用来自“kenyon_君羊”的评论

引用来自“Raynor1”的评论

引用来自“kenyon_君羊”的评论

引用来自“Raynor1”的评论

我还以为这物化视图可以像ORACLE一样。。

比oracle的功能要少很多

嗯。看了一下你测试的功能确实好少。不过我想既然有了后面肯定会完善。

还有一些没写,比如物化视图上的索引、约束以及和触发器结合使用等,期待后面的版本吧

内部实现 真正的物化视图, 其实蛮复杂的
这也是PG在这个实现上遇到问题的真正原因
需要很复杂的逻辑策略来支持
kenyon_君羊
kenyon_君羊

引用来自“Raynor1”的评论

引用来自“kenyon_君羊”的评论

引用来自“Raynor1”的评论

我还以为这物化视图可以像ORACLE一样。。

比oracle的功能要少很多

嗯。看了一下你测试的功能确实好少。不过我想既然有了后面肯定会完善。

还有一些没写,比如物化视图上的索引、约束以及和触发器结合使用等,期待后面的版本吧
Raynor1
Raynor1

引用来自“kenyon_君羊”的评论

引用来自“Raynor1”的评论

我还以为这物化视图可以像ORACLE一样。。

比oracle的功能要少很多

嗯。看了一下你测试的功能确实好少。不过我想既然有了后面肯定会完善。
kenyon_君羊
kenyon_君羊

引用来自“Raynor1”的评论

我还以为这物化视图可以像ORACLE一样。。

比oracle的功能要少很多
乌龟壳
乌龟壳

引用来自“宏哥”的评论

这个东西, 我做了一些评估
基本上和下面等价:
create table as select ....
---------------
lock table
truncate table
insert into select .....
unlock table
--------------
目前来说, 物化视图, 通过自定义的增量更新, 更加现实一些.
坦率一点说, 目前的这个实现, 毫无价值.

+1
Raynor1
Raynor1
我还以为这物化视图可以像ORACLE一样。。
kenyon_君羊
kenyon_君羊

引用来自“陈景峰”的评论

如果表中有很多外键约束,怎么解决物化视图的修改与新增等等问题?

现在版本的物化视图是全量删除,全量更新,跟表外键没什么关系,除非物化视图本身有其他外键约束
ora2pg 12.0 发布,Oracle 转 PostgreSQL 工具

ora2pg 12.0 修复了很多 bug,增加了三个新特性: 1. 使用 REORDERING_COLUMNS 指令来对列进行重新排序 2. PG_SUPPORTS_MVIEW 来使用 PG 9.3 新语法导出物化视图 3. USE_TABLESPACE 变量用来...

oschina
2013/10/24
1K
0
ora2pg 9.3 发布,Oracle 转 PostgreSQL 工具

ora2pg 9.3 发布,该版本包含大量改动,包括 5 个新的配置指令,新的导出类型 MVIEW 用于导出物化视图,自动检测和设置 Oracle 字符集以及 PostgreSQL 客户端编码等等。 Ora2pg可以用来将Ora...

oschina
2012/10/08
705
0
postgresql9.5 物化视图测试

copyright http://cupegraf.com/ 视图是指数据库只存储定义该视图的查询语句(内容是查询时产生),而物化视图是一个其查询语句查询后的内容并存储的视图(内容是创建物化视图刷新视图时产生,...

廖君
2015/07/30
0
0
PostgreSQL 9.3 Beta 1 发布

PostgreSQL 9.3 首个 Beta 版本发布了,该版本包含大量新特性,包括可写的 foreign 表、自动可更新的视图、横向 (lateral) 联合、索引正则表达式搜索、使用 checksum 来检测文件系统的数据损...

oschina
2013/05/14
1K
7
这些特性,Postgre SQL秒杀其他数据库

你可能会问自己 “为什么选择PostgreSQL ?” 开源数据库我们有好几种选择(本文参考 MySQL, MariaDB 和 Firebird ), 那么 PostgreSQL 具有哪些其它开源数据库不具备的特性呢? PostgreSQL 宣...

三墩IT人
2016/02/04
0
0

没有更多内容

加载失败,请刷新页面

加载更多

data.world

产品思路: 1、共享数据集。 2、数据结构化。 3、数据展示。 https://data.world

colin_86
12分钟前
1
0
冒泡排序的三种写法

冒泡排序是程序员最耳熟能详的排序了,本文简单叙述下我写过的三种冒泡排序写法。 冒泡,顾名思义,待排序的数字像气泡一样,通过比较,以升序或降序的方式排序。 我最开始是这样写冒泡排序的...

niithub
37分钟前
1
0
Akka实战:HTTP大文件断点上传、下载,秒传

访问:https://github.com/yangbajing/scala-applications/tree/master/file-upload 获取本文所述完整源码,包括Akka HTTP后端和HTML5实现的前端。 在很多应用里面都会有类似大文件上传的需求...

羊八井
47分钟前
5
0
node:event-loop & 宏任务 & 微任务

event-loop(事件轮询) 代码进入执行栈后会判断当前代码是同步任务还是异步任务,如果是同步任务则会将任务调到主线程同步执行,如果是异步任务则会将任务调到异步队列中。 主线程同步任务执...

小草先森
51分钟前
3
0
php-fpm配置文件详解

php-fpm配置文件详解 php-fpm配置文件路径: /usr/local/php-fpm/etc/php-fpm.conf ,还包含了一个配置文件路径下的所有以.conf结尾的配置文件(子配置文件)/usr/local/php-fpm/etc/php-fp...

李超小牛子
今天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部