A materialized view (sometimes called a sorted, projected,
and materialized view or SPM view) is a view whose columns
have been sorted, projected, and materialized (that is, stored physically
in a unique table).
In each materialized view, the system automatically adds a column
that specifies, for each record in the view, the block number of the
corresponding record in the base table. The system also automatically
creates a zone map for each materialized view.
A materialized view improves query performance in the following
ways:
- It reduces the amount of data that must be transferred from the
disk during scans.
- Because its data is sorted, the resulting zone map for ORDER BY
columns is more efficient than it otherwise would be.
- Because its data is sorted and it has a zone map, a query that
targets only a few records can retrieve the block locations of the
records in the base table more quickly than by other means.
You can use materialized views to improve performance without
rewriting your applications. The query planner/optimizer automatically
uses a materialized view when doing so is faster than using the corresponding
base table.
IBM PureData System for Analytics, Version 7.1