Do I need to enumerate the columns used in a materialized view when creating the MV log

data-warehouseoracleview

I'm currently creating materialized view logs on a whole bunch of tables in order to support materialized views with FAST REFRESH. The materialized views we're creating contain approx 30 columns from various tables. One of our fact tables contains about 15 columns that will be used in a materialized view.

Is it necessary to enumerate all of the columns in the base table that will be required in the MV when creating the MV Log?

CREATE MATERIALIZED VIEW LOG ON SCHEMA.TABLE_A 
  WITH ROWID, PRIMARY KEY, SEQUENCE (COL1, COL2, COL3, ..., COL48)
  INCLUDING NEW VALUES;

The above is an example of how I'm currently creating the MV Log. I'll admit that the above is a result of trial and error, without a thorough understanding of how each component in the statement works (I understand most).

Under what circumstances is it necessary for me to define which columns should be included in the MV log?

Some materialized views will just be a join across multiple tables. Others will include aggregations, groupings, sums, etc.

Best Answer

No, you do not need to enumerate the columns used in a materialized view when creating the materialized view log. In fact you cannot create a materialized view log using the primary key method and include all the columns because you would be including the primary key column itself, which is not allowed.

The concept of a materialized view log is to store the rowid or primary key of the data that changed. The refresh can then look up the entire record from the table. Adding specific columns to the WITH clause of the log explicitly records the data in the materialized view log itself. If your materialized view query is filtering on these columns or joining on them it could speed up the refresh.

Theoretically if all the columns used in the MV are in the log, then it should be able to refresh without referencing the table. The documentation does not indicate that this is being done. It would be interesting to trace this to see. Even if it does, the additional storage requirements may not make this route worth the trouble.

You should probably just create the materialized view logs without specifying a column list, like this:

CREATE MATERIALIZED VIEW LOG ON SCHEMA.TABLE_A 
   WITH ROWID, PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES;

More Information:

Related Solutions

Oracle 11gR2 – Limits on Materialised View replication between databases

This sounds like a job for Change Data Capture (CDC), which allows you to (among other possibilities) ship your archivelogs from the OLTP database to the reporting one, mine them for the changes, then query the changes out, ignoring any you don't want (e.g., changes of type 'D' for DELETE), and using whatever process you might devise apply those changes to your reporting tables.

I have no idea how well CDC would do with a ruleset encompassing 4700 source tables from another database. I've never used it for more than about 50 tables myself.

FYI, there are licensing-related limits on CDC. The full feature set is only available on Enterprise Edition.

Way to alter a table definition (add columns) in Oracle and have it replicate to materialized views on remote databases

As long as you don't add data between the last refresh before the column add and after the column add, you should be fine. If you must allow for this then the easiest thing would be to do a complete refresh at the end of your plan. You shouldn't need to re-create the materialized view log.

Setup

--Create master table.
drop table t1;
create table t1 (id number(10) primary key, datetime date);
insert into t1 values (1, sysdate-1);
create materialized view log on t1 with primary key;

--Create materialized view table.
drop materialized view t2;
drop table t2;
create table t2 as (select * from t1);

--Create materialized view.
create materialized view t2 on prebuilt table with reduced precision
refresh fast on demand
as select id, datetime from t1;

--Show an empty materialized view.
select * from t2;

--Add data to t1.
insert into t1 values (2, sysdate);
commit;

--Refresh
execute dbms_snapshot.refresh('T2','f');

--Show changed data.
select * from t2;

Planned Column Add

--Add Column.
alter table t1 add n varchar2(1);
alter table t2 add n varchar2(1);

--Re-create the materialized view.
drop materialized view t2 preserve table;
--***T1 updates between last refresh and the following statement will be lost.
create materialized view t2 on prebuilt table with reduced precision 
   refresh fast on demand
   as select id, datetime, n from t1;

--Add data.
insert into t1 values (3,sysdate+1,'a');
commit;

--Refresh.
execute dbms_snapshot.refresh('T2','f');

--Show changed data.
select * from t1;
select * from t2;

Best Answer

Related Solutions

Oracle 11gR2 – Limits on Materialised View replication between databases

Way to alter a table definition (add columns) in Oracle and have it replicate to materialized views on remote databases

Related Question