物化视图日志问题

Question

我有一个基于两个或多个其他物化视图的物化视图。我想为物化视图安排快速刷新，但问题是它没有任何日志，所以我必须先创建日志。对于物化视图，我是新手，所以我不知道如何为两个底层物化视图创建日志。是否为这些视图使用的每个基础表创建日志？

Answer 1

假设您希望所有内容都可以快速刷新，则需要MV日志：

• 基表
• MVs作为“最终”MV

的基础

在MV上创建MV日志的方式与常规表相同：

create table t1 (
  c1 int primary key, c2 int
);
create table t2 (
  c1 int, c2 int, primary key ( c1, c2 )
);

create materialized view log on t1
  with rowid, primary key ( c2 )
  including new values;

create materialized view log on t2
  with rowid, primary key
  including new values;

create materialized view mv1
refresh fast on commit as
  select * from t1;

create materialized view mv2 
refresh fast on commit as
  select * from t2;

create materialized view log on mv1
  with rowid ( c1, c2 )
  including new values;
create materialized view log on mv2
  with rowid ( c1, c2 )
  including new values;

create materialized view mv3 
refresh fast on commit as
  select mv1.c1, count (*) 
  from   mv1 
  join   mv2
  on     mv1.c1 = mv2.c1
  group  by mv1.c1;

insert into t1 values ( 1, 1 );
insert into t1 values ( 2, 2 );

insert into t2 values ( 1, 1 );
insert into t2 values ( 1, 2 );
insert into t2 values ( 2, 2 );

commit;

select * from mv3;

        C1   COUNT(*)
---------- ----------
         1          2
         2          1