【PostgreSQL】物化視圖

標籤：int   new   ref   rate   tables   ora   操作   postgre   font   

PostgreSQL 9.3 以後出現了物化視圖。物化視圖是由實實在在的資料群組成，這是和一般視圖的根本區別。

1. 物化視圖建立文法如下：

--建立文法CREATE MATERIALIZED VIEW table_name    [ (column_name [, ...] ) ]    [ WITH ( storage_parameter [= value] [, ... ] ) ]    [ TABLESPACE tablespace_name ]    AS query    [ WITH [ NO ] DATA ]--重新整理文法REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name    [ WITH [ NO ] DATA ]

2. 建立物化視圖樣本

--建立測試表test=# CREATE TABLE TEST01 (ID INT);CREATE TABLEtest=# INSERT INTO TEST01 SELECT GENERATE_SERIES(1,1000000);INSERT 0 1000000--建立物化視圖test=# CREATE MATERIALIZED VIEW V_TEST01 AS SELECT * FROM TEST01;SELECT 1000000test=# test=# INSERT INTO TEST01 VALUES (1000001);INSERT 0 1test=# \timingTiming is on.--不加CONCURRENTLY參數的重新整理test=# REFRESH MATERIALIZED VIEW V_TEST01;REFRESH MATERIALIZED VIEWTime: 1888.860 mstest=# INSERT INTO TEST01 VALUES (1000002);INSERT 0 1test=# CREATE UNIQUE INDEX INX_ID ON V_TEST01(ID);CREATE INDEX--加CONCURRENTLY參數的重新整理test=# refresh materialized view concurrently v_test01;REFRESH MATERIALIZED VIEWTime: 100476.707 ms可見，加了CONCRRENTLY參數後，重新整理時間明顯增加。但加了CONCRRENTLY參數的更新不會阻塞查詢操作。

 

The End！

2017-08-21

【PostgreSQL】物化視圖