V1.0
Revision
Author/Modifier
Comments
No.
Date
1.0
2012/2/15
张中靖
一、 目的
说明物化视图的常用功能和限制条件
二、 适用范围
l 所有线上库
三、 风险评估
l on commit模式创建的物化视图会基表上的分布式事务失败。
l 创建物化视图时查询里使用了*代替基表所有列，后续基表加字段时，物化视图需要重建
l 创建支持快速刷新的物化视图，后续遗忘刷新增量数据，导致物化视图log积压很多
l 多表join的物化视图增量刷新时有重复数据，谨慎使用
四、 操作流程
1. 准备工作
a) 检查用户权限，物化视图的所有者需要CREATE MATERIALIZED VIEW和create table权限，以及基表的查询权限。
如果创建支持on commit刷新的物化视图，需要对基表有ON COMMIT REFRESH权限。
如果创建支持query rewrite的物化视图，需要对基表有QUERY REWRITE权限。
b) 检查用户的表空间配额(quota)，物化视图的数据需要独立存储空间。
c) 如果基表跟物化视图不在一个库上，需要创建dblink。
d) 准备物化视图的查询sql，根据需要调整sql结构，保证执行计划正常。
e) 如果创建支持快速刷新的物化视图，需要保证：
l 物化视图不能包含对不重复表达式的引用，如SYSDATE和ROWNUM
l 物化视图不能包含对LONG和LONG RAW数据类型的引用
l 基表必须有主键
l FROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志
l 如果物化视图基表是通过dblink来访问的，查询子句中多表的连接条件必须是主键。
2. 执行过程
a) 创建物化视图日志：（创建非快速刷新的物化视图不需要此步骤）
物化视图的关键点在with子句部分，下面oracle文档上with子句的语法结构：
object id：仅当基表是对象类型时，with 子句可以此项，一般极少使用。
primary key：默认情况下，主键是包含在物化视图日志里，可以不指定此项。
rowid：指定时，物化视图日志里会记录rowid信息。
sequence：指定时，物化视图日志里多一列sequence$$，给每个操作一个SEQUENCE号，从而保证刷新时按照顺序进行刷新。
column：指定哪些列的数据存放在物化视图日志里，注意这些列不能是主键列。
new_value_clause：有两个选项。
默认是excluding new values，即物化视图日志里不记录数据变更时的新值
including new value：即物化视图日志里记录数据变更前后的新旧值，即同
一个主键id会对应两条日志记录。
下面是几个创建物化视图的示例：
CREATE MATERIALIZED VIEW LOG ON customers WITH PRIMARY KEY, ROWID;
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID, SEQUENCE(amount_sold,
time_id, prod_id)INCLUDNG NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON order_items WITH (product_id);
b) 创建物化视图：
创建物化视图的重点在刷新方式上，刷新方式的语法参照下面：
fast：使用快速刷新，需要先做步骤一，这种刷新方式使用物化视图日志做增量，刷
时间短。
complete：每次都是全量刷新，全量刷新时先truncate物化视图现有数据，重新
从基表生成数据，刷新时间比较长，对应用影响大，一般应用有实时要求的
不能采用这种方式。
force：这种刷新方式会先尝试fast刷新，如果不支持fast，则用complete方式数据，
不指定时，默认是这个。
on commit：物化视图的基表数据发生修改时实时刷新物化视图，这种方式对应用
事务稍有影响，一般不建议使用。
on demand：根据需要刷新，一般采用这个方式，用job或者crontab或者应用程序主动调用dbms_mview.refresh方法来刷新数据。不指定的情况下，默认是这个。
build immediate：创建物化视图的同时做一次全量刷新，一般采用这种方式。不指定时默认是这个。
build deferred：创建物化视图时不生成数据，后续第一次刷新时进行全量刷新。不指定时默认是这个。
enable/disable query rewrite：是否支持查询重写，如果是enable，oracle优化器会对一些sql使用该物化视图进行优化，这样来减少查询时间。
下面是几个创建物化视图的示例：
CREATE MATERIALIZED VIEW sales_mv
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS SELECT t.calendar_year, p.prod_id,
SUM(s.amount_sold) AS sum_sales
FROM times t, products p, sales s
WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_id;
CREATE MATERIALIZED VIEW sales_by_month_by_state
TABLESPACE example
PARALLEL 4
BUILD IMMEDIATE
REFRESH COMPLETE
ENABLE QUERY REWRITE
AS SELECT t.calendar_month_desc, c.cust_state_province,
SUM(s.amount_sold) AS sum_sales
FROM times t, sales s, customers c
WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
GROUP BY t.calendar_month_desc, c.cust_state_province;
下面这个是基于dblink的物化视图，查询子句中连接条件不是主键，因此不支持fast
refresh。
CREATE MATERIALIZED VIEW TPCRM_LEADS_INFO
BUILD IMMEDIATE
REFRESH COMPLETE
SELECT o.customer_id as id,
o.gmt_create,
o.gmt_modified,
o.owner_1,
c.country,
c.member_id
FROM caesar.caesar_customer_ggs@crmg c, caesar.caesar_opportunity@crmg o
WHERE c.is_deleted = ‘n’
AND o.is_deleted = ‘n’
AND c.cus_id = o.customer_id;
dbms_mview.explain_mview：如果物化视图创建时报错，可以使用该方法检查报
错原因。下面是个示例，来源于杨铤锟的博客
SQL> alter table fact drop constraint fk_fact_aid;
表已更改。
SQL> alter table dim_a drop primary key;
表已更改。
SQL> create materialized view mv_fact refresh fast on commit as
2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id(+)
6 and f.bid = b.id;
from fact f, dim_a a, dim_b b
*
ERROR 位于第 4 行:
ORA-12052: 无法快速刷新实体化视图 YANGTK.MV_FACT
SQL> begin
2 dbms_mview.explain_mview(’select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id,
3 a.name a_name, b.name b_name, num
4 from fact f, dim_a a, dim_b b
5 where f.aid = a.id(+)
6 and f.bid = b.id’);
7 end;
8 /
PL/SQL 过程已成功完成。
SQL> select msgtxt from mv_capabilities_table where capability_name = ‘REFRESH_FAST_AFTER_INSERT’;
MSGTXT
———————————————————-
在内部表的联接列上没有唯一性约束条件
SQL> alter table dim_a add primary key (id);
表已更改。
SQL> alter table fact add constraint fk_fact_aid foreign key (aid) references dim_a(id);
表已更改。
3. 验证方案：
a) 检查物化视图刷新机制是否正常：
－－快速刷新指定的物化视图
execute dbms_mview.refresh(‘物化视图名字‘,‘f’);
－－全量刷新指定的物化视图
execute dbms_mview.refresh(‘物化视图名字‘,‘c’);
b) 对物化视图的基表做数据修改，然后使用物化视图的查询子句创建一个临时表。
create table tmp_物化视图名 as 物化视图的查询子句。
然后分别按照上面方法手工刷新物化视图，刷新后核对物化视图和临时表的数据量是否一致。
五、 核心对象风险
物化视图可以用来对核心表进行数据迁移，以及支持核心表的数据抽取。
由于在数据量比较大的情况下，全量刷新耗时比较久，核心表上物化视图尽量使用fast refresh模式。
核心表访问量大，数据变更频繁，其上的物化视图不能使用on commit模式创建。
创建跨dblink的且多表join的物化视图时，如果join条件不是主键，不支持fast refresh，尽量不要在核心表创建这种物化视图。
六、 回退方案
创建物化视图失败时：
如果基表上第一次创建物化视图，使用包dbms_mview.explain_mview检查创建失败原因，如果是物化视图日志不符合要求，删除物化视图日志即可。
如果不确定基表上是否有其他物化视图，检查基表上是否有其他物化视图，如果没有，直接删除物化视日志。
如果存在，需要评估对其他物化视图的影响。
检查基表上依赖的物化视图：
select a.owner, a.name, a.mview_site
from dba_registered_mviews a, dba_base_table_mviews b
where a.mview_id = b.mview_id
and b.master = ‘基表名‘;
七、 历史故障及教训
略
