带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