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

Question

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.

Answer 1

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:

• SQL Snippets
• Oracle® Database SQL Language Reference
• Oracle® Database Data Warehousing Guide