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

2
Introduction
A materialized view is a pre-computed data summary that transparently allows users to query large
amounts of data much more quickly than they could access the original data. This summary is stored
in a table so that users can query it directly, create indexes on it for performance, use partitioning to
promote scalability, and so on. The materialized view is like a cache — a copy of the data that can
be accessed quickly. This speed difference can be critical in applications where the query rate is high
and the materialized views are complex, for example, aggregate queries over large volumes of data.
Materialized views are useful in applications such as data warehousing, billing, recording systems
and mobile systems. For example, they provide a framework in which to collect information from
several databases into a data warehouse. Queries on the warehouse can then use the materialized
views without accessing the base tables.
As the base tables change, the materialized view must be updated, or refreshed, as well. One way is
to delete the existing data in the MV table, then compute the view query from the base tables and re-
insert the result into the MV table. This REFRESH method is called recomputing. But there is a better
way: MVs can be incrementally refreshed instead of recomputed. That is, the changes that need to be
applied to the MV to bring it up to date can be computed based on the changes applied to the base
tables since the MV was last updated. Typically, incremental MV REFRESH time is significantly shorter
than recompute time.
One of the most common uses of materialized views is summary tables, materialized aggregate
views. Data warehouses store large volumes of transaction history (fact tables) which are almost
always queried using dimension grouping. For example, in a retail database, sales transaction history
can be queried by region, by item-category and month, or by year and store-id. Performing these
queries from sales data in a reasonable time is almost impossible. To overcome the performance
problem, users can define summary tables on the fact table and use these summary tables to perform
faster queries.
The HP implementation of materialized views in Neoview SQL has these advantages over competing
products:
• Supporting indexes
Indexes are created by the system. In most, if not all, of the competing products, the user must
create supporting indexes.
• Dependent aggregate functions
These are automatically created by the system. For example, if a MV uses SUM(a), it must also
include COUNT(a), and it is added automatically. In competing products, these functions usually
must be provided by the user as part of the MV definition.
• The ability to define MVs on top of other MVs