oracle刷新物化视图语句,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;

--必须有中的所有列

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