ORACLE物化视图--物化视图的快速刷新

带UNION ALL物化视图的快速刷新

1.   定义的查询的union all操作必须在最顶等级（查询重写后）

2.   物化视图日志指定rowid和including new values

3.   如果表设涉及到一个大量的insert或者直接路径加载，deletes,update，指定sequencea

4.   from列表中所有表的rowid必须出现在select子句中

5.   select列中必须包括一个维护列，被称为union all marker

6.   远端数据库的表不支持union all

7.   不支持聚集操作，因为没有rowid

 

CREATETABLEtest1ASSELECT*FROMdba_objects;

CREATETABLEtest2ASSELECT*FROMdba_objects;

CREATETABLEtest3ASSELECT*FROMdba_objects;

 

CREATEMATERIALIZEDVIEWLOGONCTAIS2.TEST1WITHROWID,SEQUENCEINCLUDINGNEWVALUES;

CREATEMATERIALIZEDVIEWLOGONCTAIS2.TEST2WITHROWID,SEQUENCEINCLUDINGNEWVALUES;

CREATEMATERIALIZEDVIEWLOGONCTAIS2.TEST3WITHROWID,SEQUENCEINCLUDINGNEWVALUES;

 

CREATEMATERIALIZEDVIEWmv_test_union_all

REFRESHFASTONDEMANDWITHROWID

AS

SELECTowner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time,TIMESTAMP, status,TEMPORARY,GENERATED, secondary,1marker,ROWIDrdFROMtest1

UNIONALL

SELECTowner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time,TIMESTAMP, status,TEMPORARY,GENERATED, secondary,2marker,ROWIDrdFROMtest2

UNIONALL

SELECTowner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time,TIMESTAMP, status,TEMPORARY,GENERATED, secondary,3marker,ROWIDrdFROMtest3

 

带聚集物化视图的快速刷新

1.   物化视图日志指定rowid和including new values

2.   如果表设涉及到一个大量的insert或者直接路径加载，deletes,update，指定sequence

3.   支持的函数sum,count,avg,stddev,variance,min,max

4.   必须指定count(*)

5.   如果使用stddev,variance，那么count和sum必须指定。ORACLE推荐同时指定sum(expr*expr)

6.   select中的列不能有来自多个表的复杂的表达式。代替方法是使用嵌套物化视图

7.   select列必须包含所有的group的列

8.   如果包含min,max,没有count，那么将只支持insert和直接路径加载

9.   如果物化视图有cube,rollup，grouping sets，那么有如下限制

a)   select列中必须包含grouping_id

b)   不支持复杂的grouping，如group by a,rollup(a,b)

 

DROPMATERIALIZEDVIEWLOGONCTAIS2.TEST1;

DROPMATERIALIZEDVIEWLOGONCTAIS2.TEST2;

DROPMATERIALIZEDVIEWLOGONCTAIS2.TEST3;

 

--必须有SQL中的所有列

CREATEMATERIALIZEDVIEWLOGONCTAIS2.TEST1WITHSEQUENCE,ROWID

      (object_type,object_id,data_object_id) 

INCLUDINGNEWVALUES;

 

CREATEMATERIALIZEDVIEWmv_test_aggr

REFRESHFASTONDEMANDWITHROWID

ENABLEQUERYRewrite

AS

SELECTobject_type,max(object_id) max_object_id,min(data_object_id) min_data_obj_id,COUNT(*) count_cFROMtest1GROUPBYobject_type

 

BEGIN

 dbms_mview.refresh('MV_TEST_AGGR','F');

END;

/

 

DROPMATERIALIZEDVIEWmv_test_aggr;

 

多表连接的物化视图的快速刷新

1.   from列表中所有表的rowid必须出现在select子句中

2.   基表必须有mview log

 

CREATEMATERIALIZEDVIEWLOGONCTAIS2.TEST2WITHROWID;

CREATEMATERIALIZEDVIEWLOGONCTAIS2.TEST3WITHROWID;

 

CREATEMATERIALIZEDVIEWmv_test_join

REFRESHFASTONDEMANDWITHROWID

ENABLEQUERYRewrite

AS

SELECTtest2.object_name,test3.status ,test2.rowid t2rowid,test3.rowid t3rowidFROMtest2,test3WHEREtest2.object_id = test3.object_id

 

聚集+连接的物化视图的快速刷新

1.   如果物化视图不带where子句，那么max,min将支持DML的快速刷新

2.   如果查询的FROM中有一个视图或者子查询，那么能快速刷新的条件是该视图或者查询能被merge

3.   如果没有外连接，那么在where中可以随意的选择和连接

4.   如果有外连接，那么只有在外表被修改才行。但是，唯一约束必须在内表的连接列上存在。而且，连接的条件必须是=而且必须是and连接的

 

DROPMATERIALIZEDVIEWLOGONCTAIS2.TEST2;

DROPMATERIALIZEDVIEWLOGONCTAIS2.TEST3;

 

CREATEMATERIALIZEDVIEWLOGONCTAIS2.TEST2WITHSEQUENCE,ROWID(object_name,object_id)INCLUDINGNEWVALUES;

CREATEMATERIALIZEDVIEWLOGONCTAIS2.TEST3WITHSEQUENCE,ROWID(status,object_id)INCLUDINGNEWVALUES;

 

CREATEMATERIALIZEDVIEWmv_test_join

REFRESHFASTONDEMANDWITHROWID

ENABLEQUERYRewrite

AS

SELECTTEST2.OBJECT_NAME, TEST3.STATUS,COUNT(*)

      FROMTEST2, TEST3

 WHERETEST2.OBJECT_ID = TEST3.OBJECT_ID

 GROUPBYTEST2.OBJECT_NAME, TEST3.STATUS