带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中的列不能有来自多个表的复杂的
select * from test where rownum <=10 下面是关于rownum的介绍 ================================ Rownum和row_number() over()的使用 ROWNUM是Oracle从8开始提供的一个伪列，是把SQL出来的结果进行编号，始终从1开始，常见的用途就是用来分页输出. 比如 SELECT * from torderdetail a WHERE ROWNUM <= 10 这条语句� ......
