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中的列不能有来自多个表的复杂的