PostgreSQL 9.3物化视图使用
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 boy910 | kenyon good boy1011 | kenyon good boy1112 | kenyon good boy1213 | kenyon good boy1314 | kenyon good boy1415 | kenyon good boy1516 | kenyon good boy1617 | kenyon good boy1718 | kenyon good boy1819 | kenyon good boy1920 | 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 boy1112 | kenyon good boy1213 | kenyon good boy1314 | kenyon good boy1415 | kenyon good boy1516 | kenyon good boy1617 | kenyon good boy1718 | kenyon good boy1819 | kenyon good boy1920 | kenyon good boy20(10 rows)postgres=# \d+List of relationsSchema | 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,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 1postgres=# insert into test_kenyon values(22,'bad boy2');INSERT 0 1postgres=# select * from test_kenyon where id>20;id | vname----+----------21 | bad boy22 | 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 boy22 | 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 boy22 | bad boy2(2 rows)postgres=# refresh materialized view mv_test_kenyon with no data;REFRESH MATERIALIZED VIEWpostgres=# \d+List of relationsSchema | 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上一个:postgresql启动错误
下一个:MongoDB导出-导入-迁移
- 更多SQLServer疑问解答:
- 配置MSSQL复制指定快照文件夹提示:不是有效的路径或文件名
- 详细解读varchar和Nvarchar区别
- SQL SERVER 2005 同步复制技术
- 进程未能大容量复制到表 解决方法
- MSSql实例教程:MSSql数据库同步
- SQLServer2000同步复制技术实现(分发和订阅)
- sqlserver
- SQLSERVER
- 测试 sqlserver 最大用户数连接
- 写出一条Sql语句:取出表A中第31到第40记录(SQLServer,以自动增长的ID作为主键,注意:ID可能不是连续的
- sqlserver2008的安装问题。
- 为什么安装的SQL server 2008我的没有MSSQLSERVER协议啊?
- 怎样把exel表里的数据复制到sqlserver表里
- sqlserver数据库主键和外键问题
- sqlserver 中的左表连接查询和右表连接查询有啥不同?有什么用?