oracle 多表关联物化视图,复杂关联查询物化视图

1、现在子表建立聚合物化视图

CREATE MATERIALIZED VIEW LOG on test_b WITH rowid ,SEQUENCE (status,p_id) INCLUDING NEW VALUES;

create materialized view mv_test_b

BUILD immediate

refresh fast on demand

start with sysdate next sysdate+1/1440 with rowid

as

select a.p_id,COUNT(*) from test_b a where a.status=1 group by a.p_id;

2、在子表聚合物化视图上建立日志

CREATE MATERIALIZED VIEW LOG on mv_test_b WITH rowid ,SEQUENCE (p_id) INCLUDING NEW VALUES;

3、在主表建立关联物化视图和日志

CREATE MATERIALIZED VIEW LOG on test_a WITH rowid ,SEQUENCE (code) INCLUDING NEW VALUES;

create materialized view mv_test_code

BUILD immediate

refresh fast on demand

start with sysdate next sysdate+1/1440 with rowid

as

select  b.code,a.rowid aid,b.rowid bid from mv_test_b a, test_a b where a.p_id=b.id;

posted on 2014-07-17 11:21 紫色心情 阅读(207) 评论(0)  编辑  收藏 所属分类: Oracle