Shulou(Shulou.com)06/01 Report--

Both Oracle and PostgreSQL provide materialized views, but Oracle is obviously much more powerful than PostgreSQL, especially query rewriting query rewrite.

Oracle

Create data tables and materialized view logs and insert data

Table dropped.TEST-orcl@DESKTOP-V430TU3 > create table t_materializedview (id int primary key,c1 varchar2 (20)), Table created.TEST-orcl@DESKTOP-V430TU3 > create materializedview log on tantalizedviewMaterialized view log created.TEST-orcl@DESKTOP-V430TU3 > drop table tantalizedViewpoint 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 materializedview log on materialized view log created.

Create a materialized view

TEST-orcl@DESKTOP-V430TU3 > drop materializedview vw_t_materializedview;Materialized view dropped.TEST-orcl@DESKTOP-V430TU3 > create materializedview 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.

Query base table

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

As you can see from the execution plan, the query statement is automatically rewritten as the query materialized view.

PostgreSQL

Create a data table and insert data

[local]: 5432 pg12@testdb=# drop table tweeted enumerated views: 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 grammatical skills | | x from generate_series (1100000) as x INSERT 0 100000Time: 600.401 ms [local]: 5432 pg12@testdb=# insert into t_materializedview (id,c1) select x training test | | x from generate_series (100001200000) as x insert 0 100000Time: 520.054 ms [local]: 5432 pg12@testdb=#

Create a materialized view

[local]: 5432 pg12@testdb=# drop materializedview vw_t_materializedview;ERROR: materializedview "vw_t_materializedview" does not existTime: 1.114 ms [local]: 5432 pg12@testdb=# create materializedview vw_t_materializedview pg12@testdb-# as select * from t_materializedview where C1 like 'test%'; SELECT 100000Time: 302.380 ms [local]: 5432 pg12@testdb=#

Query data

[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 materializedview 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 has not yet implemented automatic rewriting based on materialized views.

references

N/A

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.