Optimizing refresh of a set of materialized views

Abstract

In many data warehousing environments, it is common to have materialized views (MVs) at different levels of aggregation of one or more dimensions. The extreme case of this is relational OLAP environments, where, for performance reasons, nearly all levels of aggregation across all dimensions may be computed and stored in MVs. Furthermore, base tables and MVs are usually partitioned for ease and speed of maintenance. In these scenarios, updates to the base table are done using Bulk or Partition operations like add, exchange, truncate and drop partition. If changes to base tables can be tracked at the partition level, join dependencies. functional dependencies and query rewrite can be used to optimize refresh of an individual MV. The refresh optimizer, in the presence of partitioned tables and MVs, may recognize dependencies between base table and the MV partitions leading to the generation of very efficient refresh expressions. Additionally, in the presence of multiple MVs, the refresh subsytem can come up with an optimal refresh schedule such that MVs can be refreshed using query rewrite against previously refreshed MVs. This makes the database server more manageable and user friendly since a single function call can optimally refresh all the MVs in the system.