OraclevsPostgreSQLDevelop（20）-MaterializedView
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尚未实现基于物化视图的自动重写
文章名称：OraclevsPostgreSQLDevelop（20）-MaterializedView
分享链接：http://mswzjz.cn/article/pejcej.html