Logging Columns in the Materialized View Log

When you create a materialized view log, you can add columns to the log when necessary. To perform a fast refresh on a materialized view, the following types of columns must be added to the materialized view log:

• A column referenced in the WHERE clause of a subquery that is not part of an equi-join and is not a primary key column. These columns are called filter columns.
  
• A column in an equi-join that is not a primary key column, if the subquery is either many to many or one to many. If the subquery is many to one, then you do not need to add the join column to the materialized view log.
  

A collection column cannot be added to a materialized view log. Also, materialized view logs are not required for materialized views that use complete refresh.
For example, consider the following DDL:

1) CREATE MATERIALIZED VIEW oe.customers REFRESH FAST AS
2)  SELECT * FROM oe.customers@orc1.example.com c
3)  WHERE EXISTS
4)    (SELECT * FROM oe.orders@orc1.example.com o
5)     WHERE c.customer_id = o.customer_id AND o.order_total > 20000);

Notice in line 5 of the preceding DDL that three columns are referenced in the WHERE clause. Columns orders.customer_id and customers.customer_id are referenced as part of the equi-join clause. Because customers.customer_id is a primary key column, it is logged by default, but orders.customer_id is not a primary key column and so must be added to the materialized view log. Also, the column orders.order_total is an additional filter column and so must be logged.
Therefore, add orders.customer_id and orders.order_total the materialized view log for the oe.orders table.

To create the materialized view log with these columns added, issue the following statement:

CREATE MATERIALIZED VIEW LOG ON oe.orders 
  WITH PRIMARY KEY (customer_id,order_total);

If a materialized view log already exists on the oe.customers table, you can add these columns by issuing the following statement:

ALTER MATERIALIZED VIEW LOG ON oe.orders ADD (customer_id,order_total);

If you are using user-defined data types, then the attributes of column objects can be logged in the materialized view log. For example, the oe.customers table has the cust_address.postal_code attribute, which can be logged in the materialized view log by issuing the following statement:

ALTER MATERIALIZED VIEW LOG ON oe.customers ADD (cust_address.postal_code);

You are encouraged to analyze the defining queries of your planned materialized views and identify which columns must be added to your materialized view logs. If you try to create or refresh a materialized view that requires an added column without adding the column to the materialized view log, then your materialized view creation or refresh might fail.

Note:

To perform a fast refresh on a materialized view, you must add join columns in subqueries to the materialized view log if the join column is not a primary key and the subquery is either many to many or one to many. If the subquery is many to one, then you do not need to add the join column to the materialized view log.