oracle vw sq 1,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