Materialized View Logs

Materialized view logs are required if you want to use fast refresh, with the exception of PCT refresh. That is, if a detail table supports PCT for a materialized view, the materialized view log on that detail table is not required in order to do fast refresh on that materialized view. As a general rule, though, you should create materialized view logs if you want to use fast refresh. Materialized view logs are defined using a CREATE MATERIALIZED VIEW LOG statement on the base table that is to be changed.

They are not created on the materialized view unless there is another materialized view on top of that materialized view, which is the case with nested materialized views. For fast refresh of materialized views, the definition of the materialized view logs must normally specify the ROWID clause. In addition, for aggregate materialized views, it must also contain every column in the table referenced in the materialized view, the INCLUDING NEW VALUES clause and the SEQUENCE clause.

An example of a materialized view log is shown as follows where one is created on the
table sales:
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID
(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)
INCLUDING NEW VALUES;
Alternatively, a materialized view log can be amended to include the rowid, as in the
following:
ALTER MATERIALIZED VIEW LOG ON sales ADD ROWID
(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)
INCLUDING NEW VALUES;
Oracle recommends that the keyword SEQUENCE be included in your materialized
view log statement unless you are sure that you will never perform a mixed DML
operation (a combination of INSERT, UPDATE, or DELETE operations on multiple
tables). The SEQUENCE column is required in the materialized view log to support fast
refresh with a combination of INSERT, UPDATE, or DELETE statements on multiple
tables. You can, however, add the SEQUENCE number to the materialized view log
after it has been created.
The boundary of a mixed DML operation is determined by whether the materialized
view is ON COMMIT or ON DEMAND.
■ For ON COMMIT, the mixed DML statements occur within the same transaction
because the refresh of the materialized view will occur upon commit of this
transaction.
■ For ON DEMAND, the mixed DML statements occur between refreshes. The
following example of a materialized view log illustrates where one is created on
the table sales that includes the SEQUENCE keyword:
CREATE MATERIALIZED VIEW LOG ON sales WITH SEQUENCE, ROWID
(prod_id, cust_id, time_id, channel_id, promo_id,
quantity_sold, amount_sold) INCLUDING NEW VALUES;

Posted in: Materialized Views