Oracle OCM考试系列教程_OCM考试实验笔记之一_物化视图

一、文档相关：
Data Warehousing Guide --> 8 Basic Materialized Views --> Creating
Materialized Views
二、创建物化视图日志：
三、
CREATE MATERIALIZED VIEW mv1 (aid,bid,aid2,bid2,acc)
BUILD IMMEDIATE
REFRESH force on commit
ENABLE QUERY REWRITE
AS
SELECT a.rowid,b.rowid,a.id2,b.id2,a.cc from t1 a,t2 b where a.id1=b.id2;
上面的物化视图，可以快速刷新，也可以查询重写。
如果不起别名：
CREATE MATERIALIZED VIEW mv1
BUILD IMMEDIATE
REFRESH fast on commit
ENABLE QUERY REWRITE
AS
SELECT a.rowid rid,b.rowid rid2,a.id2 aid2,b.id2 bid2,a.cc from t1 a,t2 b
where a.id1=b.id2;
类似这样的语句也可以查询重写：select b.id2,a.cc from t1 a,t2 b where a.id1
=b.id2;
上面的例子已反复验证，无需怀疑，
CREATE MATERIALIZED VIEW mv1
BUILD IMMEDIATE
REFRESH fast on commit
ENABLE QUERY REWRITE
AS
SELECT a.cc from t1 a,t2 b where a.id1=b.id2 group by a.cc;
SELECT a.cc,sum(a.id2) from t1 a,t2 b where a.id1=b.id2 group by a.cc;
SELECT a.cc,sum(a.id1) from t1 a,t2 b where a.id1=b.id2 group by a.cc;
上面这三条语句都可以快速刷新。

455