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 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