Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 6 down vote accepted

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:

share|improve this answer
    
All columns within the MV are available for filtering (used in a WHERE clause if I'm understanding correctly). I think this is where I was confused. Thank you for the explanation. –  Josh Smeaton Jun 1 '11 at 22:37
    
@Josh If you are referring to columns from the tables referenced in the MV query you are correct. This means that the query could filter on a column that is not in the select portion and therefore not in the materialized table. Thanks for a great question. –  Leigh Riffel Jun 2 '11 at 2:00
    
would that be for query re-write reasons? Only columns in the MV are being queried, so if I'm understanding correctly, there is no need for me to specify filter columns, since they exist in the MV already and are indexed appropriately there? –  Josh Smeaton Jun 2 '11 at 2:17
    
@Josh You are correct. –  Leigh Riffel Jun 2 '11 at 11:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.