Oracle vs PostgreSQL Develop（20） - Materialized View

2025-04-11 技术教程

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