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

11
MAV on an Explicit Join (MAJV):
• ON STATEMENT MAJVs are not supported.
• Only inner equi-joins are supported. Outer joins and cross joins are not supported.
• Must be a single block SELECT-FROM-WHERE query (UNION and ORDER BY are not allowed).
• The REFRESH of MAJV with a MIN or MAX aggregate might become non-incremental. A MAJV
with a MIN/MAX aggregate is guaranteed to be incremental only if all its underlying tables have
INSERTLOG attribute (see INSERTLOG) and/or if they appear in the IGNORE CHANGES clause.
• Same restrictions as for MAV on MJV or a single table.
A user can define a MAV on a join indirectly by defining a MAV on an MJV. The disadvantage of
this solution is that the MJV will be materialized itself and will require its own disk space and extra
update time. Therefore, a MAV on an explicit join is usually a better solution unless the MJV is needed
for other purposes.
General comments on incremental MVs:
• Base tables can only be used once (directly or indirectly through a regular view or MV).
• Cannot include non-repeatable expressions (e.g. CURRENT_TIME).
• Single table predicates other than equal are allowed (e.g. T1.A < 5, T1.A < T1.B)
Mandatory columns
When the MV select list does not include columns that are required for the MV’s incremental
maintenance (mandatory columns), the missing columns are automatically added by the system. The
alias of the added columns will be SYS_name_num, where name is the aggregate function name for
MAV and column name for MJV and num is added to guarantee column name uniqueness. The
added columns are not displayed by the SELECT * command. However, they are displayed if
specified explicitly in the select column list. The added columns are displayed by INVOKE. The added
columns can be used by the user when creating the MV such as to cluster or to partition the MV.
For MJV the mandatory columns include the clustering index columns of all the underlying tables.
For MAV and MAJV, the mandatory columns include:
• All group-by columns.
• COUNT(*)
• For each SUM(expr), MIN(expr) and MAX(expr) – COUNT(expr).
• For each STDDEV(expr) and VARIANCE(expr) - SUM(expr), SUM(expr*expr) and COUNT(expr).
• For each STDDEV(expr, weight) - SUM(weight), SUM(expr*weight) and SUM(expr*expr*weight).
CREATE
MATERIALIZED VIEW Cdr_by_phone_month
REFRESH ON REQUEST
INITIALIZE ON REFRESH
AS SELECT area_code, phone_no, mnth, yr,
SUM(call_cost) cost,
SUM(call_duration) duration
FROM
(SELECT area_code,
phone_no,
extract(month from disconnect_time) mnth,
extract(year from disconnect_time) yr,
call_cost,
call_duration
FROM Call_Detail) as cdr
GROUP BY area_code, phone_no, mnth, yr;