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 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)說明儲存了資料，有相應的物理檔案，並且有類似表的結構

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刪除表中重複資料行

PostgreSQL資料庫連接池PgBouncer的搭建

Windows平台編譯 PostgreSQL

PostgreSQL備份心得筆記

PostgreSQL 的詳細介紹：請點這裡
PostgreSQL 的：請點這裡