Best Practices for Using Materialized Views in HP Neoview Release 2.4 (R2.4, R2.4 SP1, R2.5)

19
Where <table-name> is the name of the MV source table, since the source table and the IUD log
share the sane name. This should be done for every MV source table.
Please refer to the Neoview SQL Reference Manual for a more comprehensive description of the
MAINTAIN utility.
Availability
Locking
A materialized view is self-maintainable if refreshing it requires that each base table T access data
either in T or its log table, but not both. Typically, a self-maintainable MV is an aggregate on a single
table. Join-based MVs (MJV or MAJV) are typically not self-maintainable, unless all but one of their
base tables appear in the IGNORE CHANGES clause. Refreshing MVs that are not self-maintainable
requires locking the base table or tables in shared mode in order to synchronize between the base
table and the log during the REFRESH operation.
Users can employ multiple levels of aggregation to maintain non-self-maintainable MVs in this
environment without locking critical tables. For example:
A first-level self-maintainable MAV using sales_by_day_prod_store
A second-level MAJV using sales_by_day_prod_store
The sales_by_day_part_store MAV is self-maintainable, and therefore refreshing it does not require
locking the fact table. The sales_by_category_month MAJV based on it and on two dimension tables
is not self-maintainable, but refreshing it does not require locking a critical table.
Availability issues for Materialized Views
There are several approaches that can help increase the availability of MVs. Users should choose
among them based on the application and on the nature of the MV. An MV that is refreshed in
multiple transactions is highly available; however, this solution is applicable only to single-delta
MAVs.
Another strategy applicable to small and medium size MVs is to duplicate the MV and redirect
queries to the MV replica while the original MV is being refreshed. Queries must use either late
binding or synonyms to be redirectable.
CREATE MATERIALIZED VIEW sales_by_day_par
t_store
REFRESH ON REQUEST
INITIALIZE ON REFRESH
AS SELECT timekey, partkey, storekey, SUM(price) tot_price
FROM sales
GROUP BY timekey, partkey, storekey;
CREATE MATERIALIZED VIEW sales_by_category_month
REFRESH ON REQUEST
INITIALIZE ON REFRESH
AS SELECT part.category, time.month, SUM(tot_price)
FROM sales_by_day_part_store, part, time
WHERE sales_by_day_part_store.timekey = time.timekey
AND sales_by_day_part_store.partkey = part.partkey
GROUP BY part.category, time.month;