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

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:22

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

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】