Materialized Views

Materializing a view is a commonly used database operation in Business Intelligence applications. After materialization, the view tuples are stored as in regular table and you can query the materialized view (MV) directly. If there are queries with complex joins that are executed often, materializing the result of the query and accessing the materialized result can provide faster access to the data since the joins are computed once and accessed many times. The speed difference can be critical in applications where the query rate is high and the queries are complex. For example, complex join over many tables with large volume of data. MVs are useful in applications such as data warehousing, replication servers, billing, recording systems and mobile systems.

MVs can either be refreshed with the incremental changes to the base tables or they can be recomputed. The MVs that are refreshed are referred to as incremental refresh MVs as they are maintained with the incremental changes that happen to the base tables. The incremental refresh can be done using the ON STATEMENT clause where the MV maintenance happens along with the base table change.

SQL/MX Release 3.5 supports an MV definition having a join query. An MV defined as a join query is known as Materialized Join View. If you define an MV having an aggregate query, or having both aggregate and join queries, SQL/MX returns an error.

SHOWDDL, mxtool operations, SQL/MXHealthCheck tool, and UPDATE STATISTICS are enhanced to support MVs.

The sections detail the changes to commands and utilities for MV support.