关于物化视图的详细阐述

http://blog.csdn.net/rickmeteor/archive/2009/07/06/4323513.aspx

以下是网上关于物化视图的详细阐述： 　　 Oracle8i版本开始提供可以创建实体化视图即物化视图(MATERIALIZED VIEW)，它确实存放有物理数据。物化视图包含定义视图的查询时所选择的基表中的行。对物化视图的查询就是直接从该视图中取出行。 　　在olap环境中，mview是以空间换时间的一种有效手段，更少的物理读/写，更少的cpu时间，更快的响应速度，所以它不适合高端的oltp环境；在oltp环境中，规模较大的报表适合使用mview来提高查询性能。 　　数据仓库中的物化视图主要用于预先计算并保存表连接或聚集等耗时较多的操作的结果，这样，在执行查询时，就可以避免进行这些耗时的操作，而从快速的得到结果。在数据仓库中，还经常使用查询重写（query rewrite）机制，这样不需要修改原有的查询语句，Oracle会自动选择合适的物化视图进行查询，完全对应用透明。 　　物化视图和表一样可以直接进行查询。物化视图可以基于分区表，物化视图本身也可以分区。 　　除了在数据仓库中使用，物化视图还用于复制、移动计算等方面。 　　物化视图有很多方面和索引很相似：使用物化视图的目的是为了提高查询性能；物化视图对应用透明，增加和删除物化视图不会影响应用程序中SQL语句的正确性和有效性；物化视图需要占用存储空间；当基表发生变化时，物化视图也应当刷新。 　　物化视图可以分为以下三种类型：包含聚集的物化视图；只包含连接的物化视图；嵌套物化视图。三种物化视图的快速刷新的限制条件有很大区别，而对于其他方面则区别不大。 　　创建物化视图时可以指定多种选项，下面对几种主要的选择进行简单说明： 　　创建方式（Build Methods）：包括BUILD IMMEDIATE和BUILD DEFERRED两种。BUILD IMMEDIATE是在创建物化视图的时候就生成数据，而BUILD DEFERRED则在创建时不生成数据，以后根据需要在生成数据。默认为BUILD IMMEDIATE。 　　查询重写（Query Rewrite）：包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。分别指出创建的物化视图是否支持查询重写。查询重写是指当对物化视图的基表进行查询时，Oracle会自动判断能否通过查询物化视图来得到结果，如果可以，则避免了聚集或连接操作，而直接从已经计算好的物化视图中读取数据。默认为DISABLE QUERY REWRITE。 　　刷新（Refresh）：指当基表发生了DML操作后，物化视图何时采用哪种方式和基表进行同步。刷新的模式有两种：ON DEMAND和ON COMMIT。ON DEMAND指物化视图在用户需要的时候进行刷新，可以手工通过DBMS_MVIEW.REFRESH等方法来进行刷新，也可以通过JOB定时进行刷新。ON COMMIT指出物化视图在对基表的DML操作提交的同时进行刷新。刷新的方法有四种：FAST、COMPLETE、FORCE和NEVER。FAST刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE刷新对整个物化视图进行完全的刷新。如果选择FORCE方式，则Oracle在刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。NEVER指物化视图不进行任何刷新。默认值是FORCE ON DEMAND。 　　显然快速刷新是物化视图刷新方式的首选。但是，不是所有的物化视图都可以进行快速刷新，只有满足某些条件的物化视图才具有快速刷新的能力。 　　在建立物化视图的时候可以指定ORDER BY语句，使生成的数据按照一定的顺序进行保存。不过这个语句不会写入物化视图的定义中，而且对以后的刷新也无效。 　　物化视图日志：如果需要进行快速刷新，则需要建立物化视图日志。物化视图日志根据不同物化视图的快速刷新的需要，可以建立为ROWID或PRIMARY KEY类型的。还可以选择是否包括SEQUENCE、INCLUDING NEW VALUES以及指定列的列表。 　　可以指明ON PREBUILT TABLE语句将物化视图建立在一个已经存在的表上。这种情况下，物化视图和表必须同名。当删除物化视图时，不会删除同名的表。这种物化视图的查询重写要求参数QUERY_REWRITE_INTEGERITY必须设置为trusted或者stale_tolerated。 　　物化视图可以进行分区。而且基于分区的物化视图可以支持分区变化跟踪（PCT）。具有这种特性的物化视图，当基表进行了分区维护操作后，仍然可以进行快速刷新操作。 　　对于聚集物化视图，可以在GROUP BY列表中使用CUBE或ROLLUP，来建立不同等级的聚集物化视图。 　　根据查询的不同，快速刷新的限制条件也不相同，下面总结一下不同类型的物化视图对快速刷新的限制条件。 　　可以参考： http://xsb.itpub.net/post/419/54722 　　所有类型的快速刷新物化视图都必须满足的条件： 　　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； 　　(3)如果对基本的操作同时包括INSERT、UPDATE和DELETE操作（即不是只包含INSERT操作），那么物化视图日志应该包括SEQUENCE。 　　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)。 　　包含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的快速刷新。 　　1.1.1 创建物化视图 　　1.关键内容： 　　l 实体视图存放有物理数据； 　　l 实体视图背后的查询只在视图建立或刷新时执行，即如果创建后不进行刷新则只得到创建时的数据； 　　l 实体视图使用DBMS_MVIEW 程序包中含有刷新和管理实体视图的过程来进行管理； 　　l 在导出和导入（EXP、IMP）中使用MVDATA参数来实现实体视图数据的导出和导入； 　　l 使用CREATE MATERIALIZED VIEW 语句创建实体视图； 　　l 实体视图中的查询表叫主表（master tables）（复制项）或详细表（数据仓库项）。为一致起见，这些主表叫主数据库（master databases.）； 　　l 为了复制目的，实体视图允许你在本地管理远程拷贝； 　　l 所复制的数据可以使用高级复制特性进行更新； 　　l 在复制环境下，通常创建的实体视图都是主键、ROWID和子查询实体视图。 　　2.创建物化视图前提： 　　l 要有授权创建实体视图的权限（CREATE MATERIALIZED VIEW 或CREATE SNAPSHOT）； 　　l 必须有访问各个主表的权限，即 有SELECT ANY TABLE 的系统权限。 　　如果在另外的用户模式下创建实体视图，则： 　　l 需要有CREATE ANY MATERIALIZED VIEW或CREATE ANY SNAPSHOT、SELECT ANY TABLE 权限； 　　l 必须有CREATE TABLE、SELECT ANY TABLE系统权限。 　　如果带查询重写有效来创建实体视图，则： 　　l 主表的主人必须有QUERY REWRITE系统权限； 　　l 如果你不是主表主人，则必须有GLOBAL QUERY REWRITE系统权限； 　　l 如果模式主人没有主表，则该模式主人必须有GLOBAL QUERY REWRITE权限。 　　3.创建实体视图语法： 　　下面给出实体视图的简单语法： 　　CREATE MATERIALIZED VIEW [ SNAPSHOT ] [schema.] [materializede_view|snapshot] 　　[ [ [ segment_attributes_clause|LOB_storage_clause|CACHE|NOCACHE ] | 　　[ CLUSTER cluster( column1,…) ] ] patitioning_clauses parallel_clause build_clause ] | 　　[ ON PREBUILT TABLE [WITH | WITHOUT] REDUCED PRECISION ] 　　USING INDEX [physical_attributes_clause| TABLESPACE tablespace ] refresh_clause 　　FOR UPDATE [ DISABLE | ENABLE ] QUERY REWRITE AS subquery; 　　参数的解释另见《Oracle8I数据库基础》。 　　4.创建实体例子： 　　例1.创建实体汇总视图: 　　下面语句建立一个移植的实体视图，并指定缺省的刷新方法、模式及时间： 　　CREATE MATERIALIZED VIEW mv1 REFRESH FAST ON COMMIT 　　BUILD IMMEDIATE 　　AS SELECT t.month, p.prod_name, SUM(f.sales) AS sum_sales 　　FROM time t, product p, fact f 　　WHERE f.curDate = t.curDate AND f.item = p.item 　　GROUP BY t.month, p.prod_name; 　　例2.自动刷新的实体视图: 　　下面语句创建一个复杂的实体视图all_emps,它查询DALLAS和BALTIMORE中的职工表: 　　CREATE MATERIALIZED VIEW all_emps 　　PCTFREE 5 PCTUSED 60 　　TABLESPACE users 　　STORAGE INITIAL 50K NEXT 50K 　　USING INDEX STORAGE (INITIAL 25K NEXT 25K) 　　REFRESH START WITH ROUND(SYSDATE + 1) + 11/24 　　NEXT NEXT_DAY(TRUNC(SYSDATE, ’MONDAY’) + 15/24 　　AS SELECT * FROM fran.emp@dallas 　　UNION 　　SELECT * FROM marco.emp@balt; 　　Oracle在早上11点自动刷新，接着就在周一的15点进行刷新。缺省刷新方法是FORCE，all_emps视图包含一个UNION，它是不支持快速刷新的，所以Oracle只能用完全（complete）刷新。 　　1.1.2 运行实体视图的条件 　　由于运行实体视图实际就是数据库复制，为了使系统能进行复制，需要启动后台进程SNP0,…SNP9和SNPA... SNPZ。要启动后台进程，就要在INIT.ORA参数文件中加上下面参数： 　　JOB_QUEUE_PROCESSES= integer ( integer > = 1 ) 　　1.1.3 与实体视图有关的数据字典 　　实体视图是新的对象，它的信息被存放在下面的数据字典中： 　　1.DBA_MVIEW_AGGREGATES存放实体视图的基本信息 　　SQL> desc dba_mview_aggregates 　　名称 空? 类型 　　----------------------------------------- -------- ------------- 　　OWNER NOT NULL VARCHAR2(30) 　　MVIEW_NAME NOT NULL VARCHAR2(30) 　　POSITION_IN_SELECT NOT NULL NUMBER 　　CONTAINER_COLUMN NOT NULL VARCHAR2(30) 　　AGG_FUNCTION VARCHAR2(8) 　　DISTINCTFLAG VARCHAR2(1) 　　MEASURE LONG 　　SQL> 　　2.DBA_MVIEW_ANALYSIS 存放实体视图的附加信息 　　（结构比较长，略去） 　　3.DBA_MVIEW_DETAIL_RELATIONS 存放实体视图的子查询等信息 　　SQL> desc dba_mview_detail_relations 　　名称 空? 类型 　　----------------------------------------- -------- ------------- 　　OWNER NOT NULL VARCHAR2(30) 　　MVIEW_NAME NOT NULL VARCHAR2(30) 　　DETAILOBJ_OWNER NOT NULL VARCHAR2(30) 　　DETAILOBJ_NAME NOT NULL VARCHAR2(30) 　　DETAILOBJ_TYPE VARCHAR2(9) 　　DETAILOBJ_ALIAS VARCHAR2(30) 　　SQL> 　　4.DBA_MVIEW_DETAIL_JOINS 存放实体视图的列的连接关系信息 　　5.DBA_MVIEW_DETAIL_KEYS 存放实体视图的列或表达式的信息 　　6.DBA_MVIEWS 存放实体视图的基本信息