实现目标：查询子表TEST_B的STATUS为1的记录所关联的父表的CODE
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;