【转载】物化视图详解

2016-01-12 14:34 AlfredZhao 阅读(3351) 评论(0) 编辑收藏举报

原文地址：http://czmmiao.iteye.com/blog/1827254

物化视图

物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。物化视图可以基于表查询，视图和其它的物化视图。通常情况下，在复制环境下，物化视图被称为主表，在数据仓库中称为明细表。
对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的。如果你想修改本地副本，必须用高级复制的功能。当你想从一个表或视图中抽取数据时，你可以用从物化视图中抽取。
对于数据仓库，创建的物化视图通常情况下是聚合视图，单一表聚合视图和连接视图。

物化视图日志
如果你想要采用快速刷新的话，必须建立基于主表的物化视图日志。物化视图日志可以记录主表被更新记录的主键、ROWID或对象标识或者二者全部。物化视图日志也可以包含其他列用以支持带子查询的物化视图的快速刷新。
物化视图日志表的名称为MLOG$_后面跟主体对象的名称。物化视图日志和主体对象建立在相同的用户模式下。一个物化视图日志可以支持多个基于主体对象的物化视图。
有下列几种物化视图日志：
主键：物化视图日志在记录主体对象发生变化的记录时，是根据主键定位的。
ROWID：物化视图日志在记录主体对象发生变化的记录时，是根据ROWID定位的。
对象标识：物化视图日志在记录主体对象发生变化的记录时，是根据对象标识定位的。
组合类型：物化视图日志在记录主体对象发生变化的记录时，是根据上面三种类型的组合来定位的。如果物化视图日志根据主键、ROWID和对象标识定位变化的数据，则这种物化视图日志同时支持主键、ROWID和对象物化视图，这种物化视图日志对于一个包含三种类型物化视图的环境是很有用的。组合类型物化视图日志和其他类型物化视图日志工作方式相同，只不过多记录了一、二种类型。
虽然主键物化视图日志和ROWID物化视图日志的差别很小，但是这些差别在实际使用中有着很大的影响。ROWID物化视图日志会在进行重组和截断时十分困难。例如，如果你对主表进行了重组和截断，那么ROWID物化视图必须执行完全刷新，这是因为ROWID发生了变化。

物化视图实践
Oracle提供以下几种不同类型的物化视图，以满足各种复制环境的需要：主键物化视图和ROWID物化视图。
主键物化视图
主键物化视图是默认的物化视图。在复制环境下，如果主键物化视图是作为物化视图组的一部分建立的，如果指定了FOR UPDATE语句，那么这个物化视图是可更新的，且这个物化视图组必须和主站点中复制组的同名。另外，可更新物化视图必须和主复制组在不同的数据库中。当修改发生后，修改的数据以行级为单位被传播，每行数据由主键确定。
主键物化视图可以包含一个子查询，因此你可以在建立物化视图时，建立所有数据的一个子集，也就是说，建立物化视图时可以只选取你需要的数据行。如果主站点中的主对象建立了物化视图日志表，那么一些包含特定类型子查询的主键物化视图仍然可以快速（增量）刷新。

SQL> create materialized view log on emp;
Materialized view log created.
SQL> create materialized view mv_emp_pk
  2  build deferred
  3  refresh fast
  4  start with sysdate
  5  next sysdate + 1/48
  6  with primary key
  7  as
  8  select * from emp;
Materialized view created.
SQL> select * from mv_emp_pk;
no rows selected

子查询物化视图
SQL>create materialized view m_test_view
as
select * from gwm_tabattribute a where exists (select * from gwm_attribute b where a.gwm_ano=b.gwm_ano);

生成数据有两大选项：
Build immediate:在创建物化视图的同时根据主表生成数据，默认选项
Bulid deferred:在创建物化视图的同时，在物化视图内不生成数据，如果此时没有生成数据，以后可以采取：EXEC DBMS_MVIEW.Refresh('MV_name','C')，注意必须使用全量刷新，默认是增量刷新，所以这里参数必须是C，因为之前都没有生成数据，所以必须全量。

刷新方式有：complete fast force
Complete：完全刷新整个物化视图，相当于重新生成物化视图，此时即时增量刷新可用也全量刷新
Fast：当有数据更新时依照相应的规则对物化视图进行更新，该选项必须在创建有物化视图日志的情况下才能使用。

Force：当增量刷新可用则增量刷新，当增量刷新不可用，则全量刷新（此项为默认选项）
不过从实际情况出发，应该尽量不使用默认选项，可以考虑使用增量刷新，对大表特别有效，大表全量更新速度是非常慢的，特别是在存在索引的情况下。在创建物化视图语句中，以下条件是所有类型的快速刷新物化视图都必须满足的条件：

1.物化视图不能包含对不重复表达式的引用，如SYSDATE和ROWNUM；

2.物化视图不能包含对LONG和LONG RAW数据类型的引用。

只包含连接的物化视图：

1.必须满足所有快速刷新物化视图都满足的条件；

2.不能包括GROUP BY语句或聚集操作；

3.如果在WHERE语句中包含外连接，那么唯一约束必须存在于连接中内表的连接列上；

4.如果不包含外连接，那么WHERE语句没有限制，如果包含外连接，那么WHERE语句中只能使用AND连接，并且只能使用“＝”操作。

5.FROM语句列表中所有表的ROWID必须出现在SELECT语句的列表中。

6.FROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志。

包含聚集的物化视图：

1.必须满足所有快速刷新物化视图都满足的条件；

2.物化视图查询的所有表必须建立物化视图日志，且物化视图日志必须满足下列限制：

(1)包含物化视图查询语句中的所有列，包括SELECT列表中的列和WHERE语句中的列；

(2)必须指明ROWID和INCLUDING NEW VALUES；

INCLUDING Specify INCLUDING to save both new and old values in the log. If this log is for a table on which you have a single-table materialized aggregate view, and if you want the materialized view to be eligible for fast refresh, then you must specify INCLUDING.
EXCLUDING Specify EXCLUDING to disable the recording of new values in the log. This is the default. You can use this clause to avoid the overhead of recording new values. Do not use this clause if you have a fast-refreshable single-table materialized aggregate view defined on the master table.

(3)如果对基表的操作同时包括INSERT、UPDATE和DELETE操作（即不是只包含INSERT操作），那么物化视图日志应该包括WITH SEQUENCE，则物化视图日子中将会包含
SEQUENCE$$，给每个操作一个SEQUENCE号，从而保证刷新时按照顺序进行刷新。
Specify SEQUENCE to indicate that a sequence value providing additional ordering information should be recorded in the materialized view log. Sequence numbers are necessary to support fast refresh after some update scenarios.

3.允许的聚集函数包括：SUM、COUNT、AVG、STDDEV、VARIANCE、MIN和MAX；

4.必须指定COUNT(*)；

5.如果指明了除COUNT之外的聚集函数，则COUNT(expr)也必须存在；

比如：包含SUM(a)，则必须同时包含COUNT(a)。

6.如果指明了VARIANCE(expr)或STDDEV(expr)，除了COUNT(expr)外，SUM(expr)也必须指明；

Oracle推荐同时包括SUM(expr*expr)。

7.SELECT列表中必须包括所有的GROUP BY列；

8.当物化视图属于下面的某种情况，则快速刷新只支持常规DML插入和直接装载，这种类型的物化视图又称为INSERT-ONLY物化视图；

物化视图包含MIN或MAX聚集函数；

物化视图包含SUM(expr)，但是没有包括COUNT(expr)；

物化视图没有包含COUNT(*)。

注意：如果建立了这种物化视图且刷新机制是ON COMMIT的，则会存在潜在的问题。当出现了UPDATE或DELETE语句，除非手工完全刷新解决这个问题，否则物化视图至此以后都不再自动刷新，且不会报任何错误。

9.如果包含inline views、outer joins、self joins或grouping set，则兼容性的设置必须在9.0以上；

10.如果物化视图建立在视图或子查询上，则要求视图必须可以完全合并的。

11.如果没有外连接，则对WHERE语句没有限制。如果包含外连接，则要求WHERE语句只能包括AND连接和“=”操作。对于包含外连接的聚集物化视图，快速刷新支持outer表的修改。且inter表的连接列上必须存在唯一约束。

12.对于包含了ROLLUP、CUBE、GROUPING SET的物化视图必须满足下列限制条件：

SELECT语句列表中应该包含GROUPING标识符：可以是GROUP BY表达式中所有列的GROUPING_ID函数，也可以是GROUP BY表达式中每一列的GROUPING函数；

例如：GROUP BY语句为：GROUP BY CUBE(a, b)，则SELECT列表应该包括GROUPING_ID(a, b)或者GROUPING(a)和GROUPING(b)。

GROUP BY不能产生重复的GROUPING。

比如：GROUP BY a, ROLLUP(a, b)则不支持快速刷新，因为包含了重复的GROUPING：(a), (a, b), (a)。

见下例

SQL>CREATE MATERIALIZED VIEW LOG ON PRODUCTS WITH
SEQUENCE,ROWID
(PROD_ID,PROD_NAME,PROD_DESC,PROD_SUBCATEGORY,
PROD_CATEGORY,PROD_CATEGORY_DESC,PROD_WEIGHT_CLASS,
PROD_UNIT_OF_MEASURE,
PROD_PACK_SIZE,SUPPLIER_ID,PROD_STATUS,PROD_LIST_PRICE,
PROD_MIN_PRICE)
including new values;
SQL>CREATE MATERIALIZEd VIEW LOG ON SALES
WITH SEQUENCE,ROWID
(PROD_ID,CUST_ID,CHANNEL_ID,PROMO_ID,QUANTITY_SOLD,
AMOUNT_SOLD)
including new values;
SQL>CREATE MATERIALIZED VIEW PRODUCT_SALES_MV
PCTFREE 0
TABLESPACE USERS
STORAGE(INITIAL 8k NEXT 8k PCTINCREASE 0)
BUILD IMMEDIATE
REFRESH FAST
enable query rewrite
AS
SELECT P.PROD_NAME,SUM(S.AMOUNT_SOLD) AS DOLLAR_SALES,
COUNT(*) AS CNT, COUNT(S.AMOUNT_SOLD) AS CNT_AMT
FROM SALES S,PRODUCTS P
WHERE S.PROD_ID = P.PROD_ID
group by p.prod_name;
SQL>CREATE MATERIALIZED VIEW SUM_SALES
PARALLEL
BUILD IMMEDIATE 
REFRESH FAST ON COMMIT AS
SELECT S.PROD_ID,S.TIME_ID,COUNT(*) AS COUNT_GRP,
SUM(S.AMOUNT_SOLD) AS SUM_DOLLAR_SALES,
COUNT(S.AMOUNT_SOLD) AS COUNT_DOLLAR_SALES,
SUM(S.QUANTITY_SOLD) AS SUM_QUANTITY_SALES,
COUNT(S.QUANTITY_SOLD) AS COUNT_QUANTITY_SALES
FROM SALES S
group by s.prod_id,s.time_id;

包含UNION ALL的物化视图：

1.UNION ALL操作必须在查询的顶层。可以有一种情况例外：UNION ALL在第二层，而第一层的查询语句为SELECT * FROM；

2.被UNION ALL操作连接在一起的每个查询块都应该满足快速刷新的限制条件；

3.SELECT列表中必须包含一列维护列，叫做UNION ALL标识符，每个UNION ALL分支的标识符列应包含不同的常量值；

4.不支持外连接、远端数据库表和包括只允许插入的聚集物化视图定义查询；

5.不支持基于分区改变跟踪（PCT）的刷新；

6.兼容性设置应设置为9.2.0。

嵌套物化视图：

嵌套物化视图的每层都必须满足快速刷新的限制条件；

对于同时包含聚集和连接的嵌套物化视图，不支持ON COMMIT的快速刷新。

(This note describes 9i Local Aggregate Materialized View Fast Refresh Restrictions. Doc ID: Note:222843.1)

刷新时间：on demand on commit start with/ next
On demand:在需要刷新时进行刷新（人工判断）
On commit:在基表上有提交操作时，进行更新
Start with:指定首次刷新的时间（一般指定的是当前时间，不过也可以在创建物化视图时不生成数据，则可以考虑在指定的时间刷新，从而生成数据）
Next:刷新的周期时间

查询重写（Query Rewrite）：包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。默认为DISABLE QUERY REWRITE。

SQL> Create materialized view MV_TEST
Refresh fast 
Enable query rewrite 
As
Select * from TEST;

ROWID物化视图
为了后向兼容性，Oracle除了默认的主键物化视图外，还支持ROWID物化视图。ROWID物化视图基于主表对象中行记录的物理标识ROWID。在复制环境，ROWID物化视图只被用在基于Oracle7版本的主对象的物化视图，它不能被用于建立基于Oracle8或更高版本主站点的物化视图。

SQL>  create materialized view mv_emp_rowid
  2    build immediate
  3    refresh force
  4    on commit
  5    with rowid
  6    as
  7*   select * from emp
Materialized view created.

删除日志： 
DROP materialized view log on emp;
删除物化视图
drop materialized view mv_emp_rowid;

基本和对表的操作一致 --物化视图由于是物理真实存在的，故可以创建索引。

物化视图的优缺点

优点：
1，物化视图的最大的优势是可以提高性能：Oracle的物化视图提供了强大的功能，可以用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。
2，物化视图有很多方面和索引很相似
3，通过预先计算好答案存储起来，可以大大地减少机器的负载
A，更少的物理读--扫描更少的数据
B，更少的写--不用经常排序和聚集
C。减少CPU的消耗--不用对数据进行聚集计算和函数调用
D，显著地加快响应时间--在使用物化视图查询数据时(与主表相反)，将会很快的返回查询结果
缺点：
1，物化视图用于只读或者“精读”环境下工作最好，不用于联机事务处理系统(OLTP)环境，在事实表等更新时会导致物化视图行锁，从而影响系统并发性。
2，物化视图有出现无法快速刷新，导致查询数据不准确的现象
3，Rowid物化视图（创建的物化视图通常情况下有主键，rowid,和子查询视图）只有一个单一的主表，不能包括下面任何一项:
A，Distinct 或者聚合函数.
B，Group by，子查询，连接和SET操作
4，物化视图会增加对磁盘资源的需求，即需要永久分配的硬盘空间给物化视图来存储数据
5，物化视图的工作原理受一些可能的约束，比如主键，外键等。

参考至:http://hi.baidu.com/csu_syh329/item/314953ddcb9dc11dd78ed03d

http://blog.csdn.net/suncrafted/article/details/4300358

http://www.blogjava.net/wxqxs/archive/2008/09/03/226694.html

http://blog.sina.com.cn/s/blog_5b2470430100ek3o.html

http://www.cnblogs.com/lanzi/archive/2010/11/16/1878344.html

http://www.2cto.com/database/201203/124905.html

http://blog.csdn.net/zhone/article/details/3285121
http://xsb.itpub.net/post/419/54722

本文原创，转载请注明出处、作者

如有错误，欢迎指正
邮箱:czmcj@163.com

刷新页面返回顶部

Alfred Zhao 从Oracle起航，领略精彩的IT技术。

【转载】物化视图详解

About