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

6
• Materialized Aggregate View (MAV): A materialized aggregate view holds the result of a
GROUP BY query with any of these aggregate functions: SUM, AVG, VARIANCE, STDDEV, MIN,
MAX, COUNT(*) and COUNT(x). The aggregate function can include an expression on the
columns, such as SUM(a+b). The DISTINCT function is not supported. ON STATEMENT MAVs are
not supported. Aggregate MVs can be of two types:
• MAV on Single Table - the MAV is defined on a single base table or MV.
• MAV on an Explicit Join (MAJV) - the MAV is defined on an inner equi-join of several
base tables, MVs, or both. For example:
Working with Materialized Views
When planning materialized views, it is important to understand the phases in the lifecycle of an MV,
and their performance implications: creation and initialization, updating base tables, refreshing, and
maintenance.
Creation and Initialization
Similar to an index, a materialized view can be created as empty in an un-initialized state or
populated and initialized upon creation. When created in un-initialized state, to be used, MVs must
be initialized, similar to populating an index. Initialization is performed by running a REFRESH
operation. This inserts the data that is the result of the view query into the MV. Initializing an MV over
one or more large tables can be time consuming. The creation of an MV sometimes triggers the
creation of secondary indexes, which are needed for performance of the incremental REFRESH
operation. Initializing the MV includes populating those indexes as well.
Logging
Maintaining ON REQUEST incremental MVs requires logging the changes performed on the base
tables. A table that is used by ON REQUEST MVs maintains a log table (or a log) associated with it.
Automatic logging is added to the execution plan of every statement that performs an INSERT,
UPDATE or DELETE operation on the base table, and may add a performance overhead to the
original transaction. However, since automatic logging is performed by the database engine, it can
take advantage of speed optimization. Refer to the Automatic Log Maintenance section later in this
document for more information on logging. It is highly recommended to create the MV after the base
tables have already been loaded with the initial data. Creating the MV on empty base tables means
that all the data from the initial load process will be logged, which may be very time consuming.
CREATE MATERIALIZED VIEW sales_store
REFRESH ON REQUEST
INITIALIZE ON REFRESH
AS SELECT price, partkey, timekey, store.name
FROM sales, store
WHERE sales.storekey = store.storekey;
CREATE MATERIALIZED VIEW sales_by_day_catagory
REFRESH ON REQUEST
INITIALIZE ON REFRESH
AS SELECT part.category, time.day, SUM(price) tot_price
FROM sales, part, time
WHERE sales.timekey = time.timekey
AND sales.partkey = part.partkey
GROUP BY part.category, time.day;