带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

CREATE TABLE test1 AS SELECT * FROM dba_objects;

CREATE TABLE test2 AS SELECT * FROM dba_objects;

CREATE TABLE test3 AS SELECT * FROM dba_objects;

CREATE MATERIALIZED VIEW LOG ON CTAIS2.TEST1 WITH ROWID, SEQUENCE INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON CTAIS2.TEST2 WITH ROWID, SEQUENCE INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON CTAIS2.TEST3 WITH ROWID, SEQUENCE INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_test_union_all

REFRESH FAST ON DEMAND WITH ROWID

AS

SELECT owner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time, TIMESTAMP, status, TEMPORARY, GENERATED, secondary,1 marker,ROWID rd FROM test1

UNION ALL

SELECT owner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time, TIMESTAMP, status, TEMPORARY, GENERATED, secondary,2 marker,ROWID rd FROM test2

UNION ALL

SELECT owner, object_name, subobject_name, object_id, data_object_id, object_type, created, last_ddl_time, TIMESTAMP, status, TEMPORARY, GENERATED, secondary,3 marker,ROWID rd FROM test3

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

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)

DROP MATERIALIZED VIEW LOG ON CTAIS2.TEST1;

DROP MATERIALIZED VIEW LOG ON CTAIS2.TEST2;

DROP MATERIALIZED VIEW LOG ON CTAIS2.TEST3;

--必须有SQL中的所有列

CREATE MATERIALIZED VIEW LOG ON CTAIS2.TEST1 WITH SEQUENCE,ROWID

       (object_type,object_id,data_object_id) 

INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_test_aggr

REFRESH FAST ON DEMAND WITH ROWID

ENABLE QUERY Rewrite

AS

SELECT object_type, max(object_id) max_object_id, min(data_object_id) min_data_obj_id,COUNT(*) count_c FROM test1 GROUP BY object_type

BEGIN

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

END;

/

DROP MATERIALIZED VIEW mv_test_aggr;

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

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

2.    基表必须有mview log

CREATE MATERIALIZED VIEW LOG ON CTAIS2.TEST2 WITH ROWID;

CREATE MATERIALIZED VIEW LOG ON CTAIS2.TEST3 WITH ROWID;

CREATE MATERIALIZED VIEW mv_test_join

REFRESH FAST ON DEMAND WITH ROWID

ENABLE QUERY Rewrite

AS

SELECT test2.object_name,test3.status ,test2.rowid t2rowid,test3.rowid t3rowid FROM test2,test3 WHERE test2.object_id = test3.object_id

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

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

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

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

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

DROP MATERIALIZED VIEW LOG ON CTAIS2.TEST2;

DROP MATERIALIZED VIEW LOG ON CTAIS2.TEST3;

CREATE MATERIALIZED VIEW LOG ON CTAIS2.TEST2 WITH SEQUENCE,ROWID(object_name,object_id) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON CTAIS2.TEST3 WITH SEQUENCE,ROWID(status,object_id) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_test_join

REFRESH FAST ON DEMAND WITH ROWID

ENABLE QUERY Rewrite

AS

SELECT TEST2.OBJECT_NAME, TEST3.STATUS, COUNT(*)

       FROM TEST2, TEST3

 WHERE TEST2.OBJECT_ID = TEST3.OBJECT_ID

 GROUP BY TEST2.OBJECT_NAME, TEST3.STATUS