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 -------------------------------------------------------------------------------- -------------------------------- | 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 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=# ``` 查询数据 ``` [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 VIEW Time: 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