Oracle More than one materialized view on a materialized log

2
votes

I would like to have more than one materialized view with refresh fast on commit.

For "refresh fast on commit" you need a materialized view log. Obviously a refresh fast on commit needs the log. The question is can I have more than one materialized view accessing the log.

Obviously I still need the log to satisfy the normal prerequisites (across all views): http://docs.oracle.com/cd/B19306_01/server.102/b14223/basicmv.htm

Cheers for any help in advance.

2
oracleoracle11g

2 Answers

2
votes

Yes. A materialized view log can support as many materialized views as you'd like.

Supporting multiple materialized views may cause the log to be larger than it would be if it was supporting a single materialized view though that probably isn't terribly significant here since your logs probably won't store the data long. If you're replicating data to remote databases (which by definition you can't do with a fast refreshable materialized view), it can be a bit tricky if one of the remote materialized views stops refreshing or goes away without the source being aware since it can cause data to queue up indefinitely in the log. But, again, that doesn't sound like the situation you're facing.

3
votes

Absolutely.

create table data_table as (
  select * 
  from dba_users
);

alter table data_table
add constraint data_table_test_pk PRIMARY KEY (user_id);

select * from data_table;

So now we have a table that looks like dba_users with a PRIMARY KEY constraint.

create materialized view log on data_table;

create materialized view mat_view_one
refresh fast on commit
as
select username, user_id
from data_table
;

create materialized view mat_view_two
refresh fast on commit
as
select user_id, username, account_status
from data_table;

There's our log and the 2 views create successfully. SYS record in the test table:

select * from mat_view_one
where user_id=0;

USERNAME                          USER_ID
------------------------------ ----------
SYS                                     0

select * from mat_view_two
where user_id=0;

   USER_ID USERNAME                       ACCOUNT_STATUS                 
---------- ------------------------------ --------------------------------
         0 SYS                            OPEN

Now lets update the SYS's name and commit and see what our views show:

update data_table
set username='WALTERWHITE'
WHERE USER_ID=0
;
COMMIT;
USERNAME                          USER_ID
------------------------------ ----------
WALTERWHITE                             0 

   USER_ID USERNAME                       ACCOUNT_STATUS                 
---------- ------------------------------ --------------------------------
         0 WALTERWHITE                    OPEN

So yes, absolutely. 1 materialized view log can serve as many materialized views as you need so long as the proper constraints are held.