ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 物化视图的快速刷新

物化视图的快速刷新

原创 Linux操作系统 作者:westzq1984 时间:2009-07-18 23:46:02 0 删除 编辑

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,那么countsum必须指定。ORACLE推荐同时指定sum(expr*expr)

6.    select中的列不能有来自多个表的复杂的表达式。代替方法是使用嵌套物化视图

7.    select列必须包含所有的group的列

8.    如果包含min,max,没有count,那么将只支持insert和直接路径加载

9.    如果物化视图有cube,rollupgrouping 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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8242091/viewspace-609602/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-04-06

  • 博文量
    251
  • 访问量
    948515