CODEWITHSUNDEEP
oracle-databasematerialized-views
Coding Duchess
Coding Duchess

Reputation: 6909

Materialized view logs questions

I have a materialized view based on two or more other materialized views. I want to schedule fast refresh for the materialized view but the issue is that it does not have any logs, so I must create the logs first. I am new to materialized views so I am not sure how to go about creating the logs for the two underlying materialized views. Do I create a log for each underlying table that is utilized by those views?

Upvotes: 1

Views: 2548

Answers (1)

Chris Saxon
Chris Saxon

Reputation: 9825

Assuming you want everything to be fast refreshable, you need MV logs on the:

  • base tables
  • MVs underlying the "final" MV

You create MV logs on an MV in the same way as regular tables:

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

Upvotes: 3

Related Questions