實現目標：查詢子表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;