Oracle vs PostgreSQL Develop（20） - Materialized View

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 test99 rows selected.TEST-orcl@DESKTOP-V430TU3>set autotrace traceonlyTEST-orcl@DESKTOP-V430TU3>select * from t_materializedview where c1 like 'test%' and id < 10;9 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 1344903509----------------------------------------------------------------------------------------------------------------| Id  | Operation                              | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------------------------|   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 processedTEST-orcl@DESKTOP-V430TU3>

从执行计划可以看到，查询语句被自动重写为查询物化视图。

PostgreSQL
创建数据表,插入数据

[local]:5432 pg12@testdb=# drop table t_materializedview;ERROR:  table "t_materializedview" does not existTime: 31.285 ms[local]:5432 pg12@testdb=# create table t_materializedview(id int primary key,c1 varchar(20));CREATE TABLETime: 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 100000Time: 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 100000Time: 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 existTime: 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 100000Time: 302.380 ms[local]:5432 pg12@testdb=#

查询数据

[local]:5432 pg12@testdb=# select * from vw_t_materializedview limit 10; id |   c1   ----+--------  1 | test1  2 | test2  3 | test3  4 | test4  5 | test5  6 | test6  7 | test7  8 | test8  9 | test9 10 | test10(10 rows)Time: 3.517 ms[local]:5432 pg12@testdb=# refresh materialized view vw_t_materializedview;REFRESH MATERIALIZED VIEWTime: 251.243 ms[local]:5432 pg12@testdb=# select * from vw_t_materializedview limit 10; id |   c1   ----+--------  1 | test1  2 | test2  3 | test3  4 | test4  5 | test5  6 | test6  7 | test7  8 | test8  9 | test9 10 | test10(10 rows)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