Database Reference
In-Depth Information
REFRESH_FAST_AFTER_ONETAB_DML N see the reason why REFRESH_FAST
_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ONETAB_DML N COUNT(*) is not present in the
select list
REFRESH_FAST_AFTER_ONETAB_DML N SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML N mv log does not have sequence # SH.PRODUCTS
REFRESH_FAST_AFTER_ANY_DML N mv log does not have sequence # SH.CUSTOMERS
REFRESH_FAST_AFTER_ANY_DML N mv log does not have sequence # SH.SALES
REFRESH_FAST_AFTER_ANY_DML N see the reason why REFRESH_FAST
_AFTER_ONETAB_DML is disabled
Some of the problems are related to the materialized view logs, others to the materialized view. Simply put, the
database engine needs much more information to perform a fast refresh.
To solve the problems related to the materialized view logs, you must add some options to the
CREATE
MATERIALIZED VIEW LOG
statements:
For the “mv log does not have all necessary columns” problem, you have to specify that every
•
column referenced in the materialized view be stored in the materialized view log.
•
For the “mv log must have new values” problem, you have to add the
INCLUDING NEW VALUES
clause. With this option, materialized view logs store both old and new values (by default only
old ones are stored) when an update is performed (in other words, two rows are written in the
materialized view log).
•
For the “mv log does not have sequence” problem, it's necessary to add the
SEQUENCE
clause.
With this option, a sequential number is associated to each row stored in the materialized
view log.
The following are the redefined materialized view logs:
SQL> CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID, SEQUENCE
2 (cust_id, prod_id, quantity_sold, amount_sold) INCLUDING NEW VALUES;
SQL> CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID, SEQUENCE
2 (cust_id, country_id) INCLUDING NEW VALUES;
SQL> CREATE MATERIALIZED VIEW LOG ON products WITH ROWID, SEQUENCE
2 (prod_id, prod_category) INCLUDING NEW VALUES;
tIMeStaMp VS. COMMIt SCN-BaSeD MaterIaLIZeD VIeW LOGS
From version 11.2 onward, there are two types of materialized view logs: those based on timestamps, and those
based on commit SCn numbers. Because timestamp-based materialized view logs are the only ones that exist
through version 11.1.0.7, they're used by default in later versions as well. To use the new type, the
COMMIT SCN
clause must be specified when creating a new materialized view log. The following SQL statement shows
an example:
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID,
COMMIT SCN
, SEQUENCE (cust_id, prod_id,
quantity_sold, amount_sold) INCLUDING NEW VALUES
