Oracle和PostgreSQL都提供了物化檢視，但Oracle的功能顯然比PostgreSQL要強大不少，特別是查詢重寫query rewrite功能。

**Oracle**
建立資料表和物化檢視日誌，插入資料
```
TEST-orcl@DESKTOP-V430TU3>drop table t_materializedview;

Table dropped.

TEST-orcl@DESKTOP-V430TU3>create table t_materializedview(id int primary key,c1 varchar2(20));

Table created.

TEST-orcl@DESKTOP-V430TU3>create materialized view log on t_materializedview;

Materialized view log created.

TEST-orcl@DESKTOP-V430TU3>  drop table t_materializedview;

Table dropped.

TEST-orcl@DESKTOP-V430TU3>create table t_materializedview(id int primary key,c1 varchar2(20));

Table created.

TEST-orcl@DESKTOP-V430TU3>
TEST-orcl@DESKTOP-V430TU3>insert into t_materializedview(id,c1) select rownum,'test'||rownum from dba_objects;

128068 rows created.

TEST-orcl@DESKTOP-V430TU3>insert into t_materializedview(id,c1) select rownum+1000000,'TEST'||rownum from dba_objects;

128068 rows created.

TEST-orcl@DESKTOP-V430TU3>commit;

Commit complete.

TEST-orcl@DESKTOP-V430TU3>
TEST-orcl@DESKTOP-V430TU3>create materialized view log on t_materializedview;

Materialized view log created.

```
建立物化檢視
```
TEST-orcl@DESKTOP-V430TU3>  drop materialized view vw_t_materializedview;

Materialized view dropped.

TEST-orcl@DESKTOP-V430TU3>create materialized view vw_t_materializedview
  2    refresh fast on demand start with sysdate with primary key enable query rewrite
  3    as select * from t_materializedview where c1 like 'test%';

Materialized view created.

```
查詢基表
```
TEST-orcl@DESKTOP-V430TU3>
TEST-orcl@DESKTOP-V430TU3>select * from t_materializedview where c1 like 'test%' and id < 10;

ID C1
---------- --------------------
         1 test1
         2 test2
         3 test3
         4 test4
         5 test5
         6 test6
         7 test7
         8 test8
         9 test9

9 rows selected.

TEST-orcl@DESKTOP-V430TU3>set autotrace traceonly
TEST-orcl@DESKTOP-V430TU3>select * from t_materializedview where c1 like 'test%' and id < 10;

9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1344903509

--------------------------------------------------------------------------------
--------------------------------

--------------------------------------------------------------------------------
--------------------------------

|   0 | SELECT STATEMENT                       |                       |     9 |
   225 |     3   (0)| 00:00:01 |

|   1 |  MAT_VIEW REWRITE ACCESS BY INDEX ROWID| VW_T_MATERIALIZEDVIEW |     9 |
   225 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN                     | SYS_C0055952          |     9 |
       |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------
--------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("VW_T_MATERIALIZEDVIEW"."ID"<10)

Note
-----
   - 'PLAN_TABLE' is old version
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        756  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

TEST-orcl@DESKTOP-V430TU3>

```
從執行計劃可以看到，查詢語句被自動重寫為查詢物化檢視。

**PostgreSQL**
建立資料表,插入資料
```
[local]:5432 pg12@testdb=# drop table t_materializedview;
ERROR:  table "t_materializedview" does not exist
Time: 31.285 ms
[local]:5432 pg12@testdb=# create table t_materializedview(id int primary key,c1 varchar(20));
CREATE TABLE
Time: 194.505 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# insert into t_materializedview(id,c1) select x,'test'||x from generate_series(1,100000) as x;
INSERT 0 100000
Time: 600.401 ms
[local]:5432 pg12@testdb=# insert into t_materializedview(id,c1) select x,'TEST'||x from generate_series(100001,200000) as x;
INSERT 0 100000
Time: 520.054 ms
[local]:5432 pg12@testdb=#

```

建立物化檢視
```
[local]:5432 pg12@testdb=# drop materialized view vw_t_materializedview;
ERROR:  materialized view "vw_t_materializedview" does not exist
Time: 1.114 ms
[local]:5432 pg12@testdb=# create materialized view vw_t_materializedview 
pg12@testdb-#   as select * from t_materializedview where c1 like 'test%'; 
SELECT 100000
Time: 302.380 ms
[local]:5432 pg12@testdb=#

```

Time: 1.709 ms
[local]:5432 pg12@testdb=#

[local]:5432 pg12@testdb=# explain verbose select * from t_materializedview where c1 like 'test%' and id < 10; 
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Index Scan using t_materializedview_pkey on public.t_materializedview  (cost=0.42..8.60 rows=4 width=14)
   Output: id, c1
   Index Cond: (t_materializedview.id < 10)
   Filter: ((t_materializedview.c1)::text ~~ 'test%'::text)
(4 rows)

Time: 2.732 ms
[local]:5432 pg12@testdb=#

```
PostgreSQL尚未實現基於物化檢視的自動重寫

**參考資料**
N/A

Oracle vs PostgreSQL Develop（20） - Materialized View

相關文章