时间戳

首页 > 解决方案 > 物化视图日志问题

oracle - 物化视图日志问题

问题描述

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

标签： oraclematerialized-views

解决方案

假设您希望所有内容都可以快速刷新，您需要在以下位置进行 MV 日志：

  • 基表
  • “最终”MV背后的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

推荐阅读