Page 176 - DCAP603_DATAWARE_HOUSING_AND_DATAMINING

Data Warehousing and Data Mining

notes If you specify REFRESH FAST, Oracle performs further verification of the query definition to
ensure that fast refresh can be performed if any of the detail tables change. These additional
checks are:
1. A materialized view log must be present for each detail table and the ROWID column must
be present in each materialized view log.
2. The rowids of all the detail tables must appear in the SELECT list of the materialized view
query definition.

3. If there are no outer joins, you may have arbitrary selections and joins in the WHERE
clause. However, if there are outer joins, the WHERE clause cannot have any selections.
Further, if there are outer joins, all the joins must be connected by ANDs and must use the
equality (=) operator.
4. If there are outer joins, unique constraints must exist on the join columns of the inner table.
For example, if you are joining the fact table and a dimension table and the join is an outer
join with the fact table being the outer table, there must exist unique constraints on the join
columns of the dimension table.
If some of these restrictions are not met, you can create the materialized view as REFRESH
FORCE to take advantage of fast refresh when it is possible. If one of the tables did not meet all
of the criteria, but the other tables did, the materialized view would still be fast refreshable with
respect to the other tables for which all the criteria are met.

Materialized Join Views FROM Clause Considerations

If the materialized view contains only joins, the ROWID columns for each table (and each instance
of a table that occurs multiple times in the FROM list) must be present in the SELECT list of the
materialized view.
If the materialized view has remote tables in the FROM clause, all tables in the FROM clause must
be located on that same site. Further, ON COMMIT refresh is not supported for materialized
view with remote tables. Materialized view logs must be present on the remote site for each detail
table of the materialized view and ROWID columns must be present in the SELECT list of the
materialized view.
To improve refresh performance, you should create indexes on the materialized view’s columns
that store the rowids of the fact table.

Example: Materialized View Containing Only Joins
CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON times WITH ROWID;
CREATE MATERIALIZED VIEW LOG ON customers WITH ROWID;
CREATE MATERIALIZED VIEW detail_sales_mv

PARALLEL BUILD IMMEDIATE
REFRESH FAST AS
SELECT s.rowid “sales_rid”, t.rowid “times_rid”, c.rowid “customers_rid”,
c.cust_id, c.cust_last_name, s.amount_sold, s.quantity_sold, s.time_id

FROM sales s, times t, customers c
WHERE s.cust_id = c.cust_id(+) AND s.time_id = t.time_id(+);

170 LoveLy professionaL university