Materialized view tuning mechanism and usability enhancement

Abstract

The materialized view technology has been widely adopted in the data warehouse systems to improve query performance. Two key underlying techniques are materialized view incremental refresh and query rewrite. However, due to the potential complexity of the materialized view defining query and dependency on the materialized view change log, not all materialized views are incrementally refreshable or generally query rewritable. Many restrictions need to be applied and addressed which make the materialized view not easy to use. This paper presents Oracle’s tuning mechanism/tool to facilitate the materialized view creation by automatically fixing and/or decomposing the defining query and addressing the change log requirements. The resulting materialized view implemented by a set of recommendation statements achieves the goals of incremental maintenance and broad query rewrite.